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     [SOLVED] Repeating 'BY' fields with stacked measures

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Repeating 'BY' fields with stacked measures
 Login/Join
 
Master
posted
Is it possible to display the by field on each line of a report with stacked measures?

Code Example:
TABLE FILE CAR
SUM 
    CAR.BODY.DEALER_COST/I11M OVER 
    CAR.BODY.RETAIL_COST/I11M OVER 
    COMPUTE MARGIN/D12.2% = (CAR.BODY.RETAIL_COST - CAR.BODY.DEALER_COST) /  CAR.BODY.DEALER_COST * 100;
BY  CAR.COMP.CAR
ACROSS LOWEST CAR.ORIGIN.COUNTRY
ON TABLE SET BYDISPLAY ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END


This is the output that I get, even with
 ON TABLE SET BYDISPLAY ON  



This is my desired output:


Any help would be appreciated.

This message has been edited. Last edited by: Tamra,


Hallway

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
 
Posts: 608 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Report This Post
Virtuoso
posted Hide Post
I don't think that this feature exist. I haven't been able to find it...

But this could be a workaround :
SET ASNAMES = ON

TABLE FILE CAR
SUM COMPUTE TCOST/D7CM = RETAIL_COST; NOPRINT
    COMPUTE COST /A20  = FPRINT(TCOST, 'D7CM', 'A20');
BY CAR
BY COUNTRY
BY TOTAL COMPUTE ROWID/I2   = 1;
BY TOTAL COMPUTE ROWX /A20V = 'RETAIL_COST';
ON TABLE HOLD AS RETAILC FORMAT FOCUS
END
-RUN

TABLE FILE CAR
SUM COMPUTE TCOST/D7CM = DEALER_COST; NOPRINT
    COMPUTE COST /A20  = FPRINT(TCOST, 'D7CM', 'A20');
BY CAR
BY COUNTRY
BY TOTAL COMPUTE ROWID/I2   = 2;
BY TOTAL COMPUTE ROWX /A20V = 'DEALER_COST';
ON TABLE HOLD AS DEALERC FORMAT FOCUS
END
-RUN

TABLE FILE CAR
SUM COMPUTE MRG  /D12.2% = (RETAIL_COST - DEALER_COST) / DEALER_COST * 100; NOPRINT
    COMPUTE COST /A20 = FPRINT(MRG, 'D12.2%', 'A20');
BY CAR
BY COUNTRY
BY TOTAL COMPUTE ROWID/I2   = 3;
BY TOTAL COMPUTE ROWX /A20V = 'MARGIN';
ON TABLE HOLD AS MARGIN FORMAT FOCUS
END
-RUN

TABLE FILE RETAILC
SUM COST
BY CAR
BY COUNTRY
BY ROWID
BY ROWX
ON TABLE HOLD AS RPTDATA FORMAT FOCUS
MORE
FILE DEALERC
MORE
FILE MARGIN
END
-RUN

TABLE FILE RPTDATA
SUM COST
BY CAR
BY ROWID NOPRINT
BY ROWX  AS ''
ACROSS COUNTRY
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE SET BYDISPLAY ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
TYPE=DATA,
   ACROSSCOLUMN=COST,
   JUSTIFY=RIGHT,
$
ENDSTYLE
END



WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Master
posted Hide Post
'OVER's', a way of collapsing multiple columns, happen very late in WebFOCUS processing: output formatting time.

So things like calculating your BY column would have already occurred, before the OVER's start to take effect.

There are different ways to get to the output you show in your model, without using the OVER's. (If it is possible to do with you want with OVER's, that would be great, of course, if someone could show us how. :-))

The MacGuyver Technique is a method of creating n output records for each incoming record.

Here is an example of using this technique to get the output you show:
-*
-* Point to the 'MacGuyver' flat sequential file.   
-*
APP PREPENDPATH COMMON
FILEDEF FSEQ DISK common/fseq.dat
-RUN
-*
-* Summarize CAR Cost Data and Calculate Margin.
-*  Each record read is handled by WF three times.     
-*
JOIN BLANK WITH BODYTYPE IN CAR TO BLANK IN FSEQ AS J1
DEFINE FILE CAR
BLANK/A1 WITH BODYTYPE = ' ';
MEASUREDESC/A16 = DECODE COUNTER (1 'DEALER_COST'
                                  2 'RETAIL_COST' ELSE 'MARGIN');
END
-*
TABLE FILE CAR
SUM
COMPUTE MEASURE/D12.2 = IF MEASUREDESC EQ 'DEALER_COST' THEN DEALER_COST ELSE 
                          IF MEASUREDESC EQ 'RETAIL_COST' THEN RETAIL_COST ELSE
                           (RETAIL_COST - DEALER_COST) /  DEALER_COST * 100;   
