Weekly Monday to Sunday in cross tab

[Luvsql]

I have been searching for an hour and still cannot get my columns to be weeks (M-Sunday) based on a date field. I am using CR 9. I have tried to modify the following formula from the BO website, which is F-T, but it doesn't work.

if dayofweek({Table.Field}) = [1,2,3,4,5] then
(numbervar x := (-(dayofweek({Table.Field}) + 1));
(dateadd("d", x, {Table.Field})))
else
if dayofweek({Table.Field})=6 then
({Table.Field})
else
if dayofweek({Table.Field})=7 then
(dateadd("d", -1 ,{Table.Field}))

I need the report to group by M-Sunday and show the date of the Monday ie:
October 25 (contains all dates from 25th - 31)
November 1 (contains all dates from Nov 1-7
November 8

 

[synapsevampire]

Try:

datepart("ww",{table.Date},crmonday) for grouping.

To display use the above formula and in the group options select the options tab-customize group name->use a formula:

// @blah is your group by field from above
// Substitute minimum(yeartodate) with your starting date
datevar Thedate:= cdate(dateadd("ww",{@blah},minimum(yeartodate)));
if dayofweek(TheDate) = 1 then
totext(dateadd("d",-6,thedate))
else
totext(dateadd("d",dayofweek(thedate)-1,thedate))

-k

 

[Luvsql]

I don't have a starting date ie the data set will always contain a Monday as its minimum date (ie I run a stored proc each Monday to update a table that this report looks at; the sp updates the table with Getdate (Monday) and on worth of data).

I tried subt. in my date field and it gives me an error saying a number is required here (where I put the date).

 

[Luvsql]

Sorry, put the wrong field in. Works great, except now it's printing datetime as my column headings. Trying to figure out how to convert it to a date instead of datetime.

 

[Luvsql]

As well, when I just use the datepart("ww",{table.Date},crmonday) for grouping, the headers show 1, 44, 45 (which converted using the other formula turns out to be January 12, 2004, then Novemer 8th, Then November 15th).

The smallest date in my date is October 25, which is a Monday, and there are all zeros in the first 1 column. Why does the Oct 25-Oct 31 group not print?

 

[Luvsql]

The formula provided by BO works perfectly for F-T and the January 12 does not print, and it starts at Oct 22 (which is the last Friday). Can this not easily be modified to work with M-Sunday?

 

[Luvsql]

I used this and it works great:

WhileReadingRecords;
DateVar MyDate := {Table.Date Field};

//DayToStart value will be from 1 to 7. Sunday = 1, Monday = 2,...Saturday = 7

NumberVar DayToStart := **PLACE NUMERIC STARTING DAY VALUE HERE** ;
NumberVar DaysToSubtract;

//If MyDate is earlier in the week then the DayToStart, then calculate DaysToSubtract as follows

If DayToStart > dayofweek(MyDate) then
DaysToSubtract:= 7 - DayToStart + dayofweek(MyDate)

//Else calculate DaysToSubtract as follows

Else
DaysToSubtract:= (dayofweek(MyDate) - DayToStart);

//MyDate - DaysToSubtract will return the previous Monday

MyDate - DaysToSubtract