Home > MDX > Generate, Top Count, and SubTotals Revisited

Generate, Top Count, and SubTotals Revisited

In my previous post, I used Generate, TopCount, and EXISTING to produce a list of the top products and the total for all other products associated with a category. The problem this post deals with is only slightly more complicated. Rather than address the top products associated with a product category, in this example, we want to display the top products associated with a sales region and in addition to the top products, we also want to display the totals for the current region’s top 5 products. The approach used in the previous post took advantage of the fact that each product was associated with one and only one product category. Since a product can be purchased in more than one region, a slightly different approach has to be used here.  Like before, the Generate statement combined with the TopCount function can be used to produce the top products for each region, [Region Top Products]. However, unlike the previous post, the sales region has to be included in the second argument of the Generate and included in the resultant set to identify the context of the current region since a single product can be returned more than once.

WITH SET [Region Top Products] AS
    Generate(
        { [Sales Territory].[Sales Territory].[Region].Members },
        [Sales Territory].[Sales Territory].CurrentMember
        * TopCount( 
            { [Product].[Product Categories].[Product].Members },
            5,
            ( [Measures].[Internet Sales Amount], [Sales Territory].[Sales Territory].CurrentMember )
        )
    )

The region subtotals are also slightly more difficult to return. Like before, a new calculated member in the [Product].[Product Categories] hierarchy needs to be created to represent the top 5 product totals. However, once again since both the identity of the region AND the product is relevant, both have to be accounted for. To do so, this region subtotal member is defined as an aggregate over the intersection of the [Region Top Products] set defined above and the crossjoin of the current sales region and products. Since this crossjoin must be re-evaluated in the context of the current sales region in each iteration, the EXISTING declarative must once again be used. The resultant definition is given below.

MEMBER [Product].[Product Categories].[All].[Top 5 Product Total] AS
    Aggregate(
        Intersect(
            [Sales Territory].[Sales Territory].CurrentMember
            * EXISTING { [Product].[Product Categories].[Product].Members },
            [Region Top Products]
        ),
        [Measures].[Internet Sales Amount]
    )

And lastly, to include the subtotals together with the top product list, two sets are combined and ordered by the sales region name. The complete final query is returned below.

WITH SET [Region Top Products] AS
    Generate(
        { [Sales Territory].[Sales Territory].[Region].Members },
        [Sales Territory].[Sales Territory].CurrentMember
        * TopCount( 
            { [Product].[Product Categories].[Product].Members },
            5,
            ( [Measures].[Internet Sales Amount], [Sales Territory].[Sales Territory].CurrentMember )
        )
    )
MEMBER [Product].[Product Categories].[All].[Top 5 Product Total] AS
    Aggregate(
        Intersect(
            [Sales Territory].[Sales Territory].CurrentMember
            * EXISTING { [Product].[Product Categories].[Product].Members },
            [Region Top Products]
        ),
        [Measures].[Internet Sales Amount]
    )
SELECT    {
        [Measures].[Internet Sales Amount]
    } ON COLUMNS,
    Order(
        [Region Top Products]
        + CrossJoin(
            { [Sales Territory].[Sales Territory].[Region].Members },
            { [Product].[Product Categories].[Top 5 Product Total] }
        ),
        [Sales Territory].[Sales Territory].CurrentMember.MEMBER_CAPTION,
        BDESC
    ) ON ROWS
FROM    [Adventure Works]
Advertisements
Categories: MDX
  1. December 18, 2012 at 2:19 PM

    Can you please also explain how to add totals to the Sales Territory hierarchy? Thanks.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: