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     display records even for count of 0
Go
New
Search
Notify
Tools
Reply
  
display records even for count of 0
 Login/Join
 
Gold member
posted
I am doing a sum of the count of the ID numbers in the table sorted by two defined fields.

For example: In the table I have two columns
payment and ID number.

I need to define the payment for different categories.
define file xyz
PAYLVL/A20 = IF PAYMENT LT 3000 THEN 'AVERAGE' ELSE IF PAYMENT FROM 3001 TO 4000 THEN 'ABOVE AVERAGE' ELSE IF PAYMENT FROM 4001 TO 5000 THEN 'HIGH1' ELSE IF PAYMENT GT 5000 THEN 'HIGH2' ELSE '???';
EXPLVL/A20 = DECODE PAYLVL('AVERAGE' 'SENR'
'ABOVE AVERAGE' 'MNGR'
'HIGH1' 'EXTV'
'HIGH2' 'EXTV' ELSE '???');
END

TABLE FILE XYZ
SUM CNT.ID_NBR
PAYMENT
BY EXPLVL
BY PAYLVL
END

In this example for CNT.ID_NBR
for payment level HIGH2 there are no records. so, the CNT.ID_NBR is 0 and PAYMENT is 0
so, the final output I am getting is:
EXTV HIGH1 3 15000
MNGR ABOVE AVERAGE 1 ..
SENR AVERAGE ...

I need to get the output as follows:
EXTV HIGH2 0 0
EXTV HIGH1 3 15000

since the count is 0 and payment is 0 I am not getting the record at all. How do I get the output to display 0 even if there are no records?

Please advice on how to do this.

Thanks
 
Posts: 76 | Registered: October 28, 2003Reply With QuoteReport This Post
Expert
posted Hide Post
RB,

Using
BY EXPLVL ROWS 'SENR' OVER 'MNGR' OVER 'EXTV' OVER '???'
should do it for you

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: 5686 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Put the possible fields PAYLVL and EXPLVL in a separate table and join that table with the calculated results from

TABLE FILE XYZ
SUM CNT.ID_NBR
PAYMENT
BY PAYLVL
ON TABLE HOLD AS HOLD1 FORMAT FOCUS INDEX PAYLVL
END

JOIN the two tables on the field PAYLVL

TABLE FILE JOINED
PRINT ....

END
....

But Tony was quicker and better I think...




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Reply With QuoteReport This Post
Gold member
posted Hide Post
Thanks Tony A and FrankDutch for the quick reply. But both these solutions are not working for me.
I am still not getting the row for zero records.
How do I make the report display for Zero records with the other defined values?
 
Posts: 76 | Registered: October 28, 2003Reply With QuoteReport This Post
Virtuoso
posted Hide Post
quote:
How do I make the report display for Zero records with the other defined values?


You might look into the McGyver technique.


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Hi RB,

Yes it is difficult to display a missing record. However almost anything is possible in Focus. Try this:

-* this is to create an example of your file
TABLE FILE CAR
LIST SALES
ON TABLE HOLD
END

DEFINE FILE HOLD
PAYLVL/A20 = IF SALES LT 10000 THEN 'AVERAGE' ELSE
             IF SALES FROM 10001 TO 20000 THEN 'ABOVE AVERAGE' ELSE 
             IF SALES FROM 20001 TO 50000 THEN 'HIGH1' ELSE 
             IF SALES GT 50000 THEN 'HIGH2' ELSE '???';
END

-* this creates the line of missing values 
TABLE FILE HOLD
PRINT LIST SALES
BY PAYLVL ROWS 'AVERAGE' OVER 'ABOVE AVERAGE' OVER 'HIGH1' OVER 'HIGH2'
ON TABLE HOLD AS HOLD1 
ON TABLE SET HOLDMISS ON
END

DEFINE FILE HOLD1
EXPLVL/A20 = DECODE E01('AVERAGE' 'SENR'
'ABOVE AVERAGE' 'MNGR' 
'HIGH1' 'EXTV' 
'HIGH2' 'EXTV' ELSE '???');
END

-* this displays 
TABLE FILE HOLD1
SUM CNT.LIST
SALES
BY EXPLVL
BY E01 AS PAYLVL 
END




Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1961 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
Gold member
posted Hide Post
Thank you Daniel. It worked perfectly!
 
Posts: 76 | Registered: October 28, 2003Reply With QuoteReport This Post
Gold member
posted Hide Post
Now, the users want to see this data in grapical format. They want to see this in a Pyramid representation. Is this doable?
 
Posts: 76 | Registered: October 28, 2003Reply With QuoteReport This Post
Virtuoso
posted Hide Post
RB,

And what is "Pyramid representation"? Do you mean using GRAPHTYPE?


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1961 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
Gold member
posted Hide Post
Yes, the graph type should be pyramid.
On the pyramid, it should show the count of ID based on the PAYLVL and EXPLVL.

I see that there are 3D graphs with Pyramids. But what I need is just one pyramid with all the three values (PAYLVL, EXPLVL,Count of ID#).

Is it possible?
 
Posts: 76 | Registered: October 28, 2003Reply With QuoteReport This Post
Virtuoso
posted Hide Post
RB,

FYI:
1. You cannot have 2 sort levels in GRAPH
2. You cannot have 2 scales in PYRAMID

My suggestion:
1. Concatenate the fields PAYLVL, EXPLVL
2. Scale the PAYMENT field (in my example SALES) to the count otherwise the count pyramid will be squashed.

SET ASNAMES=ON
TABLE FILE CAR
LIST SALES
ON TABLE HOLD
END

DEFINE FILE HOLD
PAYLVL/A20 = IF SALES LT 10000 THEN 'AVERAGE' ELSE
             IF SALES FROM 10001 TO 20000 THEN 'ABOVE AVERAGE' ELSE
             IF SALES FROM 20001 TO 50000 THEN 'HIGH1' ELSE
             IF SALES GT 50000 THEN 'HIGH2' ELSE '???';
END

TABLE FILE HOLD
PRINT LIST AS NUM
      SALES
BY PAYLVL ROWS 'AVERAGE' OVER 'ABOVE AVERAGE' OVER 'HIGH1' OVER 'HIGH2'
ON TABLE HOLD AS HOLD1
ON TABLE SET HOLDMISS ON
END

DEFINE FILE HOLD1
EXPLVL/A20 = DECODE E01('AVERAGE' 'SENR'
'ABOVE AVERAGE' 'MNGR'
'HIGH1' 'EXTV'
'HIGH2' 'EXTV' ELSE '???');
LEVELS/A40 = EXPLVL | E01;
END
TABLE FILE HOLD1
SUM CNT.NUM
    SUM.SALES
ON TABLE SAVE
END
-RUN
-READ SAVE &MNUM.A5. &MSALES.A6.
-SET &R=INT((&MSALES / &MNUM) / 10000) * 10000;
-TYPE &MNUM &MSALES &R
-GOTO A

TABLE FILE HOLD1
SUM
     CNT.NUM
COMPUTE SAL&R/D6c= SALES / &R; AS 'SALES,&R.s'
BY EXPLVL
BY E01 AS 'PAYLVL'
ON TABLE NOTOTAL
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
     GRAPHTYPE=DATA,
     COLUMN=N3,
     GRAPHLOOK=BAR,
     GRAPHCOLOR=RED,
$
     GRAPHTYPE=DATA,
     COLUMN=N4,
     GRAPHLOOK=BAR,
     GRAPHCOLOR=BLUE,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='TIMES NEW ROMAN',
     SIZE=10,
     COLOR='BLACK',
     BACKCOLOR='NONE',
     STYLE=NORMAL,
$
TYPE=DATA,
     COLUMN=N3,
     COLOR='RED',
$
TYPE=DATA,
     COLUMN=N4,
     COLOR='BLUE',
$
ENDSTYLE
END
-EXIT
-A
GRAPH FILE HOLD1
SUM      CNT.NUM
COMPUTE SAL&R/D6c= SALES / &R; AS 'SALES,&R.s'
ACROSS LEVELS
ON GRAPH SET LOOKGRAPH 3DPYRAMD
ON GRAPH SET GRAPHEDIT SERVER
ON GRAPH SET BARNUMB OFF
ON GRAPH SET 3D OFF
ON GRAPH SET VZERO ON
ON GRAPH SET GRID OFF
ON GRAPH PCHOLD FORMAT PNG
ON GRAPH SET GRAPHSTYLE *
setMarkerDisplay(true);
setConnectLineMarkers(true);
setConnectScatterMarkers(true);
setO1LabelDisplay(true);
setO1AxisSide(0);
setO1MajorGridDisplay(false);
setO1MinorGridDisplay(false);
setAxisAssignment(0,0);
setSeriesType(0,2);
setAxisAssignment(1,1);
setSeriesType(1,2);
setY1LabelDisplay(true);
setY1AxisSide(0);
setY1MajorGridDisplay(false);
setY1MinorGridDisplay(false);
setTextFormatPreset(getY1Label(),-1);
setTextFormatPattern(getY1Label(),"#.##");
setY2LabelDisplay(true);
setY2AxisSide(1);
setY2MajorGridDisplay(false);
setY2MinorGridDisplay(false);
setTextFormatPreset(getY2Label(),-1);
setTextFormatPattern(getY2Label(),"#.##");
setPieFeelerTextDisplay(1);
setPieLabelDisplay(0);
setTextFormatPreset(getPieSliceLabel(),1);
setRiserBorderMode(1);
setSeriesDefaultTransparentBorderColor(true);
setUseSeriesBorderDefaults(true);
setLegendDisplay(true);
setFontSizeAbsolute(getY1Title(),true);
setFontSizeAbsolute(getY1Label(),true);
setFontSizeAbsolute(getY2Title(),true);
setFontSizeAbsolute(getY2Label(),true);
setFontSizeAbsolute(getO1Title(),true);
setPlace(true);
ENDSTYLE
ON GRAPH SET STYLE *
     PAGESIZE='Letter',
     LEFTMARGIN=0.250000,
     RIGHTMARGIN=0.250000,
     TOPMARGIN=0.250000,
     BOTTOMMARGIN=0.250000,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='TIMES NEW ROMAN',
     SIZE=10,
     BACKCOLOR='NONE',
     STYLE=NORMAL,
$
TYPE=DATA,
     ACROSSCOLUMN=N1,
     COLOR='RED',
$
TYPE=DATA,
     ACROSSCOLUMN=N2,
     COLOR='BLUE',
$
ENDSTYLE
END



Good Luck!


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1961 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Danny

I was reading your graphing solution, it looks fine (I can not try it since I'm at home) but I have two questions

1) here you say
-GOTO A

what is what comes after that for?

...TABLE FILE HOLD1 etc

and then
2)
COMPUTE SAL&R/D6c= SALES / &R; AS 'SALES,&R.s'

what does the little .s do?

Never to old to learn Frank...




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Frank,

1. In the fex there are 2 outputs, one a report and one a graph. Since you can't see both unless you insert them in some HTML and i wanted RB to see the graph, I skip over the report part directly to the graph part with -GOTO A.

2. The values of SALES are much larger that the count. So, in order to see 2 pyramids, I scale down the SALES by dividing by &R. Say that &R is 1000, then I wanted the title to be 'SALES,1000s'. The '.s' separates the 's' from the variable &R.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1961 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     display records even for count of 0

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