|
Return to Newsletter Contents...
Slice, Dice and Julian
by:
Aaron Zechman,
BI Consultant, MCSE
Custom functions are formulas that are
created in Crystal to make calculations on, transform and evaluate data.
Using a custom function performs all the operations defined within it, so
that all the code does not need to be defined again.
While slicing, dicing and Julian may
be cuts in cooking they also all relate to data.
The data we return from Crystal may not always be in the format we need
it to be in so we have to manipulate the data using formulas and/or functions.
One example of this is when a database stores a Julian date instead of
the Gregorian date that we are used to seeing printed (ex. 11/10/2007).
Now although Crystal has many functions already created for a user to use
there is not one to convert from a Julian date to a Gregorian date.
This is OK though because through the option of creating our own
functions we should be able to do this.
First, there are two types of functions, report functions and repository
functions. The difference between
the two are that report functions are only good on that report and are editable,
while repository functions can be used throughout many reports and are not
editable unless disconnected from the repository.
So to create a function inside the formula workshop, right click on
report custom functions and click New.
The code in the box that will need to be typed or created from one of your
existing formulas may look like this (convert Julian date to Gregorian date):
Function (numberVar
v1)
local numbervar z :=
floor(v1+.5,1);
local numbervar w :=
floor((z - 1867216.25)/36524.25, 1);
local numbervar x :=
floor(w/4, 1);
local numbervar aa :=
floor(z+1+w-x, 1);
local numbervar bb :=
floor(aa+1524, 1);
local numbervar cc :=
floor((bb-122.1)/365.25, 1);
local numbervar dd :=
floor(365.25*cc, 1);
local numbervar ee :=
floor(((bb-dd)/30.6001), 1);
local numbervar ff :=
floor(30.6001*ee, 1);
local numbervar days := bb-dd-ff;
local numbervar months;
local numbervar years;
if((ee-13) <= 12 AND
(EE-13) > 0) then
months := ee - 13
else
months := ee - 1;
if (months = 1 or months =
2) then
years := cc - 4715
else
years := cc - 4716;
date(years,months,days);
Now once created, if you have Crystal
Enterprise or Business Objects Enterprise you can right click on this formula
and then select add to repository.
Go to Top |
Return to Newsletter Contents
|
