Hitachi Consulting BI Blog

RS MDX in B2

I just wanted to follow-up on Stacia’s (very interesting) recent post about Reporting Services and MDX. In Beta 2, the MDX that the wizard generates returns data from only a single level, and then the report aggregates the numbers into totals. There are a couple of problems with this approach, the most serious of which is that it doesn’t work properly with cube calculations. If you have, for example, a calculated member that divides Dollars by Units to get Price, the Beta 2 MDX would retrieve the low-level price and then add it up.

The dev team has assured me that by Beta 3 it will work properly. They will generate good multi-level MDX and the report will be able to grab the cube-generated value at all levels, which will return the right answer.

One other little note if you’re playing with OLAP as a data source in Beta 2. The Report Wizard automatically sorts by the Grouping items, so if you retrieve a Data dimension that includes Month Names, the wizard sorts the month names alphabetically. The workaround is simply to go into the group definition and delete the sort—so the report will sort in the data source order. (You also want to make sure your date dimension is to the left of other dimensions so that the other dimensions don’t mess up the sort order.)

- Reed