I need to create a crosstab report. I have never done it before and need some help with it. I would appreciate any help and guidance.
I have a report that has the grouping as below
Region
Sector
Interval
Area
Crew
I have 5 formula’s in the detail section of my report that I have summarized in the Crew Group Header. Then, I have another 3 formula’s in Crew Group Header that are based on the above 5 summarized formulas. What I want to do now is add a crosstab report to the Interval group header that will display all the areas within that interval as column heading and Crew as row heading. The formula for the cross-tab report will depend on the above 3 formula’s in the Crew Group Header. The formula’s are as below
@Scheduled_Tasks = sum({@EquipNotAvailable},{@Group_CrewUnit}) + sum({@PartsOnOrder},{@Group_CrewUnit}) + sum({@TimelyComplete},{@Group_CrewUnit}) + sum({@LateComplete},{@Group_CrewUnit}) + sum({@Incomplete},{@Group_CrewUnit}) + sum({@MissedTasks},{@Group_CrewUnit})
@TotalTask = {@ScheduledTasks} - (sum({@Incomplete},{@Group_CrewUnit}) + sum({@PartsOnOrder},{@Group_CrewUnit})+ sum({@EquipNotAvailable},{@Group_CrewUnit}))
@PercentComplete =
If {@ScheduledTasks} = 0 then
"N/S"
Else If {@TotalTasks} = 0 then
"N/D"
Else
cStr( (sum({@TimelyCOmplete},{@Group_CrewUnit})/(sum({@TimelyCOmplete},{@Group_CrewUnit}) + sum({@MissedTasks},{@Group_CrewUnit}) + sum({@LateComplete},{@Group_CrewUnit}))) * 100, 2)
If @Scheduled_Tasks for a corresponding Crew and Area is 0 then it needs to display N/S and if @Scheduled_Tasks <> 0 and @TotalTask = 0 then display N/D else display the %Complete.
Here is some sample data for a particular region, sector and interval. Sorry, I was having a tough time formatting it. If you paste it in word it might make sense.
Thanks
I have a report that has the grouping as below
Region
Sector
Interval
Area
Crew
I have 5 formula’s in the detail section of my report that I have summarized in the Crew Group Header. Then, I have another 3 formula’s in Crew Group Header that are based on the above 5 summarized formulas. What I want to do now is add a crosstab report to the Interval group header that will display all the areas within that interval as column heading and Crew as row heading. The formula for the cross-tab report will depend on the above 3 formula’s in the Crew Group Header. The formula’s are as below
@Scheduled_Tasks = sum({@EquipNotAvailable},{@Group_CrewUnit}) + sum({@PartsOnOrder},{@Group_CrewUnit}) + sum({@TimelyComplete},{@Group_CrewUnit}) + sum({@LateComplete},{@Group_CrewUnit}) + sum({@Incomplete},{@Group_CrewUnit}) + sum({@MissedTasks},{@Group_CrewUnit})
@TotalTask = {@ScheduledTasks} - (sum({@Incomplete},{@Group_CrewUnit}) + sum({@PartsOnOrder},{@Group_CrewUnit})+ sum({@EquipNotAvailable},{@Group_CrewUnit}))
@PercentComplete =
If {@ScheduledTasks} = 0 then
"N/S"
Else If {@TotalTasks} = 0 then
"N/D"
Else
cStr( (sum({@TimelyCOmplete},{@Group_CrewUnit})/(sum({@TimelyCOmplete},{@Group_CrewUnit}) + sum({@MissedTasks},{@Group_CrewUnit}) + sum({@LateComplete},{@Group_CrewUnit}))) * 100, 2)
If @Scheduled_Tasks for a corresponding Crew and Area is 0 then it needs to display N/S and if @Scheduled_Tasks <> 0 and @TotalTask = 0 then display N/D else display the %Complete.
Here is some sample data for a particular region, sector and interval. Sorry, I was having a tough time formatting it. If you paste it in word it might make sense.
Code:
Region – EA
Sector – 11
Interval – Weekly or Greater
Crosstab Report
Crew Area1 Area2 Area3 %Complete
AAA 100 100 N/S 97.61
BBB 100 N/S N/S 100.00
CCC 0.00 100 N/S 81.25
DDD N/S 100 N/S 100
EEE N/S 96.87 N/D N/D
----- ------- -------
%Complete 98.28 100.00 N/D
New Page
Region – EA
Sector – 11
Interval – Weekly or Greater
Area – Area1
Crew--SchedTask--EquipNotAvail--PartsOnOrder--TotalTask--TimelyComp--LateComp--Incomplete--MissedTask--%Comp
AAA--------156----------------0--------------0-----------------153--------153----------------0----------3---------------0--------100.00
BBB--------5-------------------0--------------0-----------------5--------------5------------------0----------0--------------0--------100.00
CCC--------19-----------------0--------------0-----------------16--------------13-----------------3----------3-------------0--------81.25
DDD--------1------------------0--------------0-----------------1---------------1------------------0----------0--------------0--------100.00
New Page
Region – EA
Sector – 11
Interval – Weekly or Greater
Area – Area2
Crew--SchedTask--EquipNotAvail--PartsOnOrder--TotalTaks--TimelyComp--LateComp--Incomplete--MissedTask--%Comp
AAA--------8------------------0--------------0------------------8----------8------------------1----------0---------------0--------100.00
New Page
Region – EA
Sector – 11
Interval – Weekly or Greater
Area – Area3
Crew--SchedTask--EquipNotAvail--PartsOnOrder--TotalTaks--TimelyComp--LateComp--Incomplete--MissedTask--%Comp
EEE--------9-------------------0--------------0-----------------8----------8------------------1----------9---------------0--------N/D
Thanks