Skip to Content
avatar image
Former Member

Is this possible in Crystal Reports?

I am only capable of grouping my data by dates IF THE DATA EXIST IN THAT DATE.

But if there is no data in the field, how do I still show the date? (from the beginning to the end of the month)

I've stumbled across this article: Displaying All Missing Dates In a Range - A Custom Function Sample

But in the later parts of displaying the formula, no result is showing, although there are no errors in my function nor the formula already.
An administrator in a Crystal Report group in Facebook suggests that I convert the "Basic Syntax" given in this article to "Crystal Syntax".

I have changed the parameter from "date" (as the article suggested) to "datetime" because it gives an error in that paramater: "Field type must be of date." I am guessing it's because it conflicts with my MS Access field's type.

Any help would be appreciated!

(Using Visual Studio 2010)
(Using OLEDB to populate reports)
(Using MS Access 2010 file as a database)

Gs6Gp.png (21.7 kB)
Untitled.png (100.5 kB)
Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

1 Answer

  • Best Answer
    Sep 11, 2014 at 04:10 AM

    Hi Ya,

    Another way to make sure you see all dates on the report is by creating a look-up table, either in the database or in an Excel Sheet, that has all the dates.

    The lookup table on the database is a more preffered option in terms of performance.

    With a Lookup table that contains a column with all the dates, you can Join it to the existing table (Left Outer Join) on the Date fields.

    Now, in the report, wherever you use the Date field from the existing table change it to use the Date field from the Lookup table. This includes Groups, Record Selection formula etc.


    Add comment
    10|10000 characters needed characters exceeded

    • hello Ya EL,

      there are several ways of dealing with missing dates in a data-set without going back to the data base and creating a new table or adding on an excel xls with the dates to your report.

      you can either use the custom function method that you've been working on, or you can create the dates in the data-set using a command object.

      for the custom function method, if you find that at the end of the report you're still missing dates, then please see the attached sample report. extract the contents and convert the .txt extension to .rpt.

      note that the report uses a custom function as well as a formula with added syntax. this added syntax checks to see if the report is on the last record. if it is on the last record then any missing dates are added at this time.

      i hope this helps,