Login
Register
Search
Home
Forums
Jobs
LawsonGuru
LawsonGuru Letter
LawsonGuru Blog
Worthwhile Reading
Infor Lawson News Feed
Store
Store FAQs
About
Forums
Performance Management
Lawson Business Intelligence/Reporting/Crystal
general reporting question
Home
Forums
Jobs
LawsonGuru
LawsonGuru Letter
LawsonGuru Blog
Worthwhile Reading
Infor Lawson News Feed
Store
Store FAQs
About
Who's On?
Membership:
Latest:
chaoticist
Past 24 Hours:
2
Prev. 24 Hours:
0
Overall:
5185
People Online:
Visitors:
476
Members:
0
Total:
476
Online Now:
New Topics
Lawson Landmark
LPL INSTR Functions
4/5/2024 8:32 PM
I'm writing a simple report using the Create R
Infor SCM
Translating 856 to get the ~ REF^CN^ field
4/3/2024 8:24 PM
We are trying to get the tracking number which is
IPA/ProcessFlow
Sample XML file create Flow
4/3/2024 3:43 PM
Hello everyone, I am new to creating XML files
Lawson S3 HR/Payroll/Benefits
bn105 error message
3/26/2024 6:40 PM
I need to change some of the set ups in our Life I
IPA/ProcessFlow
IPA executing Job
3/13/2024 7:08 PM
New to the IPA world and was wondering, can an IPA
Lawson S3 HR/Payroll/Benefits
Life Age Reduction on benefits plans
3/12/2024 7:15 PM
For our optional life we have an age based coverag
Lawson S3 HR/Payroll/Benefits
BN53.1 Add-In
3/7/2024 3:31 PM
We are migrating to Solstice. They require a
Lawson Business Intelligence/Reporting/Crystal
Domain Name Change
3/5/2024 7:45 PM
Our domain name needs to change and was hoping I c
S3 Customization/Development
Cobol calling Shell Script
2/29/2024 1:27 PM
Has anyone created or modified a Lawson Cobol prog
Infor ION
ION vs IPA
2/29/2024 1:24 AM
We had a person new to Lawson and Infor go to Info
Top Forum Posters
Name
Points
Greg Moeller
4184
David Williams
3349
JonA
3288
Kat V
2984
Woozy
1973
Jimmy Chiu
1883
Kwane McNeal
1437
Ragu Raghavan
1348
Roger French
1311
mark.cook
1244
Forums
Unanswered
Active Topics
Most Liked
Most Replies
Search Forums
Search
Advanced Search
Topics
Posts
Prev
Next
Forums
Lawson Business Intelligence/Reporting/Crystal
general reporting question
Sort:
Oldest First
Most Recent First
You are not authorized to post a reply.
Author
Messages
Phil Simon
Veteran Member
Posts: 135
7/12/2007 4:43 PM
I really enjoyed the series on reporting. Let me make sure that I understand XI's limitations with Lawson's OLEDB connector. While you get the benefit of security, you have the disadvantage of reporting on only "Lawson-approved"
relationships). As a result, more complex reports aren't possible without some creativity. You wrote about the benefits of doing Crystal Reports based on SQL queries. For this to work through Crystal Enterprise for light users (i.e., not hard core developers on their desktops), these "queries" would really have to be views stored in the dB, right?
Thanks in advance.
John Henley
Senior Member
Posts: 3348
7/12/2007 5:21 PM
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.</p>
Will
Veteran Member
Posts: 39
7/12/2007 5:22 PM
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.
Phil Simon
Veteran Member
Posts: 135
7/12/2007 6:10 PM
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.
John Henley
Senior Member
Posts: 3348
7/12/2007 6:30 PM
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.
Phil Simon
Veteran Member
Posts: 135
7/12/2007 6:36 PM
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.
You are not authorized to post a reply.