How to tally up occurrences of date ranges ...

[lsantos]

Hi,

I am using CR v7 accessing an AIX/Informix database. My query is that follows:

I have 2 files: event (parent) and tasks (child). Each event is indexed by department and category. Each task as a "completion by date". I need to produce a (cross tab) summary report of outstanding tasks by date range. Therefore I need to compare each task "completion by date" with the (current or parameter) date and summarize totals as follows:

(days) 0-30 31-60 61-90 91-120
==== ===== ===== ======
Dept1 / Cat1 10 5 2 1
Dept1 / Cat2 8 6 4 2
Dept5 / Cat1 2 1 2

Totals 20 12 8 3

I have come across Aged0to30Days, Aged31to60Days, Aged61to90Days, Over90days, Next30Days, Next31to60Days, Next61to90Days, and Next91to365Days functions but I cannot figure out how to use them in this instance as the 0-30, 31-60, ... columns will be calculated on the fly for every event / task. so (as per my example above) every task will add 1 to the corresponding column (i.e. date range).

I thought I could define variables for every period range and increment when the condition is met. I was also looking into arrays and if I could define two-dimension arrays and load them while crystal reads through the files ....

I just don't know if I am on the right track and if crystal is able to compile the information in such fashion. Maybe I have to write a program of some sort to achieve this...

Any suggestion or crystal examples I could use to bring some light into my dim thoughts??

Thank you very much.

 

[lbass]

You could accomplish this with a manual crosstab. Create formulas for your columns like this:

if datediff("d",{table.complbydate},{?date}) in 0 to 30 then 1 else 0

if datediff("d",{table.complbydate},{?date}) in 31 to 60 then 1 else 0

if datediff("d",{table.complbydate},{?date}) in 61 to 90 then 1 else 0

if datediff("d",{table.complbydate},{?date}) in 91 to 120 then 1 else 0

Place these formulas in the details section.

I'm a little unclear on whether the department and category make the event unique, but if they do, just concatenate the values to create a row group {@event}:

{table.dept}+{table.cat}

Group on {@event}, and then insert sums on the column formulas at the group and grand total levels, and then suppress the details.

If you were always planning to base the report on the current date, you could use formulas like:

if {table.complbydate} in Aged0to30Days then 1 else 0

Otherwise I would use a parameter as in my earlier examples.

-LB