Top Tips for Date Calculations in Crystal Reports

/ Uncategorized / By

Here are some of our Top Tips for Date Calculations in Crystal Reports:

Calculate Days between two dates

Date calculations are frequently used to calculate a period of time, for instance a number of days that has passed since an event has occurred.

Formula Name:@Processing Days
Formula Purpose:To calculate the number of days it took to process an order from the Order Date to the Shipment Date.
Table NameOrders
Field NamesShip Date & Orders Date
Actual Formula:{Orders.Ship Date}-{Orders.Order Date}

Boolean Formulas

A Boolean can only provide with a True/False response. The method used is to test a condition and gives you either response as appropriate.

You can also format the field to give a Yes/No or Y/N response if preferred. Right click over the formula when it has been placed in your report and select Format Field. The Boolean tab will be displayed automatically, click on the drop down list box and select Yes/No.

Formula Name:@Boolean
Formula Purpose:To display a True/False or Yes/No reponse by testing a value. For example to test the result of the above Processing Days formula to confirm if the Customers Goods have been despatched or not. The result can then be formatted to display a Yes/No or similar response.
Table NameCustomer
Field/Formula Name@Processing Days
Actual Formula:{@Processing Days} > 0

Calculate Current Age

  1. In Crystal Reports, open the report on which you have a database field that contains birthday.
  2. Create a formula and enter the following code:

DateVar birthday := <INSERT THE BIRTHDAY FIELD HERE>;
DateVar vToday := Today;

// Verify if the birthday was on a leap year, and if the current year is a leap year.
If Month(birthday) = 2 and Day(birthday) = 29 Then
If not isDate(ToText(Year(vToday),0,””)+”,2,29″) Then
birthday := birthday + 1;

// Calculate the age
DateDiff(‘yyyy’,birthday,vtoday) – (If Date(Year(vToday),Month(Birthday),Day(Birthday)) > vToday Then 1 Else 0)

  1. Insert the date field in the formula where it is written: <INSERT The BIRTHDAY FIELD HERE>
  2. Save and close the formula.
  3. Insert the formula on the report where you want to display the age.

Group On A 4-4-5 Accounting Period

  •  
    1. In Crystal Reports, open the report on which you want to group by an accounting period of 4-4-5
    2. Create a formula for the accounting period 4-4-5, like:

 WhileReadingRecords;
Local DateVar myDate := <INSERT YOUR DATE FIELD HERE>;

Local NumberVar myWeek := DatePart(“ww”,myDate);
Local NumberVar myMonth := Switch (myWeek in 01 to 04, 1, // 4
myWeek in 05 to 08, 2, // 4
myWeek in 09 to 13, 3, // 5
myWeek in 14 to 17, 4, // 4
myWeek in 18 to 21, 5, // 4
myWeek in 22 to 26, 6, // 5
myWeek in 27 to 30, 7, // 4
myWeek in 31 to 34, 8, // 4
myWeek in 35 to 39, 9, // 5
myWeek in 40 to 43, 10, // 4
myWeek in 44 to 47, 11, // 4
myWeek in 48 to 52, 12, // 5
myWeek = 53, 1 // 4
);

  If myWeek < 53 and myMonth in 1 to 12 Then
Date(Year(myDate),myMonth,1)
Else
Date(Year(myDate)+1,myMonth,1)

Notes:
– Insert in the code above, your database field of data type: Date, where it is written <INSERT YOUR DATE FIELD HERE>
– The formula will output the first day of the calendar month for each 4-4-5 accounting period.
– The above formula is an example on how to group by accounting period, but may require modification to fit your specific accounting period definition.

  •  
    1. Save the formula.
    2. Under the menu “Insert”, select “Group”, and group by the formula created in step 2, in ascending order, for each month.

Create A Calendar-Style Report

Often there is a requirement to create a report showing appointments or bookings on a day-by-day basis, just as they would appear in a calendar or month planner.

The calendar table

In order to create this type of report, you will need to add a simple calendar table to your database. As a minimum, this table consists of one column, with a row for each day of the year to which the calendar will apply. The rows contain consecutive dates within the year.

If you are unable to add tables to your main database, don’t worry. You can easily set up the table in Excel. To do so, enter the date of the first day of the year in cell A1. Then drag the fill handle (the small square in the bottom right corner of the cell) downwards until you have created a range of dates for the entire year – or for as many years as you like.

The appointment table

You will also need a table containing the appointments, bookings or other data that you want to appear in the calendar. This table will need a date column, plus a column containing the text that you want to display in the calendar. There is no problem in having more than one appointment for a given day, provided there is enough space in the day square (in this article, I will refer to the data as appointments, but the technique will work equally well with room bookings, rental reservations, or anything similar.)

Start a new report

Having identified your tables, the next step is to start a new report. Add the date table to the report. Then proceed as follows:

  1. Change the page orientation to landscape. To do so, open the Page Setup dialog from the File menu (or, depending on your version of Crystal Reports, the File / Print menu), then choose the Landscape option.
  2. In the report, insert a group based on the date field. In the control labeled ‘This section will be printed for’, pick ‘For each month’. Check ‘Use customized group name based on a formula’ and enter the following formula (using your own table and field names in place of xl and date):

