Lawson Crystal Reports and Prod Verses Dev

Hi Layne

I write my sql into the Crystal report and publish into Production since these reports aren't updating anything. Of course, prior to publishing into production, I test the Crystal reports by running outside of LBI and making sure they work correctly. Right now, we're not using any stored procedures but I'd suspect we'd have to do that in a test or dev environment/area first since we play by I.T. rules then. Have you guys written any stored procedures for your reports so far? I'd be curious to know what rules are set for stored procedure creation, testing, and publishing are. Is it just I.T. that creates stored procedures, or can your more technical business analysts also create them?

I don’t have that many reports, but have a similar issue.

I have two directories set up, one for production, one for dev. I write one Crystal report in production and put it in the production directory with a prd_ prefix.

I then do a save as, rename the report with a dev_ prefix and save it in the dev directory. I go into the Crystal Database \ Set Datasource Location option to point to the correct server for development.

If it is a revised report, the republish option can be used in LBI. Otherwise, I publish the new ones to their respective dashboards. (We are in the process of upgrading, so cannot publish in the same place.)

I have not done this, but in your situation with that many reports, I would think of having two LBI dashboards separated by Tabs, one called Production, one called Development.

We don’t use stored procedures.

Good luck!

@Jan. Yes, the majority of our reports use Stored Procedures and when they are not used, it usually means the Report Writer didn't have that access to the database. The things that Stored Procedures can do to the database could be devastating since they can delete, insert, update, and truncate (to mention some of the things) the data. We have some Analyst in our rather large organization that can write SQL at this level, but it would be posted by someone on our team. They could not post it themselves.

@Jan. Yes, the majority of our reports use Stored Procedures and when they are not used, it usually means the Report Writer didn't have that access to the database. The things that Stored Procedures can do to the database could be devastating since they can delete, insert, update, and truncate (to mention some of the things) the data. We have some Analyst in our rather large organization that can write SQL at this level, but it would be posted by someone on our team. They could not post it themselves.

>In developing Crystal Reports and their associated Views, Stored Procedures, and SQL Commands, where do most of you do your work?

I never do development in production before running my scripts in a non-prod environment. It is a major violation of best practices to do report design work in production first. Depending on your level of permissions, you could potentially cause outages at worst and at best degrade system performance.


>Do you do most of it in a Lawson Development Environment and then transfer all needed SQL and RPT files to Production, or simply do your work in Production to start with?

Yes, this is the best way to control impact on the production environment.

> If you needed to maintain a Development Environment so your users could always have access to all of your reports, how do you keep the Production and Development Product Line Crystal Reports, Views, and Stored Procedures synced without a decent amount of manual intervention?

You may already be doing this but I would recommend trying to create a "semantic" layer for all your reports. Id imagine a lot of your report logic has just slight variations on each other, mostly around filters (WHERE clause). Create common views or stored procedures that returns the base data set for collections of reports and only customize the WHERE or Selection Expert for each report. Youll still need to run ALTER scripts for your DB objects in PROD when youve made changes in DEV.

In terms of reports synch, with LBI the only complete way to do this is to do database restore speriodically. Make a backup of the source RS database and restore it to the target RS database. then copy the directory for the source report file repository and copy it to the target.

The short answer is, if you want to truly synchronize 2 environments, theres no quick way to do it. You can also try using Solution Packs to just bring specific reports from one environment to another.

>Once a report is published to Production and changes are desired, do most of you just make those changes in Production or in Development first.

Again, always in development.


> Not sure if we are large by some comparisons, but we have over 200 SmartNotes and over 200 Crystal Reports and this can be a major ordeal. Our users have occasional need to test some of the reports in Development, and actual reports are sometimes needed to test changes or upgrades to LBI, as well as patches that may change the structure of the database. Just looking for ideas on how others approach this issue. Thanks in advance.

I would say thats a pretty hefty load, of course that all depends on user load and frequency of use as well as the size of the data sets of each report but I would think what would benefit you most would be a data warehouse, data mart or at least reporting tables. In this case you dont have to worry so much about production/development question since the data is decoupled from your operational systems. Refresh can be handled via an ETL process to load the tables and you dont have to worry about migration as much.

hth,
Matt