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,

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