Conditional formatting in PIVOT

Hello All,

I am trying to apply conditional formatting to %_USED field on a EXCEL PIVOT TABLE.
I need to show different colors based on the % usage of an account.
I have tried many ways but did not succeed.

 TABLE FILE ACCOUNTS
PRINT
BALANCE
CREDIT_LIMIT
%_USED
COMPUTE PCNT/I1 = IF %USED LT 20 THEN 1 ELSE IF %_USED GT 20 AND %_USED LT 70 THEN 2 ELSE 3; NOPRINT
BY NAME
BY ACCOUNT_TYPE
ON TABLE SET EXPANDABLE ON
ON TABLE PCHOLD FORMAT EXL2K PIVOT
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=LANDSCAPE,
$
TYPE=REPORT, GRID=OFF, FONT='Calibri', SIZE=11, COLOR='BLACK', BACKCOLOR='WHITE', STYLE=NORMAL, $
TYPE=TITLE, STYLE=BOLD, COLOR='BLACK', BACKCOLOR=RGB(159 217 253), JUSTIFY=CENTER,$
TYPE=DATA, COLUMN=%_USED, WHEN=PCNT EQ 1, BACKCOLOR='RED',$
TYPE=DATA, COLUMN=%_USED, WHEN=PCNT EQ 2, BACKCOLOR='YELLOW',$
TYPE=DATA, COLUMN=%_USED, WHEN=PCNT EQ 3, BACKCOLOR='GREEN',$
ENDSTYLE
END
-RUN

In the above code, I get error at execution, since there is a field with NOPRINT which is not allowed in PIVOT, can I use it as CACHEFIELDS and then apply the conditions? I have tried that, but with no luck, I have very little knowledge on EXL2K PIVOT.
I should be able to collapse and expand the data based on the sort field, so i think PIVOT is my only option to work with excel output.

I am working on
7.6.9
excel 2007
ie 6

In Focus since 2008
WebFOCUS 8.2.0.1
Windows 7 - IE,Chrome,Firefox
Excel, PDF, HTML, AHTML, XML
JavaScript, jQuery, D3.js, Highcharts

Posts: 79 | Location: New York | Registered: February 04, 2010