SSRS Using Sum Based On Condition From Multiple Procedures?

Question

I am creating a report and trying to Get a Sum combining values from multiple tables and can't figure it out and am beginning to wonder if it is even possible?

I have 2 stored procedures returning the same data with different time frame. One stored procedure is returning data based of Amounts in the past 12 months. The other procedure is returning sum of all Amounts for 13+ months. I have a sum for both to get the total collected for 1 year and total for 1 year+. I want to combine these total to get total for all years. The problem I am facing is totals are grouped by classification and name.

I have the following example data:

Date        Class  Name  Earned Count  Earned Amount  Collected Count  Collected Amount
Sept-11     Red    Jack      5         10.50           2                54.20                  
Sept-11     Red    Bill      2         22.75           4                120.58
Sept-11     Blue   Jill      9         43.23           25               443.32  
Sept-11     Green  Bob       84        45846.33        62               4843.22
Sept-11     Green  Pam       2         13.55           1                23.23
Sept-11     Green  Tammy     32        2332.22         443              33232.33

Aug-11      Red    Jack      1         23.50           2                33.20                  
Aug-11      Red    Bill      2         52.75           4                323.22
Aug-11      Blue   Jill      3         43.23           23               33.32  
Aug-11      Green  Bob       4         46.33           22               653.22
Aug-11      Green  Pam       5         13.55           1                46.23
Aug-11      Green  Tammy     6         1132.22         111              89.33

Totals Looks Like the following:

Date      Class  Name  Earned Count  Earned Amount  Collected Count  Collected Amount   
Sub-Total Red    Jack   33           ##.##          ##               ##.##      
                 Bill   ##           ##.##          ##               ##.## 
          Blue   Jill   ##           ##.##          ##               ##.## 
          Green  Bob    ##           ##.##          ##               ##.## 
                 Pam    ##           ##.##          ##               ##.## 
                 Tammy  ##           ##.##          ##               ##.##   

Year+     Red    Jack   4344         ##.##          ##               ##.##      
                 Bill   ##           ##.##          ##               ##.## 
          Blue   Jill   ##           ##.##          ##               ##.## 
          Green  Bob    ##           ##.##          ##               ##.## 
                 Pam    ##           ##.##          ##               ##.## 
                 Tammy  ##           ##.##          ##               ##.##

So I want to combine Year+ total to year total for each class and name. For example, I want to find Jack's Earned Count for a year and year+ so it would be 33 + 4344, except in reality I want to do this for everyone for all values listed.

In SSRS, I have grouped by class and name and have totals for year+ and I am trying to add the sum from year but It only gets the total for everyone and not just for one person and their class. I am using the following expression for one field:

=Fields!EarnedCount.Value + Sum(Fields!EarnedCount.Value, "MyEmployeeRpt")

Is there a way to take a sum based on a value like only get the Sum when Name = Bill and Class = Red. Another issue I am facing is that I don't know how many names or classes I might have. Any thoughts? Thanks for the help!

Matthew Warman Matthew Warman · Accepted Answer · 2012-11-26T11:54:14

SSRS 2008 R2

Firstly if you are using SSRS 2008 R2, the LOOKUP function is well worth a look:

Lookup(source_expression, destination_expression, result_expression, dataset)

SSRS 2008

If you are SSRS 2008, there are two solutions:

Rewrite SQL Query

The SQL could be extracted from the stored procedures and then the queries joined, extending the time frames to span both and updating the report to filter to the required time frames, meaning that you could use the total of the whole dataset to achieve required result.

This may not be possible due to the natures of the stored procedures.

Use a Sub Reports

This solution requires you to make a sub report which you pass the Class, Name and Earned Count, the sub report then returns the combined value.

Create a new Sub Report with Parameters for Class, Name, and Earned Count
Add a new tablix with the SQL to return Year+
Either Update the SQL to Filter on Parameters (this may not be possible due to it being stored procedure) Example:
```
...
Where [Class] = @Class and [Name] = @Name
```
OR using Filters on Tablix, filtering by Class and Name where they equal there respective parameters
Delete all columns and rows of the Tablix apart from one field, add the following expression to return the Sum of Earned Count + value passed by the parameter
```
=Sum(Fields!EarnedCount.Value)+Parameters!EarnedCount.Value
```
Resize the Report to be same size as tablix
Remove border formatting (end up with a double border otherwise)
Return to the original report add the Sub Report as a field in your tablix where you want to see a combined total passing parameters Class, Name, and EarnedCount
Add the border to the sub report

SSRS Using Sum Based On Condition From Multiple Procedures?

1 Answers

SSRS 2008 R2

SSRS 2008