none
How to create a running sum of 2 expression fields? RRS feed

  • Question

  • I am struggling to get a running sum to work on a report. What I want is the following:

    Product    Qty1   Qty2  Total  RunningTotal
    ABC123    
    100    200   300    300
    DEF321    
    50     100   150    450
    XYZ123    
    -100   -150  -250   150

    The total column is an expression like

    =ReportItems!Qty1.value + ReportItems!Qty2.Value

    For running total I have tried many different things, most of which tell me that it has to be in a header or footer. The obvious expression which I thought would work is

    =ReportItems!Total.Value + previous(ReportItems!Total.Value)

    but that doesnt work.

    Can anyone explain what I am doing wrong here?

    Cheers Dean

    Saturday, May 26, 2012 4:01 AM

All replies

  • Dean,

    I am not quite sure what you call a "Running Total". Is it the sum of Total, up to the current line ? If yes, then there could be an error in your table, because on the last line, it should be 200 (450 - 250).

    Now, why do you use the ReportItems collection ? Wouldn't it be easier to sum the fields which contain the data ? In this case, your Total is :

    =(Fields!Qty1.Value + Fields!Qty2.Value)

    And your RunningTotal is :

    = (Sum(Fields!Qty1.Value) + Sum(Fields.Qty2.Value))

    Tuesday, May 29, 2012 2:40 PM
  • Hi Sygrien,

    You are correct, it should be 200, just a type there when I typed in in :)

    The problem is that Qty1 and Qty1 and not fields, they are expressions based on other fields in the report therefore they do not show up on Fields.

    Regards,

    Dean

    Wednesday, May 30, 2012 1:24 AM
  • Dean,

    In any case, you can't SUM the ReportItems values. So you'll have to go back to the original definition of Qty1 and Qty2 and SUM the Fields.

    Wednesday, May 30, 2012 7:09 AM