changing weekstart date whem summarising crosstab column by week

[warner]

crystal 8.5 has a default week start day of sunday. I have found code to alter this within a reports group structure. However for the report i am writing i cannot group by date.

I have a main report which links to a sub-report. i have a crosstab in the sub-report which has a date field in the column, which i have then grouped by week. However the main report returns totals by week (mon-sun) and the sub-report crosstab returns summaries for week (sun-sat). is there any way i can trick the crosstab into starting the week on monday instead of sunday?

 

[poujor]

I'm guessing a bit now:
In the main report you group on a formula based on the code you found.
If this is the case you propbably have to use the same formula in the crosstab in the subreport

 

[warner]

no i dont. the main report contains a whole set o fformula fields which basically calculate totals by week(mon-fri) from one table. i.e.

week1 week2 week3 week4 week5 etc

tickets opened
tickets closed

I then am using the subreport to get totals from another table of tickets worked on. I have to do a distinct count in the sub-report as the same ticket could be worked on more than once in a week. the only way i could see to do this was to create a crosstab with date worked as the column, then group by week. but i then hit the problem that crystals working week defaults sun-sat. If i didnt have to do a distinct count it would of course be very easy i would just duplicate the formula structure from the main report.

The code i found on crystals site enables you to cheat the default week when using a group structure in the report, But i was wondering whether there was any way of doing the same in a crosstab grouping option I am already restricting the data in my selection for the sub-report to be the same as in the main report ( 8weeks starting on amon finishing on a sun). Even with this in place the grouping within the crosstab still defaults to sun-sat.

 

[lbass]

In the crosstab, use the following formula for the column field:

datepart("ww",{table.date},crMonday)

Then highlight the field while still in the expert->group options->customize group name->use a formula to customize name->and enter:

totext({table.date}-dayofweek({table.date},crMonday),"MM/dd/yyyy")

-LB

 

[warner]

thanks lbass, that worked. now is there a way to get that to then display in correct date order,as it is sorting in text order currently?

 

[lbass]

If you used the datepart formula for the column field, then it WILL sort in correct order. The other formula is just for the display, so that you can see the week beginning date instead of the weeknumber.

-LB

 

[warner]

ah yes thanks, got it now. thanks for the help