Multi-Select Prompt Changing to Single Select After Using the Prompt Value

[PaulBappoo]

Hi All,

TIA for any time you spend on this.

I've created a report with multiple charts and tables and multiple parameters / prompts.

One of those prompts gets it's data from a query, is presented as a multi-select listbox and is filtered based on a prior prompt. The filtering of the listbox based on the prior prompt all works fine.

The queries in the report are all hand coded SQL and the filtering is done inside the queries using this type of code:

and evt_object in #prompt('EquipmentPrompt')#

At one point during my build, the prompt suddenly stopped allowing multiple selections, so having Googled around a bit I went back over the report and checked that the query that was being used to populate the prompt values was getting it's data from a completely different table to that which was being queried. I also checked that the queries using the prompt value were using "in" rather than "="

Everything looked fine, yet the prompt would not allow multiple selections unless I commented it out of the queries, at which time it popped back to allowing multiples.

So, I started to take the report apart bit by bit, deleting queries one at a time, to see if I could eliminate which one was causing the issue - only after I managed to delete all of them did the prompt jump back to multiple mode.

Of course, I saved off my queries in a text editor, so I figured (since I need the practice anyway) I'd rebuild the report from scratch - I set up my prompt page, my prompts, parameters etc and launched into the first query. This time it worked fine, with the filter in place. I worked through all pages (about 7) of the report, adding queries and tables and charts as I went and all was good - I was delighted.

Then, on the very last page, I added the very last query, with it's filter intact and carefully checked to ensure it was using "in" and POP! The prompt fell back to single item mode. Not to worry, I thought, just undo the changes, remove the last query I added and I'll be back to where I was.

Unfortunately this didnt work, now, running through the entire set of queries and commenting out the filter for this prompt is the only way to get the prompt back to multi select. As soon as I put the filter on that prompt back into any of the queries, the prompt goes back to single entry mode.

I close my browser, cleared the cache, deleted all the queries, rebuilt the first query, deleted and rebuilt the prompt and it still doesnt allow multiple selects.

I've been really careful about this but can't think of anything I've touched or tweaked which would explain this odd behaviour, but I am assuming there must be a setting somewhere or an order of doing things that I am missing. To me, at the moment, it seems that something, like the number of queries on the report or an incorrect filter in a query kinda "trips" Cognos into believing that a prompt should be single select and after that happens, it seems impossible to get it to switch back.

Any pointers would be most welcome at this stage. Happy to provide any further info that might help.

Thanks.

[hespora]

Hi Paul,


if you're grabbing the prompts by macro rather than the parameter syntax, make sure you use #promptmany()# rather than #prompt()#. Using #prompt()# on a multiselect prompt does exactly what you are describing, it turns the prompt into singleselect.