Display All Dates

[DKirksgirl]

How would I display all dates of the month regardless of whether any records were extracted?

Example:
Stat_Dt # Records
06/01/2002 1
06/02/2002
06/02/2002 5
06/03/2002 3
06/04/2002
06/05/2002 8

Even though 06/02/2002 and 06/04/2002 had no records, I still want the date to display on my report.

 

[dgillz]

Crystal doesn't do this - no data means no group. You can trick crystal by putting some "dummy" data in your database for every day - even if it is a value of zero - for crystal to include it in the report.


You could then change your record count into a conditional record count (count only if some value is not zero) to get your desired results.
Software Support for Macola, Crystal Reports and Goldmine
714-348-0964
dgilsdorf@mchsi.com

 

[rosemaryl]

DKirksgirl,

This is easily done using a stored procedure to generate the dates for the period (month, year, whatever).

Since you cannot link a table to a stored procedure you would have to report on the stored procedure and insert a subreport linked on the datefield. This is especially suited to a summary style report as you indicated above.

Let me know if you need more info,

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[synapsevampire]

Since I keep seeing similar requests, I've considered cheating this as follows for those unable to address this from the database side (theory only):

For a full month:

Create 5 string array variables, the first 4 being of size 7, the 5th being the remaining number of days for that month (except Feb). This is to allow for 254 char limit in our text being saved.

Check to see which array needs to be populated in a formula based on the day of the month(arrays 1 to 5)

prepopulate the array values with the text:
month(date of interest)/day(for x = 1 to number of days in the month)/year(date of interest)

Now append to the the array text " " + totext(count({Yourfield)}) as you iterate through your rows.

Now you can use
join(myarray1,chr(13))
join(myarray2,chr(13))
etc.

in separate sections with a can grow.

You'd get:

THE DATE THE COUNT (plus a carriage return)

A VERY unappetizing solution, but, it should work... A little work required yet to flesh out which array to use and the subscripting.

Right, I'm kinda bored...

Ro gave you the best solution, it's very useful to have a period table in a database for this type of thing, standard fare in data warehouses. Then if you can use a Stored Procedure to retrieve the data, you get the best performance with the best schema.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[rosemaryl]

SynapseVampire,

I don't bother having a period table. I use the following stored procedure to generate the dates on the fly:

In the referenced table in the database where I use the stored procedure (MagicTSD Help Desk Software) There are opened dates and closed dates. As you will see the first two parameters are the Start Date and the End Date. Crystal will prompt you for these when you link it. So this can generate a month or a year or years, your choice. It will generate all the dates in between the start date and end date for use on the report.

Here is the code (MS SQL 2000):

if exists (select * sysobjects where id = object_id('dbo.sp_CalReport') and sysstat & 0xf = 4)
drop procedure dbo.sp_CalReport
GO
CREATE PROCEDURE dbo.sp_CalReport

declare
@start datetime,
@end datetime,
@counter int
set @start = '1/1/2001'
set @end = '1/30/2001'
set @counter = 1
select dates.date
from
(
-- This select produces a sequence of dates starting from @start
select DATEADD(dd, seq.id, @start) as date
from
(
-- This select produces a sequence from 0 to 399 using Cartesian product.
select (a0.id + a1.id + a2.id) as id
from
(select 0 id union select 1 union select 2 union select 3 union select 4 union select 5 union
select 6 union select 7 union select 8 union select 9) a0,

(select 0 id union select 10 union select 20 union select 30 union select 40 union select 50 union select
60 union select 70 union select 80 union select 90) a1,

(select 0 id union select 100 union select 200 union select 300) a2
-- ...
) seq
) dates

left join _SMDBA_._TELMASTE_
on dates.date between [DATE OPEN] and coalesce([Closed On], [Closed On], '12/31/9999')
where
dates.date between @start and @end
group by
dates.date
order by 1

I did the above, because I had the issue of showing all the dates and counts of calls on dates even if there were none!

This works like a charm...

ro

(when copying the stored procedures make sure those comments are on one line - the posting here sometimes changes it)

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.