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.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Create Subtotals without Using SUBTOTAL or TOTAL

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Create Subtotals without Using SUBTOTAL or TOTAL
 Login/Join
 
JulieA
Platinum Member
posted
Challenge: To create column subtotals (i.e. YR) on another field (i.e. DEALER_NAME) in addition to totals without using SUBTOTAL or TOTAL functions but using small cell file

A few highlights of the situation:

1. The procedure code relies upon the small cell file using an -INCLUDE statement
2. We can obtain column totals (yearly totals).
3. We need column subtotals (yearly subtotals) on another field (i.e. DEALER name).
4. We cannot use SUBTOTAL or TOTAL functions as found in the GUI.
5. The concept is similar to one expressed in a thread from last summer titled [SOLVED] Rolling Five_year Percentage of Change and posted in June 2017 in that we need to calculate subtotals and append them appropriately.We think we need additional code starting around Phase 2 or 3 of the small cell file.

Here is the procedure code: 


-* EXTRACT SOURCE DATA
DEFINE FILE MV_MODEL_CAR
TERMSET/I4 = EDIT(CALENDAR_YEAR);
YR /YY = TERMSET;
SORT_MODEL/A46 = IF DEALER_NAME EQ 'BMW'  THEN '    BMW' ELSE
	         IF DEALER_NAME EQ 'Honda' THEN '   Honda' ELSE
	         IF DEALER_NAME EQ 'Volvo'   THEN '  Volvo'    ELSE CAR_NAME ;
END

-SET &MAXYR = &YR;
-SET &MINYR = &MAXYR-5;

TABLE FILE MV_MODEL_CAR
-* SET values for small protect processing and -INCLUDE.
-* Some choices are optional.
-*INCLUDE small_cell_protect_5
-*-----------------------------------------------------------------------------------------------
SUM
SUM.MV_MODEL_CAR.MV_MODEL_CAR.COUNT AS 'COUNTS'
BY  LOWEST MV_MODEL_CAR.MV_MODEL_CAR.DEALER_NAME
BY  MV_MODEL_CAR.MV_MODEL_CAR.CALENDAR_YEAR
BY  MV_MODEL_CAR.MV_MODEL_CAR.YR
BY  LOWEST MV_MODEL_CAR.MV_MODEL_CAR.SORT_MODEL
WHERE (MV_MODEL_CAR.MV_MODEL_CAR.YR GE &MINYR);
WHERE (MV_MODEL_CAR.MV_MODEL_CAR.YR LE &MAXYR);
ON TABLE HOLD AS EXTDATA FORMAT FOCUS
END
-RUN

-* EXTRACT MAXIMUM YEAR IN A VARIABLE
-DEFAULTH &YRMAX = 0
TABLE FILE EXTDATA
SUM MAX.YR/I4 AS 'YRMAX'
ON TABLE HOLD AS MAXYR FORMAT BINARY
END
-RUN
-READFILE MAXYR
-SET &YRMAX = &YRMAX;
-TYPE &YRMAX

-* EXTRACT ALL INDIVIDUAL YEARS IN A VARIABLE
-DEFAULTH &EXTYR = 0
TABLE FILE EXTDATA
BY YR/I4 AS 'EXTYR'
ON TABLE HOLD AS YRLIST FORMAT BINARY
END
-RUN

-SET &NBYR = &LINES;

-REPEAT READYR FOR &I FROM 1 TO &NBYR STEP 1
-DEFAULTH &YR.&I = 0
-READFILE YRLIST
-SET &YR.&I = &EXTYR;
-TYPE &YR.&I
-READYR

DEFINE FILE EXTDATA
CALENDAR_YEAR/A100 =EDIT(YR)
END

TABLE FILE EXTDATA
SUM
COUNTS
BY DEALER_NAME
BY CALENDAR_YEAR
BY LOWEST SORT_MODEL
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE HOLD AS REPINPUT
ON TABLE SET STYLE *
ENDSTYLE
END

