Missing Date Fields

[Smithsc]

I've written a quick report which produces a list of records and the dates they were added to the table. When I group these record by the creation date and then within the Change Group Options window change the 'Section will be printed' to for each month - I can see how many records were created each month.

My problem is that there are missing months in my result and I would like to fill these in with zero totals.

For example:
Current Result shows

Creation Date --- Sum of Records
01/2013 ---------- 45
02/2013 ---------- 23
05/2013 ---------- 56

I would like to include 03/2013 and 04/2013 as records in my output.

What's the easiest way to do this?

Any help would be much appreciated.

Stuart.

 

[hilfy]

There is no "simple" way to do this. Basically you have to have a "calendar" table in your database that contains all dates. You then do a left outer join from that table to the dates in your data table and group by the date from the calendar table. This way you get all of the dates regardless of whether they have data.

I usually do something like this for the structure of a calendar table:

Calendar_Date
Month_Start_Date
Quarter_Start_Date
Year_Start_Date
FY_Start_Date

By having all of the dates in the table, it makes it easier to group and filter the dates both in Crystal and in other applications.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com