Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Running Totals for Each Group

A while ago, I posted a blog on how one could perform running totals using Reporting Services and a user recently asked how he could do this within groups of data. So, I thought why not re-address this with another blog. Business Intelligence Design Studio (BIDS) allows us to create running totals for entire data sets and within groups of data as well. With that being said, I am going to show you how you can create this running tally effect within each group.

 

If you want to start a new report from scratch, I have provided a sample script with fifteen records containing three groups and quantity values that can be totaled up within each group. Script 1 below creates a temporary table containing three columns (SalesOrderID, GroupDesc and QtySold). The two main columns that we care about are actually the GroupDesc and QtySold. The quantity sold values will be summed up for each of the groups contained in the group description column at the reporting layer below.

 

Script 1: Sample Records

CREATE TABLE #RunningValueGroups

(

      SalesOrderID      INT PRIMARY KEY IDENTITY(1,1)

      , GroupDesc       VARCHAR(3)

      , QtySold         INT

)

GO

 

INSERT INTO #RunningValueGroups SELECT 'DDT',10

INSERT INTO #RunningValueGroups SELECT 'SQR',20

INSERT INTO #RunningValueGroups SELECT 'BTK',40

INSERT INTO #RunningValueGroups SELECT 'DDT',20

INSERT INTO #RunningValueGroups SELECT 'SQR',10

INSERT INTO #RunningValueGroups SELECT 'BTK',30

INSERT INTO #RunningValueGroups SELECT 'DDT',70

INSERT INTO #RunningValueGroups SELECT 'SQR',80

INSERT INTO #RunningValueGroups SELECT 'BTK',10

INSERT INTO #RunningValueGroups SELECT 'DDT',20

INSERT INTO #RunningValueGroups SELECT 'SQR',90

INSERT INTO #RunningValueGroups SELECT 'BTK',30

INSERT INTO #RunningValueGroups SELECT 'DDT',10

INSERT INTO #RunningValueGroups SELECT 'SQR',20

INSERT INTO #RunningValueGroups SELECT 'BTK',50

 

SELECT * FROM #RunningValueGroups

 

DROP TABLE #RunningValueGroups

 

If you are following along and have a data set created to return the above results, go to your design surface and drag over a Tablix. Then drag the SalesOrderID and the QtySold columns over onto the first two columns of your Tablix. Then drag and drop the GroupDesc field into the row group pane, just above the [details] group. Your results should look something like that shown in figure 1 below (without the expression of course). This brings us to the last step in this process! Right click in the empty textbox in the far right column and choose “Expression…”

 

Enter this expression into the expression window:

=RunningValue(Fields!QtySold.Value,SUM,"GroupDesc") 

 

This is where the magic is occurring! What this expression is doing is telling Reporting Services to create a running tally (SUM) of the values (Fields!QtySold.Value) within the group called “GroupDesc”. If you changed the name of the group, my expression above will not work. So, if you do change it, please be sure to change the expression accordingly.

 

Figure 1: Screenshot in Design Mode

Design Mode 

 

Minus all of the extra formatting, your results when executing this report should be similar to figure 2. I have red square around each set of Quantity Sold values that make up the “Running Value Within Group” column. As an example, Sales Order ID 3 and 6 added together gives us the 70 shown as the running total. Then if we sum up Sales Order ID 3, 6 and 9, we now have 80. So on and so forth.

 

When a new group is shown, the running value is reset and as such, re-aggregated. You can see this in Sales Order ID 1 and 2.

 

Figure 2: Results Containing Running Values

Results in Preview Mode

 

For your convenience, you can download this example here. I hope that you have enjoyed this post. If you did, please take just a moment to rate it below! Also, if you don’t already, please be sure to follow me on twitter at @briankmcdonald. Also note that you can subscribe to an RSS feed of my blogs here.

 

Until next time, thank you for reading,

 

 

Brian K. McDonald, MCDBA, MCSD
Business Intelligence Consultant – Pragmatic Works

Email: bmcdonald@pragmaticworks.com | Blogs: SSRSGeek | SQLServerCentral | BIDN

Twitter: @briankmcdonald

LinkedIn: BrianKMcDonald

Comments

Posted by Anonymous on 12 October 2010

Pingback from  Twitter Trackbacks for                 SQL Server Central, Running Totals for Each Group - BrianKMcDonald         [sqlservercentral.com]        on Topsy.com

Posted by Brian K. McDonald on 22 October 2010

The link for RDL didn't come across. However, you can download a zipped up rdl file in this path:

www.sqlbigeek.com/.../RunningValueForGroup.zip

Please let me know if you have any problems with the file.

Thanks

Leave a Comment

Please register or log in to leave a comment.