Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.



Read-Only Read-Only Topic
Go
Search
Notify
Tools
Conditional Styling
 Login/Join
 
Silver Member
posted
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
 
Posts: 35 | Registered: August 17, 2007Report This Post
Expert
posted Hide Post
Deep,

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 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Virtuoso
posted Hide Post
Tony 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
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Silver Member
posted Hide Post
Hi
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
 
Posts: 35 | Registered: August 17, 2007Report This Post
Virtuoso
posted Hide Post
Deepa,
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
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Silver Member
posted Hide Post
Hi,

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
 
Posts: 35 | Registered: August 17, 2007Report This Post
Virtuoso
posted Hide Post
Deep

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

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Silver Member
posted Hide Post
Hi,

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
 
Posts: 35 | Registered: August 17, 2007Report This Post
Expert
posted Hide Post
Deep,

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 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Silver Member
posted Hide Post
Thank a lot!!!!
Itz working now.

Deep


WebFocus 7.1.4, Windows Server 2005 , HTML/EXL/PDF
 
Posts: 35 | Registered: August 17, 2007Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic


Copyright © 1996-2020 Information Builders