Here's a way to chart created, closed, and still open. First create a command to use as your datasource:
Select 'Created' as type, DocumentChangeRequest.`RequestCreatedDate` as alldates, DocumentChangeRequest.`Request ID`,DocumentChangeRequest.`Status`
From DocumentChangeRequest
Where DocumentChangeRequest.`Status` <> 'Withdrawn' and
DocumentChangeRequest.`RequestCreatedDate` >= dateserial(year({?Run Date}),month({?Run Date})-17,1) and
DocumentChangeRequest.`RequestCreatedDate` < {?Run Date}+1
union all
Select 'Closed' as type, DocumentChangeRequest.`RequestClosedDate` as alldates, DocumentChangeRequest.`Request ID`,DocumentChangeRequest.`Status`
From DocumentChangeRequest
Where DocumentChangeRequest.`Status` <> 'Withdrawn' and
DocumentChangeRequest.`RequestClosedDate` >= dateserial(year({?Run Date}),month({?Run Date})-17,1) and
DocumentChangeRequest.`RequestCloseedDate` < {?Run Date}+1
Insert a group on {command.alldates} on change of month. Then create a formula {@null} by opening and saving a formula with entering anything. Then create these formulas:
//{@CreatedDate}:
if {command.type} = 'Created' then {Command.alldates} else
cdate({@null})
//{@ClosedDate}:
if {command.type} = 'Closed' then {Command.alldates} else
cdate({@null})
//{@Open} to be placed in the group footer for the date group:
whileprintingrecords;
numbervar open := {#Created}-{#Closed};
shared stringvar showopen := showopen+ totext(open,0,"") + '^';
...where the running totals referenced in {@Open} are set up like this:
//{#Created}:
count of {command.requestID}
evaluate based on a formula:
{command.alldates} = {@CreatedDate}
Reset never
//{#Closed}:
count of {command.requestID}
evaluate based on a formula:
{command.alldates} = {@ClosedDate}
Reset never
Then insert a subreport in the report footer. Copy and paste the command from the main report into the command area for the subreport.
In the subreport, create the following formulas again: {@null},{@CreatedDate},{@ClosedDate}.
Insert a group on {command.alldates} on change of month.
Then create a formula {@showopen}, but DO NOT place it on your report:
whileprintingrecords;
shared stringvar showopen;
val(split(showopen,"^")[groupnumber])
Insert a chart in the report footer of the subreport, and add {command.alldates} as your 'on change of' field->group options->on change of month. Then add {@CreatedDate},{@ClosedDate}, and {@ShowOpen} as your show value fields, and for {@ShowOpen} check "do not summarize".
-LB