I'm finding limitations using the OLE DB Adapter (other than performance - a whole other topic) in the way I can build my queries.

For example, I need a status report that has some user fields on it. The adapter does not have a relationshiop defined if I start with the EMPLOYEE table to the HREMPUSF table. However, there is one going the other way - BUT - I can write my report that way because I would need ALL employees on the report regardless of if they have any values in the HREMPUSF table.

In addition, I can't start with the HREMPUSF table because I need some other relationships that are already built on the EMPLOYEE table (DEPARTMENT, SUPERVISOR, JOB-CODE).

I know in theory I could add two adapter connections to the report - one with the EMPLOYEE, DEPARTMENT, SUPERVISOR,JOB-CODE data on it, and the other with HREMPUSF - and then build the links in Crystal manually - BUT - that would pull in WAY too much data into crystal to begin with and it would take too long to run.

Does anyone know of a way to build manual joins through the OLE DB Adatper to accomplish this?

AND - BEFORE YOU SAY IT - the decision has already been made that we cannot build reports diriectly against the data becuase we have security that limits users to only certain data within individual tables - in LAUA we limit users to only certain process level, for example. There is no way to do this that I am aware of in SQL Server.

You are running into one of the main reasons why OLE DB is not practical as a way to enforce data level security.

I'm guessing you are not on LBI 9? If you were, bursting eliminates many of your concerns. If you are using LRS or Enterprise Reporting, you can use a combination of the object/folder level security and report filters to accomplish this. If you are trying to restrict data access to report developers, utilizing database views is an option.

I think your stuck with the two Commands option if you want to stick with OLE DB. Try that but incorporate the following hints:

1. add this string to the end of each OLE DB Command:

&NOIROWLOCATE

2. Utilize parameters. If you add a filter on say PROCESS_LEVEL and instead of entering a literal value such as "1100" enter "?". When you add the command, a Crystal parameter will be added. Give it a logical name. What ever value the end user adds will be used in the query BEFORE the data comes back.

If none of this, you may want to consider creating your own view that gets this info using SQL and then in Lawson add it to the dbdeff. It can then be secured via security class just like any other file. There is another recent thread here dealing with that i believe.

hth
Matt

What is the performance comparison between LBI and Reporting Suite 1.3?

Performance in what respect? LBI has no effect on the OLE DB Query engine. Reports written with the OLE DB Provider for Lawson will still have the same performance. The applications (Reporting Services, Framework Service, SmartNotifications, etc) perform far better as they are on a new version of the JDK and Tomcat, but none of this will improve response time for queries, that still is all on the back end. One thing to check is, the latest release of the Query engine runs on Java Servlets instead of cgi and supposedly the performance is much better, however, it still has major issues.

Sorry - I phrase my question very poorly. I was thinking 9.0 and it came out LBI - I was curious if the "removal" of RMI has improved the performance of OLE DB - my guess is that it is still going through the same security and business logic layer and therefore will still be very slow.

sorry i edited that post after i posted. but yeah, ive run queries on LSF 9 and had the same issues as previous releases.