CODEWITHSUNDEEP
reporting-servicesssrs-2008reportingservices-2005
DtotheG
DtotheG

Reputation: 1307

SSRS - Subtracting column values without knowing row details

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

Upvotes: 0

Views: 2286

Answers (2)

Anup Agrawal
Anup Agrawal

Reputation: 6669

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.

Upvotes: 1

Shruti Kapoor
Shruti Kapoor

Reputation: 1116

you may try using LookupSet or MultilookupSet

Upvotes: 0

Related Questions