Crosstabs zeros

[RepRider]

CRXI with SQL

I have a crosstab that displays a productivity percentage for each employee. It has 12 monthly columns. When staff are hired or leave there may be 0.0 for several months as their productivity percentage. Since they were not employed those months 0.0 would not be correct. Is there a way to have those 0.0's be displayed as NA or something through a formula? Through highlighting I can "color" them white so they aren't seen but would rather have "NA" so that if exported to excel I won't have to deal with them then.

Any advice is appreciated!! Thank you!

 

[AndersonCJA]

Hi,

I'm not experience in working with Crosstabs, however I belive this formula will provide the results you want.

whileprintingrecords;
if
(isnull({Tablename.fieldname}) or {tablename.fieldname} = 0.00)
then
"NA

 

[lbass]

Right click on the field->format field->number->customize->suppress if zero.

-LB

 

[RepRider]

Lbass
Thanks. That removed them from the grid and very simply!
Will the zero still be included in a summary? I am hoping not.

I may also try the other suggestion to see if NA can be inserted. Thanks for the reply!!

 

[lbass]

Yes, it will likely affect the summary, unless you are using a sum. What is your summary field? If a formula, what is the content of the formula?

-LB