Fill in missing cross-tab dates

[aks12]

Hi,

I have a cross-tab that shows # of phone calls by day for any given time period that is determined by begin and end date parameters. My problem is that I want to see zeros for days which may not be included in my data table.
For example, instead of this...

October
________________________________
10/1 10/2 10/3 10/4 10/6
Calls handled 65 76 51 37 14


...I want to see this:

October
_____________________________________
10/1 10/2 10/3 10/4 10/5 10/6
Calls handled 65 76 51 37 0 14


My current formula to construct the date field in the cross-tab is:

totext(month({TABLENAME.DATE})) & "/" & totext(day({TABLENAME.DATE}))

How can I make the formula show a date that doesn't exist in the data table?

 

[synapsevampire]

Preferably by making it exist in the data:

faq767-4532

One way is to create a period table, then use your selection criteria against the period table and left outer the period table to your table.

Or you can create a manual cross-tab, and build out the dates in an array and then display them, however this is a good deal of work.

-k