MDX Essentials: Basic Set Functions: The TopCount() Function, Part II - Page 4

Perform a Topcount() within the Context of Another Dimension, and Return a Related String for Presentation Purposes

Our next exercise comes in the form of a request from the same group of information consumers (the Accounting and Finance department at FoodMart), who have expressed satisfaction with our first solution. This request also involves "top counts" of Warehouses, and on the surface may sound simple to many of us. The consumers want a query that will produce a plain list containing the top individual Warehouse, for each of the twelve Months of 1998 (denominated simply in numbers one to twelve, as they appear in the Warehouse cube), this time in terms of number of Units Shipped within that month.

We again draft a sketch of the proposed report layout, to confirm our understanding and to agree fully on the requirement. The sketch appears as illustrated in Table 2.

We agree to pursue a solution, and consider the requirement in bite-size pieces. One challenge that stands out immediately is the apparent need to return the Warehouse Name, a text string, from the lowest level of the Warehouse dimension. TopCount() is still at the heart of our contemplated approach, however, with the Units Shipped the measure upon which it is based.

Let's construct part of the query to get started. First, we will construct a query that lists all Warehouses, and respective quantities shipped, for each of the Months in 1998. This serves to build the core ON ROWS specification, the "months" part of which we will use within our final solution. It also serves to present the quantities for each Warehouse, each Month, allowing us to "proof" the accuracy of the results of the next step, where we generate the name of the top Warehouse from the perspective of shipping, for each Month.

-- MDX032-2-1, Shipping Volume Qty by Month, 1998, for Individual U.S.    
    Warehouses"
SELECT 
  {[Measures].[Units Shipped]} ON COLUMNS,
  NONEMPTYCROSSJOIN(
      {DESCENDANTS([Time].[1998], [Time].[Month])},
            {DESCENDANTS( [Warehouse].[All Warehouses].[USA], 
                  [Warehouse].[Warehouse Name] )})
  ON ROWS
FROM 
              [WAREHOUSE]

The Results pane is populated by Analysis Services, and the dataset partially shown in Illustration 7 appears.

At this stage, we can easily discern the largest number for each month within the measure column, which identifies at a glance, of course, the top shipping Warehouse for each month. Next, we will add the MDX required to generate the name of the top shipper for each month, initially placing the name next to the column displaying shipping volumes for each Warehouse, for easy verification of the accuracy of the name produced.

-- MDX032-2-2, Addition of Calculated Member to Produce Name of Warehouse with 
--   Highest 1998 Volume Monthly Shipping

WITH 
MEMBER
     [Measures].[Largest Shipper]
AS
    'TOPCOUNT( DESCENDANTS( 
            [Warehouse].[All Warehouses].[USA], [Warehouse].
                [Warehouse Name] ), 
                     1,  (Time.CURRENTMEMBER, [Measures].[Units Shipped]) 
                ).ITEM(0).ITEM(0).NAME'

This will create the calculated member Largest Shipper, which will allow us to present a string containing the name of the Warehouse shipping the largest volumes for each month, alongside the member Warehouses' respective volumes.