Need help with crosstab

[ekta22]

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.

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

 

[ekta22]

Anyone???

 

[ekta22]

really need to get this working???

 

[Madawc]

You haven't said what the problem is.

Madawc Williams (East Anglia, UK)

 

[ekta22]

Problem is how to create the cross-tab report which should look like below.

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

 

[ekta22]

I have been trying to figure this out. I go to Insert crosstab and select Area as my column heading and Crew as my rows. But now I want to use @PercentComplete formula as the summarized field but I don't see it in available fields and even if I create new formula from within the crosstab window I still don't see it. Any suggestions as to why I am not seeing this formula. Formula is as below


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)

 

[Madawc]

I'm not an expert on subreports, but I suspct that it's summary data. There are limits to what you can do with summary data.

Madawc Williams (East Anglia, UK)

 

[ekta22]

Thanks for your help so far. I hope somebody who knows more about summarized data and crosstab can help me. Thanks