Cognos 8.4 - In List lookup question

[Bazkur]

Greetings,
First off, I do not currently have Cognos 8.4 but have a basic understanding of it from training I just took. Hopefully somebody here has it and can assist. We are in the process of implimenting software that utilizes Cognos 8.4 for reporting and I have a question that needs answered. When filtering based on a list of options for a field, does Cognos auto-generate that lookup list from the available options in the specified field or does it rely on a configured lookup list?

Hypothetical example, say I have a table that stores information about a person’s appearance. One of the fields is called “Eye Color”. Within the application we may have a lookup list that the user can choose from with the options “Blue, Green, Brown, Hazel, Other”. Let’s say there are records in the database that are using all of these options except for “Hazel”. Now I’m going to pull a report of all people and want to filter the report for specific colors. When I tell Cognos to filter by list and it shows me available options, is it showing me “Blue, Green, Brown, Hazel, Other” or is it showing me only the values that have been used which would be “Blue, Green, Brown, Other”?

Any thoughts would be greatly appreciated. I'm sure it is easy to answer if you have Cognos in front of you but we are still in the early stages of development. I assume/hope it is quering the field for a distinct list.

 

[blom0344]

The lookup should be based on a dimension table that stores all possible values rather than quering a fact table (which is time-consuming and would show only used values)
However, in case you have a definition of 60 colors and just a handful are used the opposite may come in handier..

It really 'all depends', having little to do with the BI tool used.

In addition it is possible to add static choices to a list (adding it to the prompt properties)

In most cases building prompts on small dimension tables is the preferred way, unless you start building cascading structures. In those cases you are pre-filtering data which is always a matter of using fact-data

Ties Blom

 

[Bazkur]

Thank you, I believe this answers my question.