Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 attend our next webcast













































   Got a SQL tip?
            We want to know!

Building Calculated Members for a SSRS Report using MDX

MSSQLTips author Ray Barley By:   |   Read Comments (14)   |   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:

develop a report that uses a ssas cube as a data source

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:

click on datasets in the report data section

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:

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


Last Update: 4/27/2011


About the author
MSSQLTips author Ray Barley
Ray Barley is a Principal Architect at RDA Corporation and a MSSQLTips.com BI Expert.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Friday, January 04, 2013 - 4:16:20 AM - Neo Read The Tip

I have a requirement to develop a report on SSRS 2008. 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 that selected month, sales for the previous month and last year's sales for the selected month. Can you give me some ideas on how to do this?


Friday, January 04, 2013 - 9:33:27 AM - Raymond Barley Read The Tip

If you're reporting against a cube then to get the previous month sales you could use a calculated member somthing like this:

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


Wednesday, February 20, 2013 - 5:56:32 PM - Adrian Read The Tip

How can this be done with a fiscal year instead?


Thursday, February 21, 2013 - 5:24:54 AM - Ray Barley Read The Tip

You need to build your time dimension to include both calendar year and fiscal year hierarchies.  For example in this tip I refer to [Date].[Calendar].[Calendar Year].  You need something like [Date].[Calendar].[Fiscal Year].

Take a look at this tip: http://www.mssqltips.com/sqlservertip/1454/built-in-time-dimension-and-time-intelligence-in-sql-server-analysis/

You can specify that you want calendar year and fiscal year when you create the dimension


Sunday, February 24, 2013 - 8:10:21 PM - atif yasin Read The Tip

Very good article,

One question if there is a selected month and it gives current YTD sales and last year YTD sales it includes the whole month. Let's say it is Sept 15 and user select month of september all sales for current YTD will include all sales happened until Sept 15 and Last Year YTD will include the whole month of September. Is it possible to include only last year YTD based on current month date....I know there is a way of doing it using the date parameter where user can select a single date sep 15, it'll include current YTD and last YTD until sep 15. Is there a way to do it with month parameter. Thanks


Monday, February 25, 2013 - 5:49:25 AM - Ray Barley Read The Tip

I found an example in the book MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook.  It shows a calculated member for "current period" sales and previous period sales which is a range of dates; a range of dates is just 2 dates delimited by a colon.  In your case you could use the first day of the year and the date selected for the report as the date range.

 

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]

Wednesday, March 20, 2013 - 3:51:47 PM - Alla Read The Tip

Hello Ray,

I hope you can help me with this MDX query

I have measure month-to-month comparison. But for the current month of March, 20th, 2013 the previous year March 2012 showed full month worth of measure. How can I do this with my MDX below:

WITH

PARALLELPERIOD ([Date].[Calendar Month].[Calendar Month],12,[Date].[Calendar Month].CurrentMember))

MEMBER [Measures].[SCInvoiceAmountCY] as([Measures].[Invoice Amount])


SELECT

 

{

[Measures].[SCInvoiceAmountCY]

, [Measures].[SCInvoiceAmountPY]

 

}

ONCOLUMNS


, ([Date].[Calendar Month].[Calendar Month]

)

ONROWS


FROM

 

[Retail Sales]


where

 

[Date].[Calendar Year].&[2013]


Thursday, March 21, 2013 - 4:16:01 PM - Raymond Barley Read The Tip

Here is an example I found in the book MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook

 

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]

Tuesday, June 11, 2013 - 5:28:00 AM - wissam Read The Tip

Thanks for the article Ray,

i was looking for something similar to this functionality but with a slighly different twist.

the user need to be able to select any month , the current sales will be able to compute based on that month, however the YTD will be the ***mulative till now.

is it possible to have mutliple values selected for the month to compute the ***mulative for those month as current sales for those months while YTD remain as the ***mulative sales  up to now.

Is it possible to achieve this with the same dataset.

Thanks

 


Tuesday, June 18, 2013 - 11:54:08 AM - Raj Read The Tip

Hello Ray,

I am trying to create a Calculated member in dataset in ssrs 2005,I am using cube as datasource,I am trying to get the sales of a region for a particular year, and region and year has to be selected dynamically and getting from a parameter,here are the thing I am trying to use

sales measure: [Measures].[Weighted Work]

Year : @Year

Region : @Region ,I have no idea how to do this by using both parameters,can you suggest me anything ?Thanks Ray

 

 

 


Wednesday, June 19, 2013 - 2:03:18 PM - Raymond Barley Read The Tip

I think your calculated member would look like this:

 

( STRTOMEMBER(@Year) , STRTOMEMBER(@Region), [Measures].[Weighted Work] )


Thursday, June 27, 2013 - 5:26:16 AM - Mehul Read The Tip

Is it possible to pass multiple months to YTD, i.e select 2 months, and output should show sum of 2 monhts this year, sum of those two months last year and so on.

 

 


Tuesday, August 12, 2014 - 3:37:37 PM - Shovan Mukherjee Read The Tip
I'm writing a simple MDX query that prints Measures in column axis and crossjoin between two DImension attributes in row axis.My query looks like below....
 with member [Type_Level_Sales] as sum ([DimProd].[Catagory].currentmember,[Measures].[Sales Amount]) 
select {Measures.[SalesAmt] , Measures.[Type_Level_Sales]} on 0, ( [DimProd].[Catagory].[Catagory] * [DimProd].[ProdName].[ProdName] ) on 1 from [CubeName]
But what is happening is both the measure is printing same value.I wanted to use the second measure Measures.[Type_Level_Sales] for some percentage calculation but this never gives correct value, it's not being executed in it's correct scope i.e. on [DimProd].[Catagory] dimentionality.Can anybody help me on this.......Shovan
 

Tuesday, August 12, 2014 - 5:00:27 PM - Raymond Barley Read The Tip

Response to previous comment (Tuesday, August 12, 2014 - 3:37:37 PM - Shovan Mukherjee) - I think you need the EXISTING keyword; see example here: http://msdn.microsoft.com/en-us/library/ms145541%28v=sql.110%29.aspx

 



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



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.