Tuesday, 26 February 2013

Crystal Reports: Three of My Favourite Formula Techniques



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.
CurrentDateDay (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

2 comments:

  1. This is AWESOME! This did exactly what I needed it to. Thanks for sharing.

    ReplyDelete
  2. Thank you. This worked brilliantly.
    Used 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!!!

    ReplyDelete