MDX Essentials: String Functions: The .Name Function

Monday Aug 1st 2005 by William Pearson
Share:

MSAS Architect Bill Pearson introduces the .Name function as a means of generating lists and fine tuning the presentation of returned datasets. Also examined: the VBA Space() function in creating picklist support for report parameterization within MDX queries.

About the Series ...

This article is a member of the series, MDX Essentials. The series is designed to provide hands-on application of the fundamentals of the Multidimensional Expressions (MDX) language, with each tutorial progressively adding features designed to meet specific real-world needs.

For more information about the series in general, as well as the software and systems requirements for getting the most out of the lessons included, please see my first article, MDX at First Glance: Introduction to MDX Essentials.

Note: Current updates are assumed for MSSQL Server, MSSQL Server Analysis Services, and the related Books Online and Samples.

Overview

In this lesson, we will expose another popular function in the MDX toolset, the .Name function. The general purpose of the .Name function is, unsurprisingly, to return the name of the object to which it is appended. It can be used with hierarchies, dimensions, levels, and members, so it is easy to see that .Name offers a great deal of flexibility. For that matter, I have found in working with MDX in Analysis Services, as well as in Reporting Services and various other reporting applications, that the .Name function allows us to exercise a great deal of presentation sleight of hand, at times.

The .Name function can be leveraged in activities that range from generating simple lists to supporting sophisticated presentations. We will introduce the function, commenting upon its operation and touching upon the creative sorts of effects that we can employ it to deliver. As a part of our discussion, we will:

  • Examine the syntax surrounding the function;
  • Undertake illustrative examples of the uses of the function in practice exercises;
  • Briefly discuss the results datasets we obtain in the practice examples.

The .Name Function

Introduction

According to the Analysis Services Books Online, the .Name function "returns the name of a level, dimension, member, or hierarchy." .Name has many applications, including its use with these Analysis Services objects, as well as its pairing with other MDX functions to leverage its power even further. As an illustration, I use it constantly with the .CurrentMember function; we will see an example of this combination within our practice exercises later.

We will examine the syntax for the .Name function after a brief discussion in the next section. We will then explore some of the "presentation leverage" it offers the knowledgeable user using practice examples for a couple of scenarios, constructed to support hypothetical business needs that illustrate uses for the function. This will allow us to activate what we explore in the Discussion and Syntax sections, where we will get some hands-on exposure in creating expressions that leverage the .Name function.

Discussion

To restate our initial explanation of its operation, the .Name function, when acting upon a level, dimension, member, or hierarchy, returns the name of the object to which it is appended with the period (".") delimiter. .Name can be used for a great deal more than the support of simple lists of object names, as we have intimated. When coupled with other functions, as we shall see, we can leverage .Name to deliver a wide range of analysis and reporting utility.

The Name function returns the name of the object, not the unique name (the "fully qualified" name in MDX), which is returned via the .UniqueName function. (We will examine the .UniqueName function in the next article of this series, where we will continue an example we begin at the end of our practice session here as a prelude to an exercise where we use .Name and .UniqueName together to produce a dataset for a specific objective.)

Let's look at some syntax illustrations to further clarify the operation of .Name.

Syntax

Syntactically, in using the .Name function to return the associated name, the object upon which we seek to apply the .Name function is specified to the left of .Name. The function takes the object to which it is appended as its argument, and returns within a string the name of the object specified. The general syntax is shown in the following string:

<<Object >>.Name

Using .Name could not be easier. When using the function to return the name of one of the several objects with which it works, we simply append it to the right of the object under consideration. We can see a syntax example, for each of the objects for which .Name can return a name, in Table 1 below.

Object

Example of Use:

Hierarchy

[Date].[Calendar Year].Name

Dimension

[Warehouse].Name

Level

[Warehouse].[Warehouse City].Name

Member

[Warehouse].[Warehouse City].[Seattle]. Name


Table 1: Examples Showing the .Name Function Employed with Different Objects

As an example, the following snippet:

 [Warehouse].[Warehouse City]. Name

returns City, the name of the Warehouse City level. As another example, the following:

[Warehouse].[Warehouse City]. [Seattle]

returns Seattle, the name of the Warehouse City level member Seattle. As is probably obvious, the .Name function can be best leveraged by combining it with other functions, particularly "relative" functions, to generate lists of names, and so forth, as we will see in short order.

NOTE: For more information on several of the "relative" functions, see my article MDX Member Functions: "Relative" Member Functions, within the Database Journal MDX Essentials series.

We will practice some uses of the .Name function in the section that follows.

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.

1.  Start the MDX Sample Application.

2.  Clear the top area (the Query pane) of any queries or remnants that might appear.

3.  Ensure that FoodMart 2000 is selected as the database name in the DB box of the toolbar.

4.  Select the Warehouse cube in the Cube drop-down list box.

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.

5.  Type the following query into the Query pane:


-- 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]  

6.  Execute the query by clicking the Run Query button in the toolbar.

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


Illustration 1: Results Dataset – Simple Use of .Name with .CurrentMember

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.

7.  Select File -> Save As, name the file MDX034-1, and place it in a meaningful location.

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.

1

2

3

4

5

USA

Year

1998

1998

1998

1998

1998

Month

1

2

3

4

5

CA

Units Shipped

#

#

#

#

#

OR

Units Shipped

#

#

#

#

#

