Search code examples
reporting-servicesssrs-tablixssrs-grouping

Finding difference at group level

I have 3 months of AR data. From SQL I am populating details records for 3 months. In SSRS I am grouping the totals by month and showing only group summary. I want to calculate the variance by difference between current month and previous month. Please see the attached output file.

enter image description here

Could anyone help me on finding the variance at group level. How do I calculate the difference by using previous function?

Is there any way to find individual group sum of the particular field like this one

Sum(Fields!Current.Value, "DataSet1")
Solution

  • Something like this perhaps?

    =Sum(iif(month(Fields!myDate.Value) = month(Max(Fields!myDate.Value)), Fields!myVal.Value, 0)) 
-Sum(iif(month(Fields!myDate.Value) = month(dateadd("M", -1, max(Fields!myDate.Value))), Fields!myVal.Value, 0))

    Where myDate is the date used to group the months on, and myVal is the specific data for the column

    UPDATE

    Using this dataset

    EOM          myValue
09/30/2015   2
10/31/2015   6
11/30/2015   19

    Gives this basic report

    enter image description here

    You can then use this formula (based on the original one in the original answer above) to determine the difference between the last and the penultimate row

    =Sum(
    iif(month(CDate(Fields!EOM.Value)) = month(Max(Fields!EOM.Value)),   
        Fields!myValue.Value, 
        0)
    ) 
 -Sum(iif(month(CDate(Fields!EOM.Value)) = month(dateadd("M", -1, max(CDate(Fields!EOM.Value)))), 
        Fields!myValue.Value, 
        0)
    )

    Then place this in the footer of the tablix as shown

    enter image description here

    This shows the difference between the two rows

    enter image description here

    Is this the behaviour you require? If not please clarify further in the original question as an update/edit.