# Sum Aggregate On Last Value

• ### 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:
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 Tuesday, June 6, 2017 9:11 PM
Tuesday, June 6, 2017 9:10 PM

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 Wednesday, June 7, 2017 11:37 AM
Wednesday, June 7, 2017 2:39 AM

### All replies

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 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