Couting rows in hierarchical dataset

[haroldholt]

Hi, I am having a problem with the counting of rows in Reporting Services hierarchical result set when using a cube as a data source.

I am trying to get my multi-valued report parameter to only display 'All' when I use the '(Select All)' option.

This is the expression (which works in all my SQL-based reports):

=iif(Parameters!ReportingPeriodReportingPeriod.Label.Length =
Count(Fields!ParameterValue.Value, "ReportingPeriodReportingPeriod"),
"All", Join(Parameters!ReportingPeriodReportingPeriod.Label,", "))

Where 'ReportingPeriodReportingPeriod' is a report parameter and also the name of the dataset containing all possible values
for the report parameter.

I use an MDX expression like this to give me a set of lookup values for a reporting period (year/quarter/month):

WITH MEMBER [Measures].[ParameterCaption] AS '[Reporting Period].[Reporting Period].CURRENTMEMBER.MEMBER_CAPTION'
MEMBER [Measures].[ParameterValue] AS '[Reporting Period].[Reporting Period].CURRENTMEMBER.UNIQUENAME'
MEMBER [Measures].[ParameterLevel] AS'[Reporting Period].[Reporting Period].CURRENTMEMBER.LEVEL.ORDINAL'
SELECT {
[Measures].[ParameterCaption],
[Measures].[ParameterValue],
[Measures].[ParameterLevel]
}
ON COLUMNS,
Descendants([Reporting Period].[Reporting Period].[Year],[Reporting Period].[Reporting Period].[Month],SELF_AND_BEFORE)
ON ROWS FROM [Conditional Bail]

which returns 1890 rows in a 3 level hierarchy, year - quarter - month.

I then count the number of rows in the result set using:

=CountRows("ReportingPeriodReportingPeriod")

or

=Count(Fields!ParameterValue.Value, "ReportingPeriodReportingPeriod")

but I only get I only get 1333 rows - which just happens to be the number of leaf 'month' rows in the
'ReportingPeriodReportingPeriod' dataset, i.e. what I would expect if the result set was somehow 'flattened'.

I know it must be something obvious so what am I missing here ?

thanks

 

[haroldholt]

Solved it - sort of

Created another measure

WITH MEMBER
[Measures].[RowCount]
AS
'Descendants([My Dimension].[All]).Count'

Then used this in the report

First(Fields!RowCount.Value, "ReportingPeriodReportingPeriod")

to return the actual number of rows in the result set...

There has to be a more elegant way to do it than this!