Charting on Two Date Fields

[nige5000]

Hi,

I have a table with three fields in the form:

ID---Date_Opened---Date_Closed

I would like to show on the same graph the number of items open and closed for each month, bearing in mind that some items will not yet be closed, and that some months may not have had a record opened.

I thought this would be a simple graph to generate but just cannot do it. After searching through the forum I cannot see a similar example...

Can someone please point me in the right direction here?

Many thanks,

Nige

(CR v9, MS-SQL DB)

 

[Madawc]

First, have you got the report grouping the way you want it?

Grouping on a date gives you the option of grouping by month, at least it does in Crystal 10. Failing that, you can always use Month({your.date}).

You might also need to group on a formula field using one date or the other, e.g.

if isnull({date.closed})
then Month({date.opened})
else Month({date.closed})

Madawc Williams (East Anglia, UK)
Windows XP & Crystal 10

 

[lbass]

I think you need to have a separate table containing all dates for this to work correctly. You would use the date from that table for the "on change of" field, setting it to print on change of month.

-LB

 

[nige5000]

Madawc - thanks for your suggestion but unfortunately grouping on one of the date fields does not provide expected results e.g.

ID-----DO------DC
1---01/Jan---01/Feb
2---01/Jan---01/Mar
3---01/Feb--- "null"

expected results are to sum totals for each month for DO & DC and present on same graph (I can do the summing discretely but cannot present on the graph)so
Jan DO=2 DC=0
Feb DO=1 DC=1
Mar DO=0 DC=1

Grouping on DO would give you:

Jan
---
1
2
Feb
---
3
Mar
---
<null>

...and counts would not work for the grouping - counting DO will give correct results but DC counts will not work.

LB - this suggestion seems to be the way I was thinking of (joining the tables to replicate some values as required), but I have minimal DB permissions and creating a new table is not one I can do easily. I was hoping for an elegant CR solution using arrays, subreports or something similar.

...and I thought this was a simple problem!

Thanks for your responses,

Nige

 

[lbass]

You could do a series of formulas of the form:

//{@Janopened}:
if month({table.dateopened}) = 1 then 1

//{@Janclosed}:
if month({table.closed}) = 1 then 1

Repeat for each month. Place these in the detail section. Then in the report footer add the chart, choosing "for all records", and adding each formula in the show value area, using sum as the summary.

-LB