Crystal Reports: Save Time with Formula Sharing

Once a report library grows to a certain size there is always going to be common formulas which 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 travelling 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 standardisation 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.



About the Author

Jason Dove

Jason Dove is a senior consultant at Scry Business Intelligence and the author of

'Crystal Reports Formulas Explained' Currently available with a free 70 page Crystal Reports XI tutorial! Jason is also the mastermind behind the revolutionary Scry Career Primer, the only product on the market dedicated to launching your IT career, covering everything from gaining industry experience and writing a killer risumi to exploiting the most popular IT sectors. Subscribe to his FREE Career newsletter!

Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Organizations are increasingly gravitating toward mobile-first application development as they assess the need to revamp their application portfolios to support touch computing and mobility. Consumerization has brought higher expectations for application usability along with the mobile devices themselves. Enterprises are increasingly shifting their new application acquisitions and development efforts toward mobile platforms. With this backdrop, it is natural to expect application platform vendors to invest in …

  • By now you've likely heard of Agile development and building products in small incremental pieces, so you can get real feedback along the way. In fact, you may even be considering using Agile on your next project. But where do you start? Agile can take a lot of forms, such as Scrum or Kanban. Each form has advantages and disadvantages, but both will help your team get the right feedback they need to build great products. Read this white paper to find out which one is right for you.

Most Popular Programming Stories

More for Developers

RSS Feeds

Thanks for your registration, follow us on our social networks to keep up-to-date