Displaying breakdown of record count with different selection criteria

[excalibr]

Hi all,

Can someone give me a few pointers to this problem? Objective: Display count of records in 3 different scenarios:

1. Finish Date > Deadline
2. Finish Date <= Deadline
3. Finish Date is NULL

In addition, these filters should be applied to narrow down the above:
- Report ID = &quot;123456789&quot;

The individual SQL statements are simple:

- select count(*) from MYTABLE where REPORT_ID = '123456789' /* total count */
- select count(*) from MYTABLE where REPORT_ID = '123456789' and FINISH_DATE > DEADLINE
- select count(*) from MYTABLE where REPORT_ID = '123456789' and FINISH_DATE <= DEADLINE
- select count(*) from MYTABLE where REPORT_ID = '123456789' and FINISH_DATE is NULL

But how do I display these 3 individual pieces of information in Crystal Report? I've tried using Groups - Specified Order but that doesn't work because it only allows you to compare against predefined dates and not another field.

Thanks very much, Excalibur

 

[FVTrainer]

Do these 3 scenarios occur in the same instance of the report? In other words, your record selection formula is not the 3 conditions you've listed, but instead within the report you want to find out the count for each of the 3 conditions. Is that what you are asking? Also, what version of Crystal are you using?

 

[Reebo99]

Edit the records selection formula and leave just :

Report ID = &quot;123456789&quot;

Then create 3 formulas :

If Finish Date > Deadline then 1 else 0

If Finish Date <= Deadline then 1 else 0

If Isnull(Finish Date) then 1 else 0

Place these 3 formula in the detail of the report. Right click a formula select insert > summary > Sum.

This will give you a conditional count for each item....

Reebo
UK

 

[synapsevampire]

Or just create 3 Running Totals and in the evaluate use a formula place the criteria for each, as in:

{table.FinishDate} > Deadline

{table.FinishDate} <= Deadline

isnull({table.FinishDate})

-k