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,

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

'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,

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
 

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.

David,
I fully agree.
See also the example I gave to Nova27.