none
How to insert group/grand totals for 'non-suppressed' records only using SSRS 2008 R2

    Question

  • I am trying to insert totals only for the non suppressed records in my report by it shows totals for whole group/dataset (ie including the 0 variance records).

    My report compares 'Contracted Hours' and 'Actual Hours' and displays days where there are variances > or < than 0.

    How can I show totals for the non 0  variances only?

    Friday, May 03, 2013 5:53 AM

All replies

  • Hi Codarosh,

    There are a couple of ways to do this.

    Are you interested in showing the 0 variance records on the report at all? If not these should be filtered out, by adding a filter to your dataset, as such it will not include these records in your totals.

    If you still want show them in the report, but don't want your totals to include those rows where variance=0, you need to amend your total expression to something like:

    =SUM(IIF(ContractedHours-ActualHours=0,0,FieldValueYouWantToSum))


    • Edited by Tom Jenkin Friday, May 03, 2013 10:06 AM
    • Marked as answer by Codarosh Monday, May 06, 2013 3:55 AM
    • Unmarked as answer by Codarosh Monday, May 06, 2013 6:11 AM
    Friday, May 03, 2013 10:05 AM
  • Hi Tom,

    Thanks for your post. I do not want to show the 0 variance records and have filters in place which omits the 0 variance records.

    I managed to get the totals for 'group2' since posting but now struggling with the grand1 and the report grand totals.

    my report format is something like this...

    Group1   group2     Date      ContractHrs   ActualHrs     Variance

                                   day1           99                  99               99

                                   day2         999                  99              999

                   group 2 total.         9999                999            9999

    group1 total                          9999               9999           9999

    Grand total                          99999              9999           99999

    I used simple code to add the totals only for the records but do you know if there is a better/easier way?

    Thanks

    Monday, May 06, 2013 3:54 AM