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.