Skip to Content
author's profile photo
Former Member

Show dates with no corresponding values

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

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

1 Answer

  • Best Answer
    author's profile photo
    Former Member
    Posted on May 03, 2011 at 03:28 PM

    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

    Add comment
    10|10000 characters needed characters exceeded