locked
Sum Aggregate On Last Value RRS feed

  • Question

  • I have a dataset that looks like the following:

    Location Category Item Name Month QTY
    NY Hardware Screw Jan 2017 100
    NY Hardware Screw Feb 2017 50
    NY Hardware Screw Mar 2017 75
    NY Hardware Bolt Jan 2017 30
    NY Hardware Bolt Feb 2017 90
    NY Hardware Bolt Mar 2017 50
    CA Hardware Screw Jan 2017 100
    CA Hardware Screw Feb 2017 50
    CA Hardware Screw Mar 2017 75
    CA Hardware Bolt Jan 2017 30
    CA Hardware Bolt Feb 2017 90
    CA Hardware Bolt Mar 2017 50

    My report needs to look like the following:

    Hardware Screw Bolt
    Current Month Total 150 100
    Yearly Total 450 340

    I need a way to limit the current month total to ONLY the current month but aggregate the values for the yearly total. I've tried using LAST in the aggregate but you can't. I've tried the following:
    =iif( DateAdd(dateinterval.Day,-1,DateAdd(dateinterval.Month,1,Fields!Sale_DATE.Value)) = Parameters!ReportingDate.Value, iif(isnothing(sum(Fields!Total.Value)),"",sum(Fields!Total.Value)),sum(0))
    but it doesn't work. I need a way to filter the cells so they aggregate the values correctly

    If I limit my report to only the current month I can't get the yearly aggregate and if I select all of the months I can't get the current month total.


    • Edited by Whalensdad Tuesday, June 6, 2017 9:11 PM
    Tuesday, June 6, 2017 9:10 PM

Answers

  • Hi Whalensdad,

    Based on my test, basically we could use four expressions to realize it. 

    I am using the same dataset with yours. 

    Then I create a table in report designer as below:

    The expressions for the yearly total are 

    =Sum(IIF(Fields!ItemName.Value="Screw",Fields!QTY.Value,0))
    =Sum(IIF(Fields!ItemName.Value="Bolt",Fields!QTY.Value,0))

    Then, based on your dataset, if the current month is "Mar 2017", we can build the expression for Month Total as 

    =Sum(IIF(Fields!ItemName.Value="Screw" and Fields!Month.Value="Mar 2017",Fields!QTY.Value,0))
    =Sum(IIF(Fields!ItemName.Value="Bolt" and Fields!Month.Value="Mar 2017",Fields!QTY.Value,0))

    Then we will get expected results just like your screenshot. 

    However, if you want the SSRS to automatically calculate the current month, for example, today should be "Jun 2017" referring your format of Month in the dataset, then we can use below two expressions to sum up the QTY for the current month, that is to say, for "Jun 2017". In order to confirm the result, I make a bit change on the original dataset: (replacing "Mar 2017" with "Jun 2017"

    The two expressions for Month total I am using:

    =Sum(IIF(Fields!ItemName.Value="Screw" and

    Fields!Month.Value=Left(MonthName(Month(Now())),3)+str(2017),Fields!QTY.Value,0))

    =Sum(IIF(Fields!ItemName.Value="Bolt" and

    Fields!Month.Value=Left(MonthName(Month(Now())),3)+str(2017),Fields!QTY.Value,0))

    Then I can get the expected results. 

    Hope this helps.

    Best Regards,

    Henry 


    • Marked as answer by Whalensdad Wednesday, June 7, 2017 11:37 AM
    Wednesday, June 7, 2017 2:39 AM

All replies

  • Hi Whalensdad,

    Based on my test, basically we could use four expressions to realize it. 

    I am using the same dataset with yours. 

    Then I create a table in report designer as below:

    The expressions for the yearly total are 

    =Sum(IIF(Fields!ItemName.Value="Screw",Fields!QTY.Value,0))
    =Sum(IIF(Fields!ItemName.Value="Bolt",Fields!QTY.Value,0))

    Then, based on your dataset, if the current month is "Mar 2017", we can build the expression for Month Total as 

    =Sum(IIF(Fields!ItemName.Value="Screw" and Fields!Month.Value="Mar 2017",Fields!QTY.Value,0))
    =Sum(IIF(Fields!ItemName.Value="Bolt" and Fields!Month.Value="Mar 2017",Fields!QTY.Value,0))

    Then we will get expected results just like your screenshot. 

    However, if you want the SSRS to automatically calculate the current month, for example, today should be "Jun 2017" referring your format of Month in the dataset, then we can use below two expressions to sum up the QTY for the current month, that is to say, for "Jun 2017". In order to confirm the result, I make a bit change on the original dataset: (replacing "Mar 2017" with "Jun 2017"

    The two expressions for Month total I am using:

    =Sum(IIF(Fields!ItemName.Value="Screw" and

    Fields!Month.Value=Left(MonthName(Month(Now())),3)+str(2017),Fields!QTY.Value,0))

    =Sum(IIF(Fields!ItemName.Value="Bolt" and

    Fields!Month.Value=Left(MonthName(Month(Now())),3)+str(2017),Fields!QTY.Value,0))

    Then I can get the expected results. 

    Hope this helps.

    Best Regards,

    Henry 


    • Marked as answer by Whalensdad Wednesday, June 7, 2017 11:37 AM
    Wednesday, June 7, 2017 2:39 AM
  • Thanks Henry. I had the SUM in the wrong spot. This is what I needed.
    Wednesday, June 7, 2017 11:37 AM