Cumulative Aggregates in SSRS

Problem

Typically when working in Sales and Marketing Departments, every individual or group of individuals are given a Target for each year. All of these targets rolls up to the company’s targets. Often companies will have one or more standard scorecards / dashboards to monitor the overall health of the organization based on a set of pre-defined KPIs /metrics. However, the individuals at the bottom or middle of the hierarchy need a way to review the detailed numbers for the current month, current quarter or current year to see how they are approaching the target. In this tip, we will see how to build a report which shows the cumulative sales amount for the current month, current quarter (QTD) and current year (YTD) in a single crosstab (tablix) report, which makes it easy to compare the data.

Solution

To address the problem stated above, we will be using one of the Running Aggregate functions in SSRS called “RunningValue“.

This exercise assumes that, you have a basic knowledge of creating Tabular reports and working with Groups in SSRS.

For this demonstration, let us assume that, the target sales amount for an individual is set to $120,000 for the entire year. Which means, the individual, should reach $10,000 every month on an average to be able to achieve $120,000 at the end of the year. Let us prepare some sample data using the below query.

SELECT

  YEAR([PeriodEndDate]) AS [Year]

  , CASE

      WHEN MONTH([PeriodEndDate]) BETWEEN 1 AND 3 THEN ‘Q1’

      WHEN MONTH([PeriodEndDate]) BETWEEN 4 AND 6 THEN ‘Q2’

      WHEN MONTH([PeriodEndDate]) BETWEEN 7 AND 9 THEN ‘Q3’

      WHEN MONTH([PeriodEndDate]) BETWEEN 10 AND 12 THEN ‘Q4’

    END AS [Quarter]

  , MONTH([PeriodEndDate]) AS [MonthNumber]

  , DATENAME(MONTH,[PeriodEndDate]) AS [Month]

  , [ActualSales]

  , [TargetSales]

FROM

  (VALUES

    (‘2011-01-31’, 11000, 120000/12)

    , (‘2011-02-28’, 8000, 120000/12)

    , (‘2011-03-31’, 12000, 120000/12)

    , (‘2011-04-30’, 10000, 120000/12)

    , (‘2011-05-31’, 12000, 120000/12)

    , (‘2011-06-30’, 11000, 120000/12)

    , (‘2011-07-31’, 9000, 120000/12)

    , (‘2011-08-31’, 8000, 120000/12)

    , (‘2011-09-30’, 12000, 120000/12)

    , (‘2011-10-31’, 11000, 120000/12)

    , (‘2011-11-30’, 9000, 120000/12)

    , (‘2011-12-31’, 10000, 120000/12)

  ) AS SalesDetails([PeriodEndDate], [ActualSales], [TargetSales])

Note that, in the above query, the last column “TargetSales” is set to “120000/12” which gives us the TargetSales at the month level when the TargetSales for the year is set to $120,000. Now, let us create a report to address the requirement/problem statement.

Step 1 – Create a new Report Server Project

Open SQL Server Business Intelligence Development Studio (BIDS), Create a new Report Server project and name it as SSRSDemo.

Step 2 – Create a new Report

Go to the Solution Explorer, Add a new Report and name it SSRSCumulativeAggregates.

Step 3 – Create a new Data Source

Go to the Report Data window, Add a new Data Source pointing to any available Dev/Test SQL Server/Database and name it as dsSSRSDemo. If the Report Data window is not visible, you can open it by going to View menu and select Report Data from the drop down, alternatively you can press Ctrl+Alt+D.

Step 4 – Create a new Dataset

Go to the Report Data window, Add a Dataset pointing to the Data Source dsSSRSDemo created in previous step and name it as dstSSRSDemo. Use the above query for this dataset.

Step 5 – Adding a new Table control

• Add a new Table control to the Report Design surface.
• By default, a Table control has 3 columns. Add three more columns to the Table towards the right as shown below. In the below screenshot, I have assigned a name to each cell (H1, H2, D1, D2 etc.) so that we can refer them easily in the subsequent steps.
  
  
  
  
  
