This is just a quick tip for fixing the default Analysis Services (SSAS) named set sub-total behavior in Excel 2010. Normally when you select a named set for the rows or columns of a pivot table you don’t get a sub-total. Which, for me, removes a lot of the usefulness of the named set. Thankfully it’s a relatively easy fix. It just takes a little additional MDX in your named set definition.
Enter: VisualTotals() (msdn function reference)
The VisualTotals() function dynamically totals child members in a set. By adding the “All” member for whatever hierarchy you’re building your set from and wrapping the whole set in the VisualTotals() function you can get Excel to treat the named set just like any other group/hierarchy when it comes to sub-totals.
Typically if you created a set like this one (all examples from Adventure Works):
WITH SET [Amasia] AS
{
[Geography].[Country].&[United States]
,[Geography].[Country].&[Canada]
,[Geography].[Country].&[Australia]
} |
You’d see something like this:
no sub-totals
If instead you create your set like so:
WITH SET [Amasia] AS
VisualTotals(
{
[Geography].[Country].[All Geographies]
,[Geography].[Country].&[United States]
,[Geography].[Country].&[Canada]
,[Geography].[Country].&[Australia]
}
) |
You’ll get a nice little sub-total at the bottom.
Yey! sub-totals
One thing to note is that you need to put the “All” member at the beginning of your VisualTotals set. Otherwise it won’t be correctly filtered by the children that follow and you’ll end up with a sub-total that is actually the full, unfiltered total of the “All” member.
