Chart Question...By month, year to date dilemma....

[jdemmi]

CR XI...Oracle 10

I have an issue I am betting is very easy to workaround or solve but I am blocked....totally...

I need to create a very simple bar graph chart which includes various counts...(Records created, records open, records closed, record past due, etc)

The problem is I want to display every month on the X even if there is no data for that month

So, assume my data looks like this

ID CREATEDDATE CLOSEDDATE DUEDATE
1 1/1/11 NULL 5/1/11
2 8/1/11 9/1/11 8/15/11


I want to see every month represented on the X, not just those for which there is data. And this is where I am struggling because I cannot set the chart's "on change of" to a date in the dataset because there are missing dates in the dataset.

I basically want the count on the Y and the every month (year to date) on the x

Am I making sense? Is this even possible?

Let me know if I can provide more details.

Thanks




-- Jason
"It's Just Ones and Zeros

 

[Madawc]

Are you able to get tables / datasets added to the database? If you got a table of DATE set up, this could be linked to the activity using LEFT OUTER and you'd still get a slot when there was no activity. (But note that any selection on the second table must do an IsNull test first, otherwise you'll not get the dates with no records, null value for the second record.)

Dates can be grouped by month, of course.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP