general reporting question

Phil,

If you need to create relationships outside the "Lawson-approved" relationships, the way to do it is to use multiple OLEDB datasources within the same report, and link them together on the appropriate fields. I demonstrate this on one of the articles:

https://www.lawsonguru.co...ries-in-Crystal.aspx

So, you could theoretically create all of your reports using the Lawson OLEDB provider. Depending on the data volume, the tradeoff then becomes security vs. performance. Since the Lawson OLEDB provider has to go through more layers, it's slower than using a native database provider. In most cases if you're intimately familiar with Lawson data structures, conditional indexes, etc. you will gain tremendous performance advantages using native SQL. If performance is absolutely a requirement, use straight SQL and use another mechanism for security.



One suggested way to do that--using either LBI or Crystal Enterprise (BOE) XI--is to bind the selection parameters to the Lawson OLEDB provider which will limit the values which a user can select from (based on their Lawson security), and then use the native SQL provider to retrieve the actual data--using the selection criteria which you've limited based on their Lawson security. You can't do this with Crystal Enterprise 8, 9 or 10, since you can't bind the parameter selections to a database--you have to be using XI.



With Crystal, you can either report against the tables--in which case Crystal generates the SQL when retrieving the data, or you can write the SQL yourself and the datasource(s) for the report are called "commands". Once the reports are published to the Crystal Enterprise (or LBI) server, there is no interaction required on the part of the user to understand SQL--presumably a "report developer" or DBA would be the one to put the SQL together. The SQL is then embedded as commands in the .rpt file. It does NOT become a view in the database.



Views are useful if you have users who are creating reports (using the Crystal Designer NOT Enterprise) who don't need to understand the table relationships. A great simple example of this is the relationship between EMPLOYEE and PAEMPLOYEE. I cannot tell you how many times I've seen people join these two tables on just EMPLOYEE...which results in a table scan!



By putting together some simple views, report creators can then just pick their fields from a simple list.

Phil,

As for your first statement about OLEDB and the benefit of security, all depends on what context you run reports. If end-users all have CR on their machine and enter logins at run time, they yes, Lawson security is enforced. If you publish reports through say LBI, then report will have to run under one logon, no matter who runs the report, hence the bursting capabilities with LBI.

You are right about Lawson OLEDB though, it is far from being flexible, it is slow but is definitely user-friendly, should you want end-users to develop their own reports.

As for non-Lawson OLEDB based reports, usually, it is easier to have stored procedures at the DB server level that are called by the report itself during run-time.

Thanks, guys. Your series on reporting (and my own personal experience with the Lawson OLDEB connector confirms this). However, I know reporting is as much an art as a science, and I think about the best way to maximize performance and security concurrently.

To that end, John - you wrote:

One suggested way to do that--using either LBI or Crystal Enterprise (BOE) XI--is to bind the selection parameters to the Lawson OLEDB provider which will limit the values which a user can select from (based on their Lawson security), and then use the native SQL provider to retrieve the actual data--using the selection criteria which you've limited based on their Lawson security. You can't do this with Crystal Enterprise 8, 9 or 10, since you can't bind the parameter selections to a database--you have to be using XI.

***

So I completely understand, you're talking about having multiple versions of the same report, right?

If LAUA security gives person A access to all employees in Company 1 on EMPLOYEE, then report_A would have a parameter of company = 1.

If LAUA security gives person B access to all employees in Company 2 on EMPLOYEE, then report_B would have a parameter of company = B.

No, it would be a single version of the report. The selection criteria would have COMPANY = {parameter}. The values that are available to whomever is logged in to run the report would be based on that user's Lawson security.

Oh, I think that I got it.

You initial connect to the data source via LAWSON OLEDB and then write the SQL (using the native SQL provider) to maximize performance and apply Lawson security simulanteously, right?

Thanks.