Cognos BI Promptmany Macro Function Error

The promptmany macro function error message is such a letdown. Error QE-DEF-0406 is normally generated when you try to use the function to pass multiple values to a Sql stored procedure. By nature if a stored procedure has parameters, you can only set the parameter equal to a single. This is a problem if you want and need to pass multiple values to a stored procedure. Traditionally, Sql programmers use cursors to handle passing individual values one at a time to a procedure. Of course programmers would rather bite off their arm than willingly write a cursor if there is another viable alternative.

So when you see the promptmany macro function in Cognos, one gets all excited with the prospect of an application allowing you to use an array. Ah Yeah you start dancing cause this will save programming time so you rush to use it in Framework Manager. Framework Manager is the natural selection because you want to pass the prompt values from the report to the stored procedure query subject. From there, Framework Manager will handle passing multiple values to the database. As soon as you go to test your report and use a prompt that requires more than one value you get Error QE-DEF-0406.

According to Cognos, the promptmany macro was not designed to handle an array. The promptmany macro only works in a query (inside a report) and not in Framework Manager. The solution they offer is to write a java script to handle multiple values. The idea is to embed the script into the prompt page of the report and the java script will take over. Good luck with that as I was unsuccessful with the script so I invented my own workaround. My process is much easier and if you are comfortable with Sql this will be a breeze for you.

1. Write a web application using Visual Studio. The web application shows the prompts and then writes the prompt values to a table. When the user click save you generate a Group ID.

2. Use the Group ID in a sub select in your where clause and set your stored procedure parameter equal to the Group ID.

3. Publish the application to your www root directory in IIS

4. Put a link to your web application on your prompt page