In a previous post (now marked as SOLVED) titled Rolling Five year Percentage of Change, I received an enormous amount of assistance. I have one final piece, albeit minor, for which I I am stumped right now. In the section marked PRODUCE FINAL REPORT, I need to format the year appropriately when it is 0 instead of an actual year.

Here's what I mean: For some instances, I might only have data beginning in 2011. If I select 2011 as my year from a dropdown box, the remaining years show as 0 instead of years. This is correct.

Right now the result looks like this:

 2011 0 0 0 0 0 5 Year Chg 

I want the zeros to appear in the same color as the background. That way, it's easy to tell there is no data for that year. I already set the NODATA character to something else, so I cannot change it unless there is a way to set up two instances. I thought about trying to format the year doing it this way:

 TYPE=DATA, WHEN = &YR2.EVAL EQ '0', COLOR=RGB (0 45 75), STYLE=NORMAL, $ 

I also thought trying to set the DEFAULTH to only appear if there was a year. I want to leave the rest of the report as is for the time being.

Here's the code:

 -* EXTRACT SOURCE DATA
DEFINE FILE EXPENSES
FISCSET/I4 = EDIT(FISCAL_YEAR);
YR /YY = FISCSET;
GROUP_NO/A6 = EDIT(GROUP_NO);
END

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

