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

RB,

Using

BY EXPLVL ROWS 'SENR' OVER 'MNGR' OVER 'EXTV' OVER '???'

should do it for you

T

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...

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?

quote:

How do I make the report display for Zero records with the other defined values?

You might look into the McGyver technique.

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

Thank you Daniel. It worked perfectly!

Now, the users want to see this data in grapical format. They want to see this in a Pyramid representation. Is this doable?

RB,

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

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?

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!

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,

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.