Report Prompt vs HandCoded Dates - SQL Generation (Native SQL vs Cognos SQL)

[fry]

Good Afternoon

I have a report that uses FM to federate DB2 and SQLServer Databases. The reports executes properly, but there is a dramatic difference in run-times between using a parameter to select the date vs hand typing the date in the filter.

When I look at the SQL, with a hand-typed date in the filter, I can see a date in the Native SQL being passed to my DB2 fact. When I use a prompt for date, the native SQL looses the date in where clause, bringing back all the data in FM and then doing the filter on the Cognos Server ??????

Thanks

[cognos810]

Hello fry,
Is this filter with prompt optional in your query? If Yes, then when you try to generate the SQL from report studio it will never prompt you for the value and will show you the SQL without the where clause. When the filter is without a prompt and is handcoded, it does make it to the where clause in the query.That does not mean that during run time the report will extract all the records and do the filtering on the report server. If on the prompt page you have a prompt UI associated with this parameter and you do pass some value to it, it will generate the SQL with the entered value in the where clause of the SQL.
Give it a try by making the filter in the query with the prompt paramater as required. You don't need to have it as required, but by doing so you will see that the prompt value makes it into the SQL.

-Cognos810

[fry]

That is a great point, thanks, will check that out and update this thread accordingly