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)
***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,
WebFOCUS 8.1.04 Windows, All Outputs
June 13, 2018, 03:20 PM
MartinY
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.
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
June 13, 2018, 05:00 PM
AprilC
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?
WebFOCUS 8.1.04 Windows, All Outputs
June 14, 2018, 10:43 AM
FP Mod Chuck
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.
Thank you for using Focal Point!
Chuck Wolff - Focal Point Moderator WebFOCUS 7x and 8x, Windows, Linux All output Formats
June 17, 2018, 04:54 PM
David Briars
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
June 18, 2018, 12:59 PM
Edward Wolfgram
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:
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.
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).
June 18, 2018, 02:17 PM
AprilC
Thank you David and Edward. This is fantastic information. I will work through my issue with this.
WebFOCUS 8.1.04 Windows, All Outputs
July 13, 2018, 12:19 PM
David Briars
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).
