Friday, September 24, 2010 6:30 PM
I have a dataset based on a stored procedure that is returning ID, Amount, Category, SubCategory, PayType. I am using this same dataset for about 5 other gauges on the same page by using filters on the gauge to categorize and sum up amounts for me based on Category and SubCategory. I have one gauge that I want to use this same dataset for, however in this case I want to basically filter by Category X and then subtract the Sum(PayType1) from Sum(PayType2). I realize the below formula doesn't work, but an example of what I wanted is sort of:
=SUM(IIF(Fields!PayType.Value, "Dataset")="Pledge", Fields!Amount.Value, Fields!Amount.Value*-1.0)
So I want to subtract all the 'payment' PayTypes from the 'pledge' PayTypes. Is there a way to do this with the dataset I have already called?
I realize that I can create another dataset and do the calculations there and have it returned, and this may even be the better way. I just wasnt sure if it took less resources to just filter the dataset I already have returned, or to hit the server with another stored procedure call.
SQL Server 2008R2
Wednesday, September 29, 2010 4:24 PMModerator
Hmm.. you could add a IsPledge calculated field to the data set (which will not break the other data regions in your report). Make the value 0 or 1. Then you can do something like:
=Sum(Fields!Value1.Value * Fields!IsPledge.Value)
To get non-Pledge totals:
=Sum(Fields!Value1.Value * iif(Fields!IsPledge.Value = 1, 0, 1))
To get the Total:
These expressions work starting in SQL 2008 R2 and won't work before that... since we made updates to how Aggregates work to allow expressions within aggregates. In prior releases, you'd need to do this work win the Query itself and return separaet Fields that you would sum individually.
Hope this helps,
Got a bug or feature request? Tell me about it at http://connect.microsoft.com. This posting is provided "AS IS" with no warranties, and confers no rights.
- Marked As Answer by spattagg Thursday, September 30, 2010 1:41 PM
Thursday, September 30, 2010 1:43 PM
Thanks. That solution is what I needed.