Cross tab report with days in a month

[goodprg]

Hello,

I have a table which has emp_id, begin_date, end_date, leave_type, hours_taken. The values are

emp_id begin_date end_date leave_type, hours_taken
1 11/02/2006 AL 8
1 11/05/2006 11/08/2006 SL 40
1 11/15/2006 AL 8
2 11/01/2006 AL 8

I want to make a report like this.

emp_id 11/01/2006 11/02/2006 11/05/2006 11/06/2006 11/30
1 8 8 8
2 8

hours taken should be printed in red if leave_type is AL and so on.

We are on CR10 with oracle as the database.

Any help is grealty appreciated.

Thanks.

 

[lbass]

Does the 40 appear, only not in red? You are using the begin date to determine the columns?

-LB

 

[goodprg]

Sorry abt the confusion. I want all the days in a month and if the leave is taken on any of the days (using begin and end date) then use the leave type to determine the color of hours taken. so page header will look like 11/01, 11/02, 11/03, 11/04 etc.

That 40 should be 32 hours of leaves taken so 32/4 (days between 11/05 and 11/08) would be 8 hours.

Genrally hours taken would be in multiple of 8 (specially if the leaves taken are in a range). color will depend on the leave type if AL then red, if SL then blue etc.

I am a regular visitor of tek-tips and really appreciate all your answers.

Thanks for your help.

 

[chelseatech]

A crosstab will only show values for one of those dates. You can't use a crosstab to take a record and include it with both start date and end date.

In addition, if a day isn't in the start or end date, it will be missing from a crosstab.

The best solution is what Ken calls a "Manual Crosstab". You can write a formula for each column (day of the month). That way you can pick up both the start and end dates. The column for day five would be.

write a formula for the column heading: (5 of December)
Date(Year(Currentmonth), month(Currentdate),5)

Then a formula to display the leave type if required.
If {table.startdate}<={@day5} and {table.enddate} >={@day5}
then {table.leavetype} else 0

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm