Is is possible to use a data item in a case statement?

[kgl_m]

Hi all, we have a very dirty table here that I need to clean up and make presentable with the help of Cognos.

The column that I'm working with is State, and this is free-form - which means that we get both state codes (MA, NY, CA) and state names (Massachusetts, New York, California) in this column.
Now, I essentially want to do a case statement that finds all full state names and replaces them with the state code for the report.
It would be cumbersome to do it in the report and I have a DB table with both names and codes, how would you do it? I dabbled in Parameter Maps but I wasn't able to make head or tail of it - any help would be appreciated.

Original State Column California MA NY Geneva

Modified State Column CA MA NY Geneva

[DustPanMan]

Why not create an Xref table that has the two columns (original and modified).  Then do a relationship on the original that allows multiple in your table to 1 in the lookup table.  Then output the modifed column in your Business View as the State Data Item.

Original       Modified
TN              TN
Tennessee   TN
FL               FL
Florida         FL
etc.....

Have your table point to the XREF table with join on "Original to State"... then pull modified into your Business View... Make sense?

[david.stachon]

Why would you want to do this at run-time in a report? ...why not just run a regular UPDATE and cleanse that data?