We have a horizontal, stacked bar graph which display top 10 customers by volume. Volume is stacked according to different types of loads. In order for the ranks to appear in order, from top to bottom, we had to concatenate the rank to the customer name.

  
DEFINE FILE TOP_CUST2
RANK_NUM/I2 = RANK;
RANK_CUST/A100 = EDIT(RANK_NUM) | '-' | CUST;
END

TABLE FILE TOP_CUST2
PRINT
RANK_CUST
*
ON TABLE HOLD AS TOP_CUST3 FORMAT FOCUS
END

GRAPH FILE TOP_CUST3
SUM TOP_CUST3.LOADS AS 'Loads'
BY HIGHEST '&LEGEND' 
ACROSS LOWEST RANK_CUST 

The problem that we are having is that when we drilldown into the customer, WF is passing the Rank-Customer value and we need the customer ID number.

We have tried adding ACROSS CUST_ID NOPRINT before, but then you can not see the customer name in the graph.

Does anyone have any ideas on how to pass cust_ID to a drilldown procedure while preserving the rank and customername in the graph?

This message has been edited. Last edited by: Joey Sandoval,

Add your CUSTOMER_ID as a NOPRINT field. Then use it in the drilldown passing the column value.

  
-* File car_stack.fex

-DEFAULT &DETAIL = 'N'
-IF &DETAIL EQ 'N' GOTO STRT_PGM;
-? &C
-? &D
-EXIT

-STRT_PGM
SET ASNAMES = ON
SET HOLDLIST = PRINTONLY
 
TABLE FILE CAR
SUM
   DEALER_COST
   RETAIL_COST
   COMPUTE COUNTRY_ID/A10 = EDIT(COUNTRY,'9') | EDIT(SEATS);   
RANKED BY HIGHEST TOTAL DEALER_COST NOPRINT
BY COUNTRY
ON TABLE HOLD AS H1
END
-RUN

GRAPH FILE H1
SUM RETAIL_COST
DEALER_COST
COUNTRY_ID NOPRINT
BY RANK NOPRINT
BY COUNTRY
ON GRAPH PCHOLD FORMAT PNG
ON GRAPH SET GRAPHDEFAULT OFF
ON GRAPH SET VZERO OFF
ON GRAPH SET HTMLENCODE ON
ON GRAPH SET HAXIS 770
ON GRAPH SET VAXIS 405
ON GRAPH SET UNITS PIXELS
ON GRAPH SET LOOKGRAPH VBRSTK1
ON GRAPH SET GRMERGE ADVANCED
ON GRAPH SET GRMULTIGRAPH 0
ON GRAPH SET GRLEGEND 0
ON GRAPH SET GRXAXIS 2
ON GRAPH SET GRAPHSTYLE *
setTemplateFile("/images/tdg/template/IBISouthWestern.txt");
setReportParsingErrors(false);
setSelectionEnableMove(false);
setTransparentBorderColor(getSeries(0),true);
setTransparentBorderColor(getSeries(1),true);
setTransparentBorderColor(getSeries(2),true);
setTransparentBorderColor(getSeries(3),true);
setTransparentBorderColor(getSeries(4),true);
setTransparentBorderColor(getSeries(5),true);
setTransparentBorderColor(getSeries(6),true);
setTransparentBorderColor(getSeries(7),true);
setTransparentBorderColor(getSeries(8),true);
setTransparentBorderColor(getSeries(9),true);
setTransparentBorderColor(getSeries(10),true);
setDepthRadius(5);
setTransparentBorderColor(getChartBackground(),true);
setPlace(true);
ENDSTYLE
ON GRAPH SET STYLE *
TYPE=DATA, COLUMN=N3, FOCEXEC=car_stack(COUNTRY_ID=COUNTRY_ID DETAIL='Y'), $
TYPE=DATA, COLUMN=N4, FOCEXEC=car_stack(COUNTRY_ID=COUNTRY_ID DETAIL='Y'), $
ENDSTYLE
END

Max,
What would the code look like if you were to only graph the sum of dealer cost, and stack the bars by model instead?


For example, given this code, how would I sort by rank while still displaying country names and drill down into each car by country id?

-* File car_stack.fex

-DEFAULT &DETAIL = 'N'
-IF &DETAIL EQ 'N' GOTO STRT_PGM;
-TYPE &COUNTRY_ID
-RUN
-EXIT

-STRT_PGM
SET ASNAMES = ON
SET HOLDLIST = PRINTONLY

TABLE FILE CAR
SUM
   DEALER_COST
   RETAIL_COST
   COMPUTE COUNTRY_ID/A10 = EDIT(COUNTRY,'9') | EDIT(SEATS);
RANKED BY HIGHEST TOTAL DEALER_COST NOPRINT
BY COUNTRY
BY CAR
ON TABLE HOLD AS H1
END
-RUN

