-* 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.DELEAR_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
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 594This message has been edited. Last edited by: JulieA,
SET NODATA = '-' TABLE FILE CAR SUM COMPUTE NET_SALES /I6 MISSING ON = IF SALES LE 0 THEN MISSING ELSE IF (SALES - RETAIL_COST) LE -1 THEN MISSING ELSE SALES - RETAIL_COST; BY CAR BY MODEL ON TABLE PCHOLD FORMAT XLSX ON TABLE SET BYDISPLAY ON END
-SET &Dummy = '&WFFMT.(HTML,XLSX,PDF).Output format'; -SET &Suffix = IF &WFFMT NE 'XLSX' THEN '_A' ELSE ''; TABLE FILE CAR SUM DCOST COMPUTE MEASURE/I11 = IF DCOST LE 5000 THEN -1 ELSE DCOST; COMPUTE MEASURE_A/A40 = IF MEASURE EQ -1 THEN '^' ELSE RJUST(20,FPRINT(DCOST,'D11','A20'),'A20'); BY COUNTRY BY CAR BY MODEL ON TABLE HOLD AS TEMPHLD1 END -RUN TABLE FILE TEMPHLD1 SUM MEASURE&Suffix.EVAL BY COUNTRY BY CAR BY MODEL ON TABLE PCHOLD FORMAT &WFFMT END -RUN
In FOCUS since 1986 | WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2 | ||
WebFOCUS App Studio 8.2.06 standalone on Windows 10 |
quote:
We need to convert the value from text to numeric no matter the format (i.e. HTML, Excel).
SET NODATA = '-' TABLE FILE CAR SUM COMPUTE NET_SALES /A10 MISSING ON = IF SALES LE 0 THEN MISSING ELSE IF (SALES - RETAIL_COST) LE -1 THEN '^' ELSE FPRINT((SALES - RETAIL_COST), 'I6', 'A10'); BY CAR BY MODEL ON TABLE PCHOLD FORMAT XLSX ON TABLE SET BYDISPLAY ON ON TABLE SET STYLE * INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty, $ TYPE=DATA, COLUMN=NET_SALES, JUSTIFY=RIGHT, $ ENDSTYLE END -RUN
quote:We need to convert the value from text to numeric no matter the format
In FOCUS since 1986 | WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2 | ||
WebFOCUS App Studio 8.2.06 standalone on Windows 10 |