BY COUNTRY                 
BY CAR
BY COUNTER 
BY MEASUREDESC   
IF COUNTER LE 3
ON TABLE HOLD AS HLDDATA
END
-RUN
-*
-* Create Report Presentation for User.  
-*
SET ACROSSTITLE = SIDE
DEFINE FILE HLDDATA
MYFORMAT/A8 = IF COUNTER EQ 3 THEN 'D12.2%' ELSE 'I11M';
END
-*
TABLE FILE HLDDATA
SUM MEASURE/MYFORMAT AS ''
BY CAR
BY COUNTER NOPRINT 
BY MEASUREDESC AS ''
ACROSS COUNTRY AS 'COUNTRY:'
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET BYDISPLAY ON
ON TABLE SET STYLE *
 INCLUDE = endeflt, $
ENDSTYLE
END   

Notes:
* Do a search for 'MacGuyver' in either IB Technical Support, or FocalPoint for more information/examples.
* The master for my FSEQ looks like this:
$ Master : FSEQ
$ File   : common/fseq.dat
$ Purpose: Blank/dummy file used to read one record multiple times.  
FILE=FSEQ, SUFFIX=FIX
 SEGNAME=SEG1
  FIELD=CONTROL, BLANK , A1, A1, $
 SEGNAME=SEG2, PARENT=SEG1, OCCURS=VARIABLE
  FIELD=WHATEVER, , A1, A1, $
  FIELD=COUNTER, ORDER, I4,  I4,$  

And the one row data file looks like this (blank in position 1 important):
 12345678901234567890  

* Some devs report that they use MacGuyver many times over, I've only used it maybe a dozen of times over the years. Whatever the case is for you, it might be worth trying.
* Secret agent Angus MacGyver, from the TV show, solves complex technical problems with whatever is at hand - duct tape, gum... (Sounds like my home projects!) Hence the name given to this technique.

This message has been edited. Last edited by: David Briars,
 
Posts: 822 | Registered: April 23, 2003Report This Post
Virtuoso
posted Hide Post
Hallway,
Another useful technique is the use of an "Alternate Master":
 
-*File hallway01.fex
-* Create a HOLD file with constants for the value names and identical formats for the values
TABLE FILE CAR
SUM 
    COMPUTE CARD/A12='DEALER_COST';
    DEALER_COST/D12.2 
    COMPUTE CARR/A12='RETAIL_COST';
    RETAIL_COST/D12.2  
    COMPUTE CARM/A12='MARGIN';
    COMPUTE MARGIN/D12.2 = (RETAIL_COST - DEALER_COST) /  DEALER_COST * 100;
BY COUNTRY
BY CAR
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS HALLWAY FORMAT ALPHA
END
-RUN
-* Create alternate master with repeating value names and values 
EX -LINES 8 EDAPUT MASTER,HALLWAY,C,MEM
FILENAME=HALLWAY , SUFFIX=FIX    
SEGMENT=HALLWAY, SEGTYPE=S0
FIELDNAME=COUNTRY, ALIAS=E01, USAGE=A10, ACTUAL=A10, $
FIELDNAME=CAR, ALIAS=E02, USAGE=A16, ACTUAL=A16, $
SEGMENT=CARS, PARENT=HALLWAY, OCCURS=VARIABLE
FIELDNAME=CARTYPE, ALIAS=E03, USAGE=A12, ACTUAL=A12, $
FIELDNAME=VALUE, ALIAS=E04, USAGE=D12.2, ACTUAL=A12, $
-RUN
-* Define different formats for the value names
DEFINE FILE HALLWAY
VFORM/A8=DECODE CARTYPE('DEALER_COST' 'D11M' 'RETAIL_COST' 'D11M' 'MARGIN' 'D12.2%');
END
-* Report output
TABLE FILE HALLWAY
SUM VALUE/VFORM
BY CAR
BY CARTYPE AS ''
ACROSS COUNTRY
ON TABLE SET BYDISPLAY ON
ON TABLE SET ACROSSTITLE SIDE
END
 


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Master
posted Hide Post
Excellent technique examples Martin and Danny.

I've done the multiple extracts-concatenate and alternative master techniques, as well as, MacGuyver.

What a great Summit presentation idea would be for us to each show each of the three techniques, and then compare and contrast, and further discuss with attendees.
 
Posts: 822 | Registered: April 23, 2003Report This Post
Virtuoso
posted Hide Post
David,
I fully agree.
See also the example I gave to Nova27.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report 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     [SOLVED] Repeating 'BY' fields with stacked measures

Copyright © 1996-2020 Information Builders