Null Dates in Group 1

[briesen]

This may be an unusual request, but I'm not sure.

I have a report where everything has a value:

Customer
Order Number
Date
Value

When I group this report by month and there is a month with no values, I want the report to automatically field out the fields as zeros.

For this example, the month of February does not have any data and here is how I want it to appear in Crystal:

Month Count Value
January 5 $20
February 4 $30
March 0 $0
April 2 $10

Is this possible to do?

 

[lbass]

If you show the results in a footer, you can use running totals that evaluate using a formula:

month({table.date}) = 3

Reset never (or on change of group where the results will be displayed). Create 12 running totals, one for each month, and use text boxes to label each.

-LB

 

[briesen]

I'd like to follow this up again because I was wondering if there is another method to doing this.

I have a report grouped by billing date by month. With this report, I want it display all the information for each month, and then if it's null, I want it to field out my sums and counts as zeros for the entire year.

I had read an idea of creating a new table that just lists the month-year as a column and then linking with my current database.

Would that work for this case?

 

[lbass]

I would set up a new table with dates and then link to your original table with a left join FROM the all dates table TO the original table. You can only use selection criteria on your new dates table if you want to maintain the left join, however.

-LB

 

[briesen]

Thanks.

I guess my next question is that the main data I actually use is in mm/dd/yyyy hh:mm:ss AM/PM. Would my date table need to have every date listed in the year in the same format?

In the end, I'm going to group it by month.

This is where I get a little confused with linking.

 

[lbass]

I think you have to have a record for every second during the time period, since AFAIK you would have to match the format exactly, and you would only return all records if the full datetime matched--which of course makes this not really feasible.

If you were able to create the table in the same database and then access both tables through a command, you could potentially link the tables using functions that convert the datetimes to dates.

-LB