|
Return to Newsletter Contents...
Custom Functions
by: Kristen St. Jean, Sr. Consultant, CRCP
Crystal reports in a very powerful reporting tool that allows us to connect to
many types of databases and semantic layers including Business Objects Business
Views and Universes.
However, sometimes just pulling data into our reports from the data source isn’t
enough. We need to manipulate the
data to display in a way so that it’s useful for end users of the report.
We may need to create calculations to determine a profit for instance.
This would include using fields such as tax, gross sales, cost of goods
sold, and anything else that our business determines should to be taken into
consideration when calculating profit.
Once we have a definition, we can then create a formula in Crystal
Reports that calculates our profit and display it as a field on the report so
that the end users do not need to manually perform this calculation.
Crystal Reports has a very robust formula syntax which allows us to manipulate
data in many ways to ensure that we can give our end users exactly what they
want to see on a report. The only
drawback to creating formulas is that sometimes they can become very complex and
when a certain complex formula is requested on multiple reports, it can become
cumbersome to recreate this formula multiple times.
For example, our profit calculation from above needs to add and subtract
multiple fields and it needs to be created on many reports because most of our
users want to see profit on their reports.
If we recreate the formula on each report, the report creation process will be
extended because we are repeating steps that we’ve previously taken, and we run
the risk of accidentally calculating profit incorrectly.
In order to make a report creation more efficient and reduce the risk of
errors, Crystal Reports has the ability to create Custom Functions.
A Custom Function is a function that is defined by the report designer.
For example, if our Profit calculation is Gross Sales – Cost of Goods
Sold – Tax Paid we could create a function by opening the Formula Editor and
choosing New – Custom Function. We
could name the Function ‘Net Profit’, and choose ‘Use Extractor.’
This will allow us to point to an already created formula and extract the
logic used. In this case, we would
choose our Profit formula, and the Extractor would extract our logic and
everywhere that we had used a field in the formula that will become a variable.
Our Function, ‘Net Profit’, will have three variables.
These will be labeled v1, v2, and v3, but we can edit those names to be
more descriptive such as, Gross Sales, Cost of Goods Sold, and Tax Paid.
Now, we know that when we use this function, these are the three items
that need to be included for the function to work properly.
Up to this point, ‘Net Profit’ is a Report Custom Function and can only be used
in the report we are currently working on.
In order to share it with other reports we can add it to the Repository
if our company has Business Objects Enterprise.
The Repository will allow us to store our Custom Function and use it in
other reports. By right-clicking on
our Custom Function ‘Net Profit’ in the Formula Tree in the Formula Editor, we
can choose ‘Add to Repository’ from the speed menu and save our Report Custom
Function into the Repository making it a Repository Custom Function and anyone
who has access to the Repository will be able to use the Custom Function that we
have created.
In this way, Repository Custom Function can greatly reduce the time it takes to
create reports that share the same complex formula logic and help to reduce
errors in the creation of formulas that need to be standardized across reports.
Go to Top |
Return to Newsletter Contents
|
