Three of my Favourite Crystal Reports
Formula Techniques
Introduction
As an avid fan of Crystal Reports and its
seemingly endless formula creation capabilities, I have, over the years been
presented with many business reporting problems to solve.
In the years that I’ve been training Crystal
Reports, I have noticed that some delegates baulk at the thought of formulas
and shy away from even trying to understand some simple techniques. But it
needn’t be too complex like the example below.
And I have also realised that not everyone that uses Crystal Reports is an SQL expert and this blog is mainly aimed at those of us who aren’t. But I hope it will also be useful for those who are.
Style Guide
- · I will write the Crystal formula syntax in italics.
- · Crystal Reports functions used within the formulas will be shaded blue.
- · The Xtreme Sample database, available from the SAP web site will be used to reference database fields.
- · Crystal Report menu names will be shown in bold.
Technique 1
A Column based formula technique
The first topic on my agenda is how to present a
rolling months report, where the column headings and their respective data
change automatically when the month changes. This is very useful when looking
at a trend over the last rolling twelve months or when forecasting for twelve
months into the future.
This formula example assumes that a reporting
period starts on the first of the month and finishes on the last day of the
month.
First day of the current month
Our first task is to determine how to calculate
the first day of the current month. The Crystal formula below shows how this is
done.
CurrentDate
– Day (CurrentDate)
+ 1
This formula initially takes today’s date and
subtracts the day element of today’s date from itself. So, for example if today
is the 17th of the month, 17 days are subtracted from today. If the
formula were to be left like this the result would show a date that would
always be equal to the last day of the previous month. This is why the ‘+ 1’ is
also included in the syntax, to add one to the result. This formula will always
display a date showing the first day of the current month.
Last day of the current month
Now we need to create a formula to calculate the
last day of the current month.
DateAdd
("m",1,CurrentDate) - Day (DateAdd("m",1,CurrentDate))
The DateAdd function has three elements to input;
an interval type, indicated by the “m”, a numeric value and a date value. The
first part of the formula adds one month to the current date.
Then, by subtracting the number of days from the
month we added on, we arrive at the last day of the current month. This formula
works for all eventualities including leap years.
Combining both formulas into one new formula to create a date range
In isolation, these formulas are fine, but they
can be utilised so much more effectively when combined together to create a
date range. Now both formulae have been assigned a variable name, and an
if-then-else test has been incorporated.
dateTimeVar startmonth := CurrentDate - Day (CurrentDate) +1;
dateTimeVar endmonth := DateAdd ("m",1,CurrentDate)
- Day (DateAdd("m",1,CurrentDate));
If {Orders.Order Date} in startmonth to endmonth
then {Orders.Order Amount} else 0
The order amount will only be shown if the date is
in the current month. As we haven’t specifically shown a static date in our
formula, the current month dates will shift automatically when the new month
arrives.
Column headings
The formula for the column headings is similar.
This uses the ToText function to convert the
date into a string. And then concatenates the month and year names together.
dateTimeVar endmonthcol
:= DateAdd ("m",1,CurrentDate) - Day (DateAdd("m",1,CurrentDate));
ToText
(endmonthcol,"MMM")&
" " & ToText (endmonthcol,"yyyy")
The next selection of formulas completes a whole
year. When used as separate columns in a Crystal Report, they can be summarised
to create the effect of a cross tab. The PrintDate
function can be used instead of the CurrentDate
to allow further functionality as the Print Date can be set from the Report menu. The examples work with
days of the week and individual days. This can be achieved by changing the
interval element of the DateAdd function.
Current month minus 1
dateTimeVar startmonth1
:= DateAdd ("m",-1,CurrentDate)-Day (DateAdd("m",-1,CurrentDate))
+1;
dateTimeVar endmonth1 := CurrentDate - Day (CurrentDate);
If {Orders.Order
Date} in startmonth1 to
endmonth1 then {Orders.Order
Amount}
else 0
Current month minus 2
dateTimeVar startmonth2
:= DateAdd ("m",-2,CurrentDate)-Day (DateAdd("m",-2,CurrentDate))
+1;
dateTimeVar endmonth2 := DateAdd ("m",-1,CurrentDate)-Day (DateAdd("m",-1,CurrentDate));
If {Orders.Order
Date} in startmonth2 to
endmonth2 then {Orders.Order
Amount}
else 0
Current month minus 3
dateTimeVar startmonth3
:= DateAdd ("m",-3,CurrentDate)-Day (DateAdd("m",-3,CurrentDate))
+1;
dateTimeVar endmonth3 := DateAdd ("m",-2,CurrentDate)-Day (DateAdd("m",-2,CurrentDate));
If {Orders.Order
Date} in startmonth3 to
endmonth3 then {Orders.Order
Amount}
else 0
Current month minus 4
dateTimeVar startmonth4
:= DateAdd ("m",-4,CurrentDate)-Day (DateAdd("m",-4,CurrentDate))
+1;
dateTimeVar endmonth4 := DateAdd ("m",-3,CurrentDate)-Day (DateAdd("m",-3,CurrentDate));
If {Orders.Order
Date} in startmonth4 to
endmonth4 then {Orders.Order
Amount}
else 0
Current month minus 5
dateTimeVar startmonth5
:= DateAdd ("m",-5,CurrentDate)-Day (DateAdd("m",-5,CurrentDate))
+1;
dateTimeVar endmonth5 := DateAdd ("m",-4,CurrentDate)-Day (DateAdd("m",-4,CurrentDate));
If {Orders.Order
Date} in startmonth5 to
endmonth5 then {Orders.Order
Amount}
else 0
Current month minus 6
dateTimeVar startmonth6
:= DateAdd ("m",-6,CurrentDate)-Day (DateAdd("m",-6,CurrentDate))
+1;
dateTimeVar endmonth6 := DateAdd ("m",-5,CurrentDate)-Day (DateAdd("m",-5,CurrentDate));
If {Orders.Order
Date} in startmonth6 to
endmonth6 then {Orders.Order
Amount}
else 0
Current month minus 7
dateTimeVar startmonth7
:= DateAdd ("m",-7,CurrentDate)-Day (DateAdd("m",-7,CurrentDate))
+1;
dateTimeVar endmonth7 := DateAdd ("m",-6,CurrentDate)-Day (DateAdd("m",-6,CurrentDate));
If {Orders.Order
Date} in startmonth7 to
endmonth7 then {Orders.Order
Amount}
else 0
Current month minus 8
dateTimeVar startmonth8
:= DateAdd ("m",-8,CurrentDate)-Day (DateAdd("m",-8,CurrentDate))
+1;
dateTimeVar endmonth8 := DateAdd ("m",-7,CurrentDate)-Day (DateAdd("m",-7,CurrentDate));
If {Orders.Order
Date} in startmonth8 to
endmonth8 then {Orders.Order
Amount}
else 0
Current month minus 9
dateTimeVar startmonth9
:= DateAdd ("m",-9,CurrentDate)-Day (DateAdd("m",-9,CurrentDate))
+1;
dateTimeVar endmonth9 := DateAdd ("m",-8,CurrentDate)-Day (DateAdd("m",-8,CurrentDate));
If {Orders.Order
Date} in startmonth9 to
endmonth9 then {Orders.Order
Amount}
else 0
Current month minus 10
dateTimeVar startmonth10
:= DateAdd ("m",-10,CurrentDate)-Day (DateAdd("m",-10,CurrentDate))
+1;
dateTimeVar endmonth10 :=
DateAdd ("m",-9,CurrentDate)-Day (DateAdd("m",-9,CurrentDate));
If {Orders.Order
Date} in startmonth10 to
endmonth10 then {Orders.Order
Amount}
else 0
Current month minus 11
dateTimeVar startmonth11
:= DateAdd ("m",-11,CurrentDate)-Day (DateAdd("m",-11,CurrentDate))
+1;
dateTimeVar endmonth11 :=
DateAdd ("m",-10,CurrentDate)-Day (DateAdd("m",-10,CurrentDate));
If {Orders.Order
Date} in startmonth11 to
endmonth11 then {Orders.Order
Amount}
else 0
Current month minus 12
dateTimeVar startmonth12
:= DateAdd ("m",-12,CurrentDate)-Day (DateAdd("m",-12,CurrentDate))
+1;
dateTimeVar endmonth12 :=
DateAdd ("m",-11,CurrentDate)-Day (DateAdd("m",-11,CurrentDate));
If {Orders.Order
Date} in startmonth12 to
endmonth12 then {Orders.Order
Amount}
else 0
Technique 2
How to extract characters in front of or behind a chosen character
This technique involves combining the Mid and Instr functions
in one formula. In the examples below, I will search for the ‘@’ sign in an
email address and then return all the characters after the ‘@’ sign in the
first example, and all the characters before the ‘@’ sign in the second.
Extract all characters after a chosen character
The Mid function
allows us to extract characters from string fields from any starting point in
the field. The starting character is sometimes depicted by a number. However,
in the example used here, the numeric result of the Instr
function is used to determine the start point.
Mid ({Customer.E-mail},Instr({Customer.E-mail},”@”)+1)
The ‘+1’ at the end
ensures that the ‘@’ sign is not included in the output, just everything after
the ‘@’ sign.
To output everything
before the ‘@’ sign a similar technique is used, but the Left function is used instead. Also, a ‘-1’ at the
end of the formula ensures that the ‘@’ sign is not included in the output.
Left ({Customer.E-mail},Instr({Customer.E-mail},”@”)-1)
The same technique
can also be used to remove the first word in a string field.
Mid ({Customer.Customer Name},InStr ({Customer.Customer Name}," " )+1 )
Technique 3
How to suffix a date field with the appropriate abbreviation
This formula uses
variables to determine the day element of the date and then suffixes the day number
with the appropriate “st’,”nd”, “rd” or “th”. Using this technique we can get
the 1st, 2nd, 3rd, or 4th displayed
on our reports. We have to convert the first character of the day element which
would normally return a numeric output to a string. The formula is used in the
conditional formatting of a date or date time field, in the ‘First’ Separator
section. The format of the date time will need to include the day of the week
for the formula to be displayed correctly.
StringVar
st := "st";
StringVar
nd := "nd";
StringVar
rd := "rd";
StringVar
th := "th";
StringVar
dayno := ToText (Day
({Orders.Order Date}),0);
If
dayno in ["1","21","31"] then
dayno+st
else
If
dayno in ["2","22"] then
dayno+nd
else
If
dayno in ["3","23"] then
dayno+rd
else
dayno+th
Conclusion
I hope you have found
these techniques useful and that you will be able to put them into practice in
your own reports.
Written by: Steve Chapman, Senior Business Intelligence Consultant, DSCallards.
Visit: www.crystalreports.co.uk
Written by: Steve Chapman, Senior Business Intelligence Consultant, DSCallards.
Visit: www.crystalreports.co.uk
This is AWESOME! This did exactly what I needed it to. Thanks for sharing.
ReplyDeleteThank you. This worked brilliantly.
ReplyDeleteUsed that formula in a group to split my transactions report per month. Also used it in the running total to obtain monthly expenses.
You are the best!!!