How to add months to a list

[SBpsc]

Hi:

I am using CR 10 with a MySQL database. I have the following setup in my database:

Date Revision
01/03/05 3
01/19/05 8
02/06/05 4
05/12/05 3

In Crystal Reports, I need to be able to list all the months even if we have no data for it. I need to enter the months that are missing and give them a value of 0 or null. In other words, I need my table to look like the following:

01/03/05 3
01/19/05 8
02/06/05 4
03/01/05 0
04/01/05 0
05/12/05 3

Can anyone help me with this. Thanks in advance.

 

[MisterMo]

What is the table structure? Is there more than one table involved? If the Revision Dates is set on a different table all you need to is a left join between the Revision Dates and Revisions

Mo

 

[SBpsc]

The date and revision columns are all housed in one table.

The database format is like the following:

Col 1 | Col 2 | Col 3
Record 1 | Date | 01/03/05
Record 1 | Rev | 3
Record 2 | Date | 01/19/05
Record 2 | Rev | 8

and so on...

Any ideas?

Thanks,
SB

 

[MrBillSC]

How is your report selecting the records? Are you prompting for a date range? A calendar year? A fiscal year?

MrBill

 

[SBpsc]

MrBill

I am using a formula that sets the date range from Current Date to 12 months prior.

Thanks,
SB

 

[MrBillSC]

I haven't tested this, but I think it will get you in a direction that will work. You will probably need to work out the kinks.
The idea is to create 12 formulas, each one calculating the date of the first day of one of the 12 months being reported. You might name these {@Month01} through {@Month12}. Create another formula that calcs the first day of the month for the current record being read. You might name this formula {@CurrMonth}. Then group on {@CurrMonth}.
In the group header for {@CurrMonth} create 12 subsections and place one of the {@Month##} formulas in each subsection. Next to the {@Month##} date enter a zero in the revision column. These subsections will be conditionally suppressed and will print if the month they represent is skipped (no data).
The formulas:
//{@Month12}
Date(Year(Today),Month(Today),01)
.
.
.
//{@Month01}
EvaluateAfter({@Month12});
DateAdd("m",-11,{@Month12})

//{@CurrMonth}
Date(Year(table.date},Month(table.date),01)

//example to conditionally suppress GH subsection for {@Month10}
{@Month10} <= Previous{@CurrMonth} or
{@Month10} >= {@CurrMonth}

MrBill

 

[Madawc]

If you're able to create tables in your database, a better solution would be a table of months. If this was linked to the data by a data-month, and as a 'left outer', you'd always get at least one entry per month. And could count it as zero if the data-date was null.

Madawc Williams (East Anglia, UK)

 

[SBpsc]

Thanks MrBill. I will test out your idea. It definitely gives me some new pointers...

Thanks Madawc...unfortunately, we can't create another table but if we could, your idea would be great.

Thanks for the effort, all.

- SB