CODEWITHSUNDEEP
sql-serverreporting-services
Kat B
Kat B

Reputation: 11

Using nested iif statements to subtract two columns of data

Working in Visual Studio 2012 to create a SSRS Business Intelligence Services Report.

Trying to do something like below where based on the value of one column, subtract the value of another column. 

=iif(Fields!ACADEMIC_YEAR.Value=Parameters!ACADEMIC_YEAR.Value, Fields!TotalProspects_Dom.Value-iif(Fields!ACADEMIC_YEAR.Value=Parameters!ACADEMIC_YEAR.Value-1,Fields!TotalProspects_Dom.Value, 0),0)

Basically my table looks like: Visual Studio Table Screencap

essentially the table for the report is two rows, the current academic year and the previous academic year and I have to compare the two. Each row is made of columns representing the data for domestic and international data for a larger group.

Upvotes: 1

Views: 804

Answers (1)

Hannover Fist
Hannover Fist

Reputation: 10860

I think you just need to have separate IIFs to determine the year.

= SUM(IIF(Fields!ACADEMIC_YEAR.Value = Parameters!ACADEMIC_YEAR.Value, Fields!TotalProspects_Dom.Value, 0) 
- SUM(IIF(Fields!ACADEMIC_YEAR.Value = Parameters!ACADEMIC_YEAR.Value - 1, Fields!TotalProspects_Dom.Value, 0)

Upvotes: 1

Related Questions