Displaying All Missing Dates In a Range - A Custom Function Sample

[JWiseman]

If you have a date field on your report, you may have days missing on the report. Sometimes there may be a need to simply display the days that are missing so that the sequence is not interrupted.

For example, your report may look like this...

Dec. 11, 2011      1234

Dec. 12, 2011      4322

Dec. 15, 2011      5631

Dec. 17, 2011      1092

And what you'd really like to have displayed on the report is this...

Dec. 11, 2011      1234

Dec. 12, 2011      4322

Dec. 13, 2011

Dec. 14, 2011

Dec. 15, 2011      5631

Dec. 16, 2011

Dec. 17, 2011      1092

There are ways of changing your database to include a lookup table to ensure that you get all days returned, but that could be a lot of work and you only need a simple way to display a space on the report for each missing date. If that's the case, then this blog post will help you create a Custom Function to display missing dates.

Please note that this function is helpful for display purposes and does not actually populate records that are missing. If you need to have missing dates populated on a report or missing from a Group, then you need to create a lookup table in your database that has all dates.

To create the Custom Function...

Steps:

1) in Crystal Reports go to the Report menu then Formula Workshop

2) right click on the Report Custom Functions folder and choose New

3) give the Custom Function a name of "Days_Between"

4) copy the code from below to paste in the formula

Function Days_Between (datefield as date, nextdatefield as date, format as string)
' This function is only used to display what data is missing within a specified date range...output type is text

dim thisdate as date
dim nextdate as date
dim output as string 'output is the text display
dim daysbetween as number
dim looptimes as number
looptimes = 0
daysbetween = 0
thisdate = datefield + 1
nextdate = nextdatefield

if nextdate - thisdate > 1 then daysbetween = nextdate - thisdate else daysbetween = 1
do
if nextdate - thisdate > 1 _
then output = output + totext(thisdate, format) + chr(10) _
else _
if nextdate - thisdate > 0 _
then output = output + totext(thisdate, format)

looptimes = looptimes + 1
thisdate = thisdate + 1
loop until looptimes = daysbetween

    Days_Between = output
End Function

5) change the output of the formula to Basic Syntax

6) Save and Close the function

You  now have a Custom Function that you can use to display missing dates.The function is expecting the following inputs:

1. datefield as date


2. nextdatefield as date


3. format as string...you can use 'yyyy/MM/DD' for example...see "Format strings for Date, Time, and DateTime values" in your Crystal Reports online help for more formatting options

Now, to use the function on your report.

1) if you want to display the missing dates in your details sections, then create a new details section below your existing details sections

2) go the Section Expert for the new details section and choose Suppress Blank Sections

3) create a New Formula that will use the Custom Function.

If you want to display the missing dates from a single database date field, then you can create a formula with syntax similar to below, changing the Ship_Date to your date.

Days_Between ({Ship_Date.Date}, next({Ship_Date.Date}), 'MMM. d, yyyy')

4) place this formula on the new details section

5) right click on the new formula and choose Format Field > Common tab > select Can Grow

You will now have a details section that will show up when there are missing dates in the date sequence. In this new details section, a formula will display the missing dates.

If you are using this technique and you find that your report is missing dates at the end of the report, then when you create the New Formula that will use the Custom Function, use syntax similar to below. In this example, the dates will be filled out based on an "EndDate" parameter.

datevar thisdate:= date({Orders.Order Date});

if not onlastrecord then datevar nextdate:= date(next({Orders.Order Date}));

if onlastrecord and thisdate < {?EndDate}

    then nextdate := {?EndDate}+1;

Days_Between (thisdate, nextdate, 'MMM. d, yyyy');

In the screenshot below, any of the dates created by the function have a rectangular border. The last set of dates that are circled, are created in conjunction with the formula syntax above.



Here is a sample report that shows the custom function being utilized with the second formula.