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
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
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