-* SET values for small protect processing and -INCLUDE.
-* Some choices are optional.
-SET &input   = 'repinput';
-SET &output  = 'finaldata';
-SET &dim1    = 'DEALER_NAME';
-SET &dim1_id = 'SORT_MODEL';
-SET &dim2    = 'CALENDAR_YEAR';
-SET &measure = 'COUNTS';
-INCLUDE IBFS:/small_protect.fex

-* Create report based on small protect output.

TABLE FILE finaldata

ON TABLE SUBHEAD
"Years &MINYR - &MAXYR"
"&DEALER_NAME"
ON TABLE SUBFOOT
"- Cells have been marked to indicate no data."

 SUM &measure
	 BY &dim1._SORT NOPRINT
	 BY &dim1      AS 'DEALER_NAME'
	 BY LOWEST &dim1_id   AS 'MODEL'
-* ACROSS &dim2._SORT NOPRINT
 ACROSS &dim2 AS 'Year'
 WHERE &dim2 NE 'Total'
 ON &dim1  SUBFOOT
ON TABLE SET PAGE-NUM NOLEAD
-*ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT &WFFMT.(<HTML Web Document (HTML),HTML>,<Excel XLSX (XLSX),XLSX>).Output type.
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = /WFC/Car_style.sty,

ENDSTYLE
END
-RUN
-LBL_END;


-*--------------------------------THIS IS THE SMALL CELL PROTECT FILE --------------------------------------------------------------

