Hi,
I'm working with Report Studio in an evaluation version of Cognos Express. I'm creating a report against a Postgres database accessed through ODBC (the DSN is ANSI).
I have the imposed restriction that all the filters must be delegated to the Postgres database, and not processed locally in Cognos.
When specifying simple filters involving static values (something like [foofield] = 'foostring') the filters are delegated to Postgres as WHERE clauses, which is fine.
But when the filters involve values obtained from prompts (something like [foofield] = ?obtainedfromprompt?) the filters are processed on the Cognos side. I have tried to set properties like "Database only" and "Native SQL" to alter this behaviour, but to no avail.
Oddly enough, if I wrap the variable from the prompt in a function (something like [foofield] = ltrim(?obtainedfromprompt?)), the query is delegated. But I would really prefer not having to do that.
Is there some property to tell Cognos to always delegate prompt-related filters?
Thanks in advance!
Not sure if this exists in Cognos Express, but it should. Highlight the query and under SQL Parameters property change the value to "literal". This will tell Cognos to populate the parameter with a value and only then send the query to the Database.