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.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Can I RECOMPUTE a field differently for SUBTOTAL and normal rows?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Can I RECOMPUTE a field differently for SUBTOTAL and normal rows?
 Login/Join
 
Member
posted
Hi,

I would like to do smth like that:

TABLE FILE CAR

SUM
RETAIL_COST
COMPUTE FIELD/D20.2 =
IF ROWTYPE EQ SUBTOTAL THEN RETAIL_COST * SEATS
ELSE RETAIL_COST / SEATS;
BY COUNTRY
ON COUNTRY RECOMPUTE FIELD
END

Basically use the same field but compute it differently for SUBTOTAL row and normal rows?

Does anyone have an idea how to achieve that? I tried something along the lines of computing the field beforehand in a seperate HOLD and then do a
 COMPUTE FIELD2/D20.2 = IF FIELD IS MISSING THEN 'new COMPUTE' ELSE FIELD; 
but that doesn't work, the subtotals aren't treated as if the FIELD is MISSING.

This message has been edited. Last edited by: FP Mod Chuck,
 
Posts: 13 | Registered: June 24, 2019Report This Post
Virtuoso
posted Hide Post
Subtotals represent cumulative values of the column so I doubt you can reinterpret them in this way. Could you provide an example of the output you're looking to create?


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Member
posted Hide Post
I'll try:

I basically have a field that prints a comment to the user for a basic analysis how fields have changed in two instances of data (for example same table but different date).

So there is a column 1 with price1 and column 2 with price2 as values. I have two BY-fields and on the lower one I can comment much more detailed than on the higher one because I know the product. The comments for the lower category are already calculated by a SQL query beforehand.

Example:
  
STORE             PRODUCT         price1        price2              COMMENT
-----------------------------------------------------------------------------------------------------------------------------------------
New Jersey                        500           600                 
                  apple           100           120                 'Due to weather in New Jersey the price for apples is higher now.'
                  smartphones     280           200                 'Smartphones are cheaper now since no one needs another one.'
                  mountainbikes   120           280                 'Since fuel is expensive mountainbikes have risen in price.'

Thats basically my set up. What I want to do now is that I add a basic comment on the subtotal line which just says 'Overall the prices in New Jersey have risen' which is calculated on the subtotal of price1 and price2, i.e.

 IF price1 GT price2 THEN 'prices are lower now' ELSE IF price1 LT price2 THEN 'prices are higher now' ELSE 'prices haven't changed' 


but the comment per product should stay as is.

Do you understand what I mean? Big Grin

This message has been edited. Last edited by: JForFun,
 
Posts: 13 | Registered: June 24, 2019Report This Post
Virtuoso
posted Hide Post
Maybe you can create a DEFINE/COMPUTE that has the IF THEN ELSE and use it in a SUBFOOT.


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Member
posted Hide Post
quote:
Originally posted by BabakNYC:
Maybe you can create a DEFINE/COMPUTE that has the IF THEN ELSE and use it in a SUBFOOT.


My Output has a lot more fields to show, would that work together? I would want the field to be shown exactly in the same column as the other comments.

For example:

STORE             PRODUCT         price1        price2      COMMENT                                                                AVERAGE_TIP
--------------------------------------------------------------------------------------------------------------------------------------------------
New Jersey                        500           600         'Prices are higher now.'                                               2.50
                  apple           100           120         'Due to weather in New Jersey the price for apples is higher now.'     0.31
                  smartphones     280           200         'Smartphones are cheaper now since no one needs another one.'          4.25
                  mountainbikes   120           280         'Since fuel is expensive mountainbikes have risen in price.'           2.94



Just to make clear: There are other fields that are calculated via normal SUBTOTAL/RECOMPUTE.

This message has been edited. Last edited by: JForFun,
 
Posts: 13 | Registered: June 24, 2019Report This Post
Expert
posted Hide Post
You can do something like this. The first two aggregations are performed at a sort break level using "WITHIN" syntax and hidden. They are subsequently used in a calculation (which is also hidden) using column notation (CN).
The DESC prefixed calculations also use CN to refer to pre-calculated values. The DESC1 is hidden but including it means that it can be referenced in a SUBFOOT as Babak suggests.

TABLE FILE CAR
SUM RETAIL_COST WITHIN COUNTRY NOPRINT
    SEATS WITHIN COUNTRY NOPRINT
    RETAIL_COST
    COMPUTE CALC/D20.2 = C1 * C2; NOPRINT
	COMPUTE DESC/A100 = 'The retail cost for' || (' ' | CAR) || (' is ') | LJUST(20,FPRINT(C3, 'D20.2c', 'A20'), 'A20');
	COMPUTE DESC1/A100 = 'The retail cost within' || (' ' | COUNTRY) || (' is ') | LJUST(20,FPRINT(C1, 'D20.2c', 'A20'), 'A20'); NOPRINT
BY COUNTRY
BY CAR
-*ON COUNTRY RECOMPUTE FIELD =  RETAIL_COST / SEATS;
ON COUNTRY SUBFOOT
" <+0> <+0> <DESC1"
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
  type=report, grid=off, headalign=body, $
  type=subfoot, backcolor=silver, $
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
Member
posted Hide Post
Hi Tony,

really cool idea, would work like a charm in a normal report. Combined with the accordion structured from
ON TABLE SET EXPANDABLE ON 
ON TABLE SET EXPANDBYROWTREE ON

however it doesn't seem to work or do I have to add another line to make him acknowledge the expandable SUBTOTAL-row as my SUBFOOT? Any Ideas?

Played around with some things and came to the conclusion that the SUBTOTAL-line for the expandable structure is neither a SUBFOOT nor a SUBHEAD so I don't think I can alter it like you suggested.

This message has been edited. Last edited by: JForFun,
 
Posts: 13 | Registered: June 24, 2019Report This Post
Expert
posted Hide Post
Try it with just the "ON TABLE SET EXPANDABLE ON"
and remove the "-*ON TABLE SET EXPANDBYROWTREE ON"

It worked for me.
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Expert
posted Hide Post
quote:
Combined with the accordion structured from
ON TABLE SET EXPANDABLE ON
ON TABLE SET EXPANDBYROWTREE ON
however it doesn't seem to work

No mention of accordian reports in your original post so I didn't cover for that.

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
Member
posted Hide Post
quote:
Originally posted by Tony A:
quote:
Combined with the accordion structured from
ON TABLE SET EXPANDABLE ON
ON TABLE SET EXPANDBYROWTREE ON
however it doesn't seem to work

No mention of accordian reports in your original post so I didn't cover for that.

T


Yeah, I didnt think it would make a difference.

Deleting it destroys the structure of the report for me tho, so that is not an option. Guess I have to work around that somehow.
 
Posts: 13 | Registered: June 24, 2019Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Can I RECOMPUTE a field differently for SUBTOTAL and normal rows?

Copyright © 1996-2020 Information Builders