WA

Units Shipped

#

#

#

#

#


Table 2: Partial Draft of the Intended Presentation Requirement

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

8.  Select File --> New from the main menu to begin a new MDX query, leaving DB and Cube settings as before.

9.  Type the following query onto the query pane:


-- 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.


Illustration 2: The New Query using .Name Function

10.  Execute the query by clicking the Run Query button in the toolbar.

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


Illustration 3: Partial Dataset – .Name Function Delivers the Desired Presentation

11.  Save the file as MDX034-2.

And so, we see the .Name function at work with a couple of different relative members, .CurrentMember and .Parent. We note that our handiwork brings about results similar to those we obtained in our first example, hierarchically leveraging the effects of combining .Name with other functions.

Let's see a final example wherein we set the stage for our next article, creating one half of a dataset that we will complete upon introduction of an additional function. We will prepare the core query that we can use to support a picklist in a reporting application, specifically where we seek to parameterize a report whose data source is an OLAP cube. This portion of the query will center on the .Name function. In our next article, where we introduce the .UniqueName function, we will re-introduce the query with further addition of .UniqueName to show a great way to handle the support of picklist parameterization for a reporting application from the Analysis Services level.

A common requirement in the reporting environment is parameterization using picklists. A specific picklist format that I like to provide my clients involves a hierarchized selection, such that they can select various levels of a dimension in the picklist and obtain "rolled up" aggregation as well as bottom level values, simply by choosing from an intuitive list of selections.

Our example will involve only the list of names that appears to the information consumer in the picklist selector of the report. The unseen portion of the support dataset, the qualified MDX name that must be passed to the query to complete the query and render the appropriately filtered presentation, will be constructed in our next article via the .UniqueName function. Our query at present will also introduce a VBA function, Space() to perform the indentation effects with which we wish to endow the selection list, to make it even more user-friendly in the ultimate report.

Let's say that we have been called upon by the information consumers to provide such a picklist: They want the users of a new report to be able, at runtime, to select any member or level of the Warehouse hierarchy to generate a report that is specified within the context selected. The information consumer group would like users to be able to run various reports with corresponding values / totals based upon the selection of a given Warehouse, a Warehouse City, a Warehouse State Province, a Warehouse Country, or all Warehouses in total. After confirming our understanding of the desired format of the selection list, we draft the tentative presentation sample in a way that appears as partially shown below:

All Warehouses (All Level)

          Canada (Warehouse Country)

                     BC (Warehouse State Province)

                             Vancouver (Warehouse City)

                                      Bellmont Distributing (Warehouse)

The information consumers confirm our understanding of the requirements, allowing us to begin taking the following actions to meet their specifications:

12.  Select File --> New from the main menu to begin a new MDX query, leaving DB and Cube settings as before.

13.  Type the following query onto the query pane:


-- MDX034-03  Using .NAME to create a hierarchical List of the Warehouse --
-- Dimension for use in a Report Picklist
WITH
MEMBER
   [Measures].[Warehouse Hierarchical Picklist]
AS
   'Space([Warehouse].Currentmember.Level.Ordinal * 6) + 
    [Warehouse].CurrentMember.Name'
SELECT 
    {[Measures].[Warehouse Hierarchical Picklist]} ON COLUMNS,
    {[Warehouse].Members} ON ROWS
FROM
    [WAREHOUSE]

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


Illustration 4: The Picklist Selection List Query

14.  Execute the query by clicking the Run Query button in the toolbar.

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


Illustration 5: Results Dataset (Partial View) – The Hierarchical List Emerges ...

The MDX query returns, via our calculated member, a display name that we can use to populate a picklist within the reporting layer of an integrated BI solution. The picklist will provide information consumers with a selection as a part of a prompted parameterization scenario within a report. The Space() VBA function is used together with the level ordinal property of the current member to generate a "space size." We then multiply that size value by six (6). The resulting indentation is then concatenated with the name of the current member ([Warehouse].CurrentMember.Name).

Our objective here is cosmetic – we are simply making the geographical levels of the Warehouse dimension appear more intuitively hierarchical for the consumers, by "indenting" each of the various levels of the Warehouse dimension in accordance with its level in the hierarchy. Note that there is more to supporting the ultimate picklist, including a "parallel" qualified name for each name in the selection list we have constructed. We will begin our next article's practice session, which focuses on the .UniqueName function, in the construction of the "other half" of the picklist, to provide the necessary qualified name to allow the MDX in the report dataset query to filter in accordance with the selected criteria.

NOTE: For a detailed, hands-on setup procedure of hierarchical picklist support within an Analysis Services cube, see my Database Journal MDX in Analysis Services article Create a Cube-Based Hierarchical Picklist.

15.  Save the file as MDX034-3.

16.  Close the MDX Sample Application, as desired.

Summary ...

In this article, we explored the MDX .Name function, which can be called upon in activities that range from generating simple lists to supporting sophisticated presentations. We introduced the function, commenting upon its operation and touching upon the creative effects we can deliver using the function.

We examined the syntax involved with the function, and then undertook illustrative practice examples of business uses for .Name, generating queries that capitalized on its primary features. Our exercises concluded with an example that looked forward to our next article, where we rejoin the exercise and employ the .UniqueName function to deliver intended results. Finally, throughout our practice session, we briefly discussed the results datasets we obtained from each of the queries we constructed.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved