Ordering the days

[janedane]

Hi All,

CR9.2

I have a report that is run over a date range. It displays the number of appointments in a crosstab by Day and time. The Formula I use to display the Days is as follows...

if DayofWeek({AEA.date}) = 1 then
"Sunday"
else
if DayofWeek({AEA.date}) = 2 then
"Monday"
else
if DayofWeek({AEA.date}) = 3 then
"Tuesday"
else
if DayofWeek({AEA.date}) = 4 then
"Wednesday"
else
if DayofWeek({AEA.date}) = 5 then
"Thursday"
else
if DayofWeek({AEA.date}) = 6 then
"Friday"
else
if DayofWeek({AEA.date}) = 7 then
"Saturday"

In the cross tab the days are displayed alphabetically i.e. Friday, Monday, Saturday etc...

The customer would like me to display the days in the order in which they actually are for the Date Range.

For example if they run the report for the 18th Oct 2004 to the 18th Nov 2004 the First day is a Monday, then they would like Monday to appear as the first Day. If they ran it for the 19th Oct they would like Tuesday to be the first day in the cross tab and so on and so forth...

Would anyone have any idea how to do this?

Many thanks,
Nicky

 

[lbass]

Use {AEA.date} as your column field. In the crosstab expert, click on {AEA.date} after adding it as your column field, and go to->group options->customize group name->use a formula as group name->x+2 and enter:

weekdayname(dayofweek({Orders.Order Date}))

If you want all Mondays together, all Tuesdays together, etc., then create a formula:

dayofweek({AEA.date})

Use this as your column field and then use the customize group name option as mentioned earlier. Be sure to refresh the report after making the changes in order to see the customized name.

-LB