cognos report studio adds detail filters to new select

Question

I have a Cognos (10.1) report with SQL. (at this moment we do not use packages)

The report has several optional prompts. The problem that I have is that Cognos doesn't add the WHERE clause straight to my SQL, but does this.

select * from (select my SQL) where

This means that it starts with collecting all the data and then filters it. Usually this isn't a problem, but my current SQL has a lot of joins on big tables so it is incredibly slow.

Is there a way to force Cognos to just add my filters to a where clause without an extra select statement?

select * from my SQL where

This would make my report a lot faster.

Answer 1

Try to include your prompt in your SQL using macros #prompt # and #promptmany #. Don't forget to set default values to make in optional.

P.S. Try to avoid direct SQL usage in Cognos reports.