Prologika Forums

I have a report (built in reporting services 2005) which accesses a cube built in analysis services 2005. This cube has one measure whose aggregation is “DistinctCount”. I also have a time dimension in this cube which has 3 levels – Year, Quarter and Month. In the report, I want to first display the measure at the year level but I want to enable drill down so that users can drill down to the Quarter level or Month level to see the measure values for quarter or month.

I have a matrix layout for the report. In the dataset which is the source for this report, I retrieve the values for all measures at the month level using MDX query. In the report layout, I defined three groups à one for year, one for quarter and one for month. In the detail, there is this measure (let’s say DistCntMeasure) which uses “DistinctCount” aggregation in the cube and there is another measure (let’s say SumMeasure) which uses “sum” aggregation in the cube. I initially want to show year level data to the user and then he can drill down to quarter or month level. Therefore, in the detail line for the SumMeasure, I define it as =SUM(Fields!SumMeasure.Value).

This measure shows correct values for Year and when the user drills down, it shows correct values for Quarter and Month. I want to implement similar functionality for DistCntMeasure but I do not know how to define the aggregation for it in the report. If I define it as =SUM(Fields!DistCntMeasure.Value) then it shows the correct values at the month level but at the quarter level it simply adds the values for the months that make up the quarter and it is not the same as the distinct count  of the measure at the quarter level. The same logic holds true for the year level as well.

Ideally, I would like the report to go to the cube and query the data at the quarter level or at the year level and display it.

As a workaround, therefore, I had to create separate reports for month level, quarter level and year level so that the MDX query fetches the year level or quarter level or month level from the cubes and the report simply displays these values. 

I would like to know if there is a way to display DistinctCount measures in the report with drilldown capability and show correct numbers at all levels.

 > I want to implement similar functionality for DistCntMeasure but I do not know how to define the aggregation for it in the report.

Assuming you use the SSAS 2005 data provider, try the Aggregate() function, i.e; =Aggregate(Fields!DistCntMeasure.Value), to request the server aggregates for the measure.

More details about Aggregate can be found in this blog.

When I use Aggregate function, it does not display anything in the report. If I simply use Fields!DistCntMeasure.Value and implement drilldown, it gives me the value for the first month of the year when I am at the year level, value for first month of the quarter when I am at the quarter level and correct values when I am at the month level.

When I use AGGREGATE(Fields!DistCntMeasure), I get an empty value.  

It should be Aggregate(Fields!DistCntMeasure.Value), right

Yes, I was using Aggregate(Fields!DistCntMeasure.Value) in the report. I had a typo while typing the response. Sorry. Without the value being there, the report gives a runtime error.

 The Value expression for the textbox ‘textbox17’ uses the function Aggregate with an invalid expression parameter. The expression parameter must be a simple field reference.

Anyway, so basically I still have a problem with the aggregate showing me a blank/empty value. I am not using it in a group header or footer as a subtotal but I have it on the detail line. I don't know if that makes any difference.

See attached report. It uses the Adventure Works cube.

Did you get a solution, I've got same problem (last zip file doen't help)

AAAAARRRGGG ... got it. Used the wizard to create the report, but if you delete the row groupings and manually reinsert, it works fine. Crazy!