Building Calculated Members for a SSRS Report using MDX

By: Ray Barley   |   Read Comments (8)   |   Related Tips: > Reporting Services Analysis Services

Problem

I have a requirement to develop a report that uses a SQL Server Analysis Services Cube as a data source. The report must allow the user to select the month from a dropdown list in a report parameter. Based on the selected month the report should show sales for the selected month, sales year to date for the selected month, last year's sales for the selected month, and last year's sales year to date for the selected month. Can you give me some ideas on how to do this?

Solution

Based on the requirements, you need to add some calculated members to your dataset query. There are built-in MDX functions that will do exactly what you need. To restate your problem, you would like to build a report that looks like this:

The following are the details on the report columns (assume the selected month is September, 2007):

• Current Month has the sales for September, 2007
• Current Year has the total sales for January, 2007 through September, 2007
• Last Year Current Month has the sales for September, 2006
• Last Year has the sales for January, 2006 through September, 2006

In an earlier tip titled How to Customize the Dropdown List in a SSRS Report that Uses a SSAS Cube Data Source I showed how to customize the dropdown list for a report parameter (called CurrentMonth) for a SQL Server Analysis Services data source. The requirement there was to populate the dropdown list with 24 months based on a current month as retrieved from a table in a relational database. I'm going to leverage the sample report from the earlier tip and just focus on building the dataset query to render the sample report as shown above.

I'll use the Adventure Works cube that was published for SQL Server 2008 R2 as the basis for the report. I'll use Report Builder 3.0 to create the sample report.

Defining the Calculated Members

The MDX syntax for a defining a calculated member within an MDX query is:

WITH
MEMBER [MEMBER NAME GOES HERE] AS
  [CALCULATION GOES HERE]

You can define one or more calculations and the scope of the calculations is limited to the MDX query that follows. You start the calculations by specifying WITH.

The calculated member we need for Current Year sales is as follows:

MEMBER [Measures].[Current Year Sales] AS
  AGGREGATE ( 
    YTD ( 
      STRTOMEMBER( @CurrentMonth ) 
    ), 
    ( [Measures].[Internet Sales Amount] ) 
  )

The components of the calculation are as follows:

• STRTOMEMBER is an MDX function that takes a string as a parameter and returns a dimension member; in this case @CurrentMonth is the report parameter that has the month selected by the user which is a string. STRTOMEMBER returns the member unique name which uniquely identifies the dimension member; e.g. September 2007
• YTD is an MDX function that returns the set of date dimension members for the year based on the first parameter; i.e. if we pass in September, 2007 we get the 9 months beginning with January, 2007 through September, 2007
• AGGREGATE is an MDX function that calculates a sum based on the the aggregation defined for the measure in the cube; in this case it is a SUM as we wold expect

The calculated member we need for Last Year Current Month is as follows:

MEMBER [Measures].[Last Year Sales Current Month] AS
    ( ParallelPeriod ( 
         [Date].[Calendar].[Calendar Year], 
         1, 
         STRTOMEMBER( @CurrentMonth ) 
      ), 
      [Measures].[Internet Sales Amount] 
    )

The components of the calculation are as follows:

• PARALLELPERIOD is an MDX function that is used to get the same period in the previous year as the month selected in the report parameter
• The first parameter is the level in the Date dimension hierarchy (i.e. year)
• The second parameter determines whether to get the previous period (positive) or a future period (negative)
• The third parameter is the relative date; in our case it is a month so we want the same month in the previous year

The calculated member we need for Last Year is as follows:

MEMBER [Measures].[Last Year Sales] AS
  AGGREGATE( 
    YTD ( 
      PARALLELPERIOD ( 
          [Date].[Calendar].[Calendar Year], 
          1, 
          STRTOMEMBER( @CurrentMonth ) 
      ),
      ( [Measures].[Internet Sales Amount] )
    )
  )

The above calculation is the same as Current Year Sales with a twist; the first parameter to the YTD MDX function is the PARALLELPERIOD expression we used in the Last Year Sales Current Month calculated member.

NOTE: The above calculated members use MDX functions that rely on the fact that your Date (or Time) dimension has been decorated with the Business Intelligence type information. When you create a Date dimension you need to specify the dimension Type as Time and also specify the attribute Type for your attributes; e.g. Year, Month, etc. Please refer to our earlier tip Using the Built-In Time Dimension and Time Intelligence in SQL Server Analysis for the details.

Create Report Dataset Filtered by Report Month Parameter

In this last step we need to create a dataset that defines our calculated members, retrieves the data for our report, and filters the data based on the month selected by the user. Right click on Datasets in the Report Data section, select Add Dataset, and fill in the dialog as shown below:

The above MDX query has to be entered as text in order to use our custom CurrentMonth parameter and the calculated members that also use the custom CurrentMonth parameter. The full query is shown below:

WITH
MEMBER [Measures].[Current Month Sales] AS
    [Measures].[Internet Sales Amount]
MEMBER [Measures].[Current Year Sales] AS
    AGGREGATE ( 
        YTD ( 
            STRTOMEMBER( @CurrentMonth )
        ), 
       ( [Measures].[Internet Sales Amount] ) 
    )
