If in an expression expression

SSAS and SSRS
1

Hi all,

I am a crystal reporter that has recently transferred in SSRS so please bear with me.

I need to write an expression along the line of the following.

If 'retest status' is null then use 'test by date'
else
if 'retest stsatus' is 1 then add 72 months to 'last test date'
else
if 'retest status' is 2 then add 36 months to 'last test date'

etc..

I cant seem to get it to work...can anyone help?

Thanks.

David.

2

Dunno about SSRS but in pure-SQL you could do

SELECT [MyColumnAliasName] = CASE
       WHEN [retest status] IS NULL THEN [test by date]
       WHEN [retest status] = 1 THEN DATEADD(Month, 72, [last test date])
       WHEN [retest status] = 2 THEN DATEADD(Month, 36, [last test date])
       ...
       ELSE CONVERT(Date, '19000101')    --- Some dummy date if no other cases match - maybe use NULL
       END
3

In SSRS, you can use IIF or SWITCH. If there are more than two or three possible conditions, it would be simpler to use SWITCH. See an example of SWITCH here. For IIF, you will need to nest the conditions like shown below.

=IIF(IsNothing(Fields!RetestStatus.Value), Fields!TestByDate!Value,
   IIF( Fields!RetestStatus.Value = 1, dateadd(m, 72, Fields!TestByDate!Value),
        dateadd(m, 36, Fields!TestByDate!Value)
	  )
	)
4

Hi,

I keep getting an ‘error – expression expected’ when using the switch idea…

I have 5 values I need to switch:-

Isnull = test date

1 – date add 72 months

2 – date add 36 months

3 – date add 12 months

4 and 5 – test date

5

If you can post the expression you are using, that would make it easier to figure out what might be wrong.

6

Hi this is my crystal formula, thanks.

if isnull ({RM_ITEM_INVENTORY_NDT_LATEST_TEST.CLASS}) then {@NDT Test by date}
else
if {RM_ITEM_INVENTORY_NDT_LATEST_TEST.CLASS} = 1 then dateadd ("m",72,{RM_ITEM_INVENTORY_NDT_LATEST_TEST.LATEST_TEST_DATE})
else
if {RM_ITEM_INVENTORY_NDT_LATEST_TEST.CLASS} = 2 then dateadd ("m",36,{RM_ITEM_INVENTORY_NDT_LATEST_TEST.LATEST_TEST_DATE})
else
if {RM_ITEM_INVENTORY_NDT_LATEST_TEST.CLASS} = 3 then dateadd ("m",12,{RM_ITEM_INVENTORY_NDT_LATEST_TEST.LATEST_TEST_DATE})
else
if {RM_ITEM_INVENTORY_NDT_LATEST_TEST.CLASS} in [4,5] then {RM_ITEM_INVENTORY_NDT_LATEST_TEST.LATEST_TEST_DATE}

7

what was asked for was:

that is in the context of your post:

what we need, please, is to see is the code which is causing that error to be raised, otherwise we are just guessing what you have written and why that error is being raised. So the code that you are trying to use in SSRS