dcsimg
Results 1 to 3 of 3

Thread: Need Hiarchical Parameter List for Previous Number of Periods

  1. #1
    Join Date
    Jan 2009
    Posts
    5

    Need Hiarchical Parameter List for Previous Number of Periods

    I have a dataset that is accessing a cube to populate a SSRS Parameter List of possible Calendar Periods. Although I'm not sure this is the best way to do it, it is working this way.

    The only issue I have with my sample parameter list is I need it to only display the periods for say the past year or so, based on the most recent period. This would eliminate a lot of extra periods that arent needed. I'm not sure how to incorporate this logic into my existing MDX query.

    Here is a small sample of my current output.

    FY 2009
    Q1 FY 2009
    Jan FY 2009
    Wk 1 FY 2009
    Mon, Dec 29, 2008
    Tue, Dec 30, 2008
    Wed, Dec 31, 2008
    Thu, Jan 01, 2009
    Fri, Jan 02, 2009
    Sat, Jan 03, 2009
    Sun, Jan 04, 2009
    Wk 2 FY 2009
    Etc, etc.....

    Here is a sample of my working MDX query. My only request is to modify it to load only the periods from my calendar hiearchy for the previous year or so. It would be possible for me to pass the year in as a filter if that is possible. I have another data set available that is returning the current Period, but not sure how to incorporate that dataset parm or period into this MDX. Any help would be appreciated.

    WITH

    /* FIND UNIQUE CALENDAR MEMBER NAMES */
    MEMBER [measures].[uniquename] AS
    '[Calendar].[Fiscal Calendar].currentmember.uniquename
    '

    /*GIVE EACH LEVEL OF THE HEARCHY A UNIQUE NON-INDENTED NAME USED FOR PARAMETER VALUE */
    MEMBER [measures].[caption] AS
    'iif([Calendar].[Fiscal Calendar].currentmember.level.ordinal = 1,
    [Calendar].[Fiscal Calendar].currentmember.name,
    [Calendar].[Fiscal Calendar].currentmember.name)'


    /*SELECT ALL THE FIELDS NEEDED ON ROWS */
    SELECT {[measures].[uniquename], [measures].[caption]} on columns,
    HIERARCHIZE({[Calendar].[Fiscal Calendar].[Fiscal Year Name].Members,
    [Calendar].[Fiscal Calendar].[Fiscal Quarter Name].members,
    [Calendar].[Fiscal Calendar].[Fiscal Month Name].members,
    [Calendar].[Fiscal Calendar].[Fiscal Week Name].members,
    [Calendar].[Fiscal Calendar].[Fiscal Calendar Literal Date].members
    })
    ON rows

    FROM [MyDataBase]

  2. #2
    Join Date
    Jan 2009
    Posts
    5
    Thought I would update my post so other may learn from it.

    I ended up creating 2 other datasets that contained the previous period and the current period then using the values from those data sets as parameters for filtering what I needed.

    Here is the "SELECT" section of code I replaced.

    SELECT {[measures].[uniquename], [measures].[indented caption], [measures].[caption]} on columns,
    HIERARCHIZE({
    {Filter (
    [Calendar].[Fiscal Calendar].[Fiscal Year Name].members,
    [Calendar].[Fiscal Calendar].currentmember.membervalue
    >= '"& parameters!GetPrevYearPeriod.Value &"'
    and
    [Calendar].[Fiscal Calendar].currentmember.membervalue
    <= '"& parameters!GetCurrYearPeriod.Value &"'
    )}
    ,
    {Filter (
    [Calendar].[Fiscal Calendar].[Fiscal Quarter Name].members,
    [Calendar].[Fiscal Calendar].parent.membervalue
    >= '"& parameters!GetPrevYearPeriod.Value &"'
    and
    [Calendar].[Fiscal Calendar].parent.membervalue
    <= '"& parameters!GetCurrYearPeriod.Value &"'
    )}
    ,
    {Filter (
    [Calendar].[Fiscal Calendar].[Fiscal Month Name].members,
    [Calendar].[Fiscal Calendar].parent.parent.membervalue
    >= '"& parameters!GetPrevYearPeriod.Value &"'
    and
    [Calendar].[Fiscal Calendar].parent.parent.membervalue
    <= '"& parameters!GetCurrYearPeriod.Value &"'
    )}
    ,
    {Filter (
    [Calendar].[Fiscal Calendar].[Fiscal Week Name].members,
    [Calendar].[Fiscal Calendar].parent.parent.parent.membervalue
    >= '"& parameters!GetPrevYearPeriod.Value &"'
    and
    [Calendar].[Fiscal Calendar].parent.parent.parent.membervalue
    <= '"& parameters!GetCurrYearPeriod.Value &"'
    )}
    ,
    {Filter (
    [Calendar].[Fiscal Calendar].[Fiscal Calendar Literal Date].members,
    [Calendar].[Fiscal Calendar].parent.parent.parent.parent.membervalue
    >= '"& parameters!GetPrevYearPeriod.Value &"'
    and
    [Calendar].[Fiscal Calendar].parent.parent.parent.parent.membervalue
    <= '"& parameters!GetCurrYearPeriod.Value &"'

  3. #3
    Join Date
    Jun 2004
    Location
    Atlanta and Manhattan
    Posts
    607

    Other Options - and Intelligent Defaults

    You have many options, as I show throughout recent articles in my MSSQL Server Reporting Services series here at Database Journal. With regard to defaults, you can always perform this through the use of the dataset filter, or you can install intelligent defaults as I demonstrate in Support Parameterization from Analysis Services Parameter Defaults.

    HTH. Let us know if you have further questions / need additional assistance.

    Bill

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •