Percentage in Cross Tab 3

[mtepfer]

Have a cross tab report that shows auditor names in the rows and results in the columns. The results are in 3 categories (Fail, Pass, Pass w/comments). These then total per auditor. I need to find the percentage passed (passed includes passed w/comments). I can create the formula to do all of this in the report footer by summming items, but this formula can't be used in the cross tab. Need to be able to show in the crosstab preferably Any ideas are appreciated.

Data showed look like :

Fail Pass Pass w/com Total Perc
Joe 3 6 1 10 70%

 

[CaptainD]

Look at this thread and see if you can make it work for you.

http://www.tek-tips.com/viewthread.cfm?qid=1487372&page=1

 

[lbass]

Instead of using the pass/fail field as a column, create separate formulas like this:

//{@Pass}:
if {table.category} = "Pass" then 1

Repeat for {@Passwcomments} and {@Fail}. Also create a formula {@0}:

whilereadingrecords;
0

Then add these as your summary fields in the crosstab, and add sum of {@0} as your last two summaries (add it twice--these will just be used to hold your results). Use sums for your five summaries (not counts).

Go to the customize style tab and choose summarized fields->horizontal and "show labels". Edit the labels to show the last two columns as "Total" and "% Pass" and to remove the "@"s.

Then in preview mode, select sum of {@pass} and sum of {@passwcolumns}->right click->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar tot := tot + currentfieldvalue;
numbervar pass := pass + currentfieldvalue;
false

Select {@fail}->right click->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar tot := tot + currentfieldvalue;
false

Then select the fourth column ({@0}->right click->format field->display string->x+2 and enter:

whileprintingrecords;
numbervar tot;
totext(tot,0)

Select the last column {@0} to hold the percentage->right click->format field->display string->x+2 and enter:

whileprintingrecords;
numbervar tot;
numbervar pass;
totext(pass%tot,2)+"%"//2 for two decimals

Then while the fifth column is still selected, go to the borders tab->color->background->x+2 and enter:

whileprintingrecords;
numbervar tot := 0;
numbervar pass := 0;
crNoColor

This last formula will reset (per row) the manual running totals that were used for the summaries.

-LB

 

[CaptainD]

lbass

whileprintingrecords;
numbervar tot := tot + currentfieldvalue;
false

What does the "false"do?

I've seen it in the other post I listed above and always wondered what it was for.

 

[lbass]

Since you are using the formatting formula areas to create these running totals, you have to follow the rules for that type of formula. You don't really intend to suppress anything with this formula, so you say "false". Similarly, you are not attempting to color anything with the reset formula, and so you add "crNoColor".

-LB

 

[CaptainD]

Thanks,

I'll give you a star once his problem is solved.

 

[mtepfer]

lbass,

thanks for the info looks like it is working perfect except for one thing, the last 2 columns(Total and Percent) are not showing the totals and percentages per row (auditor). Sure it is something simple I am missing.

	  Fail	  Pass	Passwcom   Total    Percent
  Anita    4	   6	  0	     0	     0.00
  Evelyn   5	   8	  1	     0	     0.00
  Tanya    6	   12	  3	     0	     0.00
Total	   15	   26	  4	    45	     66.67%

 

[lbass]

You need to implement the formulas in the inner cells for the Total and Percentage columns as well. It looks like you only added them to the column total cells.

-LB

 

[mtepfer]

Thanks lbass did figure that out after messing around with it last night. Thanks for your insight.

 

[Kibee]

IBass... truly this is amazing. congratulations

i just used this for a cross tab i am building and give you title of GURU MASTER for this solution

Thank you

K