Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Aug 1, 2005

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

By William Pearson

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.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM