Force Impromptu to filter on database 1

[pwhp67]

I noticed that some reports filter a date range in the database while others return a brazillion records and then filter on the PC.

For example. If I use:

and ( Transaction Date between ?Start Date? and ?End Date? )

No date filter is in the SQL!


If I use:

and ( Transaction Date >= ?Start Date? and Transaction Date <= ?End Date? )

Then the SQL will have:

and T2."TRANSACTION_DATE"<=to_date('2007-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')

Which really blows because we have 6 years of data in this database and returning all records before April 1 takes forever.

Is there any way to force Impromptu to filter the date in the database?

Thanks.

 

[elalacran]

Try Query processing: Database only in Client/Server Tab on the Query option of the Report Menu.

I hope this help...

 

[richardh9920]

Change to the Profile tab and click on the SQL radio button. Then hold down Left-Shift, Left-Ctrl and Left-Alt all at the same time and left click the Edit button.

This will take you into the "Magic Query" window where you will see what the "true" SQL is, i.e. what is going to be passed to the database query server processor.

Whatever you do exit from the Magic Query, never click OK or your report will become SQL only.

 

[richardh9920]

Or, if you really want control over what is sent to be processed then you can edit the SQL directly and then save it. This way you can add hints etc. but you lose some of the drag and drop functionality of a normal report.

The best way to do this is to write a report and get it pretty much how you want it to look. Then change it to a SQL report and edit the SQL so you also get the performance you require.

 

[pwhp67]

Database only in Client/Server Tab gives the error:

Cannot execute query.
Processing requirements exceed the current Client/Server limits.
=========================================================
It won't let me edit the SQL. Because the report has totals it sends two queries; one for the totals and one for the data. So if I click on Edit I get an error that it can't send a single query to the database. I don't think I could use the prompts in the SQL anyway - it looks like Impromptu passes an actual value to the database when it works. I would have to edit the SQL every time I run the query.

I'm stuck using 7.0 by the way. Does anyone know if the newer versions are capable of calculating totals while fetching the data (Like just about EVERY other reporting software in existence.) or does it still send a query?


Thanks for all the help.

 

[pwhp67]

This will take you into the "Magic Query" window where you will see what the "true" SQL is...


Hey, that's cool! In this window it shows that the SQL is using the date prompts. So why doesn't it show that in the SQL window?

Thanks again for that.

 

[blom0344]

I'm stuck using 7.0 by the way. Does anyone know if the newer versions are capable of calculating totals while fetching the data (Like just about EVERY other reporting software in existence.) or does it still send a query?

I think in C8 most calculations are still translated to a query expression. Occasionaly you need to activate limited local processing for the Cognos server to perform part of the load.
Do you mean that Impromptu formulates a separate query to calculate a total?

Ties Blom

 

[pwhp67]

Do you mean that Impromptu formulates a separate query to calculate a total?


Oh yeah. Every single time. It doesn't matter how you define the total either. You can do it in the Data tab or you can do a data insert after the report runs and define the total that way. Then you look in the SQL window and the first select statement is a select SUM( )... and the next select is the data.

The other report programs I've used, you define a total or count or whatever and the software performs the calculation on your PC after it receives all the data.

 

[blom0344]

You have to distinguish between full client reporting tools and fully web-based ones.
For example , with full-client BO (Business Objects) you can first fetch the data and perform all kind of calculations locally on the PC. In terms of functionality web-based tools have yet to close the gap.
(with Cognos8 coming very close!)

Ties Blom