GetPeriodSet function missing?

Topics: User Forum
Jan 10, 2011 at 10:00 AM

Hi!

I had SSAS 2005 and I was using ASSP assembly functions in my RS reports. After moving all Cubes and RS to a new server (SQL Server 2008 R2) I suddenly started to get "'[ASSP].[GetPeriodSet]' function does not exist." errors. I  haven't followed the changes from older versions of ASSP to current active version. Is there a reason to drop this function or is this some kind of bug and it's not really missing?

ASSP version 1.3.5
AS part of SQL Server 2008 R2

 

Coordinator
Jan 10, 2011 at 3:45 PM

Hmm. I'm not aware of that function. Did you possibly customize the ASSP source code and add it for your own private build? (If so, post your source code for that function and we'll consider adding it.)

If you go to the Downloads tab, you can download prior releases. Feel free to install prior releases and let me know if you find that function in a prior release. (We certainly didn't intend to remove it in the latest release, but I can't find that function in the source code at all.)

Jan 10, 2011 at 9:57 PM

You're right ... I asked around today and somebody from our team has made couple of customizations, one being GetPeriodSet. We use this function a lot. Below is a class definition we added to your project. The problem (probably) is that our GetPeriodSet works only when your date format is like ours :P But of course, you could modify it a bit and add a globally acknowledged date format option to let people use whichever date format they like. Here you go...

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.AnalysisServices.AdomdServer;


namespace ASStoredProcs
{
    // Sample ASSP.GetPeriodSet("[Time].[Date]", "01.01.2006", "31.12.2006")
    // // return set:{[Time].[Date].&[2006-01-01T00:00:00]:[Time].[Date].&[2006-12-31T00:00:00]}
    public class PeriodSet
    {
        public static Set GetPeriodSet(string sLevel, string date1, string date2)
        {
            string sSet;

            sSet = "{" + sLevel +  DateKeyStr(date1) + ":" + sLevel + DateKeyStr(date2) + "}";
            return new Expression(sSet.ToString()).CalculateMdxObject(null).ToSet();
        }
        private static string DateKeyStr(string date)
        {
            string res;
            string datefmt = date.Replace('.','-').Replace('/','-');
            string yr = datefmt.Split('-')[2];
            if( yr.Length == 1 )
                yr = "200" + yr;
            else if( yr.Length == 2 )
                yr = "20" + yr;
            else if (yr.Length > 4)
                yr = yr.Substring(0,4);
            string mon = datefmt.Split('-')[1].PadLeft(2, '0');
            string day = datefmt.Split('-')[0].PadLeft(2, '0');
            res = ".&[" + yr + "-" + mon + "-" + day + "T00:00:00]";
            return res;
        }
    }
}

Coordinator
Jan 12, 2011 at 1:23 PM

Glad you found the source code you'd customized for that function.

You're right that that particular function isn't nearly generic enough to be used on many people's cubes, so probably we won't include it in ASSP. (If other people disagree and think we should take this idea and add it to ASSP post here.)

Also, if you were to write those reports again, you could probably do it with straight MDX instead of a sproc. Something like the following would work:

StrToMember("[Time].[Date].&[" + VBA!format(VBA!dateadd("d",-1,VBA![date]()),"yyyy-MM-ddT00:00:00") + "]")

Or rather add two dates with a colon between and use StrToSet instead.