Cumulative Aggregates in SQL Server Reporting Services

By:   |   Comments (11)   |   Related: > Reporting Services Analysis Services


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.
    SQL Server Reporting Services table control


  • 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.
    SQL Server Reporting Services Row Groups

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:

Cell

Label/Expression

Width

GH1 Year 0.5in
GH2 Quarter 0.6in
GH3 Month 0.8in
H1 Monthly Actual 0.6in
H2 Monthly Target 0.6in
H3 QTD Actual 0.6in
H4 QTD Target 0.6in
H5 YTD Actual 0.6in
H6 YTD Target 0.6in
GD1 =Fields!Year.Value 0.5in
GD2 =Fields!Quarter.Value 0.5in
GD3 =Fields!Month.Value 0.5in
D1 =Fields!ActualSales.Value 0.5in
D2 =Fields!TargetSales.Value 0.5in
D3 =RunningValue(Fields!ActualSales.Value,Sum,"rgrpQuarter") 0.5in
D4 =RunningValue(Fields!ActualSales.Value,Sum,"rgrpQuarter") 0.5in
D5 =RunningValue(Fields!ActualSales.Value, Sum, "rgrpYear") 0.5in
D6 =RunningValue(Fields!TargetSales.Value, Sum, "rgrpYear") 0.5in

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.

Cell

Text Align

Vertical Align

Font Style

Format

Width

GH1 Center Middle Arial, 10pt, Bold Leave Blank 0.5in
GH2 Center Middle Arial, 10pt, Bold Leave Blank 0.6in
GH3 Center Middle Arial, 10pt, Bold Leave Blank 0.8in
H1 Center Middle Arial, 10pt, Bold Leave Blank 0.65in
H2 Center Middle Arial, 10pt, Bold Leave Blank 0.65in
H3 Center Middle Arial, 10pt, Bold Leave Blank 0.65in
H4 Center Middle Arial, 10pt, Bold Leave Blank 0.65in
H5 Center Middle Arial, 10pt, Bold Leave Blank 0.65in
H6 Center Middle Arial, 10pt, Bold Leave Blank 0.65in
GD1 Left Middle Arial, 10pt, Bold Leave Blank 0.5in
GD2 Left Middle Arial, 10pt, Bold Leave Blank 0.6in
GD3 Left Middle Arial, 10pt, Bold Leave Blank 0.8in
D1 Right Middle Arial, 9pt, Normal #,# 0.65in
D2 Right Middle Arial, 9pt, Normal #,# 0.65in
D3 Right Middle Arial, 9pt, Normal #,# 0.65in
D4 Right Middle Arial, 9pt, Normal #,# 0.65in
D5 Right Middle Arial, 9pt, Normal #,# 0.65in
D6 Right Middle Arial, 9pt, Normal #,# 0.65in

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


SQL Server Reporting Services Final Report

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Dattatrey Sindol Dattatrey Sindol has 8+ years of experience working with SQL Server BI, Power BI, Microsoft Azure, Azure HDInsight and more.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Thursday, November 20, 2014 - 6:30:58 PM - Sonya Back To Top (35362)

Hi there - nice work.

Just wondering if you can help me out:  I'm new at creating SSRS reports.

I need to create a report based on Region and its current year sales vs previous year sales - I have list of all the transactions from all the years and have already created data set.  I would really appreciate it if you can assist me in creating this.

 

Thank You

Sonya


Sunday, December 29, 2013 - 11:38:36 PM - Ruhul Back To Top (27909)

I need ***ulative aggregate after each column for my report. I have a dataset with following data samples.

Data    
ProductName Transdate Quantity
A 21-Nov-13 100
A 1-Nov-13 50
A 1-Dec-13 40
A 1-Jan-13 150
B 11-Nov-13 30
B 2-Dec-13 20
B 13-Dec-13 10
B 12-Jan-13 80

 

I want a report which look like below. That means Product wise each month total and ***ulative total. I am able to get month wise total value. Only need suggestion for month wise ***ulative total.


Report            
Product Nov'13 Cumulative Total Dec'13 Cumulative Total Jan'13 Cumulative Total
A 150 150 40 190 150 340
B 30 30 30 60 80 140

