Search code examples
sumaggregatessrs-2012

Summing the value of the aggregate function Last()

enter image description hereI am trying to design a report in ssrs that returns the last opening stock for each product in the dataset. To achieve this I used

=Last(Fields!CustProdAdj_new_openingstockValue.Value).

This works fine. But where I encountered a problem is in getting the sum of all the opening stock for each product. I tried using

=sum(Last(Fields!CustProdAdj_new_openingstockValue.Value))

but I got the error message [Error on Preview]

enter image description here

Please is there another way to go about this

I have tried using aggregate(), runningValue(), to no avail

This is the dataset

enter image description here

This is the report layout

enter image description here

On previewing having used max()

Solution

  • This is probably easier to do directly in the dataset query but assuming you cannot change that, then this should work...

    This assumes your data is ordered by the CustProdAdj_createdon column and that this is a date, datetime or some other ordered value, change this bit if required.

    =SUM(
     IIF(Fields!CustProdAdj_createdon.Value = Max(Fields!CustProdAdj_createdon.Value, "MyRowGroupNameHere"),
         CustProdAdj_new_openingstockValue,
         0)
    )

    Change the MyRowGroupNameHere to be the name of the rowgroup spelled exactly as it is in the rowgroup panel below the main design panel. Case sensitive and include quotes.

    What this does is, for each row within the rowgroup "MyRowGroupNameHere", compare the CustProdAdj_createdon date to the max CustProdAdj_createdon across the rowgroup. . If it is the same then return the CustProdAdj_new_openingstockValue else return 0.

    This will return the value required on only 1 record within the group.

    For example, if you had 1 row per day then only on the last day of the month would a value be returned other than 0 because only the date of the last record would match the maximum date within the group.

    Then it simply sums the results of this up.