Auto-Generated prompts in Cognos 10 BI

I had an issue with Cognos 10 today whereby the automatically generated prompt control for a BI report was showing a drop down list, rather than a type in prompt.

The reason this was an issue is that I wanted to set up a schedule of this report, with this particular prompt being for ‘Client Code’. As we have many thousands of clients, there seems to be a limitation with auto-generated drop down prompts whereby they only showing the first thousand or so records.

After some experimenting, I was able to find that coalescing the column with a null string would force the auto-generated prompt to be a type-in box, thus allowing the client code to be entered.

Here is the code originally used by my filter:

#/* Strategic Client */#

[Engagement Revenue].[Strategic Client Level Dimension].[cons_client_cd] = ?pStrategicClient2?

and here is the modified code that uses a type-in prompt box:

#/* Strategic Client */#

coalesce ([Engagement Revenue].[Strategic Client Level Dimension].[cons_client_cd], ”) = ?pStrategicClient?

Below is a picture showing both these prompt behaviours:

Note: I’ve since discovered you can also set the prompt behaviour in the Framework Package