Need to Aggregate on the Last Month for a Grouped Report

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 for the current month total aggregate. My Date value is the 1st day of the month and my parameter is the last day of the month, so I needed a way to match the 2 that is why there is the date addition. The jist is to try and match the current month, which is a parameter to the date column:

=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 only works if the query that returns the dataset returns ONLY the current month. If the query returns all of the months in the year it shows 0's. 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.

Answer 1

You can do this using the built-in grouping functions without any fancy expressions.

Add a row group by month. Filter the row group to the current month. Add a column group by Item Name. Add a row outside and below the row group to get your yearly totals. The expressions will all simply be a sum of the Qty. The report will take care of summing the values within each group scope.

Answer 2

Actually, using grouping as suggested alone doesn't work. I was already using groups in my matrix. The issue is that I needed a different form of grouping within the same column and I needed to restrict the grouping to different date ranges. My problem was that I had the SUM in the wrong position in my formula.

The expressions for the yearly total should have been:

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

Then, based on the dataset, if the current month is "Mar 2017" I 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))

These examples are using my sample dataset in this question. Since I couldn't hard code dates, wanted to use parameters, and needed to do a calculation this is what I ACTUALLY used:

SUM(iif(DateAdd(dateinterval.Day,-1,DateAdd(dateinterval.Month,1,Fields!REL_DATE.Value))= Parameters!ReportingDate.Value,
Fields!Total_OnTime.Value,0))/SUM(iif(DateAdd(dateinterval.Day,-1,DateAdd(dateinterval.Month,1,Fields!REL_DATE.Value))= Parameters!ReportingDate.Value,
Fields!Total.Value,0))

Hope this helps someone else.