Parameterised MDX (SSAS2008) from SSRS2008

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Reporting Services » Reporting Services » Parameterised MDX (SSAS2008) from SSRS2008

Parameterised MDX (SSAS2008) from SSRS2008

Rate Topic

Display Mode

Topic Options

Author

Message

jm99

Posted Tuesday, May 03, 2011 7:39 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 3:58 PM
Points: 114, Visits: 276

I'm trying to run a MDX query that contains the topcount() function and I wish to parameterised the count parameter. For example:

select
{ Measures.Meas1, Measures.Meas2 } on columns
topcount( nonempty({ dimension set}), 500, Measures.Meas2 ) on rows
from
cube

where the hardcoded 500 is replaced by a SSRS parameter.

(I know this could be done in the report by using the Top functionms on the widgets but I do not want the vaolume transfer of the excluded rows from SSAS to SSRS)

So far I've done:
1) Created a SSRS UI parameter @TopN
2) Added the parameter to the dataset's Parameter list as @TopN
3) Referenced @TopN in the topcount() MDX called

==> Unable to parse MDX error: Parameter @TopN not defined

4) Attempted to use the parameter dialog from the MDX builder to define @TopN but this insists that the parameter is related to a dimension.

Any ideas, apart from:
a) Pass all of the MDX result set and let SSRS sort it out or,
b) Create dynamic MDX statement using the VBA f(x) editor

Help appreciated.

Post #1102342

nikitac

Posted Wednesday, October 31, 2012 3:15 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, March 28, 2013 4:06 AM
Points: 2, Visits: 33

Hello There,

Please follow below steps for the query to work. Please follow all the below steps in Query designer of SSRS.

Step 1 : Taking into consideration your sample code, You just need to add a variable @TopXCount instead of hardcoding it as '500'.

select
{ Measures.Meas1, Measures.Meas2 } on columns
topcount( nonempty({ dimension set}), @TopXCount, Measures.Meas2 ) on rows
from
cube

Step 2 : Goto 'Query Parameter' in query designer and Add below details
Parameter : TopXCount
Dimension : leave it blank
Hierarchy : Leave it blank
Multiple Values : leave it blank
Default : Say 5

Step 5 execute the query and check whether it is running for the default value.

If its successfully running then u r succeeded. Smile

Regards,
Nikita C.

Post #1379209

« Prev Topic | Next Topic »

Permissions