Happiness: RS Report Designer smarter than I thought

Ever since the earliest Yukon beta's I've been frustrated with how RS report wizard handles AS as a data source. The problem has to do with non-additive measures--for example, a distinct count. In the cube, you create a measure or calculated member that gets the right answer at every point in the cube. But when you use the RS wizard to create the MDX and the report, it only queries for the lowest level used in the report, and then uses Sum for all the higher levels. This is fine when it is a Sum, but doesn't work at all for Distinct Count.

Brian Welcker kept telling me that they added the Aggregate function and fixed the problem, but every time I tried, I still saw the same leaf-level MDX and Sum function. And I know that you can't get the right answer with only leaf-level MDX. So I would change the MDX to return the multi-level values, and then change the report to use the Aggregate function instead of Sum, and it did work. But it really didn't make a good story.

Well, earlier this week, I discovered the secret. I had always fixed the MDX first, and then changed the Sum function to Aggregate. That was obvious to me, because how would Aggregate get the higher level value if the MDX is wrong? Well, I "accidentally" changed the Sum function to Aggregate first, and when I previewed the report, it showed the right answer. How could it do that? Switching to the data pane, the MDX was still the old detail level. So I used Profiler to capture the MDX, and the MDX was different--it included the additional levels.

The bottom line is that if you use the MDX designer to create the MDX, and then modify the report to use the Aggregate function instead of the Sum function (assuming you have non-additive measures), then the report designer changes the MDX to make it work. When I think about it, this really does make sense. If all you have is Sum formulas, it's more efficient to just add them up in RS. RS can't really know whether a measure is additive or not, so it does default to use the Sum function. But all you have to do as a user is build the report (with the wizard) and then go change the function for any non-additive measures to Aggregate. The report then adjusts the MDX to make it work.

It's a bit creepy for me to have it do all that "automatically" for me. (The windows on my car open with crank handles--or zippers--and don't even talk to me about automatic door locks.) But it actually does make sense and it does work. Maybe it's even documented in the new July BOL ;-).

- Reed Jacobson