Show Blank Dates in Cross Tab Reports

[moodyb]

Does anyone know how to ensure all columns are shown in a cross-tab report even when the columns are not populated with data? I'm working on a cross-tab scheduling report that lists an individual's name based on various shifts (row) for that day (date=columns). The problem I'm running into is there is no one listed on the weekend, yet we want to see the date with an empty cell. Do you know how to do this?

(ignore underscores, entered to help preserve formatting)

Currently, weekends skipped:

__________11/16/12_____11/19/12
Shift1_____Bob__________Jane
Shift2_____Jane_________Bob
Shift3_____Joe__________Joe

Prefered, weekends shown w/blank values:

________11/16/12_____11/17/12_____11/18/12________11/19/12
Shift1___Bob______________________________________Jane
Shift2___Jane_____________________________________Bob
Shift3___Joe______________________________________Joe

 

[lbass]

One way would be to create an Excel spreadsheet that contains all possible dates (use the fill procedure to make this easy), and then add this as a table to the report. Use a left join FROM this table TO the date field in your current table. Add the xls.date field as a column in your crosstab, add shift as the row, and then add the max of the person's name as the summary field.

-LB