Sage 100

Does the 64-bit ODBC driver with version 2013 improve performance on intense Crystal Reports? I have a client that will be upgrading to 2013 and they have a couple of Crystal Reports that are incredibly slow on 4.30.

Interesting question, looking forward to the response. At the risk of beating the dead horse, our workaround has been to use Access PTQ. 10X faster performance using the SAME Crystal report by linking it to the Access mirror DB. Runs off the MAS menu. End user doesn't know the difference but you look like a hero for the increased performance.

How does the Crystal Report/PTQ work when you have the same report for multiple companies?

Hmmm... thanks @JeffSchwenk. So let me ask this question: the client currently mirrors all of the MAS data files to a SQl database. So are you saying that if I change the report to link to the SQL data tables instead that the same report will run xxx times faster??

@JaneScanlan - yes it will. And you might be able to boost performance further by creating a view in SQL so that SQL handles the joins and selects instead of Crystal.

Excellent @PhilMcIntosh this would be a perfect solution. @RobertWood - A tad more complicated but doable. You would need a PTQ for each company. If there are a limited number or companies, you would then create a union query in Access to consolidate the individual PTQ's into one table (adding the company code record to each record) then insert a parameter in the Crystal to pull records for the specific company. You can also create consolidated reports with this method. But it does get tedious if you have a significant number of companies. Perhaps @DanBurleson can offer up some thoughts on multiple company reporting.

Thank you so much 90minds techies!

That's what I was thinking @JeffSchwenk. Not pretty if you have several companies with a ton of data.

@RobertWood - Several years back when I encountered this issue, I had some Access programming done so that created PTQ's for each company based on a default table plus plus it updated the silent ODBC connection for each PTQ. With that, you create a default ""mirror"" db then run a macro to customize it for each client and their companies.

@JaneScanlan - Sage 100 ERP 2013 is still a 32 bit program even though it can run on a 64 bit machine. In addition, the ODBC driver SOTAMAS90 is also 32 bit. Further Crystal 11 is a 32 bit program using 32 bit drivers even when installed on 64 bit machines. The solutions offered in this thread are good alternatives. I have also implemented the data warehouse concept that works good too. Set up a SQL server or other database and have stored procedures or triggers to query the MAS tables and refresh the SQL table(s). the Crystal reports use ODBC or OLEDB to access the SQL table(s). The performance improvement is astonishing. One method of attaining increased performance is to normalize the database tables. Create a schema that uses only the Sage ERP fields that are necessary. So if you previously linked 3 or 4 tables together in providex, create a SQL table using only the necessary fields from the 3 or 4 providex tables. This is simila to the work file concept used with the standard Sage reports (they have a providex program front end.).

Thank you for the info @DougHiggs !

@DougHiggs when you use SQL as a data warehouse, are you using ODBC to connect to the Providex data? How often do you usually refresh data?