Showing weekdays in Cross tab report

[ransmen]

i have a cross tab report where i display weekdays in column and count of person booked a flight for each week

something like

Sun Mon Fri Sat

05/01/2005 1 2 4 7
05/08/2005 7 6 1 9

the problem here is it displays week days only for those
records which exist on a given day like this will not
display Tue as there is no record for that day

i would like to display it in this format

Sun Mon Tue Fri Sat

05/01/2005 1 2 0 4 7
05/08/2005 7 6 0 1 9

how can i do this with a cross tab query

currently for displaying days i am using this function

select DayOfWeek ({tblMember.BookDate},crSunday)

case 1:'1-Sun'
case 2:'2-Mon'
Case 3:'3-Tue'
case 4:'4-Wed'
case 5:'5-Thu'
case 6:'6-Fri'
case 7:'7-Sat'


and for rows i have done a grouping on tblMember.BookDate
for each week for the first day in period

summary is the count of memberid in the same table

any help is appreciated

thanks

 

[lbass]

You won't be able to do this with an inserted crosstab unless you have a table that contains all dates that you can use to derive your column field.

However, you can easily create a manual crosstab. You would insert a group on date (weekly) and then create formulas like:

//{@Sun}:

if dayofweek({table.date}) = 1 then 1

//{@Mon}:
if dayofweek({table.date}) = 2 then 1 //etc.

Then right click on each formula and insert a summary (Sum, not count). Then suppress the detail fields.

If you have duplicate records, you would need to use running totals instead where you would select the member ID, count, evaluate based on a formula:

dayofweek({table.date}) = 1 //for sunday

Reset never or on change of group (flight?).

Running totals would have to be placed in a footer section. If you can use the formulas, that would be preferable as running totals can slow reports.

-LB