none
Manipulate a dataset in a Gauge

    Question

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

    Friday, September 24, 2010 6:30 PM

Answers

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

    -Lukasz


    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
    Wednesday, September 29, 2010 4:24 PM

All replies

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

    -Lukasz


    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
    Wednesday, September 29, 2010 4:24 PM
  • Thanks.  That solution is what I needed.

     

    G

    Thursday, September 30, 2010 1:43 PM