OLE DB substitute?

Russel,

What is the main reason you want to use the OLE DB adapter, to retain Lawson Security or to connect to Lawson Back Office reports. If the latter it could be a bit difficult. If the former there are some solutions you can explore using Native drivers to your database combined with Rights Management.

Post back and the community can give more details Im sure.

Matthew, according to the Lawson Knowledge Base, Report Writer reports which we use within Lawson can also supposedly be used as the data source for Crystal Reports. That part gets beyond my usual scope but they say to use the OLE DB connector and there is a 17 page document explaining how to use that to connect to reports that are on the Lawson system. I don't know if those are considered 'backoffice' or not but we run them, I believe, using RW100. When I asked about this with my rep he said they no longer sell the OLE DB connector on its own. I do see that if I go into Crystal Reports I can add a Lawson OLE DB connection but it only gives me references to tables and fields but not to reports on the Lawson system. I assume that is what makes their OLE DB connector 'special'.

I hope I'm giving you enough info to go on; as this is rather new territory for me if I'm not giving you some info you need to help, please let me know and I will try to better answer your questions. Thanks very much. I just think it's pretty dumb of Lawson to make this unavailable when it is certainly usable with some of their basic reporting abilities. They asked me to consider buying LBI to get it - that's not happening.

Russell,

What the OLE DB really gives you is the ability to launch the Lawson Query Builder and embed a dme query in your Crystal Report. In addition to a simplistic approach to querying tables, its also what enables you to connect to your Lawson Back Office Reports, of RW100 is one.

Without this there is very little chance you will be able to use the RW100 as a data source. The one possibility, and I havent tried this so its pure conjecture, is if you used the csv output of RW100 as the datasource for the crystal. This would require you to use the same user to run RW reports.

The other option, which is a larger work effort, is re-writing your RW100 reports using SQL off the source system. I know its not optimal but without the OLE DB Connector, its really the most sustainable.

hth
Matt

Matthew - I saw some articles on line about using an XML file as a datasource in Crystal Reports. It doesn't look easy but if the RW100 was created with the Xtra XML flag turned on, could that XML file then be used without Lawson's tools?

Thanks guys for the input. I did see that about the XML output and was going to play around with that but haven't had a chance - been under the weather and just got news I may become a dad.

I'll see what I can figure out if anything with the XML and will check back in case there are any other suggestions. I wish we had the OLE DB connector but they won't sell it separately anymore. Grrr.

I'm back with an update and a few questions. We don't have Lawson's OLE DB connector and they won't sell it separately anymore (thanks for nothing Lawson) but I'm seeing something for which I don't understand the significance....

These are the steps:
1. I open Crystal Reports 2008 and create a new blank report.
2. Database Expert window opens. Under 'Create New Connection' expand the OLE DB (ADO) item.
3. The OLE DB Provider window opens. In the window of available Providers, within that list appears "OLE DB Connector for Lawson OLE DB Object Services". I select it.
4. Next I get a 'Connection Information' window with Data Source, User ID and Password fields.

I have yet to be able to get it to let me log onto the server. I must be mis-intepreting what is wanted in these fields - I have tried the web address to our server and the port number for the database, as well as the IP address and port number used in the Oracle ODBC connector, and neither works. There is also a 'Location' field which is blank on the Advanced Information' window, not sure what that would get.

My questions:
1. Where did this mention of a Lawson OLE DB Connector come from in Crystal Reports? There is no mention of it or anything 'Lawson' in the 'Data Sources' area of my PC's control panel. I have MS Addins installed on the PC - could it be related to that? I don't know any other way my PC might have reference to Lawson unless by some chance it's built into CR2008.
2. Does anyone know what to enter into the various fields to make this connector 'connect' and if so, is it of any real use?

You can see how thrilled I am with Lawson that they refuse to sell something of value - they apparently want us to buy a whole new application just to get something that would probably fit on a floppy disk. Any thoughts or suggestions would be most appreciated. Thanks.

