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.
Excluding Specific Members from Hierarchies in XLCubed
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.
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.
Leveraging MDX to Create Custom Sub-Cubes in XLCubed
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: Setting Up the MDX Query Generator in XLCubed
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.
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.
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.
Now we have the custom set we want, its simple to add a sub-cube to our original grid.
And that’s it, now our grid gives us the result we were after and the totals are updated to reflect the removed member.
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.
