Manipulate a dataset in a Gauge
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. Any thoughts? SQL Server 2008R2 Thank you.
September 24th, 2010 9:30pm

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: =Sum(Fields!Value1.Value) 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, -LukaszGot 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.
Free Windows Admin Tool Kit Click here and download it now
September 29th, 2010 7:24pm

Thanks. That solution is what I needed. G
September 30th, 2010 4:43pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics