none
Get a Running Total (Cumulative) of Totals Row

    Question

  • Afternoon,

    I have a report where I am trying to complete a Monthly / YTD Sales Figures by Calender Year.

    I have managed to get my RunningValue function working fine per month, however I would like to apply the same to my 'Totals'

    Each Month I would like to Cumulatively Add my Totals but because you cannot nest an aggregate function within another I'm stuck can anyone shed some light on this for me please.

    Here is a screenshot of my table as it stands.

    YTDPrem is the following Expression:

    Cheers.

    Monday, January 21, 2013 4:52 PM

All replies

  • Hi Lynchie,

    We can use custom code to achieve this requirement. The sample code would like:
    Dim public nettotal as Double
    Public Function Getvalue (ByVal subtotal AS Double) AS Double
       nettotal = nettotal+ subtotal
       return subtotal
    End Function
    Public Function Totalvalue()
       return nettotal
    End Function

    Then you can use the expression below in the text-box.
    =Code.Getvalue(Fields!Premium.Value)
    =Code.Totalvalue()

    If you have any questions, please feel free to ask.

    Regards,
    Charlie Liao


    Charlie Liao
    TechNet Community Support

    • Marked as answer by Lynchie1987 Tuesday, January 22, 2013 9:31 AM
    • Unmarked as answer by Lynchie1987 Wednesday, May 01, 2013 12:20 PM
    Tuesday, January 22, 2013 8:10 AM
  • Hi Charlie,

    I used your code and then the =Code.Getvalue(Fields!Premium.Value) line in my table like so:

    It only returned the data like:

    Surely it should be the sum of the Year To Date column rather than just the top line?

    Forgive my stupidity.

    Regards,

    James

    Tuesday, January 22, 2013 9:10 AM
  • Nevermind I changed

    =Code.GetValue(Fields!Premium.Value) to =Code.GetValue(Sum(Fields!Premium.Value)) and this seemed to work, hopefully it will next month also.

    Cheers.

    Tuesday, January 22, 2013 9:31 AM
  • Hi Charlie,

    It isn't working on subsequent months, the person in question has only just noticed on the report - please see below:

    

    Any help would be greatly appreciated.

    Wednesday, May 01, 2013 12:20 PM
  • I re-read your message and changed the code a little bit again and now get this:

    Regards

    Wednesday, May 01, 2013 12:24 PM
  • Charlie, 

    Could I get some help on this please? :/

    Cheers,

    James

    Friday, May 03, 2013 12:11 PM