Grouping by a Date

[menuch]

I am trying to group by a date and only display the months. The problem is I dont have all dates in the field to show a full calendar year. How can I do that so for the months that I dont have the total will be 0 (Zero)?

Menuch
Report Designer

 

[synapsevampire]

This is an age old problem in CR, you want to display 0's for periods that don't exist.

My pat answer is to resolve this problem forevermore by creating a Periods Table and joining the Periods table to your data, then use the date criteria against the period table.

A Periods table has various attributes, such as Year, month, day, date, fiscal period, holiday, etc.

Standard fare in Data Warehousing.

In your case, make sure that you add in a row which has months but no date, then you can select for month = X and date is null.

Since a Grouping is based on existing data, you can't really do this within Crystal - without some elaborate non-reusable code, doesn't make sense in the long term.

If you hadn't needed to group on it, we might have had a simple solution.

OK you could create a formula to check the next month at the report header and group footer level and output month + carriage return + 0 to cheat this in a loop to cover the difference.

-k

 

[menuch]

Ok, I undrstand what you are saying, but can you elaborate more on the formula at the end?

Thanks.

Menuch
Report Designer