Crystal and Lawson's OLE DB - LawsonGuru.com

Name	Points
Greg Moeller	4184
David Williams	3349
JonA	3291
Kat V	2984
Woozy	1973
Jimmy Chiu	1883
Kwane McNeal	1437
Ragu Raghavan	1375
Roger French	1315
mark.cook	1244

Bob Beverley

Advanced Member Send Private Message

Posts: 31

7/27/2006 7:01 PM

I'm able to generate reports in Crystal using the Lawson Connector (not by choice, but it's the way the client wants me to connect). Couple of issues:
1. When I first run the report I am prompted that the table isn't recognized and I have to respond 'No' to 'do you want to remove the table from the report'. However, the report runs successfully. Why is it that Crystal doesn't think it's a valid table?

2. When I view the SQL (Database/Show SQL Query) I get just a list of the tables with nothing else.

As I read the Lawson 'Reporting Services User and Administration Guide', it appears that I should be able to build my queries in Crystal (as opposed to creating them in Lawson's query builder) - but I am concerned that there seems to be a 'disconnect' between Crystal and the OLE DB connector.

John Henley

Posts: 3351

7/27/2006 8:35 PM

What version of Crystal are you using to build the reports?

Bob Beverley

Advanced Member Send Private Message

Posts: 31

7/27/2006 9:09 PM

Sorry for the delay I'll watch it a little closer - I'm using Crystal 11 and OLE DB Ver 9

Bob Beverley

Advanced Member Send Private Message

Posts: 31

7/27/2006 9:14 PM

I should also mention that it's iSeries

John Henley

Posts: 3351

7/27/2006 9:18 PM

When you create the report -- are you selecting the table from the table list, or are you using "Add Command"?

Bob Beverley

Advanced Member Send Private Message

Posts: 31

7/27/2006 9:24 PM

Selecting from a list

Bob Beverley

Advanced Member Send Private Message

Posts: 31

7/27/2006 9:25 PM

Is the add command preferred?

John Henley

Posts: 3351

7/27/2006 9:29 PM

You definitely want to use "Add Command", which *should* bring up the Lawson query builder, and build the AGS query. That will fix both of your problems, in fact, when you view the SQL (Database/Show SQL Query), you will see the AGS query.

If "Add Command" does not bring up the Lawson Query Builder, you need to make a one-time registry tweak to connect the Lawson OLEDB provider to Crystal XI:

Save the following into a file (e.g. CRXI_Lawson.reg), and then double-click CRXI_Lawson.reg to load it into the registry:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Business Objects\Suite 11.0\Crystal Reports\DatabaseOptions\Lawson.LawOLEDBC.4]
"QueryBuilderFunctionName"="BuildQuery"
"QueryBuilderProgID"="LawQueryBuilder.Query"

Bob Beverley

Advanced Member Send Private Message

Posts: 31

7/27/2006 10:13 PM

Thanks for your help - I can see now that if you're using ole db you 'have to' use the add command nad build the basic report with query builder and just format it in Crystal.

Linda Herrmann

Basic Member Send Private Message

Posts: 8

7/26/2007 6:47 PM

John...

I'm using Crystal 9 with Query Builder 9.0.3.1. I'm on Lawson 8.1.0.4

When I click "Add Commmand" query builder does not launch. The Enter SQL Query box appears.

I have the same settings as you show above in my registry with one exception..

[HKEY_LOCAL_MACHINE\SOFTWARE\Business Objects\Suite 11.0\Crystal Reports\DatabaseOptions\Lawson.LawOLEDBC.4]
"QueryBuilderFunctionName"="BuildQuery"
"QueryBuilderProgID"="LawsonQueryBuilder.Query" --- Lawson is spelled out.

I scoured the Lawson KB. Their "fix" was to uninstall Crystal and uninstall Query Builder. Then install Crystal (first) and install Query Builder.
Needless to say, didn't work!

Any suggestions?

John Henley

Posts: 3351

7/26/2007 6:57 PM

Hi Linda,
You'll need to add those registry keys under the "Crystal Reports" key for Crystal 9...those registry keys are for Crystal XI ...

For Crystal 9, I think it's under

[HKEY_CURRENT_USER\Software\Crystal Decisions\9.0\Crystal Reports\DatabaseOptions\Lawson.LawOLEDBC.4]
"QueryBuilderFunctionName"="BuildQuery"
"QueryBuilderProgID"="LawQueryBuilder.Query"

Linda Herrmann

Basic Member Send Private Message

Posts: 8

7/26/2007 7:16 PM

Thanks John.

It worked but, in case someone else experiences the same issue as I, I did have to change it FROM
"QueryBuilderProgID"="LawQueryBuilder.Query"
TO
"QueryBuilderProgID"="LawsonQueryBuilder.Query"

Linda

John Henley

Posts: 3351

7/26/2007 8:17 PM

Thanks. I think that changed from v4 to LSF9 version of the OLEDB provider...