MDX Essentials: String Functions: The .Name Function - Page 2

Practice

Preparation

To reinforce our understanding of the basics we have covered so far, we will use the .Name function in a couple of ways that illustrate its operation. We will do so in simple scenarios that place .Name within the context of meeting a business need. The intent is, of course, to demonstrate the operation of the .Name function in a straightforward manner.

Let's return to the MDX Sample Application as a platform from which to construct and execute the MDX we examine, and to view the results datasets we obtain.

Let's assume, for our practice example, that we have received a call from the Reporting department of the FoodMart organization, requesting our assistance in meeting a specific report presentation need. A group of report authors wants to display the names of the US Warehouse Cities, alongside the respective Warehouse Sales figures for those Warehouse Cities, within a returned dataset that they will use in a Reporting Services report.

This represents a simple, yet practical, need that we can readily answer using the .Name function in conjunction with a relative function, .CurrentMember. We will create a basic query that returns the Warehouse City names for each US City in which we conducted Warehouse operations over the past couple of years (1997 and 1998), along with total Warehouse Sales for each respective US Warehouse City. The query will ultimately find its way into the Dataset definition of a report the authors intend to construct within Reporting Services.

Let's construct a simple query, therefore, to return the Warehouse Sales by City information, presenting the names and Sales data in the columns and the corresponding Warehouse City members as rows.

-- MDX034-01  Using .NAME  to display member names alongside values within the 
-- data grid
WITH
MEMBER
     [Measures].[Warehouse USA City]
AS
     '[Warehouse].CurrentMember.NAME'
SELECT
     {[Measures].[Warehouse USA City], [Measures].[Warehouse Sales]} 
         ON COLUMNS,

     {DESCENDANTS([Warehouse].[All Warehouses].[USA], [Warehouse].[City])} 
         ON ROWS
FROM 
     [Warehouse]  

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

We see Warehouse City Names, the output of the Warehouse USA City calculated member, populating the first column, with the respective Warehouse Sales values occupying the second column. The Warehouse City members themselves occupy the row axis, as we requested. The calculated member Warehouse USA City employs the .Name function in conjunction with the "relative" .CurrentMember function, which, as we can easily see from our practical example, results in a list of the names of the members that we specify in our row axis. (Similarly, if we had specified the Warehouse State Province or Warehouse Country levels in the row axis instead, we would have obtained a list of the members of those levels as a result). Intersecting the calculation with the members under consideration can be leveraged, in similar fashion, to produce sophisticated results within more elaborate structures and processes.

Let's look at an example that expands upon our first, again to produce a specific presentation effect. Let's say that we have presented our solution in the first example to the information consumers, who have accepted it as meeting the initial requirement. The users, who have begun to grasp the possibilities that our efforts have revealed, ask for something along the same lines – a presentation capability that we have often seen described in requests for assistance in forums and elsewhere. The consumers wish to present Units Shipped information, with the Year and Month, in crossjoined fashion, across the column axis within the data grid, and the Warehouse States in the row axis.

To confirm our understanding of the requirement, we work with the consumers to create a "draft" of the requirement, which is partially represented in Table 2 below.

After receiving confirmation of our understanding of the requirements, we take the following actions to meet the new consumer request:

-- MDX034-02  Using .NAME ( X 2!) to allow display of members of  two 
--levels from the same dimension on same axis
WITH
 MEMBER
    [Measures].[Display_Year]
AS
    '[Time].CURRENTMEMBER.PARENT.PARENT.NAME'
MEMBER
    [Measures].[Display_Month]
AS
    '[Time].CURRENTMEMBER.NAME'
SELECT
  {DESCENDANTS([Time].[1998], [Time].[Month])} ON COLUMNS,
    {
        {([Warehouse].[All Warehouses].[USA], [Measures].[Display_Year])},
            {([Warehouse].[All Warehouses].[USA], 
                  [Measures].[Display_Month])},
                      CROSSJOIN({DESCENDANTS
                         ([Warehouse].[All Warehouses].[USA], 
                             [Warehouse]. [State Province])}, 
                                {[Measures].[Units Shipped]})}  ON ROWS  
FROM 
   [Warehouse]

The Query pane appears as depicted in Illustration 2, with the new query in place.

The Results pane is populated, and the dataset partially shown in Illustration 3 appears.