LBI/LRS and Crystal Report using ODBC Driver

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

Kate Liamero

Veteran Member

Posts: 70

3/9/2007 6:31 PM

We have crystal reports designed using data direct ODBC driver to oracle database 10G. The datasource that we are using to design the reports are also setup on our LBI/LRS servers exactly the same way.

We have PROD TEST and DEV environments. PROD and TEST are remote installs with the db, apps and lbi/lrs residing on 3 different servers. DEV is all on one box. DB and APP boxes are Unix/Sun. LBI/LRS Windows Version 9 for everything.

We are saving the login information with the oracle login and password with the report.
When we publish a report to Dev which is all on one box we can see the report no problem.

When we publish a report to PROD or TEST lbi/lrs asks for a login and even if you input the correct information nothing happens.

Any clues where to look?

As always thanks for any suggestions

Kate Liamero

Will

Veteran Member

Posts: 39

3/9/2007 7:04 PM

Hi Kathryn,

The report overriding feature on LRS is something Lawson seems to struggle about across LBI versions. This is not working on 9.0.1.7 for a reason we cannot explain yet. Consequently, the report datasource should always match with the LRS datasource.

However, you can check few things:
- Websphere logs on the windows reporting boxes, see what happens when you mannually run a report
- sub-reports. LRS does not really like subreports, on some LBI version, there is no way to make them work. Eventually, you will need to get rid of them by rewriting your reports.
- The 'save data with report' Crystal Report feature (under the File menu). Make sure it is unchecked when saving your reports, otherwise LBI may have issues overriding any db information stored when using this option.

Hope this helps,

/G

pvhmc

Basic Member

Posts: 5

3/9/2007 7:30 PM

try to manually run the reports from the LBI server. That way you'll be manually trying to do what LBI has to do. If you can run the report, then the no connectivity issue with the db so far. Next, in LBI, override the report data source with a data source you've got set up in LBI, use a valid username/pw and the proper (ODBC) datasource connection. If you get an error, validate that the username and password is good. These steps test out every thing that LBI does. You should validate that you can set up a good datasource connection on LBI. If you have a problem there you may need to drop the classes12.jar file into your tomcat libraries and restart tomcat.

Chris Martin

Veteran Member

Posts: 277

3/12/2007 2:55 PM

Sounds like a connectivity issue. Make sure you have the oracle client installed and test connectivity by logging into the database via sql*plus on the server

Kate Liamero

Veteran Member

Posts: 70

3/12/2007 6:55 PM

The datasource is good. I can connect from the LBI server using SQL Plus. The datasource name on lbi is exactly the same as on the report. I'm thinkin it must be in the configuration somewhere. It works on the LBI where the DB , Apps and web server are all on one server but it doens' work where they are on 3 separate servers. Thanks everyone for the ideas. I keep checking as I get each reply. Didn't try the classes12.jar. Someone else here qould ahve to do that. We don't save the data with the reports either.

Thanks

Kate

John Henley

Posts: 3353

3/12/2007 8:27 PM

Hello Kate,
"using data direct ODBC driver to oracle database 10G".
Have you tried to change / install the report using Oracle (not Lawson) OLEDB Provider, or native Ora*Net connections? That would rule out some options, as ODBC adds a layer of complexity.

pvhmc

Basic Member

Posts: 5

3/12/2007 10:25 PM

if you can run the report manually from the LBI server, you really need to look at classe12.jar file part of the problem. This is the file that will allow Tomcat to connect to the Oracle DB. There's an article in the knowledge base about this and it's a really simple task for an admin to do. make sure to restart tomcat after you put the classes12.jar file in the [TomcatHome]/common/lib/ directory.

John Henley

Posts: 3353

3/12/2007 11:53 PM

My understanding is that the classes12.jar is only an issue if you're using Oracle as the LBI repository or if you're trying to make a JDBC connection for the report. Since she's using ODBC, I don't think that's an issue.

However, the easiest way to find out is to try making the ODBC connection via the server (i.e. 'test connection' via ODBC control panel, and if that works, try the report--from Crystal installed on the LBI server--using the ODBC connection. If all that works, then it's an LBI issue. If it doesn't it's ODBC. Even then, I'd still question using the Data Direct ODBC route at all--why not use Oracle native or OLEDB--it's one less layer of frustration and overhead.