Need Hiarchical Parameter List for Previous Number of Periods

[mknsawdust]

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]

[mknsawdust]

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 &"'

[Island1]

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