Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     FORMAT COLOR OF DEFAULT VARIABLE
Go
New
Search
Notify
Tools
Reply
  
FORMAT COLOR OF DEFAULT VARIABLE
 Login/Join
 
Platinum Member
posted
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
 


WebFocus 8.2.04
WebFocus 8.2.04

 
Posts: 171 | Registered: September 18, 2015Reply With QuoteReport This Post
Expert
posted Hide Post
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'

...


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Master
posted Hide Post
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.


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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.


WebFocus 8.2.04
WebFocus 8.2.04

 
Posts: 171 | Registered: September 18, 2015Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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.


WebFocus 8.2.04
WebFocus 8.2.04

 
Posts: 171 | Registered: September 18, 2015Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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


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: 2298 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Expert
posted Hide Post
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



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 
 
Posts: 5669 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
Expert
posted Hide Post
What if you want your measures and dimensions to have different NODATA values?


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     FORMAT COLOR OF DEFAULT VARIABLE

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.