I’m trying to create a report designed by our business team and I’m not found a way to create what they want. They are wanting subheadings but the fields under the subheadings are all different. Using the CAR file this would be similar to what they are wanting: (I realize this report seems silly but it was the only way I could think to show what I’m wanting with data we can all use)

  			My Car Options
		Alfa Romeo	Audi	BMW	Datsun	Jaguar
		Comfort
Seats			8	5	29	4	7
Accel			0	13	0	33	15
		Costs
Dealer Cost		16235	5063	49500	2626	18621
Retail Cost		19565	5970	58762	3139	22369
		Specification
Length			510	187	1122	163	388
Width			188	69	397	61	136
Height			159	55	337	54	102
Weight			7215	2571	11300	2050	7635

***Note, sorry it did not format correctly when this prints in the post. I'm not sure how to fix it. My Car Options is the report heading. The names of the cars are the Across fields. Comfort, Costs, and Specifications would be sub-headings.


Can anyone think of a way to do this? For the example I showed I limited my show to just the first 4 cars. But here is the code to create this report(minus the subheadings):
TABLE FILE CAR
SUM
CAR.BODY.SEATS OVER
CAR.SPECS.ACCEL OVER
CAR.BODY.DEALER_COST OVER
CAR.BODY.RETAIL_COST OVER
CAR.SPECS.LENGTH OVER
CAR.SPECS.WIDTH OVER
CAR.SPECS.HEIGHT OVER
CAR.SPECS.WEIGHT
ACROSS LOWEST CAR.COMP.CAR
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
TYPE=ACROSSVALUE,
ACROSS=1,
BACKCOLOR='WHITE',
$
ENDSTYLE
END

This code DOES NOT show the subheadings that I want in the report. That’s the piece that I just cannot figure out. Any ideas?

This message has been edited. Last edited by: FP Mod Chuck,

Using FML or MacGyver technic may be your best options.


Note : when posting sample code or sample output layout, use the code tag.

</>

It will keep the formatting as you type it.

Martin, Thanks for the tip on using the code tag for my example file. I edited my original post so the sample report is easier to see.

I've never heard of FML or the MacGyver technique before now. I did some digging and I'm not sure the MacGyver technique will work but the FML seems promising. I'll have to dig more into that and see if I can make it work. Do you know of any other examples of the FML and when people used it?

AprilC

FML stands for Financial Modeling Language and is primarily used for balance sheets etc. It is well doucmented in the Reporting Language / Creating Reports with WebFOCUS manual in a section named Creating Financial Reports with Finacial Modeling Language.

April-

If I understand your requirement correctly, you need to display multiple report records for each incoming record from your data file.

Most reporting is either detail (one report record for every incoming record) or summary (one report record for a group of incoming records).

However every so often we have a requirement to 'explode' an incoming record.

While there are different ways to do this in the WebFOCUS language, you might want to consider the Macguyver technique.

-*
-* Create the temporary MacGuyver Master and data file.
-*  Code from Focal Point: http://forums.informationbuild...027040786#1027040786
-*
EX -LINES 7 EDAPUT MASTER,FSEQ,C,MEM
FILENAME=FSEQ,SUFFIX=FIX
SEGNAME=CHAR1,SEGTYPE=S0
FIELDNAME=BLANK,BLANK,A1,A1,$
SEGNAME=CHARS,SEGTYPE=S0,PARENT=CHAR1,OCCURS=VARIABLE
FIELDNAME=CHAR,CHARS,A1,A1,$
FIELDNAME=COUNTER,ORDER,I2,I4,$
-RUN
FILEDEF FSEQ DISK FSEQ.FTM
-RUN
-WRITE FSEQ  FILEFORMACGUYVERFILEFORMACGUYVERFILEFORMACGUYVER
-RUN
-*
-* Create 'Car Options' report.
-*
-* Report Environment Settings:
SET ACROSSLINE = SKIP
SET ACROSSTITLE = SIDE
SET PAGE = OFF
-* JOIN to 'Macguyver' file.  
JOIN BLANK WITH LENGTH IN CAR TO BLANK IN FSEQ AS J1
-* Temporary fields:
DEFINE FILE CAR
 BLANK/A1 WITH LENGTH = ' ';
 MEASUREDESC/A16 = DECODE COUNTER (1 'Seats'
                                   2 'Acceleration'
			           3 'Dealer Cost'
				   4 'Retail Cost'
				   5 'Length'
				   6 'Width'
				   7 'Height'
				   8 'Weight');
 MEASURE/D12.2 = IF COUNTER EQ 1 THEN SEATS ELSE
                 IF COUNTER EQ 2 THEN ACCEL ELSE 
			     IF COUNTER EQ 3 THEN DEALER_COST ELSE
			     IF COUNTER EQ 4 THEN RETAIL_COST ELSE
			     IF COUNTER EQ 5 THEN LENGTH ELSE
			     IF COUNTER EQ 6 THEN WIDTH ELSE
			     IF COUNTER EQ 7 THEN HEIGHT ELSE
			     IF COUNTER EQ 8 THEN WEIGHT;
 MEASUREFORMAT/A8 = IF COUNTER EQ 3 OR 4 THEN 'D12M' ELSE 'I9C'; 
 SECTIONDESC/A24 = IF COUNTER LE 2 THEN 'Comfort' ELSE 
                   IF COUNTER LE 4 THEN 'Costs' ELSE 
				   'Specificatons';  