TABLE FILE EXPENSES
SUM
EXPENSES.EXPENSES.GROUP_NO
EXPENSES.EXPENSES.GROUP_TOTAL/P11M
BY EXPENSES.EXPENSES.REGION
BY EXPENSES.EXPENSES.REGION_NAME
BY EXPENSES.EXPENSES.STORE_NAME
BY EXPENSES.EXPENSES.GROUP_TYPE
BY EXPENSES.EXPENSES.GROUP_TYPE_NAME
BY EXPENSES.EXPENSES.GROUP_NAME
BY EXPENSES.EXPENSES.YR
WHERE ( EXPENSES.EXPENSES.FIN_NAME EQ 'All Operating Expenses' );
WHERE ( EXPENSES.EXPENSES.REGION_NAME EQ '®ION_NAME.(<Southwest,Southwest>).Region:.' );
WHERE ( EXPENSES.EXPENSES.STORE_NAME EQ '&STORE_NAME.(FIND EXPENSES.EXPENSES.STORE_NAME IN EXPENSES).Store:.' );
WHERE ( EXPENSES.EXPENSES.YR GE &MINYR);
WHERE ( EXPENSES.EXPENSES.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

-* CALCULATE INCREASE PCT FOR DETAILED ROWS
TABLE FILE EXTDATA
SUM
GROUP_TOTAL/P11M
    COMPUTE LAG_1 = IF STORE_NAME EQ LAST STORE_NAME THEN LAST GROUP_TOTAL ELSE 0 ;
    COMPUTE LAG_2 = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_1 ELSE 0;
    COMPUTE LAG_3 = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_2 ELSE 0;
    COMPUTE LAG_4 = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_3 ELSE 0;
    COMPUTE LAG_5 = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_4 ELSE 0;
    COMPUTE PCTCHG/P8.1C%  MISSING ON = IF LAG_5 NE 0 AND YR EQ &YRMAX THEN (GROUP_TOTAL - LAG_5) / LAG_5 *100 ELSE MISSING;
BY TOTAL COMPUTE ROWID/I2 = 1;
BY GROUP_TYPE/P3
BY GROUP_TYPE_NAME/A20
BY GROUP_NO/A6
BY GROUP_NAME/A30
BY YR
ON TABLE HOLD AS DETDATA FORMAT FOCUS
-*ON TABLE PCHOLD FORMAT HTML
END
-RUN

-* CALCULATE INCREASE PCT FOR SUBTOTAL ROWS
TABLE FILE EXTDATA
SUM
GROUP_TOTAL/P11M
    COMPUTE LAG_1 = IF STORE_NAME EQ LAST STORE_NAME THEN LAST GROUP_TOTAL ELSE 0 ;
    COMPUTE LAG_2 = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_1 ELSE 0;
    COMPUTE LAG_3 = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_2 ELSE 0;
    COMPUTE LAG_4 = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_3 ELSE 0;
    COMPUTE LAG_5 = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_4 ELSE 0;
    COMPUTE PCTCHG/P8.1C%  MISSING ON = IF LAG_5 NE 0 AND YR EQ &YRMAX THEN (GROUP_TOTAL - LAG_5) / LAG_5 *100 ELSE MISSING;
BY TOTAL COMPUTE ROWID/I2 = 2;
BY GROUP_TYPE/P3
BY TOTAL COMPUTE GROUP_TYPE_NAME/A20 = ' ';
BY TOTAL COMPUTE GROUP_NO/A6 = ' ';
BY TOTAL COMPUTE GROUP_NAME/A30 = 'Group Subtotal';
BY YR
ON TABLE HOLD AS STOTDATA FORMAT FOCUS
-*ON TABLE PCHOLD FORMAT HTML
END
-RUN

-* CALCULATE INCREASE PCT FOR TOTAL ROW
TABLE FILE EXTDATA
SUM
GROUP_TOTAL/P11M
*-I believe these lines are no longer necessary-*
	COMPUTE LAG_1 = IF STORE_NAME EQ LAST STORE_NAME THEN LAST GROUP_TOTAL ELSE 0 ;
    COMPUTE LAG_2 = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_1 ELSE 0;
    COMPUTE LAG_3 = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_2 ELSE 0;
    COMPUTE LAG_4 = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_3 ELSE 0;
    COMPUTE LAG_5 = IF STORE_NAME EQ LAST STORE_NAME THEN LAST LAG_4 ELSE 0;
	COMPUTE PCTCHG/P8.1C%  MISSING ON = IF LAG_5 NE 0 AND YR EQ &YRMAX THEN (GROUP_TOTAL - LAG_5) / LAG_5 *100 ELSE MISSING;
BY TOTAL COMPUTE ROWID/I2 = 9;
BY TOTAL COMPUTE GROUP_TYPE/P3
BY TOTAL COMPUTE GROUP_TYPE_NAME/A20 = 'Total Operating Exp.';
BY TOTAL COMPUTE GROUP_NO/A6 = ' ';
BY TOTAL COMPUTE GROUP_NAME/A30 = ' ';
BY YR
ON TABLE HOLD AS TOTDATA FORMAT FOCUS
-*ON TABLE PCHOLD FORMAT HTML
END
-RUN

-* MERGE DETAIL WITH TOTAL AND SUBTOTALS
TABLE FILE DETDATA
SUM
   	GROUP_TOTAL
	PCTCHG
BY LOWEST GROUP_TYPE
BY LOWEST GROUP_TYPE_NAME
BY LOWEST GROUP_NO
BY LOWEST GROUP_NAME
BY LOWEST ROWID
BY GROUP_TYPE_NAME
BY GROUP_NO
BY GROUP_NAME
BY YR
-*ON TABLE PCHOLD FORMAT HTML
ON TABLE HOLD AS RPTDATA FORMAT FOCUS
MORE
FILE STOTDATA
MORE
FILE TOTDATA
END
-RUN

-* PRODUCE FINAL REPORT
-DEFAULTH &YR1 = 0, &YR2 = 0, &YR3 = 0, &YR4 = 0, &YR5 = 0, &YR6 = 0
DEFINE FILE RPTDATA
COL1 /P11M = IF YR EQ &YR1.EVAL THEN GROUP_TOTAL ELSE 0;
COL2 /P11M = IF YR EQ &YR2.EVAL THEN GROUP_TOTAL ELSE 0;
COL3 /P11M = IF YR EQ &YR3.EVAL THEN GROUP_TOTAL ELSE 0;
COL4 /P11M = IF YR EQ &YR4.EVAL THEN GROUP_TOTAL ELSE 0;
COL5 /P11M = IF YR EQ &YR5.EVAL THEN GROUP_TOTAL ELSE 0;
COL6 /P11M = IF YR EQ &YR6.EVAL THEN GROUP_TOTAL ELSE 0;
COL7 /P8.1C% MISSING ON  = IF YR EQ &YR6.EVAL THEN PCTCHG ELSE MISSING;
END

TABLE FILE RPTDATA
SUM COL1 AS '&YR1.EVAL'
  	COL2 AS '&YR2.EVAL'
  	COL3 AS '&YR3.EVAL'
	COL4 AS '&YR4.EVAL'
	COL5 AS '&YR5.EVAL'
	COL6 AS '&YR6.EVAL'
/*The line below will obviously be updated*/
  	COL7 AS '5 Year Chg'
BY LOWEST GROUP_TYPE NOPRINT
BY LOWEST ROWID NOPRINT
BY GROUP_TYPE_NAME AS 'Subdivision'
BY GROUP_NO NOPRINT
BY GROUP_NAME AS 'Category'

HEADING
"Operating Expenses by Category"
"&RPT_HEAD"
"Fiscal Years &MINYR - &MAXYR"
FOOTING
"Source: Operating expense summaries supplied by the plants themselves        "
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
$
TYPE=REPORT, TITLETEXT='WebFOCUS Report', ORIENTATION=LANDSCAPE, $
ENDSTYLE
END
-RUN
 

The WHEN clause of WF styling is very limited - you can only use report columns and the WHEN can't be complex.

How about setting one Year variable for your calculations and one for the column titles, setting it to blank as default?

...

-REPEAT READYR FOR &I FROM 1 TO &NBYR STEP 1
-DEFAULTH &YR.&I = 0;
-DEFAULTH &YR_TITLE.&I = '';

-READFILE YRLIST
-SET &YR.&I = &EXTYR;
-SET &YR_TITLE.&I = &EXTYR;

-TYPE &YR.&I 
-TYPE &YR_TITLE.&I
-READYR

...

SUM 
COL1 AS '&YR_TITLE1'
COL2 AS '&YR_TITLE2'
COL3 AS '&YR_TITLE3'
COL4 AS '&YR_TITLE4'
COL5 AS '&YR_TITLE5'
COL6 AS '&YR_TITLE6'

...

Why are you making the year 0? what if you made the value null (MISSING in WF Speak) instead of zero. Then use SET NODATA=''; so that there is no value to display. This way you don't have to mess with trying to make your value blend in with your background.

Francis,

That's not a bad idea. I took the snippet of code you gave me, and it almost works for me.

 -REPEAT READYR FOR &I FROM 1 TO &NBYR STEP 1
-DEFAULTH &YR.&I = 0;
-DEFAULTH &YR_TITLE.&I = '';

-READFILE YRLIST
-SET &YR.&I = &EXTYR;
-SET &YR_TITLE.&I = &EXTYR;

-TYPE &YR.&I 
-TYPE &YR_TITLE.&I
-READYR 

Then below I did:

  TABLE FILE RPTDATA
SUM
COL1 AS '&YR_TITLE1'
COL2 AS '&YR_TITLE2'
COL3 AS '&YR_TITLE3'
COL4 AS '&YR_TITLE4'
COL5 AS '&YR_TITLE5'

It works perfectly except when I run the fex I, of course, get boxes for &YR_TITLE1, &YR_TITLE2, etc. The report formats perfectly though.

quote:

Originally posted by eric.woerle:
Why are you making the year 0? what if you made the value null (MISSING in WF Speak) instead of zero. Then use SET NODATA=''; so that there is no value to display. This way you don't have to mess with trying to make your value blend in with your background.

My SET NODATA is set to something else for another portion of the code. I agree. That would be easier.

quote:

-REPEAT READYR FOR &I FROM 1 TO &NBYR STEP 1
-DEFAULTH &YR.&I = 0;
-DEFAULTH &YR_TITLE.&I = '';
...
get boxes for &YR_TITLE1, &YR_TITLE2

Even with the DEFAULTH in the loop, it seems that it not doing it properly (it does on my side).
Since you could only have 5 years, force the DEFAULTH. An easy work around.
-DEFAULTH &YR_TITLE1 = ''
-DEFAULTH &YR_TITLE2 = ''
-DEFAULTH &YR_TITLE3 = ''
-DEFAULTH &YR_TITLE4 = ''
-DEFAULTH &YR_TITLE5 = ''

Another option could be with your Client setting. Look at Admin Console / Configuration / Application Setting / Parameter Prompting

quote:

My SET NODATA is set to something else for another portion of the code. I agree.

Then set it just for that table request -

ON TABLE SET ODATA 'x'

T

What if you want your measures and dimensions to have different NODATA values?