Focal Point
[SOLVED] Spaces in Sub total/Grand total when no value is the

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/3877024676

May 28, 2015, 05:03 PM
wf1998
[SOLVED] Spaces in Sub total/Grand total when no value is the
Hi,

i want to display spaces where no value is available in Sub total or grand total.
i tried to use RECAP but it's still displaying 0 or 0% etc..
is there a way we can display spaces.



code which i used:
DEFINE FILE PCSUBTOT
PGBR/A4 = '
';
NBSP/A6 = '&' | 'nbsp;';
NBSP3/A20 = NBSP | NBSP;
END
-RUN

SET NODATA = '';

TABLE FILE PCSUBTOT
PRINT
Curr_SC AS 'Subs'
Curr_NBPC AS 'Bd'
COMPUTE Curr_NBPCPt/P33.1% MISSING ON = IF Curr_NBPnPC NE MISSING THEN ((Curr_NBPC/Curr_NBPnPC)-1)*100
ELSE MISSING; AS 'Bd vs. Pn'

Curr_NBPnPC NOPRINT
NBSP3 NOPRINT
BY Curr_BS NOPRINT
BY Curr_Year NOPRINT
BY PIQUARTER NOPRINT
BY PIMONTHNAME AS ''
BY PIMONTH NOPRINT
ON TABLE SUBFOOT
" <+0> <+0> "
"<+0>Run Date: <+0> &DATEMDYY"
" "
ON TABLE SUBHEAD
" "
" Report Filters "
"Year: - 2015"
"Select BS: A(51)"
" "
"PC Report"
HEADING
""NB"
"p Count"
ON TABLE SET PAGE-NUM NOLEAD
ON PIQUARTER RECAP
SQ_Curr_SC/I11C = Curr_SC;
SQ_Curr_NBPC/I11C = Curr_NBPC;
SQ_Curr_NBPCPt/P33.1% = IF Curr_NBPnPC NE 0
THEN ((Curr_NBPC/Curr_NBPnPC)-1)*100 ELSE 0;

ON PIQUARTER SUBFOOT
"Sub-Total<+0>WHEN PIMONTH LE 3

ON Curr_Year RECAP
SF_Curr_SC/I11C = Curr_SC;
SF_Curr_NBPC/I11C = Curr_NBPC;
SF_Curr_NBPC/P33.1% = IF (Curr_NBPnPC EQ 0) THEN 0 ELSE ((Curr_NBPC/Curr_NBPnPC)-1)*100;

ON Curr_Year SUBFOOT
"YTD 2015<+0>
ON Curr_BS PAGE-BREAK

ON TABLE PCHOLD FORMAT HTML
ON TABLE NOTOTAL
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,PAGESIZE='Letter',
LEFTMARGIN=0.1000000,RIGHTMARGIN=0.1000000,
TOPMARGIN=0.100000,BOTTOMMARGIN=0.100000,
SQUEEZE=ON,ORIENTATION=LANDSCAPE,PAGECOLOR=RGB(250 250 250),$
TYPE=REPORT,GRID=ON,FONT='ARIAL', SIZE=9, COLOR='NAVY', BACKCOLOR='NONE',$
STYLE=NORMAL, WRAP=OFF,
RIGHTGAP=0.0500000, LEFTGAP=0.0500000, TOPGAP=0.013889, BOTTOMGAP=0.027778,
TITLETEXT='IRF - PC Report',$

TYPE=DATA,SIZE=8,BACKCOLOR=( 'WHITE' RGB(231 236 255) ),TOPGAP=0.013889,BOTTOMGAP=0.027778, JUSTIFY=CENTER,$
TYPE=DATA,COLUMN=PIMONTHNAME,SIZE=8,BACKCOLOR=( 'WHITE' RGB(231 236 255) ),JUSTIFY=LEFT,$

TYPE=TITLE,BACKCOLOR =RGB(193 193 193),STYLE=NORMAL,TOPGAP=0.013889,BOTTOMGAP=0.017778,JUSTIFY=CENTER,$

