Syntax help with calculated field in SQL Report Builder

I am needing some help with a calculated field in SQL Report Builder.  I am a novice and converting from Crystal Reports to SSRS.  Yes I am building my first report but got to start somewhere.  Here is what I have attempted but obviously this throws up a big error.

=IIf(Fields!ShipViaCode.Value='CT1' then 'COMPANY TRK - GNG' else IIf(Fields!ShipViaCode.Value='CT2' then 'COMPANY TRK - NWC' else IIf(Fields!ShipViaCode.Value='CT3' then 'COMPANY TRK - NRC' else IIf(Fields!ShipViaCode.Value='CT4' then 'COMPANY TRK - NWN' else IIf(Fields!ShipViaCode.Value='FEDX' then 'FED-EX GROUND' else IIf(Fields!ShipViaCode.Value='LTL' then 'FREIGHT TRUCK' else IIf(Fields!ShipViaCode.Value='PU1' then 'PICK UP - GNG' else IIf(Fields!ShipViaCode.Value='PU3' then 'PICK UP - NRC' else IIf(Fields!ShipViaCode.Value='PU2' then 'PICK UP NWC' else IIf(Fields!ShipViaCode.Value='PU4' then 'PICK UP NWN' else IIf(Fields!ShipViaCode.Value='SLSP' then 'SALESMAN DELIVERY' else IIf(Fields!ShipViaCode.Value='SSI' then 'SSI' else IIf(Fields!ShipViaCode.Value='TAR' then 'TARTER TRUCK' else IIf(Fields!ShipViaCode.Value='WST3' then 'TARTER WEST - NRC' else IIf(Fields!ShipViaCode.Value='TIDR' then 'TI DIRECT DELIVERY' else IIf(Fields!ShipViaCode.Value='UPSP' then 'UPS -PLP' else IIf(Fields!ShipViaCode.Value='UPSW' then 'UPS - WEST' else IIf(Fields!ShipViaCode.Value='UPS4' then 'UPS - NWN - GNG' else IIf(Fields!ShipViaCode.Value='UPSS' then 'UPS - SPI' else IIf(Fields!ShipViaCode.Value='UPS2' then 'UPS 2ND DAY AIR' else IIf(Fields!ShipViaCode.Value='UPSG' then 'UPS GROUND' else IIf(Fields!ShipViaCode.Value='UPSO' then 'UPS OVERNIGHT' else IIf(Fields!ShipViaCode.Value='UPST' then 'UPS TI' else IIf(Fields!ShipViaCode.Value='UPTO' then 'UPS TI OVERNIGHT' else IIf(Fields!ShipViaCode.Value='MAIL' then 'US MAIL' else Fields!ShipViaCode.Value end
LVL 3
Trygve ThayerIT DirectorAsked:
Who is Participating?
 
James0628Commented:
IIF doesn't use "then" or "else".  The basic form is IIF(condition,true result,false result).  You could theoretically handle this using nested IIF's, which may have been what you were trying to do.  Basically, replace each "then" and "else" with a comma, add a bunch of ) at the end to close out all those IIF statements, and drop the "end" that you had at the end.

 With that many values, Switch might be a better option.  Switch lets you use a series of tests and results, like Select-Case in CR.

=Switch(Fields!ShipViaCode.Value='CT1', 'COMPANY TRK - GNG', Fields!ShipViaCode.Value='CT2', 'COMPANY TRK - NWC', Fields!ShipViaCode.Value='CT3', 'COMPANY TRK - NRC', <etc., etc., etc.>, Fields!ShipViaCode.Value='MAIL', 'US MAIL', True, Fields!ShipViaCode.Value)

Open in new window


 The True condition at the end is to get ShipViaCode as the result if all of the tests fail (ie. it doesn't match any of the recognized values).

 But the best solution, in the long run, might be to put those values in a table so that you can just look them up.  Then if you need to change/add a value, you can just change the table, instead of having to go into each report that uses those values and edit an IIF/Switch expression.

 James
1
 
Trygve ThayerIT DirectorAuthor Commented:
Did not get it working as I had to move on to other tasks.  Hope to get back to it soon but unable to pursue the solution now.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.