Monthname(Month({xl.date}))

  1. Insert a new Page Header section, and drag the group name to the Page Header_a section. Open the Section Expert, select the Group Header #1 section, and check Suppress. Then select the Group Footer #1 section, and check New Page After. In the formula area, enter this formula:

Not OnLastRecord

  1. Create a formula called {@Weeknumber}:

DatePart(“ww”,{xl.date})

  1. Insert a new group (Group #2), based on this {@Weeknumber} formula. Right-click in the gray area to the left of the main report area, and select ‘Insert section below’. There should now be a Group Header #2a section and a Group Header #2b section. Open the Section Expert, select the Group Footer #2 section, and check Suppress.
  2. Create seven formulas, one for each day of the week. Name them {@Sun}, {@Mon} and so forth. The {@Sun} formula should look like this:

if DayOfWeek({xl.date}) = 1 then Day({xl.date})

For the other six formulas, change the ‘1’ to ‘2’ (Monday), ‘3’ (Tuesday), ‘4’ (Wednesday), etc.

  1. Place each of these seven formulas in the Details section. Arrange these in a horizontal row, starting with the formula for Sunday and continuing across the page in sequence.

Note: If you want a different day to be the first of the week, you will need to add the FirstDayOfWeek argument wherever the DayOfWeek and DatePart functions are used in both the main and subreports.

  1. For each of the formulas, insert a summary, based on the Maximum function. Drag the summaries to the Group Header #2a section, positioning them equidistant across the page. Drag the lower boundary of Group Header #2b downward until it is the size that you want to allocate to the day square.
  2. Suppress the Report Header, Details, Page Footer, and Report Footer sections.
  3. To eliminate results of ‘0,’ go to the Format Editor of each of the summaries in turn. On the Number page, choose Customize and check Suppress if Zero.
  4. Next, create the following two SQL expressions, named {%Month} and {%Year} respectively, which we’ll use for linking the subreports:

Month(`xl`.`date`)

Year(`xl`.`date`)

Note that you could use formulas instead of SQL expressions, but the use of SQL expressions will allow your report to run faster.

  1. If your Excel table contains dates for more than one calendar year, create a number parameter, {?Year}. and then add the following record selection formula to the main report:

{%Year} = {?Year}

If you would like the report to show a calendar for one month only, you can create a number parameter for the month and add a second clause to the selection formula:

And {%Month} = {?Month}

Create the grid

  1. Next, place a box over Page Header_b and extend this downwards to the bottom of the Group Header #2b section. Then add vertical lines extending from the top of the box to the bottom to divide the days, and add a horizontal line along the top of Group Header #2a to divide the weeks.
  2. For both the box and the vertical lines, open the Format Editor and check the box labeled ‘Extend to Bottom of Section when Printing.’
  3. Left-justify and reposition the summaries (in Group Header #2a) so they are in the upper left corner of each day box, as you would normally see them in a calendar. In Page Header_b, position the column labels (‘Sun’, ‘Mon’, etc.) so that they are centred above each square.

Add the subreports

At this point, you should see an empty calendar, with one month per page. So far, the steps I have described have been completely generic. You can use them to create the basic calendar grid for a given year, regardless of the data that you want to appear within the calendar.

For the next steps, we will add the actual data. To do this, we need to create a series of subreports.

  1. Create a subreport called ‘Sun’ (choose Insert / Subreport / Create a Subreport). Select the appointment table (or whatever other table(s) hold your data). In the sample report accompanying this article, I used the Orders table from the Xtreme database, and I created a time formula to substitute for the actual {Orders.Order Date}, which always shows time as midnight, in order to create times throughout the day.
  2. Add the field(s) containing the descriptions that you want to appear in the calendar. You can add as many fields as you like here, provided you have enough space. For example, you might add one field containing a description of the appointment, and another holding the appointment’s start time. Add these fields to the Details section of the subreport, and suppress all other sections. Add a record sort by your time field, if you are using one.
  3. Still in the subreport, create a formula called {@WeekNumber}:

DatePart(“ww”,{Appointments.Appointment Date})

  1. Also create three SQL expressions in the subreport, using the delimiters appropriate to your database or connectivity, and naming them as indicated here:

{%DayOfWeek}:

{fn dayofweek(`Appointments`.`Appointment Date`)}

{%Month}:

Month(`Appointments`.`Appointment Date`)

{%Year}:

Year(`Appointments`.`Appointment Date`)

  1. In the record selection formula of the subreport, add the following:

{%DayOfWeek} =1 // 1 = Sunday

  1. Link the subreport to the main report using the {@Weeknumber} formula from each, and also linking {%Year} to {%Year} and {%Month} to {%Month}. Then place the subreport in the Sunday square of Group Header #2, resizing it to fit. Right-click on the subreport, choose Format Subreport, select the Border page, and change the four line styles to ‘None’.
  2. Repeat steps 16 to 21 for each of the other days of the week, taking care to substitute the correct day number in the record selection formula in each case.

Our full range of training courses from introduction to advanced levels covering all Crystal Reports versions are available at our venues in Glasgow and Edinburgh or onsite at your premises.