Show dates with no corresponding values

[Former Member]

Howdy all....

I have a report that quantifies newspaper draws by the day of the week for the purpose of giving to the delivery persons. I am trying to use a cross-tab to show the amount of papers for each location, in columns by the day of week. When a location is closed on a particular day (having a null entry in the draw record), there is simply no record for that day, leading to a situation where as one looks down the column in the cross tab table, one is not necessarily looking at the same day's data in the column. For instance, if Sunday is the first day of the sales week, I would like Sunday to be the entire first column, irrespective of the amount allocated to that location, so that the entire column sums to be the total for that date rather than for the first day that the location has papers assigned to it. Every location would have a value for all seven days, regardless of the number of days with draws assigned.

Hope that make sense. I'm certain that it's something that should be apparent to me, but it just isn't. Any help is greatly appreciated!

Edited by: MicahY on May 3, 2011 4:47 PM

[Former Member · Accepted solution]

YOu will need to build a manual cross tab where you have formula to define your columns.

eg

@sunday

if dayofweek(yourdatefield) = 1 then valuefield else 0

repeat for each day. add these to details and suppress detail section

Add sum summary to your relevant group.

Now you will get a column for each day irresepective as to whether there is data or not.

Ian