RS Aggregate and Manual MDX

In a recent post, I said RS Report Designer is smarter than I thought. And it is. But it is also really, really weird when you try customizing your MDX. (Not that I ever do that.)

This might be a bug, but it seems to happen only when you customize the MDX. Here's a scenario:

1) Use the wizard to create a table report with Country, State-Region, and City levels. Use Sales Amount (additive) and Average Unit Price (non-additve) as measures, and include subtotals.
 [At this stage, the automatic MDX includes just the City level]

2) Change the Sum function for Average Unit Price (and Sales Amount too, if you feel like it), to Aggregate. [At this stage, the MDX changes to include the Country and State-Region attributes.]

3) Delete the Country group from the report. [The auto-MDX changes to remove the Country attribute, and the report still works fine.]

BUT. If between step 2 and step 3, you go make an intsy-weentsy change to the MDX, then the auto-MDX does not remove the Country attribute. IN THIS CASE, none of the lower-level Aggregate functions work. They all return Null. If you have unused higher-level attributes in your MDX, lower-level Aggregate functions return Null. 

The really wacko part is that if you never touch the MDX, and just let the Report Designer fiddle with it, everything seems to work OK. But if you do use the Aggregate function and you do customize the MDX statement, make sure that you have a grouping in the report for each attribute that has higher-level totals.

Incidentally, the place I ran into this big time was when I was decided to add an All level to a hierarchy in the cube. When I had the All level there, the report showed all blanks. When I removed the All level in the cube, the report worked. It was really strange. Why would a change like that in the cube definition make the report switch between showing correct values and showing nulls? Well, naturally, I was writing my own MDX, and I asked for [Hierarchy].Members, rather than explicitly cross-joining specific attributes. So having the All level in the hierarchy returned an extra level in the result set--a level that wasn't "used" in a grouping in the report, so all the Aggregate functions below that level returned Null. Removing the All level from the hierarchy made it so that all the levels in the result set were actually used in the report.

- Reed Jacobson