crosstab header 1

[bluerain25]

hi again,

need help here.. is there another way in chaning the column header of a crosstab report? here's the scenario: A report should be able to prompt the user for the start date and end date. the start is on the 16th of the previous month and end date is on the 15th of the current month (e.g: April 16, 2008 to May 15, 2008)if the collection day is monday and tuesday then payments from April 16 to 26 is week 1, if wednesday then week 1 is 16 to 22, thursday week 1 is on 17 to 23 and friday week 1 is on 18 to 24. what i did is to use the transation date as column header --> specified order so that i could define teh header as week 1, week2 .. week 4 or week 5 but in some coverage my formula does not work. like there are payments made after the collection date that are supposed to be included in week 4 but in the reportis displayed under week 5.. ideas anyone? i just added the 6 days on the fromdate to get week 1... thanks

 

[lbass]

What determines the collection date?

-LB

 

[bluerain25]

collection date depends on the meeting schedule. example group 1 with 20 members is met every monday. group 2 every tuesday. in the database group table there's a collection day field

 

[lbass]

So what does the collection day field return if placed on the detail section? And what is its datatype?

-LB

 

[bluerain25]

2 for monday, 3 for tuesday and so on... data type is number

 

[lbass]

Try a formula like this to add as your column field:

numbervar colldate := {table.collectiondate};
numbervar datawk := datepart("ww",{table.date},colldate);
numbervar startwk := datepart("ww",{?Start Date},colldate);;
if dayofweek({?Start Date},colldate) > colldate then
startwk := startwk+1 else
startwk := startwk;
if datawk = startwk then "Week1" else
if datawk = startwk+1 then "Week2" else
if datawk = startwk+2 then "Week3" else
if datawk >= startwk+3 then "Week4" else
"Week1"

-LB

 

[bluerain25]

Thanks lbass it works but there are cases where the report displays 2 columns with week1. mostly happens when the start of the coverage does not falls on monday and the collection day is monday but prior to that a member paid on the 16 and pay another premium on the proper collection date. both should be should be displayed in week 1 but the report displayed it in two columns..

 

[lbass]

Please identify your column, row, and summary fields.

-LB

 

[bluerain25]

hi LB,

heres a sample data that should be displayed in the report
for the coverage of May 16 to June 15 and collection date is monday:
WEEK1 WEEK2 WEEK3 WEEK4 TOTAL
AA 10 10 10 10 40
BB 10 10 10 30
total 20 20 20 10 70

if client AA pays on May 16 (before the collection date) the report display the ff:
WEEK1 WEEK1 WEEK2 WEEK3 WEEK4 TOTAL
AA 10 10 10 10 10 50

it should be
WEEK1 WEEK2 WEEK3 WEEK4 Total
AA 20 10 10 10 50


thanks for helping

 

[lbass]

Please identify your column, row, and summary fields. You would never get two instances of the column unless you have some higher order column.

-LB