CODEWITHSUNDEEP
reportbuilder3.0
Morallis
Morallis

Reputation: 151

Report Builder - Difference between values in Grouped Columns

The title sums it all up, I have values for various invoices over a period of two months. The is grouped in a table in Report Builder with Row Grouping of Company Name and Column Group of Month.

So it looks something like this:

        May     April   Difference
Bob     100     100     0
Mary    200     250     -50
Sue     230     100     130
Totals  530     450

Currently I'm working out the 'Difference' column like this: =Last(Fields!Monthly_Inv_Amt.Value) - First(Fields!Monthly_Inv_Amt.Value)

I would like to add a sum total for the difference to show the total change between the two months of invoicing however First and Last cannot be specified as nested aggregates.

I'm sure there is a 'simple' solution to this however I'm just not clicking.

Thanks in advance, Dane

Solution:

I added a Calculation field to my dataset, I had to calculate this between two different rows so I did the following. I didn't know about this so hopefully it will help someone else out.

,Table1.Monthly_Inv_Amt - (SELECT Table2.Monthly_Inv_Amt
   FROM AGR_Invoice_Amt AS [Table2] 
    WHERE (Table2.Month_Nbr) = (Table1.Month_Nbr - 1)
     AND Table2.AGR_Header_RecID = Table1.AGR_Header_RecID) AS [DIFF]

Upvotes: 0

Views: 1636

Answers (1)

Ross Bush
Ross Bush

Reputation: 15185

You can add a Calculated field to your dataset and set its value to the difference. Then use the sum of the calculated field--> SUM(Fields!CALCFieldDifference.Value)

You can also add a group variable to the user group and set the value to the difference and, I think, you can sum the variable --> SUM(Variables!varGroupUserDifference.Value) However, you may be preventing from aggregating a group value down, I haven't tried it.

Upvotes: 1

Related Questions