OLE db report for LBI 9031

If you'll paste what's in your record selection formula here, I'll tell you if it can be optimized - soemtimes that's the problem

Also, what is the purpose (and parameters) for the report?

I just ran into a similar issue last week.  If the cause is the same (at this point, I couldn't say if it is), then the problem isn't the sql but the bursting process.  You didn't mention if your report is bursting, so at this point, I'll assume it is.

Our problem was that the bursting process (which was by company/accounting unit) was timing out internally on the LBI server.  That happened because the report was very large and because our financial liaisons had rights to see every accounting unit.  The bursting process timed out because of the way I cached the report in my LBI setup for that report.  On the Report Details page, check out your >Data Filtering>Bursting Options page -- you may have the Cache Files options set to Creation Time.  If you change this to View Time, the report won't burst until someone views it.  This means your report will publish quicker.  However,  if you do have some users who have a large set of viewing rights, their particular attempt to view the report may run into problems.  Users who have only a few rights should be able to view with no problems.

Behind the scenes the problem was caused because our LBI server is running on a Windows Standard edition server -- which has a max of 4 gb of ram available.  The bursting process was running out of space.  Our I.T. group is now looking at a work-around for this limitation but hasn't come up with anything yet.

This is not a bursted report. i am just refreshing the report right now. I am using an index to filter the reprot where it uses 4 fields:

1.company, acct_uint , account and date.

Hello

Ppossibly the problem is still a resource issue on the server side. If the report is very large with filter and the refresh never completes, maybe the filtering process is causing problems on your LBI server. It might be worth while to check with your I.T. folks to see if they monitor what's going on with your process -- any why results are never returned to you.

This report has not been published to lbi yet. I am using the crystal designer to refresh the report.

If you're using something in record selectiom that won't pass in the where clause, the report will run longer than necessary. For example, datadiff won't pass dateadd will pass so if you'll paste your record selection, I can tell you if it will all pass. Also, you can tell by this - start the report running and look in the lower left corner of Crystal Reports and as it starts counting the two sets of numbers should stay the same like 100 of 100, 675 of 675, 2050 of 2050...... if they aren't even and it's more like 0 of 100, 1 of 675, 15 of 2050 - that's the sign that the report selection could be optimized

the record selection is dst_acct_unit <> 80060. It is showing 4006 of 4006.

Nothing more you can do on the report side then. When you say "table scans" - simply using tables in a report won't do a true "table scan" - it uses the indexed fields very efficiently and Lawson is suprisingly well indexed for reporting. I'd expect to bring back four thousand rows in well under a minute if instead of using OLEDB you went right to the tables. I'm not a fan of OLEDB at all; the performance is terrible and anytime you have to use multiple commands because they don't have all possible links available then you really are doing a true table scan because the queries are separate. I doubt it would take more than a few minutes to link the tables you're using directly within a report and cut and paste the record selection - I think you'd be extremely pleased with the results.

Thanks for the reply. I am not a fan of oledb also but this report was created about 4 years back when I did not know the performance issues between oledb and odbc. There was a lot of back and forth changes to get it right which is in the condition it is and because of the derived fields and validation time etc I cannot convert the report to odbc right away. that would be a whole big project in getting this report to work right.

I understand. If you want to outsource it, I'll convert it for you in less than a day's work if it's worth it to you

I am in the final stages of getting the report to work. What I do see is on the filter or the index I would like to add a range like (600-620) and (700 to 720). what is the best way to acheive this in oledb. I want to set it up on the index or filter level.