User Role Based Crystal Reports in LBI

Hi Chris,

LBI will allow your users to run/export Crystal Reports directly from a dashboard. Bursting rights can be created (either at a user or role level) to burst the data automatically.

Crystal is an excellent formatting tool, however it is often necessary to develop complex sql, leverage the database's procedural language (ie PL/SQ, TSQL), or even create a data mart to be used as a data source for your reports. The logic utilized in Access can likely be leveraged for your Crystal data sources.

For those experiencing longer run times in LBI vs Crystal Reports Designer, there are many variables that can come into play. A major factor is the architecture and/or configuration of the LBI environment. However if the Crystal Reports logic is identical, the run times should be fairly similar. There are strategies you can implement to allow for your LBI reports to "run" even faster, including the utilization report scheduling.

The challenges include an LBI environment properly scaled for the anticipated usage levels, properly defined bursting rights, Crystal reports tuned to run efficiently, etc.

Thanks,
Chris Martin
Independent LBI Consultant (Certified)
cjmart@swbell.net

Chris, the key for you here with these reports is how they are built (tool, driver, selection criteria). We have converted many HR related reports to LBI to be on demand/Scheduled. We look at each report and determine the best tool to utilize (crystal, smartnotes, scorecard, etc) and then work on delivery of the info.

If you utilize indexing and if the field you need to burst upon is in your data, you can limit the number of records in the query or burst after the fact. One example we have here is a total compensation report that all employees pull off a dashboard. It has two subreports and is burst by userid. When I first rolled the report out it was scheduled and burst, even with that it took about a minute to pull the report to the screen. We rewrote the report with a different ODBC driver, limited the record selection based on user id, and the report now runs on demand in less then 5 seconds from the time you click on the link on the dashboard.

Planning around how to burst and pull the data is your biggest hurdle. We do not use user rights maintained in LBI for bursting, we have made the decision to utilize data either from the application or security to burst reports. It takes a little more effort to build the report but we don't have duel maintenance on bursting rights.

We have a dashboard related just to benefit enrollments rolled out to HR that is on demand/Schedule with a combination of about 30 exception based reports to pull anything from eligability to EOI info. These are half and half crystal and smartnotes based on data, requirements, need to access Lawson screens, etc.

It is hard to say the LBI will meet all your needs, as you said you have some very complex reports. But for the most part you should be able to get the needed info into the hands of your users on a timely basis without too many issues.

If you have more specific questions around how to handle particular report questions reach out to me.

Hi Chris,

Thank you for the rapid response, its very much appreciated. I understand bursting can be established for user or role levels, but is it possible to have users run/export Crystal reports based on their user or role level?

Unfortuantely data marts aren't an option at this time and we need to make do with what we already have in place. For the record, I'm not a big fan of Crystal.

Thanks again,
Chris

If you are asking about access to and privileges (ie export/print) on reports at a user/role level, then the answer is yes.

Data marts aren't a requirement. There are other ways to handle the complex logic required. One of those ways is utilizing stored procedures, either as a data source for the report or as a data source for a custom table (with the table used as a data source for your Crystal report. If utilizing the current Access DB's is a requirement, you could utilize flat file extracts as your Crystal data source.

Is it possible to create the output file on a folder and have LBI import it with a scheduler?

This might help with Leadership needing to run the report on-demand and bogging down the system. The reporting applications wouldn't need to be modified since all the reports are delivered in Excel, PDF and CSV. And I already have the reports fully automated, the only difference is the delivery method which is email.

LBI offers the functionality of creating every user's email address as a delivery device and you can set it up in such a way that whenevr the report refreshes through the LBI scheduler the report is delivered to the email delivery device.

Since some of the VPs may not like logging into the dashboards to get a report, you can have LBI directly email them the report by setting up threir email addresses each as a delivery device. This procedure is explained in detail in the reporting services manual.

The one constraint that I have had with this, is when the report is emailed the report can only have a pdf output but this may have changed in newer versions where a csv output could be delivered via email.

CSV file? YES you can deliver any LBI output to email! We are 2 versions behind - 9.0.3.0 vs. latest 9.0.4.0. Our LBI delivers both Excel and Excel Data-Only reports via email. Reports can be bursted as they are emailed also.

We also have a new method of setting up on-demand reports. Users never have to "go into" LBI. Instead, we set up reports in LBI, set up the users in LBI (which also applies bursting security when appropriate), but then we set up a Portal bookmark that directly invokes running the program from Reporting Services. The user just clicks on the bookmark, enters the parameters of the report, and the report appears in the Portal. Really neat, and it 'hides' LBI from the user, making it look like we set up a special report in Lawson just for them.

Thanks Milo for your reply. For sure I know the excel option works. We are on 904 and have not yet tested this again but will try and hopefully everything will work.

Thanks once again.

This is a great post - thanks for sharing your experiences. Are the users with e-mail devices required to have Lawson Security IDs? joanna.bledsoe@ps.net