Charting Question 1

[Regelos]

I am working on a chart that shows 3 values per month number opened number closed and number left open, for an 18 month period. I have tried a few things and I cant figure out how to properly format this data to accomplish what I need to do.. anyone have any ideas?

 

[Tatertot45]

How do you want the chart to look? Moths accross the top, Numbers down the left? Month as columns, and the numbers as "subset" within the month columns? Are you having formatting the months to group better? Are you using Crosstab? or something different, like a graph? A few more details would be great. Also be sure to post the version of Cystal you are using. That helps alot as well.

 

[Regelos]

its crystal xi I want to go back 18 months with the months down the bottom then 3 lines (or bars doesnt matter I can change between the 2) One that shows Number opened per month (wich is one field opendate) Number closed (closeddate) and number that basically have no close date.

 

[lbass]

Do you have a separate date field that defines the 18 months? Or what field are you using to define the months?

-LB

 

[Regelos]

basically right now I'm selecting the last 18 months of requsts created and trying to chart when the complete >though I've been considering changing that selection but that works for now<
the problem is I dont have a uniques field that creates the 18 months seperate no.

 

[lbass]

So what field are you using for the 18 months? Please name your fields.

-LB

 

[Regelos]

requests created

 

[Regelos]

and I need to chart number of requests created and requests closed for the past 18 months on 2 seperate line graphs

 

[Regelos]

this is my exact selection formula

{DocumentChangeRequest.Status} <> "Withdrawn" and
{DocumentChangeRequest.RequestCreatedDate} in dateserial(year({?Run Date}),month({?Run Date})-17,1) to {?Run Date} or {DocumentChangeRequest.RequestClosedDate} in dateserial(year({?Run Date}),month({?Run Date})-17,1) to {?Run Date}

run date is just a user entered perameter

 

[lbass]

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

 

[Regelos]

dateserial isnt a valad SQL command I'm going to try to find what command can replace it thank you.

 

[lbass]

It may not be valid for your datasource, but it does work for some, as I tested this solution using the Xtreme database (Access).

-LB

 

[Regelos]

I'm off a stander MS sql source.

 

[lbass]

If you mean MS SQL Server, check out:
thread183-1389500

Or, more generally, forum183, and do a search using "dateserial" as a keyword.

-LB

 

[Regelos]

Select 'Created' as type, DocumentChangeRequest.RequestCreatedDate as alldates, DocumentChangeRequest.RequestID,DocumentChangeRequest.Status
From DocumentChangeRequest
Where DocumentChangeRequest.Status <> 'Withdrawn' and
DocumentChangeRequest.RequestCreatedDate >= DateAdd(Year, 0, DateAdd(Month, DateDiff(Month, 0, {?Run Date})-17, 0) -1) and
DocumentChangeRequest.RequestCreatedDate < {?Run Date}+1
union all
Select 'Closed' as type, DocumentChangeRequest.RequestClosedDate as alldates, DocumentChangeRequest.RequestID,DocumentChangeRequest.Status
From DocumentChangeRequest
Where DocumentChangeRequest.Status <> 'Withdrawn' and
DocumentChangeRequest.RequestClosedDate = DateAdd(Year,0, DateAdd(Month, DateDiff(Month, 0, {?Run Date})-17, 0) -1)and
DocumentChangeRequest.RequestClosedDate < {?Run Date}+1


well this is what I am getting to work selecting times one problem closed doesnt seem to be working do you see anything that chould be stopping it?

 

[lbass]

Why do you think "closed" is not working? After using the union, the closed and created dates will appear in the same field "alldates". If you add {command.type} to the detail field, you should see both closed and created.

I see you also forgot the ">" in the where clause of the second part of the union. Should be >= NOT =. Probably this is the issue.

-LB

 

[Regelos]

that worked thats great