Group by: Date; but the date range is not there!

[geek15]

I am grouping data by date in half-year sections.
If a certain half-year contains no records it is left out.

The situation is this: Every 6 months x percent is added to the unpaid amount on an invoice i.e. a type of interest. All payments a customer makes toward an invoice in a 6 month period are displayed. At the end of the period the x percent is added to the unpaid amount. BUT when the customer makes no payments during a period the period does not show up in the report, and the x percent is NOT added. How do I make all 6 month periods between two dates (the invoice date and the current date, which is not Now but a date field) show up so I can calculate the charge?

 

[briangriffin]

Typically I would create a date table in the database, then left join it to your data. This ensures that each date interval appears in the report, even if you have no records for that period.

Is this an option for you?

 

[geek15]

Modifying the database is something that would be a last resort. It is possible, but would require script, as one program - and one report - has multiple data sources that need to match. Every time a new database is created, it needs to be modified.

Is there an easier way?

 

[Madawc]

One option is a 'Mock-Crosstab'. This is something that looks like a Crosstab, but in fact you define each column yourself, normally as a running total. This would need to go in the report footer, because running totals count as the reports 'run' and they will not be complete until then. Crystal should have included an example along with the Crosstabs.

You can save a little time by doing a paste to a dummy report, changing the name and then pasting back. In Crystal 11.5, you can also duplicate formula fields using the Field Explorer.

Each running total will count the record if it was within the criteria - in your case, six-month intervals. You'd get these from Currentdate using 'DateAdd', which can also subtract.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[geek15]

OK so i know next to nothing about crosstabs so i might need some help there though your explanantion sounds good.
two things - 1. currentdate is completely irrelevant. 2. i assume something more than dateadd is required since these half year periods must correspond to the first or last half of the year.

 

[briangriffin]

What is your backend database?

 

[Madawc]

Crystal will give you quarters, so group quarters 1 & 2 and then 3 & 4. Combined with the year this will give you half years.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[IanWaterman]

What is your select statement.

I am guessing you are filtering on payment date.

Thus if invoice is 6 months old and no payments it does not show in report.

Do you have an unpaid flag?

thus filter becaomes

(PaymentDate in {?startdate} to {?enddate}
OR
Paidflag = false)

wrapping in () allows you to add futher clauses.

Instead of paid flag could be balance amount <> 0

For grouping use a formula

If isnull(paymentDate) then invoicedate else paymentdate.

Ian

Ian

Ian