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. 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 myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Across report with subheadings

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Across report with subheadings
 Login/Join
 
Silver Member
posted
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,


WebFOCUS 8.1.04
Windows, All Outputs
 
Posts: 34 | Registered: July 02, 2014Report This Post
Virtuoso
posted Hide Post
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
 
Posts: 2410 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Silver Member
posted Hide Post
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
 
Posts: 34 | Registered: July 02, 2014Report This Post
Virtuoso
posted Hide Post
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
 
Posts: 2128 | Location: Customer Support | Registered: April 12, 2005Report This Post
Master
posted Hide Post
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   
 
Posts: 822 | Registered: April 23, 2003Report This Post
Gold member
posted Hide Post
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 


IBI Development
 
Posts: 61 | Registered: November 15, 2005Report This Post
Master
posted Hide Post
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).
 
Posts: 822 | Registered: April 23, 2003Report This Post
Silver Member
posted Hide Post
Thank you David and Edward. This is fantastic information. I will work through my issue with this.


WebFOCUS 8.1.04
Windows, All Outputs
 
Posts: 34 | Registered: July 02, 2014Report This Post
Master
posted Hide Post
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
 
Posts: 822 | Registered: April 23, 2003Report 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] Across report with subheadings

Copyright © 1996-2020 Information Builders