MDX Query Filter on the parameter in reporting services

Question

MDX Query Filter on the parameter in reporting services

Sanju-518612

SSC-Addicted

Points: 432
More actions
November 26, 2007 at 11:19 am

#177001

I have Analysis services cube as my source for reporting. In my reporting services, I have date parameter where all the values are populated from date dimension. I dont want all the date values in the parameter list. In this regard, I would like to know how I can I use filter in MDX query only to get last 60 dates from the whole list in the dimension table.
eg : If I have dates's from Jan 1 2007 to Nov 26 2007..I only want from Sep 26 2007 to Nov 26 2007 in my parameter list in reporting services. Please help me in writing the MDX Query.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sam Peascod SSCrazy Points: 2497 More actions · Answer 1

Hi Sanju,

You might be able to use the LastPeriods() function to get this information out - or something close to it. Try something along the lines of LastPeriods(60, STRTOMEMBER(@DateParam)), with your @DateParam defaulting to an MDX string for today, calculated using VB. This may not be the same date parameter as the one you use to answer the data query. There could also be a better way to do this.

You might also like to consider a date picker for your parameter rather than a list of string values returned by MDX. There are a couple of ways to do this:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2207630&SiteID=1

http://prologika.com/CS/forums/t/222.aspx

http://www.sqlservercentral.com/Forums/Topic412581-150-1.aspx#bm418177

HTH, and apologies if it's a bit vague.

Sam

happycat59 One Orange Chip Points: 29389 More actions · Answer 2

You could use the tail function to return the dates you want.

e.g

WITH

MEMBER [Measures].[Name] as [Date].[Date].CurrentMember.Name

MEMBER [Measures].[UniqueName] as [Date].[Date].CurrentMember.UniqueName

SELECT {[Measures].[name], [Measures].[UniqueName] } ON COLUMNS,

NON EMPTY { Tail ([Date].[Date].MEMBERS, 60 ) } ON ROWS

FROM [Adventure Works]

This is based on the code that Reporting Services generates for a parameter. The TAIL (xxx, 60) just returns the last 60 members from the date attribute of the date dimension.

Dave Balsillie SSCertifiable Points: 5589 More actions · Answer 3

happycat59 got it eactly right, but I thought I'd add a couple of quick recommendations:

- Consider sorting the list as well, so that the most recent date appears at the top.

- I standandize my parameter queries and rename them from the RS generated values (i.e. I prefix the datasets with "parm"). That stops RS from messing with them.

- Place the parameter calculations in the cube centrally. This'll make it easier to support, and makes your queries easier to read.

Something like:

With

Set [theSet] as Order( Tail( [Date].[Date].Members, 60), [Date].[Date].CurrentMember.UniqueName, Desc)

SELECT

{ [ParameterCaption]

, [ParameterValue]

} ON COLUMNS

, Non Empty [theSet]

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME on ROWS

from [Adventure Works]

and add the following to your calculation script (at the bottom)

CREATE MEMBER CURRENTCUBE.[MEASURES].[ParameterValue]

AS Axis(1).Item(0).Item(0).Dimension.CurrentMember.UNIQUE_NAME,

VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[MEASURES].[ParameterCaption]

AS String( Axis(1).Item(0).Item(0).Dimension.CurrentMember.Level.Ordinal * 1 , ' ' ) + Axis(1).Item(0).Item(0).Dimension.CurrentMember.Member_Caption,

VISIBLE = 1;

chicovb6 Valued Member Points: 55 More actions · Answer 4

chicovb6

Valued Member

Points: 55

May 26, 2008 at 2:06 pm

#820263

:):):)

Ian C0ckcroft Hall of Fame Points: 3373 More actions · Answer 5

HI Dave, hope you still around.

what do you mean

'I standandize my parameter queries and rename them from the RS generated values (i.e. I prefix the datasets with "parm"). That stops RS from messing with them.'

I am having an issue with RS changing MDX query willie nillie. Its driving me crazy.

Thread is :

http://www.sqlservercentral.com/Forums/Topic1086036-1633-1.aspx

any help will be greatly appreciated

thanks

Ian

Ian Cockcroft
MCITP BI Specialist

Dave Balsillie SSCertifiable Points: 5589 More actions · Answer 6

Seems you got to it shortly after your post.

The SuppressAutoUpdate flag was added to a service pack for 2005 I believe (it's definately in 2008). As of yet, I can't find any official Microsoft documentation, nor do they seem to have exposed this property via the dialogues.

Hey Microsoft, how hard can it be to add a check box to the query dialogue? :crazy:

Ian C0ckcroft Hall of Fame Points: 3373 More actions · Answer 7

haha, yes, would be nice

thanks Dave

Ian Cockcroft
MCITP BI Specialist