Wednesday, December 11, 2013 - 9:40:10 AM - Ola Wale Back To Top (27763)

 

Hello Dat,

  Am working on a SSAS tabular report and I need to calculate the difference between the sales amount of two months and divide it by the previous month. Is there any aggregation function  I use here to perform this task.

Month   Amount         

Jan         2000                   (feb-jan)/jan

Feb         3000

March      4000


Monday, November 25, 2013 - 11:31:46 PM - Don Back To Top (27605)

Rewritten, should be easier to understand. See prvious post.

 

The bold lines are the groups for Invoice and Customer, and Salesperson. Sorry it was hard to format in the email. So the invoice has some calculations that do not come from the line items (Credit Card Costs etc.) so that's why I can't just sum the line items and really need to summarize on the group expression on the Invoice . I thought of running value but couldn't get that to work.

Any thoughts would be great.

Don


Monday, November 25, 2013 - 11:28:27 PM - Don Back To Top (27604)

 

I am working with Sales data from line items and header information. There are 3 groups, InvocieNumber, CustomerNUmber, SalespersonID. I need to summarize on a calculated field or expression that I created on the group header line. I don't want to summarize on all the detail lines.

INvoice   CustomerID   Salesperson  ITem  ExtPrice  Expression

100        C1               S1                I100   $1,000  

100        C1               S1                I200   $2,500  

100        C1                                                           $3,000

200        C1               S1                I100   $3,000  

200        C1                                                            $2,500

           C1     $5,500

250        C2               S1                I100   $1,000  

250                                                                            $800

275        C2               S1                I200   $1,500

275                                                                       $1,100

         C2                   $1,900

S1     $7,400

The bold lines are the groups for Invoice and Customer, and Salesperson. Sorry it was hard to format in the email. So the invoice as some calculations that do not come from the line items (Credit Card Costs etc.) so that's why I can't just some the line items and really need to summarize on the group summaries themselves. I thought of running value but couldn't get tha to work.

Any thoughts would be great.

Don

 

 


Sunday, March 31, 2013 - 11:14:37 PM - ian Back To Top (23094)

Good Acticle!

Can I ask, if you have already encounter the same problem like mine.

I want to sum all the runningValue in the same Tablix1. How could I do it...?

 

SAMPLE

Day            RunningValue

1                        3

2                        7

3                        5

TOTAL>>            15

 

What should i use to sum the running Value so that I could get the total..? Any Idea how...? thankz

 


Thursday, January 31, 2013 - 9:02:53 AM - santhu Back To Top (21835)

hiya i had a small query ....

 in my table i got date/mm/year column and total sales. The date column consists of different months and i want sum the amount of sales only for i month with out using parameter in ssrs table report.


Friday, October 19, 2012 - 3:44:05 PM - LMW Back To Top (20000)

Very good article for grouping explanation!


Saturday, May 19, 2012 - 8:45:05 AM - Neelam Back To Top (17548)

Good Article!!


Saturday, January 21, 2012 - 11:58:27 AM - Dattatrey Sindol Back To Top (15724)

Hi Don,

In some scenarios, it might be beneficial to perform the rollup/aggregation at the reporting layer instead of database layer. For example, say you are getting the monthly data into the report which is being displayed in multiple formats like 1. A line chart (Sales Amount vs. Sales Month); 2. ***mulative Sales amount for every month like the one demonstrated in this tip etc.. In this kind of scenario, performing the aggregation in the report layer gives additional benefit in terms of performance since you are fetching the data only once and you are using it to represent it in different formats with different levels of aggregations.

Hope that answers your question.

Best Regards,

Dattatrey Sindol (Datta)


Friday, January 20, 2012 - 11:12:57 AM - Don Goodman Back To Top (15715)

I was surprised not to find aggregating code in the sample. Normally when aggregating sales and marketing data you would would ROLLUPs, CUBEs and code involving ROW_NUMBER OVER, RANK() OVER, DENSE_RANK or NTILE.

These tools are there specifically to provide aggregations to sales and marketing numbers. They should be used.















get free sql tips
agree to terms