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
Trygve ThayerIT DirectorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.