I need to have a prompt that is a simple choice of Company or Non-Company in a check box or list value prompt. The value sent to the database for Company is only one code, but there are multiple codes that comprise Non-Company. The problem is how to pass multiple codes for one prompt selection.
I tried putting multiple codes in the Use field of Static Choices (i.e. N’,‘A’,'C ), leaving off the end single quotes since Cognos supplies those, but the single quote is an escape character in the SQL Server database and the query sent to the database (viewed using the Profiler) contains multiple single quotes for each supplied single quote in the Use field.
I tried the example report and it works great, but my attempts at modifying the code to use a value prompt with space separated values in the Use field instead of a text box prompt bombs completely.
Does anyone know how to do this? It seems like there should be a way.
Here’s the original text box to value prompt code: