Hello everyone!

I’m start to dive into graphs and I’m running into an issue with drill down. I have a have gauge graph I’m trying to drill down on and I don’t know how to reference the drilldown. In my code I have:
$
TYPE=DATA
,FOCEXEC=graphtes(GEOGRAPHYNAME=GeographyName StartDate='&StartDate' EndDate='&EndDate' GeographyTypeCode='&GeographyTypeCode'),
COLUMN=GeographyName(*),
$

When I run the report, I cannot drilldown anywhere. I’m not sure how to reference the column to drill down. I know that WebFOCUS will assign aliases to columns (i.e. N1, N2) but I have no idea how to figure out which column is which. Can anybody help me out here? I rest of my code is below


-SET &ECHO=ALL;
-SET ASNAMES = ON;
-*-SET &STACK=OFF;
-DEFAULT &StartDate = '01/01/2006'
-DEFAULT &EndDate = '06/01/2006'
-DEFAULT &GeographyTypeCode ='DLVL1'
-DEFAULT &GEOGRAPHYNAME = 'Contracts and Duplicates'

ENGINE SQLMSS SET DEFAULT_CONNECTION BIW_View
SQL SQLMSS EX WebFOCUSPOC2 '&GEOGRAPHYNAME','&StartDate','&EndDate','&GeographyTypeCode';
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS SQLOUT
END
GRAPH FILE SQLOUT
SUM BaseAmt
BY GeographyName
HEADING
"This my graph test"
ON GRAPH SET LOOKGRAPH GAUGE1
ON GRAPH SET GRAPHEDIT SERVER
ON GRAPH SET BARNUMB OFF
ON GRAPH SET 3D ON
ON GRAPH SET VZERO ON
ON GRAPH SET GRID ON
ON GRAPH SET VAXIS 300
ON GRAPH SET HAXIS 300
ON GRAPH PCHOLD FORMAT PNG
ON GRAPH SET GRAPHSTYLE *
setMarkerDisplay(true);
setConnectLineMarkers(false);
setConnectScatterMarkers(false);
setO1LabelDisplay(true);
setO1AxisSide(0);
setO1MajorGridDisplay(true);
setO1MajorGridStyle(0);
setO1MinorGridDisplay(false);
setAxisAssignment(0,0);
setY1LabelDisplay(true);
setY1AxisSide(0);
setY1MajorGridDisplay(true);
setY1MajorGridStyle(0);
setY1MinorGridDisplay(false);
setTextFormatPreset(getY1Label(),-1);
setTextFormatPattern(getY1Label(),"#.##");
setPieFeelerTextDisplay(2);
setPieLabelDisplay(0);
setTextFormatPreset(getPieSliceLabel(),6);
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,
GRAPHCOLOR='WHITE',
$
TYPE=REPORT,
GRID=OFF,
FONT='TIMES NEW ROMAN',
SIZE=8,
BACKCOLOR='NONE',
STYLE=NORMAL,
$
TYPE=HEADING,
LINE=1,
JUSTIFY=CENTER,
$
TYPE=HEADING,
LINE=1,
OBJECT=TEXT,
ITEM=1,
COLOR='BLACK',
$
TYPE=FOOTING,
LINE=1,
OBJECT=TEXT,
ITEM=1,
SIZE=10,
$
TYPE=DATA
,FOCEXEC=graphtes(GEOGRAPHYNAME=GeographyName StartDate='&StartDate' EndDate='&EndDate' GeographyTypeCode='&GeographyTypeCode'),
COLUMN=GeographyName(*),
$

ENDSTYLE
END

I did a test and found that when the graph type is a guage, the drilldown did not work. Here's an example of a working drill-down. I use an ACROSS instead of a BY and use ACROSSCOLUMN=N1 (specifying the real column name COUNTRY did not work).

-SET &ECHO=ALL;

-DEFAULT &COUNTRY='';

-SET &SEL_COUNTRY = IF &COUNTRY EQ '' THEN '' ELSE 'WHERE COUNTRY EQ ''&COUNTRY.EVAL''';

GRAPH FILE CAR
SUM
SALES
ACROSS COUNTRY
&SEL_COUNTRY
HEADING
"This my graph test"
-*ON GRAPH SET LOOKGRAPH GAUGE1
ON GRAPH SET GRAPHEDIT SERVER
ON GRAPH SET BARNUMB OFF
ON GRAPH SET 3D ON
ON GRAPH SET VZERO ON
ON GRAPH SET GRID ON
ON GRAPH SET VAXIS 600
ON GRAPH SET HAXIS 600
ON GRAPH PCHOLD FORMAT PNG
ON GRAPH SET GRAPHSTYLE *
setMarkerDisplay(true);
setConnectLineMarkers(false);
setConnectScatterMarkers(false);
setO1LabelDisplay(true);
setO1AxisSide(0);
setO1MajorGridDisplay(true);
setO1MajorGridStyle(0);
setO1MinorGridDisplay(false);
setAxisAssignment(0,0);
setY1LabelDisplay(true);
setY1AxisSide(0);
setY1MajorGridDisplay(true);
setY1MajorGridStyle(0);
setY1MinorGridDisplay(false);
setTextFormatPreset(getY1Label(),-1);
setTextFormatPattern(getY1Label(),"#.##");
setPieFeelerTextDisplay(2);
setPieLabelDisplay(0);
setTextFormatPreset(getPieSliceLabel(),6);
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,
GRAPHCOLOR='WHITE',
$
TYPE=REPORT,
GRID=OFF,
FONT='TIMES NEW ROMAN',
SIZE=8,
BACKCOLOR='NONE',
STYLE=NORMAL,
$
TYPE=HEADING,
LINE=1,
JUSTIFY=CENTER,
$
TYPE=HEADING,
LINE=1,
OBJECT=TEXT,
ITEM=1,
COLOR='BLACK',
$
TYPE=FOOTING,
LINE=1,
OBJECT=TEXT,
ITEM=1,
SIZE=10,
$
TYPE = DATA, ACROSSCOLUMN=N1, TARGET=_BLANK,
FOCEXEC= GRTEST2 ( COUNTRY=COUNTRY ), $
ENDSTYLE
END

Thanks for the response.

I’ve found that you cannot drill down on certain types of graphs. I’ve tried a pie graph and couldn’t do that. I also couldn’t get a horizontal bar graph to work. I’m not sure if it’s something I’m doing wrong or not.


One question…
How do you know to reference the acrosscolumn as ‘N1”? I’ve been trying to figure this out for a while and cannot. I’ve tried to reference a column as ‘Country(*)’ but that doesn’t seem to work for me either. I just want to know so when I’m hand coding a drilldown, I can put the link on the appropriate column.

My example still works if you add "ON GRAPH SET LOOKGRAPH PIE".

I don't actually know when to reference a column by N1. I've been at this game for years, and only after I spend hours trying to solve the problem using column(*), do I realize it may work with N1!

And, I cannot seem to find the definitive piece of documentation regarding N1, P1, etc. I would love to know where this is hiding.

I'll try the pie.... it could have been user error

Ok... do you know any guidelines on referencing a column by N1, P1 etc...? Do you just play around with the code until you find the reference that works? Does it just work on across columns?

Thanks

I sometimes have to play around. I would really appreciate someone telling us where the documentation is.

The documentation is in the "Creating Reports with WebFOCUS Manual". In the Version 7.1 Manual (DN4500695.0505) it is on page 25-9.
http://documentation.informationbuilders.com/masterinde...tifying2.htm#1008779

Francis,

Check out the Creating Reports with WebFOCUS manual page 24-5 for your version. Doc number is DN4500580.0904.

T

Hmmm.... still can't get the drill down to work on a pie. I'll post all of my code below, but I think the relavant parts are:
ON GRAPH SET LOOKGRAPH PIE

TYPE=DATA,
COLUMN=B1,
FOCEXEC=graphtes(GEOGRAPHYNAME=GeographyName StartDate='&StartDate' EndDate='&EndDate' GeographyTypeCode='&GeographyTypeCode'),

Any ideas on what i'm doing wrong?


Entire report:
-SET &ECHO=ALL;
-SET ASNAMES = ON;
-*-SET &STACK=OFF;
-DEFAULT &StartDate = '01/01/2006'
-DEFAULT &EndDate = '06/01/2006'
-DEFAULT &GeographyTypeCode ='DLVL1'
-DEFAULT &GEOGRAPHYNAME = 'Contracts and Duplicates'

ENGINE SQLMSS SET DEFAULT_CONNECTION BIW_View
SQL SQLMSS EX WebFOCUSPOC2 '&GEOGRAPHYNAME','&StartDate','&EndDate','&GeographyTypeCode';
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS SQLOUT
END
GRAPH FILE SQLOUT
SUM BaseAmt
BY GeographyName
HEADING
"This my graph test"
ON GRAPH SET LOOKGRAPH PIE
ON GRAPH SET GRAPHEDIT SERVER
ON GRAPH SET BARNUMB OFF
ON GRAPH SET 3D ON
ON GRAPH SET VZERO ON
ON GRAPH SET GRID ON
ON GRAPH SET VAXIS 300
ON GRAPH SET HAXIS 300
ON GRAPH PCHOLD FORMAT PNG
ON GRAPH SET GRAPHSTYLE *
setMarkerDisplay(true);
setConnectLineMarkers(false);
setConnectScatterMarkers(false);
setO1LabelDisplay(true);
setO1AxisSide(0);
setO1MajorGridDisplay(true);
setO1MajorGridStyle(0);
setO1MinorGridDisplay(false);
setAxisAssignment(0,0);
setY1LabelDisplay(true);
setY1AxisSide(0);
setY1MajorGridDisplay(true);
setY1MajorGridStyle(0);
setY1MinorGridDisplay(false);
setTextFormatPreset(getY1Label(),-1);
setTextFormatPattern(getY1Label(),"#.##");
setPieFeelerTextDisplay(2);
setPieLabelDisplay(0);
setTextFormatPreset(getPieSliceLabel(),6);
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,
GRAPHCOLOR='WHITE',
$
TYPE=REPORT,
GRID=OFF,
FONT='TIMES NEW ROMAN',
SIZE=8,
BACKCOLOR='NONE',
STYLE=NORMAL,
$
TYPE=HEADING,
LINE=1,
JUSTIFY=CENTER,
$
TYPE=HEADING,
LINE=1,
OBJECT=TEXT,
ITEM=1,
COLOR='BLACK',
$
TYPE=FOOTING,
LINE=1,
OBJECT=TEXT,
ITEM=1,
SIZE=10,
$
TYPE=DATA,
COLUMN=B1,
FOCEXEC=graphtes(GEOGRAPHYNAME=GeographyName StartDate='&StartDate' EndDate='&EndDate' GeographyTypeCode='&GeographyTypeCode'),
$

ENDSTYLE
END

Have you tried the CAR file example I provided?

Well I changed the BY column to an 'across' and it works. Not sure why it was drillingdown with a BY feild

I think the column references (at least how it was explained to me) are N numbers are column numbers including NOPRINTS, P numbers are printable columns, and B numbers are by-fields. It's all in Chapter 25 of WebFocus Reporting manual, but I don't see a nice syntax summary like there used to be in the manuals. Across columns can be addressed as ACROSSCOLUMN=N1 or whatever.