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. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at email@example.com and provide your corporate email address, company, and name.
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,
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?
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
Posts: 2128 | Location: Customer Support | Registered: April 12, 2005
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
FILEDEF FSEQ DISK FSEQ.FTM
-WRITE FSEQ FILEFORMACGUYVERFILEFORMACGUYVERFILEFORMACGUYVER
-* 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'
3 'Dealer Cost'
4 'Retail Cost'
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
TABLE FILE CAR
SUM MEASURE/MEASUREFORMAT AS ''
ACROSS CAR AS 'Car:'
BY SECTIONDESC NOPRINT
ON SECTIONDESC SUBHEAD
BY COUNTER NOPRINT
BY MEASUREDESC AS ''
IF COUNTER LE 8
ON TABLE SET STYLE *
INCLUDE = ENGreen_DarkComp, $
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 ;
TABLE FILE CAR
PSEATS PRCOST PDCOST PLEN PHEIGHT PWIDTH PWEIGHT
IF DUP EQ 1
ON TABLE HOLD AS CARFMLHD FORMAT BINARY
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
DEFINE FILE CARFML
MYCAR/A8 = EDIT(CAR,'99999999') ;
MYVAL/P9.2 = ITMVAL ;
TABLE FILE CARFML
SUM MYVAL ACROSS MYCAR
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
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
You can use the full power of the FML language to fine-tune your report. Notice the new PROFIT line in the example.