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     [CLOSED] Format Total in a Masking Procedure

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Format Total in a Masking Procedure
 Login/Join
 
Platinum Member
posted
Dear FocalPoint Forum Community,

I have figured out nearly everything I need to use this masking procedure (see below) within another fex that shows a rolling five years' worth of data. I am down to one minor item.

Objective: I need to format my total line in a specific color, font, etc. I'd appreciate any ideas.

The total line comes from Phase 3 in my code below. In another fex, I used something similar to what's found immediately below in the three lines where TYPE=DATA. As the TOTAL LINE comes in differently from another fex, it does not seem I can use the TYPE=DATA technique albeit I could be wrong or missing something. It's either that, or I need to define the TOTAL somehow in the actual table fex and format it to override my style sheet.

 
TYPE=DATA,       WHEN = STORE EQ 'Total Sales', BORDER-BOTTOM=LIGHT, BORDER-BOTTOM-COLOR=RGB(0 58 99),$
TYPE=DATA,       WHEN = STORE EQ 'Total Sales', BACKCOLOR=RGB(0 58 99), STYLE=NORMAL, $
TYPE=DATA,       WHEN = STORE EQ 'Total Sales', COLOR=RGB(255 226 146), STYLE=NORMAL, $ 


Basically, what I'm trying to do is get the lines labeled TOTAL to be formatted in a manner similar to the three lines listed above. I have tried several different things, and I have narrowed down to the masking procedure. Or, at least that seems to be the issue.

 
-*-----------------------------------------------------------------------------------------------
-* 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/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 &output
 END
-RUN


 

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
Virtuoso
posted Hide Post
Something such as this should work

DEFINE FILE small_cell_data
ISTOTAL / A1 = IF STORE EQ 'Total Sales' THEN 'Y' ELSE 'N';
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
 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/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'

 ISTOTAL NOPRINT

-*ON TABLE HOLD AS &output

ON TABLE SET STYLE *
     DEFMACRO=IS_TOTAL,
     MACTYPE=RULE,
     WHEN=ISTOTAL LT 'Y',
$
TYPE=DATA,
     BORDER-BOTTOM=LIGHT,
     BORDER-BOTTOM-COLOR=RGB(0 58 99),
     BACKCOLOR=RGB(0 58 99),
     COLOR=RGB(255 226 146),
     STYLE=NORMAL,
     MACRO=IS_TOTAL,
$
ENDSTYLE
END
-RUN


Sample
DEFINE FILE CAR
ISTOTAL / A1 = IF COUNTRY EQ 'ITALY' THEN 'Y' ELSE 'N';
END
TABLE FILE CAR
PRINT RETAIL_COST
      DEALER_COST
      SEATS
      ISTOTAL NOPRINT
BY COUNTRY
BY CAR
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE SET BYDISPLAY ON
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
     DEFMACRO=IS_TOTAL,
     MACTYPE=RULE,
     WHEN=ISTOTAL EQ 'Y',
$
TYPE=DATA,
     BORDER-BOTTOM=LIGHT,
     BORDER-BOTTOM-COLOR=RGB(0 58 99),
     BACKCOLOR=RGB(0 58 99),
     COLOR=RGB(255 226 146),
     STYLE=NORMAL,
     MACRO=IS_TOTAL,
$
ENDSTYLE
END


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     [CLOSED] Format Total in a Masking Procedure

Copyright © 1996-2020 Information Builders