-*-----------------------------------------------------------------------------------------------
-* Phase 0 - Initialize
-* Establish default values. This defaulting sytax requires calling proceudre to -SET
-* to override the default parameters.
-DEFAULTH &small   = '5';
-DEFAULTH &tsmall  = '3';
-DEFAULT &input   = 'input';
-DEFAULT &output  = 'output';
-DEFAULT &dim1    = ' ';
-DEFAULT &dim1_id = '&dim1.EVAL';
-DEFAULT &dim2    = ' ';
-DEFAULTH &dim2_id = '&dim2.EVAL';
-DEFAULT &measure = ' ';

 SET ASNAME=ON,HOLDLIST=PRINTONLY
 FILEDEF small_cell_data DISK small_cell_data.ftm (APPEND

 DEFINE FILE &input.EVAL
 DIM1_ID/A100 = &dim1_id.EVAL;
 DIM1/A100    = &dim1.EVAL;
 DIM2_ID/A100 = &dim2_id.EVAL;
 DIM2/A100    = &dim2.EVAL;
 MEASURE/D20  = &measure.EVAL;
 END

-*-----------------------------------------------------------------------------------------------
-* Phase 1 - Dim Orders
-* Assign a number to each distinct dimension value and append a Total entry with the last value.
-* This is used for sorting the rows and columns in the final report, including the totals which
-* are calculated in the reporting data (not doing ROW-TOTAL and COLUMN-TOTAL features).
 TABLE FILE &input.EVAL
 SUM     DIM1 NOPRINT
 COMPUTE DIM1_SORT/I11 = IF LAST DIM1 EQ DIM1 THEN DIM1_SORT ELSE DIM1_SORT + 1;
      BY DIM1
 ON TABLE HOLD AS dim1 FORMAT FOCUS INDEX DIM1
 END
 SQL INSERT INTO dim1 VALUES ( 'Total' , 999999999 );
 END

 TABLE FILE &input.EVAL
 SUM     DIM2 NOPRINT
 COMPUTE DIM2_SORT/I11 = IF LAST DIM2 EQ DIM2 THEN DIM2_SORT ELSE DIM2_SORT + 1;
      BY DIM2
 ON TABLE HOLD AS dim2 FORMAT FOCUS INDEX DIM2
 END
 SQL INSERT INTO dim2 VALUES ( 'Total' , 999999999 );
 END

-*-----------------------------------------------------------------------------------------------
-* Phase 2 - Main Data
-* Define a counter SMALL_COUNTER for small values. Define NONSMALL_TOTAL for summing all non-small values.
 DEFINE FILE &input.EVAL ADD
 SMALL_COUNTER/I11  = IF MEASURE LT &small THEN 1       ELSE 0;
 NONSMALL_TOTAL/I11 = IF MEASURE GE &small THEN MEASURE ELSE 0;
 END

 TABLE FILE &input.EVAL
 PRINT   MEASURE
         SMALL_COUNTER
         NONSMALL_TOTAL
 COMPUTE DETAIL/A1 = 'D';
      BY DIM1_ID
      BY DIM1
      BY DIM2_ID
      BY DIM2
 ON TABLE HOLD AS small_cell_data FORMAT ALPHA
 END

-*-----------------------------------------------------------------------------------------------
-* Phase 3 - Row/Col Totals
-* Calculate totals for both dimensions (what will become totals row and column in the final
-* report) and append to previous hold file.
 TABLE FILE &input.EVAL
 SUM     DIM1_ID
         DIM1
 COMPUTE DIM2_ID/A100 = ' ';
 COMPUTE DIM2/A100   = 'Total';
         MEASURE
		 SMALL_COUNTER
         NONSMALL_TOTAL
 COMPUTE DETAIL/A1 = 'T';
      BY DIM1    NOPRINT
      BY DIM1_ID NOPRINT
 ON TABLE HOLD AS small_cell_data FORMAT ALPHA
 END

 TABLE FILE &input.EVAL
 SUM
 COMPUTE DIM1_ID/A100 = ' ';
 COMPUTE DIM1/A100   = 'Total';
         DIM2_ID
         DIM2
         MEASURE
		 SMALL_COUNTER
         NONSMALL_TOTAL
 COMPUTE DETAIL/A1 = 'T';
      BY DIM2    NOPRINT
      BY DIM2_ID NOPRINT
 ON TABLE HOLD AS small_cell_data FORMAT ALPHA
 END

-*-----------------------------------------------------------------------------------------------
-* Phase 4 - Join Sorts
-* Join to dim sort tables to get proper column and row sorting, including Total row and column
-* at the bottom and right.
 JOIN DIM1 IN small_cell_data TO DIM1 IN dim1 AS J1
 JOIN DIM2 IN small_cell_data TO DIM2 IN dim2 AS J2

-*-----------------------------------------------------------------------------------------------
-* Phase 5 - Final Logic
-* Join to dim sort tables to get proper column and row sorting, including Total row and column
-* at the bottom and right.
-SET &ECHO='ALL';
 TABLE FILE small_cell_data
 PRINT
 DIM1           -*AS '&dim1'
 DIM1_ID        -*AS '&dim1_id'
 DIM1_SORT      -*AS '&dim1._SORT'
 DIM2           -*AS '&dim2'
 DIM2_ID        -*AS '&dim2_id'
 DIM2_SORT      -*AS '&dim2._SORT'
 DETAIL
 MEASURE
 SMALL_COUNTER
 NONSMALL_TOTAL
 COMPUTE MEASURE_STR/I11 =
         IF DETAIL EQ 'T' AND SMALL_COUNTER GE 2 AND MEASURE-NONSMALL_TOTAL LE &tsmall THEN NONSMALL_TOTAL ELSE
         IF DETAIL EQ 'T' AND SMALL_COUNTER EQ 1                                       THEN NONSMALL_TOTAL ELSE
		                                                                                    MEASURE;
 COMPUTE MEASURE_END/I11 =
         IF DETAIL EQ 'T' AND SMALL_COUNTER GE 2 AND MEASURE-NONSMALL_TOTAL LE &tsmall THEN MEASURE_STR + &small - 1 ELSE
         IF DETAIL EQ 'T' AND SMALL_COUNTER EQ 1                                       THEN MEASURE_STR + &small - 1 ELSE
                                                                                            0;
-*updated I11 to d11 to shift from integer to decimal in order to display comma formatting
COMPUTE FMEASURE/I11 = IF DETAIL EQ 'D' AND MEASURE LT &small THEN -1
					    ELSE IF MEASURE_END EQ 0 THEN MEASURE_STR;
-* COMPUTE FMEASURE/A100 =
-*         IF DETAIL EQ 'D' AND MEASURE LT &small THEN  '^'                                                                            ELSE
-*         IF MEASURE_END EQ 0                    THEN FPRINT(MEASURE_STR, 'D11', 'A40');-*                                               ELSE
-*		                                             FPRINT(MEASURE_STR, 'D11', 'A40') || ' -' || FPRINT(MEASURE_END, 'D11', 'A40');
               -* AS '&measure'
 ON TABLE HOLD AS small_cell_data FORMAT ALPHA
 END


 TABLE FILE small_cell_data
 PRINT
 DIM1           AS '&dim1'
 DIM1_ID        AS '&dim1_id'
 DIM1_SORT      AS '&dim1._SORT'
 DIM2           AS '&dim2'
 DIM2_ID        AS '&dim2_id'
 DIM2_SORT      AS '&dim2._SORT'
 DETAIL
 MEASURE
 SMALL_COUNTER
 NONSMALL_TOTAL
 MEASURE_STR
 MEASURE_END
 FMEASURE      AS '&measure'
 ON TABLE HOLD AS &output
 END
-RUN


Here is some sample output:

CAR Inventory							
Inventory by Model							
Sales Year 2012-2017							
							
							
		Year					
		2012	2013	2014	2015	2016	2017
Dealership	Model						
Abdul's Cars	Honda	25	61	274	94	159	352
	Ford	50	76	76	63	102	121
	Toyota	73	92	96	76	68	84
	Subaru	13	6	5	8	9	11
	Volvo	20	21	19	14	24	28
	Jaguar	6	8	8	-1	-1	-1
	BMW	-	-	-	-	-1	-1
Total		187	264	478	254	360	594

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


WebFocus 8.2.04
WebFocus 8.2.04

 
Posts: 191 | Registered: September 18, 2015Report This Post
MartinY
Virtuoso
posted Hide Post
Hi JulieA,

Using the ACROSS-TOTAL may be the simplest way
DEFINE FILE GGSALES
YR /YY = DATE;
END
TABLE FILE GGSALES
SUM DOLLARS/D8C
BY REGION
ACROSS YR ACROSS-TOTAL AS 'Total'
ON TABLE COLUMN-TOTAL AS 'Grand-Total'
ON TABLE SET PAGE-NUM NOLEAD
END
-RUN



When the ACROSS-TOTAL doesn't do it as expected do it manually, use the below similar technic as I already provide you
-* EXTRACT SOURCE DATA
DEFINE FILE GGSALES
YR /YY = DATE;
END
TABLE FILE GGSALES
SUM DOLLARS
BY REGION
BY YR
ON TABLE HOLD AS EXTDATA
END
-RUN

-* CREATE YR ROW TOTAL
TABLE FILE EXTDATA
SUM DOLLARS
BY REGION
BY TOTAL COMPUTE YR /YY = 9999;
ON TABLE HOLD AS YRTOT
END
-RUN

-* NMERGE DETAIL WITH TOTAL
TABLE FILE EXTDATA
SUM DOLLARS
BY REGION
BY YR
ON TABLE HOLD AS RPTDATA
MORE
FILE YRTOT
END
-RUN

-* PRODUCE FINAL REPORT
DEFINE FILE RPTDATA
YRX /A5 = IF YR EQ 9999 THEN 'Total' ELSE FPRINT(YR, 'YY', 'A4');
END
TABLE FILE RPTDATA
SUM DOLLARS/D8C
BY REGION
ACROSS YR  NOPRINT
ACROSS YRX AS ''
ON TABLE COLUMN-TOTAL AS 'Grand-Total'
ON TABLE SET PAGE-NUM NOLEAD
END
-RUN

This message has been edited. Last edited by: MartinY,


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: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report 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] Create Subtotals without Using SUBTOTAL or TOTAL

Copyright © 1996-2020 Information Builders