• Go to the Tablix Properties window and set the Dataset name to dstSSRSDemo.

Step 6 – Updating and Adding Row Groups

• Go to Grouping tray at the bottom of the Report Design surface. If the Grouping tray is not visible, go to Report menu – View – Select Grouping.
• Go to the Details group properties. In the Group Properties window, change the name to rgrpDetails, set the Group expression to MonthNumber and click OK.
• Right click on the Details group, Select Add Group and then Parent Group… from the context menu. Set Group by to MonthNumber in the Tablix group window and click OK.
• Go to MonthNumber group properties and set the name to rgrpMonth.
• Right click on the rgrpMonth group, Select Add Group and then Parent Group… from the context menu. Set Group by to QuarterNumber in the Tablix group window and click OK.
• Go to QuarterNumber group properties and set the name to rgrpQuarter.
• Right click on the rgrpQuarter group, Select Add Group and then Parent Group… from the context menu. Set Group by to Year in the Tablix group window and click OK.
• Go to Year group properties and set the name to rgrpYear.
• Now the table should look like the below screenshot.
  
  
  
  

Step 7 – Updating the Cell Labels and Values/Expressions

Update the values in each of the cells in the table as per the below specification:

Notice that, in the above table, we are using the RunningValue function to get the QTD and YTD sales. The RunningValue function has the following syntax:

RunningValue(Expression, Function, Scope)

• Expression: Data to be aggregated. Expression cannot contain any aggregate functions.
• Function: The type of aggregation to be applied, like Sum, Avg, Count etc.
• Scope: The scope in which the aggregation to be performed. It can be a group, a data region, a dataset etc. and it cannot be an expression.

Also notice that, for QTD Actual and QTD Target columns, we have specified the scope as “rgrpQuarter” which is a group on the Quarter field since we want the aggregation (sum) to happen within this group and we want the aggregation to reset for each new instance of the group (Q1, Q2 etc.). Similarly, for the YTD Actual and YTD Target columns scope is specified as “rgrpYear”.

Now, apply some basic formatting as per the below table.

Now preview the report and the report should look something like below.

In the above output, QTD Actual in the second row (2011-Q1-February) is the sum of values in 1st and 2nd rows of the Monthly Actual column, which is the Actual Total Sales at the end of 2nd month (February) for the current quarter (2011-Q1). YTD Actual in the second row (2011-Q1-February) is the sum of values in 1st and 2nd rows of Monthly Actual Column, which is the Actual Total Sales at the end of 2nd month (February) for the current year (2011).

QTD Actual in the third row (2011-Q1-March) is the sum of values in the 1st, 2nd and 3rd rows of the Monthly Actual Column, which is the Actual Total Sales at the end of the 1st Quarter of 2011. Similarly, the values in the 6th, 9th, and 12th row under QTD Actual column represent the Actual Total Sales and the data under the QTD Target column represent the Target Total Sales at the end of 2nd, 3rd, and 4th quarter respectively. Also notice that, the values under QTD Actual and QTD Target columns get reset on the 4th, 7th, and 10th rows as those rows represent the beginning of the next quarter.

YTD Actual in the last row (2011-Q4-December) is the sum of all the values for January through December 2011, which is the Actual Total Sales at the end of year 2011.

Summary: At any given point in time, if you view the data in any of the rows, it shows the Actual and Target values for that particular month, along with the cumulative values for the quarter or year based on the month. This can help the users easily compare the Actual Values against the Target Values at any given point, to see how they are meeting the defined target and can help them plan accordingly.

The SSRS RunningValue Running Aggregate function makes it so easy to show the cumulative aggregates. In the above report, if you are interested in comparing the Average Actual Sales against Average Target Sales, then you can replace “Sum” with “Avg” in the RunningValue function in the cells D3, D4, D5, and D6.

Next Steps

• Apply some conditional formatting to the above report to indicate whether Actual Sales is Less Than, Equal To, or Greater Than the Target Sales.
• Explore other advanced functions in SSRS:
  • RowNumber Running Aggregate Function
  • Lookup Functions
• Check out other SQL Server Reporting Services related tips