Search code examples
reporting-servicesreportssrs-2012

SSRS Grouped total sum issue

I'm creating a SSRS report that shows projected sales for the next 12 months, grouped by ProductID. While the detail cells are showing correctly, the group sums for each month are displaying all sales for the 12 months rather than just the related month.

For example, here are all the table values for a single Product:

ProductID EstimatedDate ProjSales
123A      Oct 10/2017   100
123A      Nov 15/2017   100
123A      Dec 01/2017   100
123A      Dec 31/2017   100

However, this is what the report is currently showing for this Product:

Product EstimatedDate   Oct 2017   Nov 2017   Dec 2017
123A    Oct 10/2017     100        0          0
        Nov 15/2017     0          100        0
        Dec 01/2017     0          0          100
        Dec 31/2017     0          0          100
        Total           400        400        400

As seen above, the detailed cells calculate perfectly as each record in the detail section displays a Projected sales value if the Year/Month matches, otherwise it displays 0. Unfortunately, the final row with the "Total" amounts is incorrect as the monthly cells are showing the total of projected sales for all months rather than just the month in question.

Here are my report expressions for December 2017:

Detail Cell: 

=IIF(Year(Fields!EstimatedDate.Value) = 2017 AND Month(Fields!EstimatedDate.Value) = 12, Fields!ProjSales.Value, 0)

Grouped Cell 

=IIF(Year(Fields!EstimatedDate.Value) = 2017 AND Month(Fields!EstimatedDate.Value) = 12, SUM(Fields!ProjSales.Value), 0)

Any idea how I can change the grouped expression to retrieve the projected sales for each month?

Edit : format code

Solution

  • Use a Matrix. Add a column group and group by year then month. Then simply have ProjSales in the detail row group and SUM(ProjSales) in the Group total. There is no need to use expressions to calculate the values. The column group captions will have to be expressions in order to pull out the month and year from the EstimateDate column. If you have the stored dates in a more conventional format YYYYMMDD etc then it would be easier as I'm not sure any DATE functions will recognise the format you show above.

    If you need a more detailed answer let me know. I'm not able to do anything more at the moment.