Crystal Reports Formula Sharing

Crystal Reports Formula Sharing

As previously stated, publishing a book was a painful process. So rather than collect any additional information into a second book, I would sooner share it in the hope of aiding fellow report developers.

And if anything I write is helpful to you, there is a good chance my book will be too. As well as extensive tutorials, there is also a large selection of sample formulas which cover a variety of reporting needs and do a good job of showing how formulas are built.

Once a report library grows to a certain size there is always going 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.

Photobucket

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.

Advertisements

About jasondove

I am a data obsessed consultant who's as happy developing dashboards (Tableau & Power BI) as implementing enterprise wide BI solutions and am always on the lookout for freelance opportunities.
This entry was posted in CodeProject, Crystal Reports. Bookmark the permalink.

3 Responses to Crystal Reports Formula Sharing

  1. I am new to the whole Crystal Report era… I would like to know if there is anyone out there who could teach me a thing or two about CR2008??? i can build a basic report but a more complex report is out of the question right now, maybe some tips and tricks to CR2008???

  2. Pingback: Crystal Reports Formula Sharing | Jason Dove's Business Intelligence Rants

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s