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?
Thank you.