SSRS - Subtracting column values without knowing row details

Question

Got a question regarding SSRS. Using 2005 version.

I've got 2 tables in my report, one with the current months data and one with data for the last 3 months.

The last 3 months table always returns 3 rows, 1 row for each month. What I want to do is subtract a value from the 2nd oldest month, basically it will always be row 2 of the table with the equivalent column in the current months table. Hope this kind of makes sense.

I will use an example here;
Table 1
Month Value Var
August 5 ?

Table 2
Month Value
July 4
June 7
May 10

I want to use the Value in Table 1 and subtract the Value of the June column in Table 2 therefore 5 - 7, returning the value -2.

There is a month parameter so the Tables data would change depending on the month but it was always be the 2nd rows Value column that I want to use.

Is this possible in SSRS?

Thanks

Is this two different datasets? What does the underlying data look like to get your results above? You're showing the end results which is good but how are these values calculated from your underlying data? — Ian Preston

Anup Agrawal Anup Agrawal · Accepted Answer · 2013-08-09T18:55:48

Method 1: It is better to solve this in the SQL (dataset) then in the report design.

If data comes from two different tables then

Select C.Month, C.Value, (C.Value - L.Value) AS Var
From CurrentMonthData C 
Left JOIN (Select * Last3MonthData where Month = CurrentMonth - 2) L
ON 1=1

If data comes from same table

Select C.Month, C.Value, (C.Value - L.Value) AS Var
From (Select * MonthData Where Month = @CurrentMonth) C 
Left JOIN (Select * MonthData where Month = @CurrentMonth - 2) L
ON 1=1

Method 2: If you prefer to do it in the Report Itself you can use ReportItems collection.

In the variance text box value put

=ReportItems!CurrentMonthValueTextBox.Value - ReportItems!Prev2MonthValueTextBox.Value

HTH.

SSRS - Subtracting column values without knowing row details

2 Answers