END
-*
TABLE FILE CAR
"Car Options"
SUM    MEASURE/MEASUREFORMAT AS ''
ACROSS CAR AS 'Car:'
BY     SECTIONDESC NOPRINT
ON     SECTIONDESC SUBHEAD
"<SECTIONDESC"
BY     COUNTER NOPRINT
BY     MEASUREDESC AS ''
IF COUNTER LE 8
ON TABLE SET STYLE *
 INCLUDE = ENGreen_DarkComp, $
ENDSTYLE
END   

Just for fun, here is a method that uses FML (Financial Modeling Language):

First, we must transform the name-driven car file into a data-driven item (as in, "line-item") file by turning all of the input fields into the same numeric format:

DEFINE FILE CAR
CARDUP/I1 WITH SEATS = CAR NE LAST CAR ;
DUP/I1 WITH SEATS = CARDUP ;
PSEATS/P8.2 = SEATS ;
PRCOST/P8.2 = RCOST ;
PDCOST/P8.2 = DCOST ;
PLEN/P8.2   = LENGTH ;
PHEIGHT/P8.2 = HEIGHT ;
PWIDTH/P8.2 = WIDTH ;
PWEIGHT/P8.2 = WEIGHT ;
END
-RUN
TABLE FILE CAR
PRINT CAR
  PSEATS PRCOST PDCOST PLEN PHEIGHT PWIDTH PWEIGHT
IF DUP EQ 1
ON TABLE HOLD AS CARFMLHD FORMAT BINARY
END
-RUN
 

You can look at the carfmlhd.mas file to see what this step generated. Next, use an alternate master to describe this carfmlhd file using Data-Driven fields: create the carfml.mas file below:

FILENAME=CARFML, SUFFIX=FIX , IOTYPE=BINARY,
  SEGMENT=CARFMLHD, SEGTYPE=S0, $
    FIELDNAME=CAR, ALIAS=E02, USAGE=A16, ACTUAL=A16, $
    FIELDNAME=ITEMENT,,A56,A56,$
  SEGMENT=ITEMS, SEGTYPE=S0, OCCURS=7, PARENT=CARFMLHD,POSITION=ITEMENT,$
    FIELDNAME=ITMVAL,, USAGE=P12.2, ACTUAL=P08, $
    FIELDNAME=ITEM,ORDER,I2,I4,$
 

Once you have the carfml alternate master pointing to the carfmlhd file, you can write an FML TABLE that can use each of our car fields as a single field (ITMVAL) distinguished the the ITEM data value (thus, Data-Driven instead of Name-Driven):

 
FI carfml disk carfmlhd.ftm (lrecl 72 RECFM FB
-RUN
DEFINE FILE CARFML
MYCAR/A8 = EDIT(CAR,'99999999') ;
MYVAL/P9.2 = ITMVAL ;
END
-RUN

TABLE FILE CARFML
SUM MYVAL ACROSS MYCAR
FOR ITEM
1 LABEL SE NOPRINT OVER
RECAP SEATS/I3 = SE; AS 'Seats' OVER
" "               OVER

"Financials: "    OVER
2 LABEL RC  NOPRINT OVER
RECAP RCOST/P9.2M = RC ; AS 'RCost' OVER
3 LABEL DC  NOPRINT OVER
RECAP DCOST/P9.2M = DC ; AS 'Dcost' OVER
BAR OVER
RECAP PRF/P9.2M   = RC-DC ; AS 'Profit' OVER
" "               OVER
"Stats: "         OVER
4 LABEL LEN  NOPRINT OVER
RECAP MYLEN/A10V = RJUST(10,EDIT(LEN,'$$$$$$999') ||'in     ','A10') ; AS 'Length' OVER
5 AS 'Width'      OVER
6 AS 'Height'     OVER
7 LABEL WT NOPRINT OVER
RECAP MYWT/A10V =  RJUST(10,EDIT(WT ,'$$$$$9999') || 'lb     ','A10')  ; AS 'Weight'

IF CAR LT PEAU
END
-RUN

You can use the full power of the FML language to fine-tune your report. Notice the new PROFIT line in the example.

       MYCAR 
           ALFA ROM    AUDI        BMW         DATSUN      JAGUAR      JENSEN      MASERATI 
 -------------------------------------------------------------------------------------------
 Seats            2           5           5           4           4           4           2 
  
 Financials: 
 RCost    $6,820.00   $5,970.00   $5,940.00   $3,139.00   $8,878.00  $17,850.00  $31,500.00 
 Dcost    $5,660.00   $5,063.00   $5,800.00   $2,626.00   $7,427.00  $14,940.00  $25,000.00 
        ----------- ----------- ----------- ----------- ----------- ----------- ----------- 
 Profit   $1,160.00     $907.00     $140.00     $513.00   $1,451.00   $2,910.00   $6,500.00 
  
 Stats: 
 Length       163in       187in       176in       163in       189in       188in       177in 
 Width        51.80       54.60       55.50       53.50       48.00       53.00       44.60 
 Height       62.20       69.00       62.50       60.80       66.00       69.00       69.60 
 Weight      2305lb      2571lb      2400lb      2050lb      3435lb      4000lb      3700lb 

Awesome example of using Financial Modeling Language (FML). Thank you Edward.

@April - In your real case are you doing inter-row calculations? Meaning, given your example, 'Total Comfort' + 'Total Costs' - 'Specfications' = Bottom Line.

If you are, that would be one reason to consider FML (RECAP command).

Thank you David and Edward. This is fantastic information. I will work through my issue with this.

My IB Customer Service Rep just sent in her periodic mailings, a link to an upcoming Virtual User Group Meeting on the MacGuyver Technique (9/14/18 11 AM EDT).

Thought I would add the link here, within our most recent thread on this technique:
https://www.informationbuilder...ter?utm_source=email