Named Set Subtotals in Excel
So many SSAS browsers, so little resemblance… And each browser has an independent view of which SSAS features it cares about and how it will go about implementing them. For example, Excel 2007 will let you use named sets on rows or columns but not as a filter. The cube browser and Report Builder let you use them in the filter area but not on columns and rows.
Issue: Business users have requested a subtotal for named set in Excel 2007. Say, you’ve defined the following named set in the cube to return the last 12 months from the current month.
CREATE DYNAMIC SET CURRENTCUBE.[Last 12 Months] AS
[Date].[Date Hierarchy].CurrentMember.Lag(11):[Date].[Date Hierarchy].CurrentMember
If you drop this named set on an Excel 2007 PivotTable report, you would notice that it doesn’t give you a subtotal that returns the aggregated total of the measure used in conjunction with the set. However, the chances are that you would need a server subtotal, especially if you use scope assignments to derive the subtotal value. By contrast, the cube browser will show the subtotal if you use the named set as a filter and drag the corresponding dimension (in this case Date) to the rows or columns. This difference of course originates from the MDX query the client generates. In the latter case, the cube browser hiearchizes the dimension All member so the server returns this member, while a named set would simply returns the members of the set and nothing more.
Workaround: A simple workaround in Excel 2007 to get a subtotal is to add the dimension all member, such as:
CREATE DYNAMIC SET CURRENTCUBE.[Last 12 Months] AS
{[Date].[Date Hierarchy].CurrentMember.Lag(11):[Date].[Date Hierarchy].CurrentMember,
[Date].[Date Hierarchy].[All]}
Of course, if the named set uses another dimension, you need to add the All member of that dimension. For example, adding a subtotal to the Adventure Works Top 50 Customers will require the following change:
Create Dynamic Set CurrentCube.[Top 50 Customers]
As {TopCount
(
[Customer].[Customer].[Customer].Members,
50,
[Measures].[Internet Sales Amount]
), [Customer].[Customer].[All Customers]}
Wish List: Unify Microsoft-based browsers as much possible in terms of functionality. In this case, it will be nice, if Excel supports named sets as filters.
