Reporting on data that is not there

[charbaugh]

I'm an intermediate Crystal developer. I have an Active Data (ADO) report that has two groupings. The first one is Salesman and the second is Meeting dates. The report groups by Salesman. For each salesman the report will list meetings for the current and next workweek. My questions is: The report will not display the workday if there are no meetings for that day. I need the report to group by workweek Sun-Sat regardless if there are meetings or not. How do I design the report to display blank days if there are no meetings for that day?

Thanks,

Corey

 

[vidru]

This type of thing is usually done by having a Calendar table in the db that can be used in an outer join to get the dates even if there's no data.

If you need to build a calendar table, have a look at this FAQ: faq767-4532

-dave

 

[Madawc]

AS vidru says, the best solution is to create a table for each category, and then link it using a left-outer to link to the details. This depends on you being allowed to create a new table, or can easily get one created to your specifications.
Another solution is to include a wider range of data, one that will include something for every date. Then suppress printing for unwanted records, showing just the group header or footer. This is an inefficient use of machine-time, but might be the simplest solution from your point of view.
A third way is to create running totals for all possible vendors. Show them in the report footer when the total is zero. Suppose that records may be for apples, oranges, pears, bananas or guavas. You can group by type of fruit, but that does not show cases where there are no oranges. So you do a set of running totals (which are an option under the [Field Object] on the [Insert] menu). Each of them does a count of something, maybe account number. But each uses the formula option on the [Evaluate] section, and counts just for the one fruit.
You could also put each running total in a different section of the report footer. Suppress that section when the total is greater than zero, so only omitted groups are mentioned. It must be the report footer, because running totals are accumulated during the course of the run.

Madawc Williams (East Anglia, UK)