Help with charting Quarterly values

[cheerfulskeptic]

I have a db table called complaints
there is a field called ComplaintID, and ComplaintDate

in the report, the only input the user gives is Quarter (ie, Q1, 2, 3, 4) and Year

now, what I need to have in a report is a on the X-axis, plot the Dates per quarter starting from the right hand side with the most recent quarter the user input, ending on the left hand side of the previous 8 (ie, 2 years) quarter data. On the Y-axis is the number of complaints (count of ComplaintID).
I do this, but I cant seem to set a date range, and I cant seem to plot the quarters where there were zero complaints.
Any help would be appreciated. Thanks!

 

[paulmarr]

Hi Cheerful,

Dont know if I can help you with your full problem - but the reason that you cant get the quarters (or time periods) to appear where there were no complaints is because there are no records.

I had this problem before - and by adding a "periods" table as the driving table - and then joining it to your db table with a left outer join - this will return all "period table" records - and any records from the db table (complaints). Crystal cant return records that aren't there - so if you want a date/month/year to appear where there are no records - this is the way to do it.

Maybe you want to create the table with everyday of the year in it and link this date field to the one in your db.

I hope this helps!

Cheers,

paulmarr