Crystal Reports: Save Time with Formula Sharing
 

by Jason Dove, http://www.scry-business-intelligence.com

Once a report library grows to a certain size there will always be common formulas that are needed again and again. Having ready access to them can save a vast amount of development time.

My book started life as a notepad document which was a list of 25-30 formulas I used a lot, or were so complicated I did not like the idea of rewriting them.

This is fine for a solo consultant traveling from company to company, but for large teams of permanent staff something more robust and expansive is required.

For those companies using a Business Objects reports server (Enterprise, Edge etc) formulas can be saved in the repository just like a report and be accessible by the whole team.

Even a solo developer can benefit from this approach as formulas can be added to a formula through this method far quicker than copy / pasting from a separate text file. There is also the added bonus to the business which gets to keep this knowledge once the report developer leaves.

I’ll end this article with a step by step instruction on how to setup and use this method of formula storage, but first I want to discuss some of the finer points of implementation.

One of the main strengths of this approach is the standardization of report calculations, but this increases the importance of the formulas being right! This may sound obvious, but a formula that is right for one report maybe not be for another.

For example: a formula that calculates working hours based on a five day working week and 08:00 to 16:00 hours is going to be wrong for reporting on a team which works a half day on a Saturday.

With this in mind, I strongly recommend that formulas are double checked, tested and checked again before they are saved to the repository. Ideally, other team members should have an active role in the testing.

In a team of mixed abilities, an extensive library of formulas can lower the required expertise for report writing and really boost productivity.

And, as the code is still visible, new report developers can use it as a study guide to expand their own knowledge.

Unfortunately, saving, or accessing a pre-saved formula requires the developer to log on to the Report Server and so uses up a license. For those companies with only five licenses, this could be a real problem, especially as the Crystal Reports software has to be shut down and reopened to free the license.

One final thing to be aware of, which struck me as odd, is that formulas saved as functions on the Report Server do not handle NULLS. This can make some things just impossible to save in this manner.

How to Setup Functions

Write and save a formula, preferably something useful!

Within the formula editor, select "Custom Function" from the New File dropdown.

Choose to "Use Extractor" and then select the previously written formula.

Crystal Reports will create a Function based on the formula and replace any database fields with variables to allow it’s re-use with other values.

Rename the computer created variable names to something meaningful. I advise using ctrl F and Find & Replace to ensure all references to the variable names are changed.

Upload to the Report Server, where the Function be saved in the Repository Custom Functions folder.

Note the consistent naming pattern to the Functions in the illustration above. This is important for later use and makes finding the correct Function a lot easier.

Using Existing Functions

To use an existing Function is simple:

Open a new formula and expand the Repository Custom Functions.

You may need to log on to the Report Server, then, highlight the Function you want.

Click the cog icon with the red arrow pointing to it.

Go back to the formula you just opened and notice that the Functions Panel now has an additional folder “Custom Functions” which contains the Function just created.

Just double click the Function you want.

Ok, that may sound a little convoluted when approached as an end to end process, but once a library of Functions builds up, accessing it as and when required becomes second nature and a genuine time saver.
 

Jason Dove is a top Business Intelligence consultant and author who has shared his expertise with some of the most respected businesses in the world.  Crystal Reports is his reporting software of choice and the subject of his book "Crystal Reports Formulas Explained" which is the most advanced book on the market.  Currently available with a free 70 page Crystal Reports XI tutorial: http://www.scry-business-intelligence.com/products/bookoffer.php

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.