May 3, 2011 at 7:39 am
I'm trying to run a MDX query that contains the topcount() function and I wish to parameterised the count parameter. For example:
[font="Courier New"]select
{ Measures.Meas1, Measures.Meas2 } on columns
topcount( nonempty({ dimension set}), 500, Measures.Meas2 ) on rows
from
cube[/font]
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.
October 31, 2012 at 3:15 am
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.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply