Creating a prompt based on if then statement

[reportit]

Hello,

I would like to build a filter drop-down prompt based on our company locations but I would like the drop-down options to be titled something different than the titles used in our location field.  My issue is I was 3of our location to be titled specific titles and our other 20 locations I want to all have the same title.

For example, our location 'PA' I would like it to show as 'Eastern Region", our 'CO' I would like it to show as 'Mountain Region', our "MO" location I would like to be "Midwest Region" and the rest I would like to be called "Corporate" in the drop-down box.

Does anyone know how to configure that?

[cognos810]

Hello reportit,
If your prompt is being fed with a query, that contains your location field in a data item [Location], then add another data item to that query with a CASE Statement, expression being:
CASE [Location]
WHEN 'PA' THEN 'Eastern Region'
WHEN 'CO' THEN 'Mountain Region'
WHEN 'MO' THEN 'Midwest Region'
ELSE 'Corporate'
END
Then use this data item as the use value of the prompt UI, with parameter as ?p_Location? for example.

To implement the filtering, to the query for the main report, add the same data item [Location] with the same CASE logic, and apply the filter in the detail filter section as [Location]=?p_Location?

-Cognos810

[reportit]

Thanks cognos810 but the prompt box isn't showing as a drop-down value prompt but rather a prompt that you search for the value by typing and then select the item and move it over.  Do you know how to change the prompt box to be one that is drop-down prompt?

[cognos810]

You will have to create the Drop down prompt yourself, by dragging and dropping "Value Prompt" from the toolbox tab in report studio and then associating it with the parameter ?p_Location? AND feeding it with Use values coming from the query as described earlier.