Display all days in a month

[kmcclung]

I need to create a report that displays a value for each day of the month, regardless of whether or not there is data on each day. Is there a way to print each day of the month so that it resembles the following example:

Number Percent
1/1 84 45
1/2 47 23
1/3
(would be blank because there is no data for 1/3)
1/4 79 36
. (and so on) . .
. . .
. . .
1/31 62 30

Thanks in advance!

 

[IdoMillet]

If you have a table (e.g., Order Table) with a field (e.g., Order_Date) where you know all days are present, you could group on that date field and select the by day grouping option.

Otherwise, you would need to create a table with all possible date values and create an Outer Join from that table to the rest of your data.

Cheers,
- Ido ixm7@psu.edu

 

[kmcclung]

Isn't there a another way to display the dates without creating a table? Ie. If 1/21/02 is entered as the parameter, print/display all the days in the month of 01 and year 02?

 

[goranm]

Hi !

Crystal Decision have a couple of sample reports
regarding Date and Time.
And there is one that shows how to display all dates in
a range even if they not exists in the database.

Here is the adress for downloading the file:

http://support.crystaldecisions.com/communityCS/FilesAndUpdates/cr_date_time_samples.zip

Hope it well help you !

/Goran

 

[Hattusas]

In my opinion the most effective way is to create a table that holds all the dates in a year like IdoMillet suggested.I will cost only single fields with only 365 records but,will help you perform exactly what you need.Also you will have to use some other methods unless you create a table and these operations may not run correctly in some situations and may decrease performance.

 

[HowardHammerman]

I agree with the last comment. That is the best way. The only other way is to create the entire report in the report footer section and hard code the dates as text fields (ugh!). then you will have to use running totals or indicator fields to create your totals.

For example, you will have to create one field for each day of the month (call this one FEB05)

if ({table.datefield}) = Date(2002,02,05) then 1 else 0

And then you will have to create one field for each total you want to display in the columns (call this one TYPE_ABC):

if ({table.type}) = "ABC" then 1 else 0

Finally you will have to create a formula to hold the actual value for each cell. Call this one FEB05_TypeABC).

FEB05 * TYPE_ABC * {table.amount}

Note that if either of the first two fields are false, the result will be zero.

Place this in the details section and then create a grand total.

Yuk, ugh, feh!!!

Create the table of dates. Howard Hammerman,

Crystal Reports training, consulting, books, training material, software, and support. Scheduled training in 8 cities.
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[kmcclung]

Thanks for the feedback. However luckily, after explaining the work out, the client is satisfied with only seeing the dates where there are records.