Cognos 8.4.1 prompts not generating all distinct values to choose from

[houlivan]

Hi

In Cognos 8.4.1, simple prompts generated using ?string? filters are not returning all distinct values for us to select from.

For example, ?subject? returns 'MAt' and not 'MAT' even though the table contains both values.

Has anyone else experienced this? Does anyone know if this is fixed and for what versions of Cognos?

I'm new to Cognos. Assuming I can download fixes myself from the IBM site (may not be the case because of how we acquire and license Cognos), do you have any pointers for researching and downloading?

Thanks.

Mike Sullivan
msulliva@northshore.edu

[blom0344]

I doubt this is Cognos fault.  Case sensitivity is a database setting. SQL server for instance out of the box is Case insensitive.  You need to enforce a Collation on a column to make the difference

(and Mike: posting your email address within the posts is not a wise thing, you can set this in your profile   ;)   )

[houlivan]

Hmmn, if it is a database setting, then would it make sense that both values (MAt and MAT) show up when doing a parameter page and just the one value show up when doing a simple prompt (just MAt) and when I log into the database directly, that I can see and query both values (using say TOAD)?

Thanks for the response

Mike

[blom0344]

I think the distinct clause will cause that effect. Without a distinct you will see each and every value. Cognos automatically adds a distinct to a query with no measures.
With a case insensitive setting  a distinct clause will not distinguish between MAT and MAt. (verified against SQL server just to be sure   ;) )

[houlivan]

I used Toad to query the Oracle database for distinct values and both MAt and MAT show up, as they do when the distinct values are shown through the parameter page. It is only the simple ?subject? prompt that only returns MAt. So the setting (if it is a setting) that needs to be changed would seem to be a Cognos setting not an Oracle setting. Any idea where that setting might be?

Thanks for taking the time that you have

Mike

[blom0344]

From a Cognos perspective you can perhaps set connection and sessions commands on the used connection.  It looks like Oracle comes with default case sensitivity (as opposed to SQL server)

http://publib.boulder.ibm.com/infocenter/c8bi/v8r4m0/index.jsp?topic=/com.ibm.swg.im.cognos.ug_cra.8.4.0.doc/ug_cra_id10494CommandBlockExamples.html