Advanced Grid Reporting – MDX:

This weeks blog is for the more technically minded among our user base and our partner network – not for everyone, but highlights some potentially very powerful capabilities!

We will be looking at using MDX: and MDXSet: along with Query Generators to create sub-cubes, meaning we can develop advanced reports that would otherwise be difficult to achieve.

The requirement

We recently had a support call with a user who had a relatively unusual, but certainly not unique requirement. A hierarchy built into their cube did not match up with the reporting requirements of the business. In this case a member at a low level should not be included in the hierarchy and should also not contribute to its parent or ancestors.

As an example, imagine a scenario where a specific product should have its sales ignored in a report, I’ll use Helmets from the sample Adventure Works cube. Not only should Helmets be removed, the totals in the grid should also be updated.

XLCubed grid with totals showing the element we wish to remove

Using the grid designer it is easy to exclude a member from the grid, but its sales will still contribute to the total.

XLCubed lets you create both common and advanced reports using a simple drag/drop interface, but sometimes you have really specific requirements which aren’t covered.

The Solution

In this case MDX: comes to the rescue and allows us to create a report that wouldn’t otherwise be possible. We want to make sub-cube containing all the members except Helmets and use that for our reporting.

The first step is to get the MDX for the set we want. I don’t like writing MDX myself as it’s tricky and error prone, so I’ll use a Query Generator to do the hard work for me 🙂

Step by Step

I inserted a query generator (from the Insert Formula menu) and set it up to pick all the subcategories, and then exclude the member I don’t want. This is just a few clicks in the standard designer.

XLCubed Query Generator showing the query

By default, the MDX generated is for the whole report, so in the Properties screen change the output to be just the hierarchy we are interested in.

Query Generator properties

Next we just need to add the MDXSet: instruction to the text to tell XLCubed we know what we are doing and are providing direct MDX. This is just a simple formula below the generated MDX.

MDX Set text

Now we have the custom set we want, its simple to add a sub-cube to our original grid.

Dialogue showing how to make a subcube

And that’s it, now our grid gives us the result we were after and the totals are updated to reflect the removed member.

Updated grid with member removed and totals updated

Quite an advanced topic today, but hopefully not too tough to follow. MDX: and MDXSet: give you a lot of options to make advanced reports, and Query Generators greatly simplify the task of getting that MDX in the first place.

Leave a Reply

Your e-mail address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.