You have the Lawson OLE DB Connector installed. Enter your Portal url (minus the "http://" and the "/lawson/portal") and your Portal credentials.

All I can figure is that the ole db connector was installed along with MS-addins. I've gotten connected but I still don't get the "Query Builder for Lawson OLD DB Object Services' window that I think I need to connect to a Report Writer report to use as a data source. Is there any chance I haven't just hit a dead end?

What version of Crystal?

I have tried to do this in both CR 2008 and CR XI with these results (below). (I tried also in Crystal Reports Pro v8.5 but it did not give me the option to 'Add Command'.)

I have been able to get the connection to work (with assistance, thanks all) and here is where I am now:
1. In the Standard Report Creation Wizard under 'Available Data Source', under the connection I just opened, I select 'Add Command' per the instructions at the Lawson KB.
2. I get a different screen than shown in the instructions - instead of the query builder screen where you can enter the user name and job name and file name (which end up in a query window at the bottom of the screen) I get a more generic window - but I can still manually enter in the same things as are in the query in the example in the KB.
3. I have a Report Writer job and output file I just ran under my own ID, so I enter the query manually with my own ID, job and filename:
xtra:_USR=i0q0ras&_JOB=RW100TEST&_STEP=1&_FN=isdeptstats
4. I get a Crystal Reports error message saying 'Failed to retrieve data from the database. Details: ADO Error Code : 0x. Source: Command. Description: Missing_JOB parameter. This is a required parameter for XTRA queries.

How do I have to write the query? It looks like other than being the customized screen with the fields to enter the query data, I can just enter the same query manually, but in my case it says there is a missing JOB parameter though one IS in the query. Any ideas?

It sounds like you are missing the registry keys that hook Crystal up to the OLEDB provider. It sounds like the provider was installed at some point on your machine, which would be puzzling since you say you don't own it. Is it possible that a consultant or some outside party installed it without your permission?=

I wonder if its because of add-ins. Add-ins and Query Builder make calls to the same engine using the same API (dme) therefore it might make sense that it uses the same drivers. You aren't getting the normal Query Builder UI because the app isn't installed but if you were to properly construct the dme call in the command box, it would make sense that it COULD work.

I'd just like to note that if this is somehow by passes Lawson licensing agreements, I don't condone this, but since in this case youre simply using technology present from a licensed and installed software, you should be fine.

No, nobody touches my PC except for me and I've installed everything on it including Windows. I have a laptop which also has CR2008 and Lawson MS-Addins installed, and it also shows the Lawson OLE DB connector in the list of connections available. It seems to be something with the job parameter that's wrong - the query appears to be working otherwise though I will keep testing. BTW I get the exact same results and error code on the laptop as I do on the desktop machine, when I run the same query.

In the Crystal Reports 'add command' window, the left area is the sql query area, and the right area is for 'parameters' which you can add and specify prompt and default values for. I would guess there is something Lawson's OLE DB connector configures without having to add it to the 'parameter' window. Just wondering what that might be... the error does say 'missing parameter'.

I have also noticed that under Program Files / Lawson Software on my PC is a directory with five dll files, one of which is LawsonQueryBuilder.dll . I don't see the Lawson Query Builder window in Crystal when I try to do an OLE DB connection, just a more generic query window. I tried to regsvr32 the LawsonQueryBuilder.dll file but I got an error message saying it was loaded but the entry point could not be found.

I have a hunch I have what I need, if I knew how to handle the bits and pieces...

The dll's are there, it sounds like it is installed. John's thoughts on missing registry keys seems to make sense for not getting the UI.

Trying to construct the dme call in the command box should work, the format is dependent on your Lawson environment. What version of Lawson are you?

We are on LSF9 and Apps 9. However - in another thread there was discussion about adding the missing registry keys, which I did, and also I copied the five Lawson *.dll files I found in \Program Files\Lawson Software\OLE DB Object Services into \Program Files\Business Objects\OLAP Intelligence11\ActiveX Components. We're in business.