Selection formula sporadically does not push to database.

[Former Member]

On CR 2008, SP3 - Oracle 10 database using native connection, report called from VB.NET:

We have a process that iteratively loads an .RPT, prints to file and inserts file into database. Report is fairly complex, 15 subs, lots of groups, etc. When we run this report on a somewhat large database (source tables range from 1M to 6M rows), it will process just fine for several iterations (10 to 30), but then it will run and NOT push selection criteria to the database via the SQL Query. We're watching the queries in the database as it runs, and see that every so often it fails to send the where clauses. With 5M rows, this causes a significant delay as it pulls all the rows in and applies selection criteria locally.

We're very experience CR developers (partners). I'm pretty sure we haven't missed anything. The main report and all subs have been reviewed to make sure that "Use Indexes Or Server For Speed" is selected, and that no selection formulas include native CR functions that would prevent the selection formula from being pushed to the server. When reviewed in design mode, SQL Queries for main and all subs show with WHERE clauses.

Any thoughts for anything else we can review?

Does anyone know exactly what criteria CR uses to determine whether to push the selection formula to the server (other than what I mention above)?

Thanks, Bill

Edited by: wlpearce on Jan 12, 2011 4:42 PM

[former_member11696 · Accepted solution]

Hi Bill,

Can you create a case in SMP and be sure to set the product to DEV NET? To complex with logs etc. to do using forums, you can't attach anything...

I believe you have my e-mail address also so create the case and send me the case number or make a note in the case to Assign the case to me.

Thank you

Don

[Former Member · Accepted solution]

Hey Don... Thanks for replying. I'll have Millie enter a tic and follow up with an email. This is going to be prickly to troubleshoot because we can't recreate it in our development environment. So far it's only occuring in our production environment, and only with large amounts of client data full of PII.

Can you comment here on the question of what criteria CR uses to determine whether to push a query to the database... outside of the server option and lack of internal functions? That might help point us to different things to try. We have a deadline of Saturday to prepare these reports, and when working correctly, they take a day and a half to run.

Thanks, Bill

[former_member11696 · Accepted solution]

Hi Bill,

Need more info, what version of Oracle client and Server are they using? Windows or WEB app?

Are you closing and disposing of the report document each time? This should disconnect the DB driver and free up the connection. It could be a Client issue but need more info.

I've never seen or heard this happening before so not sure what or why. If CR has the WHERE clause in the original report then there is no reason why it would drop it... Unless the registry key can't be read due to system resources, not sure how your app works so not sure what may affect it.

Be sure to get Millie to note the patch level and DB Server/Client version being used and as much info as you have. Also the code you use to set database connection and attach a report that it fails on so I can see the option used in the RPT file also.

Thanks again

Don