GRAPH FILE H1
SUM 
DEALER_COST
COUNTRY_ID NOPRINT
BY CAR
-*ACROSS RANK NOPRINT
ACROSS COUNTRY
ON GRAPH PCHOLD FORMAT PNG
ON GRAPH SET GRAPHDEFAULT OFF
ON GRAPH SET VZERO OFF
ON GRAPH SET HTMLENCODE ON
ON GRAPH SET HAXIS 770
ON GRAPH SET VAXIS 405
ON GRAPH SET UNITS PIXELS
ON GRAPH SET LOOKGRAPH VBRSTK1
ON GRAPH SET GRMERGE ON
ON GRAPH SET GRAPHSTYLE *
setTemplateFile("/images/tdg/template/IBISouthWestern.txt");
setReportParsingErrors(false);
setSelectionEnableMove(false);
setTransparentBorderColor(getSeries(0),true);
setTransparentBorderColor(getSeries(1),true);
setTransparentBorderColor(getSeries(2),true);
setTransparentBorderColor(getSeries(3),true);
setTransparentBorderColor(getSeries(4),true);
setTransparentBorderColor(getSeries(5),true);
setTransparentBorderColor(getSeries(6),true);
setTransparentBorderColor(getSeries(7),true);
setTransparentBorderColor(getSeries(8),true);
setTransparentBorderColor(getSeries(9),true);
setTransparentBorderColor(getSeries(10),true);
setDepthRadius(5);
setTransparentBorderColor(getChartBackground(),true);
setPlace(true);
ENDSTYLE
ON GRAPH SET STYLE *
TYPE=DATA, COLUMN=N3, FOCEXEC=car_stack(COUNTRY_ID=COUNTRY_ID DETAIL='Y'), $
TYPE=DATA, COLUMN=N4, FOCEXEC=car_stack(COUNTRY_ID=COUNTRY_ID DETAIL='Y'), $
ENDSTYLE
END
  

This message has been edited. Last edited by: Joey Sandoval,

You can customize your ACROSS based on rank using the ACROSS SORT COLUMNS 'A' AND 'B' syntax.
Change your drilldown item to an ACROSSCOLUMN because you are drilling on an across column.

  
-DEFAULT &DETAIL = 'N'
-IF &DETAIL EQ 'N' GOTO STRT_PGM;
-TYPE &COUNTRY_ID
-RUN
-EXIT


-STRT_PGM
SET ASNAMES = ON
SET HOLDLIST = PRINTONLY


-* Find the Country Rank. Adding BY CAR messed up the ranks.
TABLE FILE CAR
SUM
   DEALER_COST
RANKED BY HIGHEST TOTAL DEALER_COST NOPRINT
BY COUNTRY
ON TABLE HOLD AS H1
END
-RUN
-SET &COUNTRY_CNT = &LINES;


-* Build the String for the custom Across columns. Syntax is ACROSS COUNTRY 'ENGLAND' AND 'FRANCE' AND 'ITALY'
-SET &COUNTRY_LST = '';
-SET &COUNTRY = '';
-REPEAT LOOP_LST FOR &N FROM 1 TO &COUNTRY_CNT STEP 1
TABLE FILE H1 
PRINT
     COUNTRY
WHERE RANK EQ '&N'
ON TABLE SAVE AS H2
END
-RUN
-READ H2 &COUNTRY.A10.
-RUN
-SET &COUNTRY_LST = IF &N NE &COUNTRY_CNT THEN &COUNTRY_LST | '''' | TRUNCATE(&COUNTRY) | '''' | ' AND ' ELSE &COUNTRY_LST | '''' | TRUNCATE(&COUNTRY) | '''';
-LOOP_LST



DEFINE FILE CAR
COUNTRY_ID/A10 = EDIT(COUNTRY,'9') | '0001';
END

GRAPH FILE CAR
SUM
DEALER_COST
COUNTRY_ID NOPRINT
BY CAR
ACROSS COUNTRY COLUMNS &COUNTRY_LST
ON GRAPH PCHOLD FORMAT PNG
ON GRAPH SET GRAPHDEFAULT OFF
ON GRAPH SET VZERO OFF
ON GRAPH SET HTMLENCODE ON
ON GRAPH SET HAXIS 770
ON GRAPH SET VAXIS 405
ON GRAPH SET UNITS PIXELS
ON GRAPH SET LOOKGRAPH VBRSTK1
ON GRAPH SET GRMERGE ON
ON GRAPH SET GRAPHSTYLE *
setTemplateFile("/images/tdg/template/IBISouthWestern.txt");
setReportParsingErrors(false);
setSelectionEnableMove(false);
setTransparentBorderColor(getSeries(0),true);
setTransparentBorderColor(getSeries(1),true);
setTransparentBorderColor(getSeries(2),true);
setTransparentBorderColor(getSeries(3),true);
setTransparentBorderColor(getSeries(4),true);
setTransparentBorderColor(getSeries(5),true);
setTransparentBorderColor(getSeries(6),true);
setTransparentBorderColor(getSeries(7),true);
setTransparentBorderColor(getSeries(8),true);
setTransparentBorderColor(getSeries(9),true);
setTransparentBorderColor(getSeries(10),true);
setDepthRadius(5);
setTransparentBorderColor(getChartBackground(),true);
setPlace(true);
ENDSTYLE
ON GRAPH SET STYLE *
TYPE=DATA, ACROSSCOLUMN=N1, FOCEXEC=car_stack(COUNTRY_ID=COUNTRY_ID DETAIL='Y'), $
ENDSTYLE
END

Thank you Max. This solution worked. Never would of thought of hard coding column names using a loop!