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
How to Setup Functions
Write and save a formula, preferably
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
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
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