Skip to Content

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.

Untitled.jpg

Attached to this blog post is a sample report that shows the custom function being utilized with the second formula. Please extract the contents and change the .txt extension to .rpt.

To report this post you need to login first.

18 Comments

You must be Logged on to comment or reply to a post.

  1. Lyle Hardin

    Excellent Example.  I used this example and expanded it to print missing Times on a single Day’s schedule of appointments.  I added formulas to print times at the beginning and ending of the day.  I would be happy to post those examples – not sure how or where to do that.

    (0) 
    1. Jamie Wiseman Post author

      thanks Lyle. there’s a couple of choices for posting the sample reports. and thanks for offering to do this as time sheets etc. are a popular topic. i would recommend that you create a blog post featuring your examples. you can then attach your reports to the blog post after changing the .rpt extension to .txt.

      (0) 
    1. Jamie Wiseman Post author

      hi Daniel, sure…please see the blog post here. several methods are mentioned here. you can use a command object to generate the data or a popular method is to use a look-up table in your db or even an excel spread sheet containing all of the required dates.

      also, if you have a database that has Series Data functionality, then you can use series to generate the data. as an example using SAP HANA the following syntax using a series table will bring back any missing dates…the generated series G is joined (left join) to your measured data, W in this example.

      SELECT W.SENSOR, W.TMP, G.GENERATED_PERIOD_START

      FROM (

        SELECT

        GENERATED_PERIOD_START

        FROM SERIES_DATA.SERIES_GENERATE_TIMESTAMP (

           SERIES TABLE SERIES_DATA.WEATHER_HOURLY

           )

        ) AS G

      LEFT JOIN SERIES_DATA.WEATHER_HOURLY AS W

      ON W.TIMER = G.GENERATED_PERIOD_START

      ORDER BY G.GENERATED_PERIOD_START;

      (0) 
  2. Uvesh Vhora

    Hi Jamie,

    I wanted to bring multiple missing dates but it is bringing only one missing dates.Capture.PNG

    Can you please help me to bring out all missing dates

    (0) 
    1. Jamie Wiseman Post author

      hello Uvesh Vhora,

      there’s one more step in the workflow that you need to do for the dates to show up…

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


      also, you’ve got lines in your report so you may want to right click on each line and choose Format Line and select “Extend to bottom of section when printing”. 


      cheers,

      -jamie

      (0) 
      1. Jamie Wiseman Post author

        p.s. if you have a look through my blogs on cr there are other methods where you can create date records for your report vs. this particula method which basically fills in a formula.

        in your case you’ve got lines and perhaps other formatting features which may be difficult to deal with using the method above. however, if you actually create the date records then this won’t be an issue. one such method is to create a lookup table for your database containing all dates and use this as the main table to drive your report. this table could also just be an excel file should you not have admin access to your database.

        best of luck.

        (0) 
        1. Uvesh Vhora

          HI Jamie,

          I am using view to bring the record in my report. is there any way to create the lookup for view. if yes than can you please write down the syntax ?

          Regards,

          Owais

          (0) 
      2. Uvesh Vhora

        Hi Jamie,

        Thanks for your quick & valuable response. Jamie, my requirement is to build the whole report based on date so i am storing that date and want to use that date-variable to get weekday name, to check for leave, e.t.c but when i am using that variable it is displaying that “Bad Date format string“.

        I need to have a line after each dates.

        Can you please help me to solve out this problem ?

        Regards,

        Uvesh

        (0) 
        1. Jamie Wiseman Post author

          hi Uvesh,

          have a look at my other blog posts on “missing dates”, in particular this one here. it has links to a couple of others where you can decide what method to use.

          as per the forum rules though please post any further questions into a new discussion.

          one other method that’s not there is to use Series Data in a Crystal Report Command object to create the data if your database supports that…this is written in your database’s native language so you’ll need to go to a forum on your particular database if you decide to do this. this particular method works in SAP HANA.

          best of luck,

          -jamie

          (0) 
    1. Jamie Wiseman Post author

      hi Kurt,

      you can use syntax like the following in your formula…you just need to change ‘yourTable’ to match the name of the table that contains your service date field.

      Days_Between ({yourTable.service_date}, next({yourTable.service_date}), ‘MMM. d, yyyy’)

      regards,
      -jamie

      (0) 
      1. Jamie Wiseman Post author

        hi Kurt, this particular function didn’t have that built in. however since custom functions are open, if you’re feeling industrious…

        …or you may wish to look at other options as this is more or less a limited functionality(no pun intended) to display only the dates that are missing. many report developers also want to display other things on the report, have those values in a chart or cross-tab, have a separate details / record line, etc.

        have a look at this blog post which also links to different options to create the actual dates themselves. this will provide a lot more functionality as your reports can have each date in a range and those dates will be actual records which can be used everywhere.

        https://blogs.sap.com/2014/01/31/missing-dates-in-your-report-create-them-using-a-command-object/

        cheers,-jamie

        (0) 
  3. Jeremy Chan

    HI Jamie,

     

    I’ve put the formula as showed by you into Crystal Report, however I am getting this msg at the ‘datefield’ and ‘nextdatefield’.

     

     

    (0) 
    1. Jamie Wiseman Post author

      hello Jeremy, sorry for the delay in getting back to you.

       

      as a guess your formula which is calling the custom function is causing the error (again, just guessing) as it looks like your custom function is identical to the one i’m using too.

       

      your formula should have syntax like the following, which uses the Ship Date field from the sample xtreme database.

       

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

       

      check to see if the date formatting piece at the 3rd input is correct…e.g. ‘MMM. d, yyyy’

       

      regards,

      -jamie

      (0) 

Leave a Reply