Adding a Set Total
Scenario: You have defined an MDX set in the cube script, such as a set that returns a few months. For the sake of simplicity, we will hardcode the months.
CREATE SET CURRENTCUBE.[12Months] AS
{
[Date].[Month].&[201302].Lag(11):[Date].[Month].&[201302]
};
As useful as the set might be, it has an issue. It doesn’t return the total and users might complain about it:
Solution: As a first try, you might attempt to add the [All] member to the set:
CREATE SET CURRENTCUBE.[Last12Months] AS
{
[Date].[Month].&[201302].Lag(11):[Date].[Month].&[201302]
+
[Date].[Month].[All]
};
You’re on the right path. Now, you’ll get the grand total row in Excel but it shows the unrestricted (non-visual total) total instead of the total across set members only. Instead, use the VisualTotals() function:
CREATE DYNAMIC SET CURRENTCUBE.[12Months] AS
{
[Date].[Month].&[201302].Lag(11):[Date].[Month].&[201302]
+
VisualTotals
(
{
[Date].[Month].[All],
[Date].[Month].&[201302].Lag(11):[Date].[Month].&[201302]
}
)
};
Here, the VisualTotals function returns the All member with the total adjusted to set members only. Notice also that I’ve changed the set definition to by dynamic (DYNAMIC keyword) to force re-evaluation of the set.
In a slightly more complicated scenario, what if you want the total to appear as an additional member of the set as opposed to a grand total row. One implementation approach will be to add a dimension calculated member to the Month hierarchy that does the aggregation and then add the new member to the set:
CREATE MEMBER CurrentCube.[Date].[Month].Agg AS Aggregate([Date].[Month].&[201302].Lag(11):[Date].[Month].&[201302])
CREATE SET CURRENTCUBE.[12Months] AS
{
[Date].[Month].&[201302].Lag(11):[Date].[Month].&[201302]
+
[Date].[Month].Agg
};
