Focal Point
Conditional Styling
December 14, 2007, 03:04 AM
Deepa497Conditional Styling
Hi,
I have a report in which average and sum is being calulated based on a column value.
I have used following(example):
DEFINE FILE MOVIES
NRATING/A10=RATING;
END
TABLE FILE MOVIES
PRINT
COPIES
LISTPR
WHOLESALEPR
DIRECTOR
BY NRATING NOPRINT
BY RATING
BY CATEGORY
ON RATING SUBTOTAL
SUM. LISTPR AS 'SUM'
ON NRATING SUBTOTAL
AVE. LISTPR
AVE. WHOLESALEPR AS 'AVE'
END
Now I have to apply conditional styling on LISTPR and WHOLESALEPR data values. Formula for this uses
Average calculated on the Subtotal for eg.
(Average LISTPR - Actual Value of LISTPR) / Actual Value of LISTPR.
To calculate it I need the Average calculated. How to capture the Average value for each column
Thanks
Deep
For this
WebFocus 7.1.4, Windows Server 2005 , HTML/EXL/PDF
December 14, 2007, 04:01 AM
Tony ADeep,
Use the ability to capture the values you need at table level -
...
SUM AVE.LISTPR NOPRINT
PRINT COPIES
LISTPR/D12.2
COMPUTE HIGH_LOW/D12.2 = (AVE.LISTPR - LISTPR) / LISTPR; NOPRINT
etc.
In FOCUS since 1986 | WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2 | | |
WebFOCUS App Studio 8.2.06 standalone on Windows 10 | |
December 14, 2007, 04:13 AM
GamPTony is almost correct. Keep in mind that this has to be done at rating level, not table level.
The complete code for this example:
DEFINE FILE MOVIES
NRATING/A10=RATING;
END
TABLE FILE MOVIES
SUM
AVE.LISTPR NOPRINT
AVE.WHOLESALEPR NOPRINT
BY NRATING NOPRINT
BY RATING
PRINT
COPIES
LISTPR
WHOLESALEPR
DIRECTOR
COMPUTE COND/D12.2 = ( AVE.LISTPR - LISTPR ) / LISTPR; NOPRINT
BY NRATING NOPRINT
BY RATING
BY CATEGORY
ON NRATING SUBTOTAL
AVE. LISTPR
AVE. WHOLESALEPR AS 'AVE'
ON RATING SUBTOTAL
SUM. LISTPR AS 'SUM'
ON TABLE NOTOTAL
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
DEFMACRO=COND0001,
MACTYPE=RULE,
WHEN=N10 LT -.5,
$
DEFMACRO=COND0002,
MACTYPE=RULE,
WHEN=N10 LT .5,
$
DEFMACRO=COND0003,
MACTYPE=RULE,
WHEN=N10 GE .5,
$
TYPE=REPORT,
GRID=OFF,
FONT='TIMES NEW ROMAN',
SIZE=10,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
$
TYPE=DATA,
COLUMN=N7,
BACKCOLOR='RED',
MACRO=COND0001,
$
TYPE=DATA,
COLUMN=N7,
BACKCOLOR=RGB(255 153 0),
MACRO=COND0002,
$
TYPE=DATA,
COLUMN=N7,
BACKCOLOR='LIME',
MACRO=COND0003,
$
ENDSTYLE
END
Hope this helps.
GamP
- Using AS 8.2.01 on Windows 10 - IE11. |
| in Focus since 1988 |
December 14, 2007, 05:56 AM
Deepa497Hi
I have following code:
.....
TABLE FILE XYZ
SUM
A AS 'MTD '
b AS 'YTD '
BY TTL NOPRINT
BY Temp NOPRINT
BY ST_PROV_C NOPRINT
BY YR_I NOPRINT
BY MO_I NOPRINT
BY CURR_PROTO_GRP_N
BY CURR_PROTO_T
BY REF_CO_LOC_I
BY HIER_LVL_4_N
ACROSS Util_Type AS ''
ON TTL SUBTOTAL
AVE. A
AVE. B AS ' Average Usage'
ON Temp SUBTOTAL
SUM. A
SUM. B AS 'Total Usage'
ON CURR_PROTO_GRP_N SUBTOTAL
AVE. A
AVE. B AS ' Average Usage'
......
Now in the conditional styling for the columns MO_USE_Q and MO_YTD_USE_Q I want to do some kind of coloring based on the formula
(store type average usage - store actual usage) divided by (store type average usage)) * 100 is less than -10, then color red ....
NOTE:
a) store type average usage is the average fouund based on CURR_PROTO_GRP_N
b) store actual usage for different columns is sum ( A ), sum(B) for different values of Util_Type(across value)
My question is how to capture the average value?
Thanks
Deep
WebFocus 7.1.4, Windows Server 2005 , HTML/EXL/PDF
December 14, 2007, 07:30 AM
GamPDeepa,
Really, the solution to your question is in the code that Tony and myself posted in the previous posts.
You will have to create the averages in the request body itself, in this case the best way is to do a so-called multiverb request.
So:
SUM
AVE.A NOPRINT
AVE.B NOPRINT
BY TTL NOPRINT
BY Temp NOPRINT
BY ST_PROV_C NOPRINT
BY YR_I NOPRINT
BY MO_I NOPRINT
BY CURR_PROTO_GRP_N
SUM
A AS 'MTD '
B AS 'YTD '
BY TTL NOPRINT
BY Temp NOPRINT
BY ST_PROV_C NOPRINT
BY YR_I NOPRINT
BY MO_I NOPRINT
BY CURR_PROTO_GRP_N
BY CURR_PROTO_T
BY REF_CO_LOC_I
BY HIER_LVL_4_N
And now, since you can't use formulas in the conditional styling, create a compute that will indicate what color is to be shown. In your case, it should be a compute that holds the formula, for instance:
COMPUTE SIGNAL=((AVE.A - A)/A)*100;
and then you can use the value of this field for coloring the MO_USE_Q and MO_YTD_USE_Q fields.
This should do the trick for you.
GamP
- Using AS 8.2.01 on Windows 10 - IE11. |
| in Focus since 1988 |
December 15, 2007, 04:03 AM
Deepa497Hi,
The logic you gave is perfectly running when no across field is there. But in my code you can see one across field is there i.e., ACROSS Util_Type AS ''
Becoz of this I am not able to apply the logic as values are not getting retrieved.
Thanks
Deep
WebFocus 7.1.4, Windows Server 2005 , HTML/EXL/PDF
December 15, 2007, 04:58 AM
FrankDutchDeep
If you know upfront which across fields you will get (a limited number) you can define these as separate fields.
Suppose your across is a month value you can say
DEFINE FILE whatever
JANVALUE=IF MONTH EQ 'JAN' THEN VALUE ELSE 0;
FEBVALUE=IF MONTH EQ 'FEB' THEN VALUE ELSE 0;
etc
DECVALUE=IF MONTH EQ 'DEC' THEN VALUE ELSE 0;
TOTALVALUE=JANVALUE+...+DECVALUE;
END
TABLE FILE whatever
SUM
JANVALUE
FEBVALUE
...
DECVALUE
TOTALVALUE
...
Now you will get control over the different columns.
|
Frank |
| prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7 |
December 17, 2007, 03:05 AM
Deepa497Hi,
Let me put it another way. Consider following ouput:
CATEGORY
TYPE ELECTRIC WATER
GM 10.5 10.0
11.0 10.0
AVERAGE GM 10.75 10
TOTAL GM 21.5 20
TS 12.5 12.5
10.5 10.5
AVERAGE TS 11.5 11.5
TOTAL TS 23.0 23
Total Average 11.125 10.75
Total Sum 44.5 43
Now in teh above output I have to put conditional styling on columns (Electric and Water ---- it's an across field)
Formula to be used is
(Average Type(for GM and TS) - Actual value of Type corresponding to Electric and Water) / Actual value of Type corresponding to Electric and Water
e.g.,
if we consider GM
it should be for the first row
(10.75 - 10.5) / 10.5
etc.....
I am not able to use the average in teh formula because of Across field (CATEGORY)
Please help me!!!
Thanks
Deep
WebFocus 7.1.4, Windows Server 2005 , HTML/EXL/PDF
December 17, 2007, 05:40 AM
Tony ADeep,
As I mentioned above (and Gamp also) -
quote:
Use the ability to capture the values you need at table (or sort) level
and then you can use that column in your conditional styling.
We try to assist but you have to do a certain amount of thinking yourself!! Otherwise, as has been said before, we will be seeking payment for doing your job for you!!!
DEFINE FILE MOVIES
NRATING/A10=RATING;
END
TABLE FILE MOVIES
SUM AVE.LISTPR
BY NRATING NOPRINT
BY RATING
BY CATEGORY
PRINT COPIES
LISTPR
WHOLESALEPR
DIRECTOR
BY NRATING NOPRINT
BY RATING
ACROSS CATEGORY
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, $
TYPE=DATA, ACROSSCOLUMN=N2, COLOR=RED, WHEN=C1 GT LISTPR, $
TYPE=DATA, ACROSSCOLUMN=N2, COLOR=GREEN, WHEN=C1 LT LISTPR, $
ENDSTYLE
END
-RUN
T
In FOCUS since 1986 | WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2 | | |
WebFOCUS App Studio 8.2.06 standalone on Windows 10 | |
December 17, 2007, 07:55 AM
Deepa497Thank a lot!!!!
Itz working now.
Deep
WebFocus 7.1.4, Windows Server 2005 , HTML/EXL/PDF