Reporting: ACCPAC 5.4A + Crystal Reports 10

[robhob]

Hello

How do I best create reports in Crystal Reports for ACCPAC.

I've written an elaborate SQL query and created a report using the standard OLEDB provider for SQL Server - adding the query as a command. When I tried to run the report in ACCPAC I got a log in error. I then changed the report to use a UDL file. That worked. The problem is I'll have to recreate the report for every company - there are 4 at the moment but ultimately there'll be about 7. It's not going to be fun rewriting the same report 7 times. I've seen on this forum that you should use an ODBC connection and add CMPNAME to the parameters but I still don't get how it works? The ODBC has a specific database that it'll connect to or if it doesn't Crystall will only give you access to the master database.

Am I going about this all wrong?

Thanks in anticipation.

Rob

 

[Jay Converse]

You have to use ODBC so that the Accpac crystal wrapper can change databases. And, you can only use Accpac tables without views.

 

[robhob]

So basically I'll have to create a new report for each company if I use a custom query. Is that right? I've tried using the CMPNAME thing and it didn't work. I'm using a command to retrieve the data.

 

[robhob]

Surely if ACCPAC changes the ODBC connection on the fly it doesn't matter what or how the data is queried. Once Crystal has the ODBC source it gets whatever it's told to.

 

[Jay Converse]

To the first question, the answer is yes. Adding CMPNAME merely prevents you from getting an error message like "# of parameters 0 < 1"

To the second question, the answer is that's just the way it works, the wrapper is designed to switch data sources. It's been that way for over eight years.

 

[robhob]

Okay! I've just done a simple experiment. I created a report in Crystal that pulls data from the ARCUS database using an ODBC connection. I ran it from two different companies and it worked! It gave me distinct data for each company. It worked with AND without the CMPNAME parameter. The problem I have now is that I use a command in my report and it doesn't work with that. It gives me the information from the original ODBC connection. The reason I use a command and not go straight to the tables is that I am using UNION to join three queries. Looks like I'll have to have a separate copy of the report for each company!