So far I've only been able to create a table in the report with the info I require.
Getting a chart will be the tricky thing because ideally it should all be in the same series.
In the report table I define my weeks for the current months with 5 fields - one for each week in the month.
I look for the last day in the current month and find out the week number for that day.
Then I go backwards 5 weeks to get all weeks in that month.
Later in the report I sum up the individual fields and get the weekly total.
My database table contains the weeknumbers so I look for the highest week number of the year and use that as a reference (this is DB field {POOpenOrders.MaxWeekNo})
//field 1 = week 1
IF ToText(pwformatdate(ToNumber({POOpenOrders.CurrentDate})),"yyyyMM") = ToText(DateAdd("m",0,currentdate),"yyyyMM")
AND DATEPART("ww",dateadd("m",1,currentdate-day(currentdate)) , crMonday) - 4 = {POOpenOrders.MaxWeekNo} //get weeknumber of 1st week of the month
THEN
{POOpenOrders.ExtendedCost}
ELSE
0
//field 2 = week 2
IF ToText(pwformatdate(ToNumber({POOpenOrders.CurrentDate})),"yyyyMM") = ToText(DateAdd("m",0,currentdate),"yyyyMM")
AND DATEPART("ww",dateadd("m",1,currentdate-day(currentdate)) , crMonday) - 3 = {POOpenOrders.MaxWeekNo} //get weeknumber of 2nd week of the month
THEN
{POOpenOrders.ExtendedCost}
ELSE
0
//field 3 = week 3
IF ToText(pwformatdate(ToNumber({POOpenOrders.CurrentDate})),"yyyyMM") = ToText(DateAdd("m",0,currentdate),"yyyyMM")
AND DATEPART("ww",dateadd("m",1,currentdate-day(currentdate)) , crMonday) - 2 = {POOpenOrders.MaxWeekNo} //get weeknumber of 3rd week of the month
THEN
{POOpenOrders.ExtendedCost}
ELSE
0
//field 4 = week 4
IF ToText(pwformatdate(ToNumber({POOpenOrders.CurrentDate})),"yyyyMM") = ToText(DateAdd("m",0,currentdate),"yyyyMM")
AND DATEPART("ww",dateadd("m",1,currentdate-day(currentdate)) , crMonday) - 1 = {POOpenOrders.MaxWeekNo} //get weeknumber of 4th week of the month
THEN
{POOpenOrders.ExtendedCost}
ELSE
0
//field 5 = week 5
IF ToText(pwformatdate(ToNumber({POOpenOrders.CurrentDate})),"yyyyMM") = ToText(DateAdd("m",0,currentdate),"yyyyMM")
AND DATEPART("ww",dateadd("m",1,currentdate-day(currentdate)) , crMonday) = {POOpenOrders.MaxWeekNo} //get weeknumber of 5th week of the month
THEN
{POOpenOrders.ExtendedCost}
ELSE
0
IP Logged
