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 17

^{th}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 1

^{st}, 2^{nd}, 3^{rd}, or 4^{th}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!!!