Is this possible in Crystal Reports?

[Former Member]

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:

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)

[abhilash_kumar · Accepted solution]

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.

-Abhilash

[Former Member · Accepted solution]

Hello Sir Abhilash, thanks for the response.

Having all dates be joined from a table was what I had in mind before, I didn't know it was a good idea.
However this would be my first time to tackle look-up tables / fields and it would entail many questions for me. Like how do I populate my field by dates then? Or is there a setup or property I must tweak within the table or field?

Thanks again!

[abhilash_kumar · Accepted solution]

You may google on how to populate a date field with all dates from a few years ago to the next few years.

-Abhilash

[Former Member · Accepted solution]

Thanks Abhilash Kumar!

No it is not easy to Google, I've been searching for half an hour and get no helpful results at all.

But still I'm grateful.

[JWiseman · Accepted solution]

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,

jamie

Custom Function - Display Missing Date Values2.txt.zip