MEMBER [Measures].[Last Year Sales Current Month] AS
    ( ParallelPeriod ( 
        [Date].[Calendar].[Calendar Year], 1, STRTOMEMBER( @CurrentMonth )
      ) ,
      ( [Measures].[Internet Sales Amount] )
    )
MEMBER [Measures].[Last Year Sales] AS
      AGGREGATE ( 
          YTD ( 
              ParallelPeriod ( 
                  [Date].[Calendar].[Calendar Year], 1, STRTOMEMBER( @CurrentMonth )
               )
          ), 
          ( [Measures].[Internet Sales Amount] ) 
       )
 
SELECT
    {
        [Measures].[Current Month Sales] ,
        [Measures].[Current Year Sales] ,
        [Measures].[Last Year Sales Current Month] ,
        [Measures].[Last Year Sales]
    } ON COLUMNS,
    {
        [Product].[Category].MEMBERS
        } ON ROWS 
FROM [Adventure Works]
WHERE STRTOMEMBER( @CurrentMonth )

Click the Query Designer button on the Dataset Properties dialog to show the query designer and associate the CurrentMonth parameter with the @CurrentMonth placeholder in the MDX query. Click the Query parameters icon on the toolbar (the icon is shown in the window title of the screen shot below) and fill in the dialog as shown:

Sample Report

The following is the output shown when running the report:

Next Steps

• Customizing a dataset query for an SSAS cube with calculated members allows you to take advantage of the many MDX functions that are available for presenting values in your reports such as year to date, last year, etc.
• You can download the sample report here.
• The Adventure Works cube used in the sample report is available from this CodePlex site.
• You can download Report Builder 3.0 here

About the author

Ray is a Principal Architect at RDA Corporation and a MSSQLTips.com BI Expert.

View all my tips

We Recommend

Print

Tweet

Become a paid author

Comments and Feedback:

MEMBER [Measures].[Current Year Sales Previous Month] AS
    ( ParallelPeriod ( 
        [Date].[Calendar].[<<PUT MONTH MEMBER HERE>>], 1, STRTOMEMBER( @CurrentMonth )
      ) ,
      ( [Measures].[Internet Sales Amount] )
    )

I'm not sure how much MDX is covered on this site; if you need more here is a good place to start: http://www.databasejournal.com/article.php/1459531
I don't do any MDX so I can't help any more on this.

If you are reporting against a relational database here is something I have used:

- transform your data so that you have it summed by month number and year
- populate the parameter list of available values with some date for each month; e.g. I use the last day of the month
- based on month selected calculate the month number and year for the current month and previous month in the current year; calculate the month number and year for the current month in the previous year
- query your data filtering on your 3 month numbers and years
- pivot the data so that for each row you get columns sales for the previous month, current month and current month in the previous year

WITH
MEMBER [Internet Sales CP] AS
   Sum( { [Date].[Calendar].[Date].&[20071201] :
          [Date].[Calendar].[Date].&[20071224] },
        [Measures].[Internet Sales Amount] )
MEMBER [Internet Sales PP] As
   Sum({ [Date].[Calendar].[Date].&[20071201] :
         [Date].[Calendar].[Date].&[20071224] },
         ( ParallelPeriod( [Date].[Calendar].[Calendar Year],
                           1, 
                           [Date].[Calendar].CurrentMember ),
       [Measures].[Internet Sales Amount] )
      )
   , FORMAT_STRING = 'Currency'
MEMBER [Internet Sales YoY %] As
   iif( [Internet Sales PP] = 0, null,
        ( [Internet Sales CP] / [Internet Sales PP] ) )
   , FORMAT_STRING = 'Percent'      
SELECT
   { [Internet Sales PP],
     [Internet Sales CP],   
     [Internet Sales YoY %] } ON 0,
   { [Product].[Color].MEMBERS } ON 1
FROM
   [Adventure Works]

WITH
MEMBER [Internet Sales CP] AS
   Sum( { [Date].[Calendar].[Date].&[20071201] :
          [Date].[Calendar].[Date].&[20071224] },
        [Measures].[Internet Sales Amount] )
MEMBER [Internet Sales PP] As
   Sum({ [Date].[Calendar].[Date].&[20071201] :
         [Date].[Calendar].[Date].&[20071224] },
         ( ParallelPeriod( [Date].[Calendar].[Calendar Year],
                           1, 
                           [Date].[Calendar].CurrentMember ),
       [Measures].[Internet Sales Amount] )
      )
   , FORMAT_STRING = 'Currency'
MEMBER [Internet Sales YoY %] As
   iif( [Internet Sales PP] = 0, null,
        ( [Internet Sales CP] / [Internet Sales PP] ) )
   , FORMAT_STRING = 'Percent'      
SELECT
   { [Internet Sales PP],
     [Internet Sales CP],   
     [Internet Sales YoY %] } ON 0,
   { [Product].[Color].MEMBERS } ON 1
FROM
   [Adventure Works]

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates Signup for our newsletter

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code