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.
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,
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, 2013