locked
Sum Min Values? RRS feed

  • Question

  • I have a report with one row group. In one of the fields I am displaying a Min value

    In the totals row of my report is there a way I can Sum up all of the Min values?

    Category Limit
    X                3
    Y                4
    Z                2
    Total          9  <<<This is what I cannot do

    So something like: =Sum(Min(Fields!Limit.Value)) but this doesn't work

    Thanks to any help in advance!

    Wednesday, February 20, 2013 2:51 PM

Answers

  • Hi,

    1. Add this custom code to your report properties.

    Public Dim totval as integer
    
    Public Function AddValue (byval val as integer) as integer
          totval=totval+val
          return val
    End Function
    
    Public Function GetTotValue()
          return totval
    end function

    2. In your textbox where you have the Min(Fields!Limit.Value), replace it with =Code.AddValue(Min(Fields!Limit.Value))

    3. In the textbox where you want the sum of the min value, enter this expression =Code.GetTotValue()



    Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

    k r o o t z

    • Marked as answer by Tom Jenkin Monday, February 25, 2013 4:06 PM
    Wednesday, February 20, 2013 5:15 PM

All replies

  • Hi Tom,

    What is the expected results:
    Why do you want to Sum, min values ? do you mean sum(Min values) by group ?

    Regards


    dj's

    Wednesday, February 20, 2013 3:32 PM
  • Hi DJ's,

    So the dataset behind my report is like:

    CAT    LIMIT
    X        9
    X        7
    X        3
    X        5
    Y        7
    Y        14
    Y        23
    Y        4
    Z        10
    Z        7
    Z        2

    The report is grouped by Cat and shows the Min Limit for each cat:

    CAT    LIMIT
    X        3
    Y        4
    Z        2

    I want in the total row to show a total limit of 8. If I just did Sum(limit) it would give me the total of all rows from the dataset, this is why I just want to total the min rows.

    Hope this makes sense!

    Wednesday, February 20, 2013 3:41 PM
  • Yes, It make sense !

    I do not think that SSRS allow you to do that, because it does not allow you to do SUMs on Placeholders.

    However you can do with Sql

    Regards


    dj's

    • Proposed as answer by Djallal.E Wednesday, February 20, 2013 4:23 PM
    Wednesday, February 20, 2013 4:23 PM
  • Hi,

    1. Add this custom code to your report properties.

    Public Dim totval as integer
    
    Public Function AddValue (byval val as integer) as integer
          totval=totval+val
          return val
    End Function
    
    Public Function GetTotValue()
          return totval
    end function

    2. In your textbox where you have the Min(Fields!Limit.Value), replace it with =Code.AddValue(Min(Fields!Limit.Value))

    3. In the textbox where you want the sum of the min value, enter this expression =Code.GetTotValue()



    Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

    k r o o t z

    • Marked as answer by Tom Jenkin Monday, February 25, 2013 4:06 PM
    Wednesday, February 20, 2013 5:15 PM
  • Thanks for the reply,

    I have put the Code.GetTotValue() in the Totals row of the report (which appears at the very end)

    However the report runs across 7 pages, and it seems to show the total for the rows on the last page only?

    Is the code re-setting when you go onto a new page?

    Wednesday, February 20, 2013 5:27 PM
  • Hi Tom,

    No, it is not resetting. The AddValue gets called everytime the Min value is displayed and that min value is added to the public variable totval. The total only showing at the end of your report is dependent on the placement of your textbox. Even if you hardcode "HELLO" there, it will still appear at the end of your report.

    Isn't it adding what it's supposed to add? It should show you the sum of all the MIN values per group from page 1 to page 7 at the end of page 7. Were you wanting a subtotal per page?


    Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

    k r o o t z

    Wednesday, February 20, 2013 5:34 PM
  • Hi,

    1. Add this custom code to your report properties.

    Public Dim totval as integer
    
    Public Function AddValue (byval val as integer) as integer
          totval=totval+val
          return val
    End Function
    
    Public Function GetTotValue()
          return totval
    end function

    2. In your textbox where you have the Min(Fields!Limit.Value), replace it with =Code.AddValue(Min(Fields!Limit.Value))

    3. In the textbox where you want the sum of the min value, enter this expression =Code.GetTotValue()



    Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

    k r o o t z

    Hi Krootz,

    I have tested this custom code, we can only get the last page total using the expression =Code.GetTotValue(). To get the expect value, we should add "Shared" when we define the "totval".

    Public Shared Dim totval as integer

    Public Function AddValue (byval val as integer) as integer
          totval=totval+val
          return val
    End Function

    Public Function GetTotValue()
          return totval
    end function

    Regards,


    Charlie Liao
    TechNet Community Support

    Monday, February 25, 2013 6:44 AM
  • Hi Charlie,

    Thanks for the reply.

    Now each time the report is run I'm getting an increased value, as though the variable is being stored permanently somewhere.

    Also interestingly I'm still not getting the correct total even on first run, although on export to excel I do.

    Anyhow what I have done is forced the tablix to fit 1 page and gone with krootz original code and all works well now :)

    Monday, February 25, 2013 4:05 PM
  • For info the variable does get reset every time a new page is loaded, however I have overcome this issue by forcing the report to fit onto 1 page.

    See the last post by Edward on this thread

    Monday, February 25, 2013 4:08 PM