Crosstab report 1

[ekta22]

Hi,

I need to create a crosstab report. I have a report that has the grouping as below

Region
Sector
Interval
Area
Crew


I need to add a crosstab report in the interval group header that will summarize the data by area and crew. I go to Insert crosstab and select Area as my column heading and Crew as my rows. Then 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)


Sample data for Crosstab is 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 am stuck. Somebody should know what the problem is. I really need to get this done. Thanks

 

[ekta22]

One last try if somebody could help me with this.

 

[shakespeare5677]

In your PercentComplete formula did you use whilereadingrecords or whilewritingrecords? The crosstab expert will only display whilereadingrecords formulas, as far as I know.

 

[ekta22]

No I don't use that in my formula.

 

[shakespeare5677]

What I meant to say is try inserting "whilereadingrecords" at the top of the formula so as to execute the code while reading the records as opposed to while actually displaying them. The crosstab will not display a formula, regardless of where you create it, unless it knows that the code will execute on read, and the "whilereadingrecords" line will indicate exactly that. I hope that helps.
-Max

 

[ekta22]

When I add "whilereadingrecords" at the top of my formula it highlights {@ScheduledTasks} and gives an error saying
" This formula cannot be used because it must be evaluated later " and the same messages for {@TotalTasks} and sum({@TimelyCOmplete},{@Group_CrewUnit}). How should this be handled? Thanks

 

[shakespeare5677]

If you are using other formula fields within the crosstab formula, you must make sure that they all are executed while reading records. If that is possible, just put the same "whilereadingrecords" line at the top of each involved formula and you should be ok. Otherwise, you may have to develop another way of calculating things while reading to give the results to the crosstab. I hope that helps.
-Max

 

[lbass]

You can't use "whilereadingrecords" with summaries.

Ekta, you will have to use a manual crosstab, as you cannot add a formula like yours to the crosstab expert. You can read about manual crosstabs in the FAQ section, or do a search using "manual crosstab" as keywords.

-LB

 

[shakespeare5677]

LB has a point. I didn't realize those are summaries and not just other formulas. My apologies
-Max

 

[ekta22]

Thanks LB and shakespeare5677. I will do that.