Crystal 9 start of the week 2

[Bhavna13]

Hi,

I've created a crystal report using the packaged crystal 9 that comes with VS.NET. It works fine except that when I group on Date for each week the week starts on a Sunday and I need it to start on a Monday.

Can anyone please please please point me the right direction on how I can change this? In SQL there is the datefirst function. Is there an equivalent function when using crystal reports?

thanx.

 

[Naith]

DayofWeek({Your.Date},crMonday)

 

[Bhavna13]

Thanks. But where would i put it? i tried including it in my recordselection formula (wasn't sure where else) and it gave an error.
does this function set the first day of the week or just tests to see whether the first day is Monday?

Sorry, im a little new crystal.

 

[Naith]

You didn't provide an example of data, so I just provided the basics - how to force the first day of the week in a date function.

Based on the information you've provided, you don't need to amend your record selection criteria. You just force your day of week into a weekly formula: DatePart ("ww", {Your.Date}, crMonday) and group on that.

Naith

 

[Bhavna13]

Sorry. Here's more information.

i have a dataset that is being used as the datasource for the crystal report. In it i have 2 tables : Job and JobDetails.
These two tables join on the JobNumber.
Job has fields JobDate,Customer.
JobDetails has fields description,units.

Using the Report Export,i created the report with jobnumber, jobdate, description, Customer,units fields.
in the group selection screen of the report export i chose the jobdate field, set the sort to ascending and grouping for each week. and displays the sum of units ofr each week.

this report does the grouping. displays correctly. But the week starts on sunday. So if there were jobs done on 22May (Sunday) it would be counted in the week of the 22nd rather than the week of the 16th(Monday before).
I want it to be part of the week of the 16th and the new week start on the 23rd.

I hope this is enough detail.

Thanx for ur help.

 

[Naith]

Did you follow the instruction in my previous post?

 

[Bhavna13]

you didnt say :where: to do this.. please remember im fairly new to crystal reports

 

[Naith]

Okay, let's take it from the top:

Create a new formula, and copy and paste the following:

DatePart ("ww", {Job.Date}, crMonday)

What this will do is number the weeks according to a start date of Monday. (i.e. 01/01/2005 = 01 30/12/2005 = 52).

Create a group on the new formula. At this point, you will see Crystal create and place an automatic Group # Header field. Erase the field it creates and insert your Job Date in it's place. Place your report fields in the details section. Run the report.

You should see your data grouped by week, Monday through Sunday.

Naith

 

[Bhavna13]

Thanks you so much!!
It worked.. The dates are all in a wierd order though.. i'll have a look at trying to sort it by date.. for some reason it didnt even though i set that to sort ascending

 

[Naith]

Maybe it's because your data spans over more than one year. See if it changes when you change your grouping formula to this:

ToNumber(ToText(Year({Job.Date}),0,"") + ToText(DatePart ("ww", {Job.Date}, crMonday),0,""))

Naith

 

[Bhavna13]

The grouping is fine. But I made the change, and it looks better.
It was the rows within the group that were not in order but i fixed it by adding a sort rows grouping.

You were a great help!


Bhavna.