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     [CLOSED] display space and zeros in subtotals and totals
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] display space and zeros in subtotals and totals
 Login/Join
 
Gold member
posted
i wants to
display Space in subtotals and Totals where all columns are missing
display 0 in subtotals and Totals where all columns sum is zero
but i am getting zero only, If I suppress zero then in second condition also showing space instead of 0.

how can I display space and zeros in subtotals and totals.


Here is the my code:
FILEDEF evdwh261 DISK D:/ibi/apps\baseapp/evdwh261.ftm
SET NODATA = ''
-SET &DEXPRDDSC = 'Year:';
-RUN

TABLE FILE evdwh261
-*PRINT *
-*END
-*-EXIT
SUM
COMPUTE CUSTA/I11C MISSING ON = IF CUST EQ MISSING THEN MISSING
ELSE IF CUST EQ 0 THEN 0 ELSE CUST; AS 'Cust Numbers'
SALES AS 'sales Numbers'
COMPUTE PLANA/P33.1% MISSING ON = IF (PLAN EQ MISSING) THEN MISSING
ELSE IF (PLAN EQ 0) THEN 0
ELSE IF (SALES/PLAN) GT 6 THEN 500
ELSE ((SALES/PLAN)-1)*100; AS 'sales vs. Plan'
-*
COMPUTE PRIORYEARA/P33.1% MISSING ON = IF (SALES GT 0 AND PRIORYEAR EQ 0) THEN 500
ELSE IF (SALES GT 0 AND PRIORYEAR EQ MISSING) THEN 500
ELSE IF (SALES/PRIORYEAR) GT 6 THEN 500
ELSE ((SALES/PRIORYEAR)-1)*100; AS 'sales vs. Prior Year'

-*SFWClaueFlag NOPRINT
BY Curr_Year NOPRINT
BY CUSTDESC NOPRINT
BY CUSTCD NOPRINT
BY PIQUARTER NOPRINT
BY PIMONTH NOPRINT
BY PIMONTHNAME AS ''
ON TABLE SUBFOOT
" <+0> <+0> "
"<+0>Run Date: <+0> 08/26/2015"
" "
" Report Filters: "
"Year: 2015"
"Selected Customer: cust(00)"
ON TABLE SUBHEAD
"Customer Count Report"
HEADING
""New Customers"
"Count"
ON TABLE SET PAGE-NUM NOLEAD
ON PIQUARTER RECAP
SQ_SUBFT_Year1/A100 = 'Sub-Total '|PIQUARTER;
SQ_CUSTA/I11C MISSING ON = CUSTA;
SQ_SALES/D25C = SALES;
-*
SQ_PLAN/P33.1% MISSING ON = IF (SALES GT 0 AND PLAN EQ 0) THEN 0
ELSE IF (SALES/PLAN) GT 6 THEN 500 ELSE ((SALES/PLAN)-1)*100;
-*
SQ_PRIORYEAR/P33.1% MISSING ON = IF (SALES GT 0 AND PRIORYEAR EQ 0) THEN 500
ELSE IF (SALES/PRIORYEAR) GT 6 THEN 500
ELSE ((SALES/PRIORYEAR)-1)*100;
-*
ON PIQUARTER SUBFOOT
"-*"WHEN SFWClaueFlag = 'Y'

-*ON PIQUARTER RECOMPUTE AS ' Sub-Total'
-*WHEN SFWClaueFlag = 'Y'
-*

-*
ON CUSTDESC RECAP
SF_SUBFT_Year1/A100 = 'YTD '|EDIT(EDIT(Curr_Year),'$$$$$$$9999');
SF_CUSTA/I11C MISSING ON = CUSTA;
SF_SALES/D25C = SALES;
-*
SF_PLAN/P33.1% MISSING ON = IF (SALES GT 0 AND PLAN EQ 0) THEN 0
ELSE IF (SALES/PLAN) GT 6 THEN 500 ELSE ((SALES/PLAN)-1)*100;
-*
SF_PRIORYEAR/P33.1% MISSING ON = IF (SALES GT 0 AND PRIORYEAR EQ 0) THEN 500
ELSE IF (SALES/PRIORYEAR) GT 6 THEN 500
ELSE ((SALES/PRIORYEAR)-1)*100;
ON CUSTDESC SUBFOOT
"
-*ON CUSTDESC PAGE-BREAK RECOMPUTE AS ' '

ON TABLE PCHOLD FORMAT HTML
ON TABLE SET WEBVIEWTARG _self
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,$

TYPE=HEADING,HEADALIGN=BODY,SIZE=8,$
TYPE=HEADING,LINE=1,ITEM=1,STYLE=NORMAL,SIZE=12,JUSTIFY=CENTER,COLSPAN=25,$
TYPE=HEADING, LINE=2,BACKCOLOR=RGB(220 220 220 ),$
TYPE=HEADING, LINE=2,ITEM=1,JUSTIFY=LEFT,STYLE=BOLD,SIZE=10,COLSPAN=2,BACKCOLOR=RGB(255 255 255),$
TYPE=HEADING, LINE=2,ITEM=2,JUSTIFY=CENTER,STYLE=NORMAL,SIZE=10,COLSPAN=7,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=4,BACKCOLOR=RGB(255 255 255),$
TYPE=SUBFOOT,HEADALIGN=BODY,$
TYPE=SUBFOOT, LINE=1,BACKCOLOR=RGB(220 220 220),SIZE=9,JUSTIFY=CENTER,STYLE=NORMAL,BY=2,$
TYPE=SUBFOOT, LINE=1,ITEM=1,COLSPAN=1,JUSTIFY=LEFT,BY=2,$
TYPE=SUBFOOT, LINE=1,ITEM=2,COLSPAN=1,BY=2,$
TYPE=SUBFOOT, LINE=1,ITEM=3,COLSPAN=1,BY=2,$
TYPE=SUBFOOT, LINE=1,ITEM=4,COLSPAN=1,BY=2,$
TYPE=SUBFOOT, LINE=1,ITEM=5,COLSPAN=1,BY=2,$
TYPE=SUBFOOT, LINE=1,BACKCOLOR=RGB(220 220 220),SIZE=9,JUSTIFY=CENTER,STYLE=NORMAL,BY=4,$
TYPE=SUBFOOT, LINE=1,ITEM=1,COLSPAN=1,JUSTIFY=LEFT,BY=4,$
TYPE=SUBFOOT, LINE=1,ITEM=2,COLSPAN=1,BY=4,$
TYPE=SUBFOOT, LINE=1,ITEM=3,COLSPAN=1,BY=4,$
TYPE=SUBFOOT, LINE=1,ITEM=4,COLSPAN=1,BY=4,$
TYPE=SUBFOOT, LINE=1,ITEM=5,COLSPAN=1,BY=4,$
ENDSTYLE
END
-RUN


field formats:
CUST E01 I11C MISSING = ON
SALES E02 I11C MISSING = ON
PLAN E03 I11C MISSING = ON
PRIORYEAR E04 I11C MISSING = ON
CUSTDESC E05 A255 MISSING = ON
CUSTCD E06 A4
Curr_Year E07 I11 MISSING = ON
PIQUARTER E08 A20
PIQUARTER_D E09 A2
PIMONTH E10 I6 MISSING = ON
PIMONTHNAME E11 A20 MISSING = ON
SFWClaueFlag E12 A1
NBSP2 E13 A20
NBSP3 E14 A20
NBSPPP E15 A20

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
 
Posts: 52 | Location: NJ,USA | Registered: May 26, 2004Reply With QuoteReport This Post
Guru
posted Hide Post
Hi wf1998,

This is a friendly follow-up regarding your question about - display space and zeros in subtotals and totals.

Possibly a screenshot of the report would be helpful to get a visual of what your report is doing.

What are the results that you are getting now?
Why are you using RECAP?

Have you tried the SUBTOTAL or RECOMPUTE command. RECOMPUTE is used when the computed columns require a subtotal - then the summed values are used within the COMPUTE to provide the proper subtotal.

Thank you for participating in the Focal Point Forum.

Kindest regards,
Tamra Colangelo
Focal Point Moderator
Information Builders Inc.


WebFOCUS 8x - BI Portal, Developer Studio, App Studio, Excel, PDF, Active Formats and HTML5
 
Posts: 487 | Location: Toronto | Registered: June 23, 2009Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] display space and zeros in subtotals and totals

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