TYPE=TABHEADING,COLOR=RGB(0 51 204),$
TYPE=TABHEADING,LINE=2,STYLE=BOLD,$
TYPE=TABHEADING,LINE=3,OBJECT=TEXT,ITEM=1,$
TYPE=TABHEADING,LINE=4,OBJECT=TEXT,ITEM=1,$
TYPE=TABHEADING,LINE=6,STYLE=BOLD,JUSTIFY=LEFT,COLOR='green',SIZE=12,$

TYPE=TABFOOTING, COLOR=RGB(0 51 204),$
TYPE=TABFOOTING, LINE=2,SIZE=8,COLOR=SILVER,$


TYPE=HEADING,HEADALIGN=BODY,SIZE=8,$

TYPE=HEADING,LINE=1,ITEM=1,STYLE=NORMAL,SIZE=12,JUSTIFY=CENTER,COLSPAN=4,$

TYPE=HEADING, LINE=2,BACKCOLOR=RGB(220 220 220 ),$
TYPE=HEADING, LINE=2,ITEM=1,JUSTIFY=LEFT,STYLE=BOLD,SIZE=10,COLSPAN=1,BACKCOLOR=RGB(255 255 255),$
TYPE=HEADING, LINE=2,ITEM=2,JUSTIFY=CENTER,STYLE=NORMAL,SIZE=10,COLSPAN=3,BACKCOLOR=RGB(255 255 255),$

TYPE=HEADING, LINE=3,BACKCOLOR=RGB(220 220 220 ),$
TYPE=HEADING, LINE=3,ITEM=1,JUSTIFY=CENTER,STYLE=NORMAL,SIZE=10,COLSPAN=1,BACKCOLOR=RGB(255 255 255),$
TYPE=HEADING, LINE=3,ITEM=2,JUSTIFY=CENTER,STYLE=NORMAL,SIZE=10,COLSPAN=3,BACKCOLOR=RGB(255 255 255),$

TYPE=SUBFOOT,HEADALIGN=BODY,$
TYPE=SUBFOOT, LINE=1,BACKCOLOR=RGB(220 220 220),SIZE=9,JUSTIFY=CENTER,STYLE=NORMAL,$
TYPE=SUBFOOT, LINE=1,ITEM=1,COLSPAN=1,JUSTIFY=LEFT,$
TYPE=SUBFOOT, LINE=1,ITEM=2,COLSPAN=1,$
TYPE=SUBFOOT, LINE=1,ITEM=3,COLSPAN=1,$
TYPE=SUBFOOT, LINE=1,ITEM=4,COLSPAN=1,$

ENDSTYLE
END
-RUN

This message has been edited. Last edited by: <Kathryn Henning>,




Env Prod:WebFOCUS 7702 ,Windows xp on 64, SQL Server 2008, IRF Tool
Env 1 Local: DevStudio 7702 - MS Windows XP SP2 - Apache Tomcat 5.0.28
Output: HTML, Excel and PDF
May 28, 2015, 05:18 PM
Francis Mariani
You need to change the data format of the report column.

Take a look at the WebFOCUS metadata for table PCSUBTOT and determine what the usage format for columns Curr_SC and Curr_NBPC are.

Then you need to change the format of the columns using the following example to override the WebFOCUS metadata:

TABLE FILE PCSUBTOT
PRINT
Curr_SC/D25.2S AS 'Subs'
Curr_NBPC/P20S AS 'Bd'
COMPUTE Curr_NBPCPt/P33.1S% MISSING ON = IF Curr_NBPnPC NE MISSING THEN ((Curr_NBPC / Curr_NBPnPC) - 1) * 100 ELSE MISSING; AS 'Bd vs. Pn'


In the example, D25.2S and P20S are the column formats with "S" appended to suppress zeros - as the documentation specifies, "If the data value is zero, prints a blank in its place." - please see Describing Data With WebFOCUS Language > Describing an Individual Field > The Displayed Data Type: USAGE > Numeric Display Options.

For the percentage, I'm not sure if P33.1S% is correct - it might be P33.1%S, just test either.


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
May 29, 2015, 12:02 PM
wf1998
Thanks Francis

its working fine




Env Prod:WebFOCUS 7702 ,Windows xp on 64, SQL Server 2008, IRF Tool
Env 1 Local: DevStudio 7702 - MS Windows XP SP2 - Apache Tomcat 5.0.28
Output: HTML, Excel and PDF