Show zerro count for month 1

[cretanion]

I need to see the all the months in a date range even if they are zero. I have it based on help_Desk_details.action id = housecalls. I am showing a six month range in one column "(help_Desk_Details.Date)" and count of the months in another "Count ({Help_Desk_Details.Date}". Even if there are no housecalls for the month I would like it to show the Month and a zero.
I am assuming there is a hard coded code for this, but I am not getting it. I tried this, but it only shows if there is a count > 0.

if Count ({Help_Desk_Details.Date}, {Help_Desk_Details.Date}, "monthly&quot = 0 then 0
else Count ({Help_Desk_Details.Date}, {Help_Desk_Details.Date}, "monthly&quot

 

[dgillz]

Make your 6 months 6 separate columns with formulas like the one below:

@Jan
if {Help_Desk_Details.Date} in Date(2002,1,1) to Date(2002,1,31) then 1 else 0

Then sum the six formulas.

Software Support for Macola, Crystal Reports and Goldmine
dgillz@juno.com

 

[cretanion]

The columns need to stay like this

September 23
October 0
November 14
December 0
January 11

I have parameters\formulas that ask for the dates. Is this possible?

 

[dgillz]

It can be done, but you must put some dummy data in your database so that crystal will pick it up, even if the dummy data is zero. If no record is found you will continue to get the same results.

If you are grouping by say, employee, you will need to put the dummy data in for each employee in order to see all six months for that employee.

Please post again if you have any questions. Software Support for Macola, Crystal Reports and Goldmine
dgillz@juno.com

 

[csm2]

cretanion
Are grouping on month?

 

[rosemaryl]

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,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.