Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Parameterised MDX (SSAS2008) from SSRS2008 Expand / Collapse
Author
Message
Posted Tuesday, May 3, 2011 7:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 2, 2013 1:49 AM
Points: 116, Visits: 289
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
Posted Wednesday, October 31, 2012 3:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 5:28 AM
Points: 2, Visits: 72
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.

Regards,
Nikita C.
Post #1379209
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse