Showing Zero Results for one Date in a Range

[DukeStKing]

How do I force a report to show results (even if zero matches) for days within a date range??

Example : I run a report to get me all transactions between 10/15 and 10/17 with a summary for each date.

Right now, I am getting the following :
10/15 Count : 2 Amount : 123.00
Trans 112 Amount 83.00
Trans 118 Amount 40.00
10/17 Count : 1 Amount : 62.50
Trans 117 Amount 62.50

I would like to see the above plus:
10/16 Count : 0 Amount : 0.00

Any ideas or suggestions would be helpful...thanks.

 

[MalcolmW]

I think the easiest solution is to create a simple table with a date field with a row for every date for the period you are likely to be reporting on.
You can now left outer join your other table(s) to this field, but use this table for grouping and filtering the date range.
This is a good practice for reporting, as this general "all dates" table is useful for many types of reports. By adding another "status" field to the table, you can also use the table to store info about whether a particular day is a stat holiday, regular business day, etc (which occasionaly impacts on reporting).
Talk to your DBA about adding this table - even if you do have sufficient rights to create tables, it is always a good idea to keep the DBA in the loop on new tables. Malcolm
wynden@telus.net
November is "be kind to dogs and programmers" month. Or is that "dogs or programmers"?

 

[kenhamady]

Unfortunately, there is no easy way to force groups to appear that don't exist.

You could create a table that had all of your groups (ie days)listed, and do an outer join to the other tables required by the report. The trick there is to not nullify the outer join by putting in criteria on the fields in the other tables. You might even have to do a subrepor for each day, using your days master table as a source.

You could also ensure that the report captures at least one record from each day, even if you (via formula) have the report ignore the values from these extra records. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[MalcolmW]

Ken,
I am curious about "You might even have to do a subreport for each day, using your days master table as a source."

Why would you consider doing something like this, let alone be forced to consider it? Maybe it is my natural prejudice against subreports that is blocking my vision... Malcolm
wynden@telus.net
November is "be kind to dogs and programmers" month. Or is that "dogs or programmers"?