Use value - Display value issues

[cognosun]

Gurus,


Code   Name                      Sales

US       United States          50$
USA     UNITED STATES        60$

We have these 3 columns in our db...2 unique country codes and 2 country names ( kind of duplicate).

We need to display unique country names ( capital letter name)  in the value prompt drop down list.

We created a data item used upper function ( upper(countryname) in it and used this column as display value.

Problem is if user selects UNITED STATES , he need to get sales amount from 2 country codes ( US and USA), but here we are getting only one sale amount ( of 'United States', which we made as capital string using upper), but we need sale amount of 110$ for UNITED STATES.

If we use 'Use value' as country name or country code..still we are facing same problem.

The basic point itseems is....if we have 10 use values...then only 10 display values work....it's not possible to use 10 use values and 20 display values

Confused :-)

[Gopinath]

Set [Country Name] as USE VALUE

and

change your filter expression to

upper([countryname]) = ?prompt_parameter?

[cognosun]

Thanks Gopi.

That works :-)

[Lynn]

Perhaps you are past the issue for the moment, but it seems to me that it could continue to plague future report development efforts unless you attempt to fix the root data quality problem.

There may also be performance implications from filtering on the name rather than the code.

Sometimes the quick and easy solution for the near term ends up being slower and more difficult in the long term.

Just my 2 cents.

[navissar]

On top of what Lynn wrote: This is the sort of case where searching for a solution inside Cognos means not solving the issue.
The example you gave suggests poor data quality, with two country codes for the same country. This will become a festering swamp that will become increasingly diffcult to handle.
Also, filtering on indexed columns, as codes tend to be, is a lot better performance-wise.