Crystal Report in ACCPAC

[muhaidib]

I have two exactly same databases meant for 2 regions. I have designed a report which can work for both. But I had to make two copies of the report with two DSNs to cater to two databases.
My question is: How can I use only one copy of report with both databases? During run time the report should link to the DSN of the database which is being used in currently activated ACCPAC.
My ultimate aim is to save on maintenance.

 

[Jay Converse]

When you add a crystal report to the Accpac desktop, it changes the DSN automatically.

 

[ettienne]

Add the CMPNAME parameter to your report, Accpac will change the database to the current company.

 

[muhaidib]

Thanks ettienne for the reply. Can you kindly explain how to use the paratmeter CMPNAME? What and how it will get the value? While scaning the database I found the field CONAME in table CSCOM. Do you mean this field?

Thanks in advance.

 

[ettienne]

Add a new parameter to your Crystal report and name it CMPNAME.

 

[Jay Converse]

CMPNAME is a special parameter, it gets its value from the CONAME automatically using Accpac's Crystal wrapper. Any other parameters must be passed through the xxRPT.INI.

 

[robhob]

Hi muhaidib

Have you figured this one out yet? I have the same problem. I've tried adding the CMPNAME parameter to the report in Crystal but when I run it on different companies I get the same results.

Thanks

Rob

 

[Jay Converse]

He must not have been using standard ODBC.

 

[muhaidib]

I am still looking for the solution.

I have not understood tuba2007's reply - "He must not have been using standard ODBC".

Please tell me how to go about it.

 

[muhaidib]

hi Rob,
CMPNAME parameter to the Crystal report in ACCPAC works fine. Just add a new parameter with name CMPNAME and insert in the report header. Run the report through ACCPAC desktop of any company. It will display company name written in Company Profile.

Problem is that it displays data of the company whoes DSN is used in the crystal report.

 

[robhob]

Hi muhaidib

I'm still trying to figure this one out myself. From what I understand you add a parmeter to your report in Crystal. The parameter doesn't get used in Crystal but ACCPAC populates it automatically when the report is run. I don't understand how or what Crystal is supposed to do with this parameter. ACCPAC will apparently automatically change your ODBC connection to match the company you're currently in when you run the report. I can't get it to work though. tuba2007 seems to think it's got something to do with my query.

 

[robhob]

Hi muhaidib

As to your previous post, I also have the same problem. Crystal displays the information from the DNS!

I've seen this CMPNAME thing on a couple of different forums but knowbody explains it properly.

Hmmm... looks like we'll have to create a report for each company.

 

[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!

 

[Jay Converse]

Try putting the command portion in a subreport. You could make the main report a simple stub against CSCOM, which has one record.

 

[robhob]

Thanks tuba2007

I tried your suggestion but unfortunately it didn't work. The ODBC on the subreport doesn't get updated...

Anyway, thanks for all your time and trouble. If I find a solution I'll post it here.

Rob

 

[Jay Converse]

Yes, I know that, I thought that's what you wanted.

The bottom line is if you have non-Accpac tables, views, or stored procedures, you can't attach the report to the desktop and expect it to work. You need to write your own Crystal wrapper, i.e, a separate executable.

 

[robhob]

Thanks tiba2007

I just create a copy of the report for each company.

Have a good one.

Rob

 

[robhob]

Hello

I've figured out that the easiest solution to my problem is to copy the report and modify the command on each copy to begin with the sql statement USE dbname. This way I don't have to redesign the report each time modify the ODBC connection. So at the end of the day, each report uses the same ODBC connection.

Not the most elegant solution but hey it works and means there's not much involved when it comes to maintaining the report.

Hope this helps someone.

Cheers

Rob

 

[DjangMan]

In theory you might be able to pass the database name as a parameter to the report. The question would be whether the report makes the connection to the database to invoke your command before it processes the parameters but I would guess not.

e.g.
USE {@ORGID}
SELECT * FROM ARCUS WHERE TERRITORY='AB'
UNION ALL
SELECT * FROM ARCUS WHERE TERRITORY='NS'
and so on.

 

[robhob]

Hi DjangMan

I tried it but it didn't work. The report wouldn't even run in ACCPAC! It worked in Crystal Reports.

Thanks for the tip though - great minds!