Dear Cretanion,
I write Crystal Reports for MagicTSD customers all the time. Your issue here is that you are trying to report on something that is Not In the database.
What I do is write a stored procedure that gives me all the dates in a time period (usually a year). The startdate in the Stored Procedure is a Paramater.
I create write a report in Crystal and Select the Stored Procedure. Mine happens to be called SP_DateReport_R.
When you select the SP to report on the StartDate parameter pops up and you select a startdate. I select 01/01/2002. Now I have in Crystal the Stored Procedure that looks just like a table and shows a value for every date in the year.
I group my report on the Date column in the Stored Procedure by Month and Suppress (No drill down) the details section.
Next I create a formula for the MonthGroupStartDate as a shared datevar (date variables) to hold the startdate and
do the same with a formula for the MonthGroupEndDate to hold the ending date for the month.
Once you have all that set up, you then create a subreport and choose the Help Desk.Details table.
In the subreport create a formula for a date I called mine subdate. This is basically a dummy field. Just make it a shared datevar. Ex.:
//@subdate
Shared Datevar Mydate;
Mydate
Link the subreport using the MonthGroupStartDate and MonthGroupEndDate fields each to the subdate.
Select and insert the Fields you want from the Help Desk.Details view, Date, Action, Description, etc. into the details section of the report. Hide everything but the group footer. Insert the Grand Totals for whatever field you are wanting to count.
I made another formula that will print 0. If no records were retrieved, then the formula shows 0 and if records were returned and the count <> 0 then I suppress the field. These two fields sit on top of each other so I either see the GrandTotal or a 0.
Last thing you need to do is change the select expert:
Here is an example of mine that works!
Date({Help_Desk.Open Date & Time}) in {?Pm-@MonthStartDate} to {?Pm-@MonthEndDate}
Now place the subreport in the group footer.
Works like a charm for me.
Your result will look as follows:
My report that I explaining this from shows counts of calls opened for a month.
January 2002 3285
February 2002 4295
March 2002 2035
April 2002 0
May 2002 0
etc.
Contact me if you need help with this!
Microflo provides expert consulting for MagicTSD and Crystal Reports.
Rosemary Lieberman
rosemary@microflo.com,
Microflo provides expert consulting on MagicTSD and Crystal Reports.
= 0 then 0