SQLTeam.com | Weblogs | Forums

Tidy up expression


#1

Hi all,

I have the following expression

=iif(Fields!CLASS.Value=1,DateAdd("yyyy",6,Fields!NDT_Test_Date.Value),iif(Fields!CLASS.Value=2,DateAdd("yyyy",3,Fields!NDT_Test_Date.Value),iif(Fields!CLASS.Value=3,DateAdd("yyyy",1,Fields!NDT_Test_Date.Value),iif(Fields!CLASS.Value=4,Fields!NDT_Test_Date.Value,iif(Fields!CLASS.Value=5,Fields!NDT_Test_Date.Value,Fields!NDT_Intervention_Date_1.Value)))))

so it checks field 'class' and if it finds a 1 it adds 6 years, if it finds a 2 adds 3 years etc... and if it finds a null then it looks at another date field. however I am getting some errors...and I am not sure its the best way of writing this...

Would anyone be able to advise if there is a better way?

thanks.

David.


#2

When nesting IIF expressions like this, it's usually easier to apply a little formatting to make it a bit more legible...

=iif(Fields!CLASS.Value = 1, 
	DateAdd("yyyy", 6, Fields!NDT_Test_Date.Value),
	iif(Fields!CLASS.Value = 2, 
		DateAdd("yyyy", 3, Fields!NDT_Test_Date.Value),
		iif(Fields!CLASS.Value = 3,
			DateAdd("yyyy", 1, Fields!NDT_Test_Date.Value),
			iif(Fields!CLASS.Value = 4, 
				Fields!NDT_Test_Date.Value,
				iif(Fields!CLASS.Value = 5, 
					Fields!NDT_Test_Date.Value,
					Fields!NDT_Intervention_Date_1.Value
					)
				)
			)
		)
	)

That said, nothing really jumps out. It looks like you have all your opening and closing parens accounted for and in the correct locations.

What are the errors that you're getting?

As far as a better way... You could move this logic to the SQL code which would allow you to use a CASE expression which would give it a MUCH cleaner syntax...

SELECT 
	ColumnName = CASE 
					WHEN x.CLASS = 1 THEN DATEADD(yy, 6, x.NDT_Test_Date)
					WHEN x.CLASS = 2 THEN DATEADD(yy, 3, x.NDT_Test_Date)
					WHEN x.CLASS = 3 THEN DATEADD(yy, 1, x.NDT_Test_Date) 
					WHEN x.CLASS IN (4, 5) THEN x.NDT_Test_Date
					ELSE x.NDT_Intervention_Date_1
				END

#3

Not tested...

=iif(Fields!CLASS.Value > 5, Fields!NDT_Intervention_Date_1.Value,
     DateAdd("yyyy", switch(Fields!CLASS.Value = 6, 1, Fields!CLASS.Value = 2, 3, Fields!CLASS.Value = 3, 1, True, 0), Fields!NDT_Test_Date.Value))