Hey all,

So, a little while ago, I made a report that displayed several summed metrics for a specific cashier across 4 weeks. When you clicked on a metric for a cashier record, a 4 week trending vertical bar graph would pop up with the 4 bars that represented each week's values for the metric clicked on for that specific cashier across those 4 weeks. Have I lost anyone yet? Hopefully not. Let me know. haha

I currently am looking into adding multiple drills (or even a single drill) to each of those bars that represent 1 week's value for a given/passed metric. Is there anyone here that can show/tell me how this is done? I've browsed the online docs, but they really don't give me much if anything about adding different drills for each bar for 1 metric. To be more specific, let's say bar 1 represents scans/minute for a cashier for week ending 09/26/2015, bar 2 represents scans/minute for week ending 10/03/2015, and so on for all 4 bars/4 weeks. That's what we've got. Now let's say we want a drill tied to ONLY bar 1 that takes bar 1's value(s) and passes them to one or more drills specific to this bar. Bar 2 or bar 3 or bar 4 would have different drills specific to their values. Catching my drift now if you haven't already? I know you can set drills to columns within your request, but if I do that the drill shows up for all 4 bars. How do I ID each bar?? Is there somewhere in the docs that explains this that I missed?

My graph code:

-* Sets passed week ending value from report to a new variable for use in next line, etc.
-DEFAULTH &WEEKENDING = '&WE.EVAL';

-* Gets and sets week ending date for 3 weeks previous to passed value.
-SET &THREEWKSPREV = AYMD(&WEEKENDING.QUOTEDSTRING, -21, YYMD);

-* Simple graph request for one metric void amount/customer for passed cashier, store, and week ending date.
GRAPH FILE srprod
SUM
	COMPUTE VOIDAMT_PER_CUST/P11.2M = ( SRPDVOIDAMT / SRPDCUSTCNT ); AS 'Void Amt,per Cstmr'
BY SRPROD.DIMDATE.WKEND
WHERE SRPROD.SRPROD.SRPDCASHIERNAME EQ '&CASHIER'
WHERE SRPROD.DIMSTORE.DSACCTNO EQ '&STORENUM'
WHERE (SRPROD.DIMDATE.WEEKENDING GE &THREEWKSPREV. (|FORMAT=YY|M|D).QUOTEDSTRING);
WHERE SRPROD.DIMDATE.WEEKENDING LE '&WE'

Any and all help with this is much appreciated. Thanks!

This message has been edited. Last edited by: CoolGuy,

UPDATE:

So, if I do it this way it works but then I have to hard-code the value of N1 as a condition to get it to work:

DEFMACRO=Condition_1, MACTYPE=RULE, WHEN=N1 EQ '09/26/2015', $

TYPE=DATA, 
COLUMN=N2, 
MACRO=Condition_1, 
TARGET='_blank', 
DRILLMENUITEM='Google', URL=http://www.google.com?(param1=SRPROD.SRPROD.SRPDVOIDAMT param2=SRPROD.DIMDATE.WKEND), 
DRILLMENUITEM='Report', FOCEXEC=IBFS:/WFC/Repository/appFolder/Reports/Cashier_Drilldown.fex(WEEKENDING=N1),
$

I need to be able to write a condition that states something like WHEN=N1 EQ DATAPOINT1 or BAR1 or something along those lines...

Any takers?

Look at this option CoolGuy

-* File : z1.fex
-***************
TABLE FILE CAR
SUM SEATS
BY TOTAL COMPUTE BIDON/D1=DECODE COUNTRY (ENGLAND 1 ITALY 2 JAPAN 3 FRANCE 4 ELSE 5);
BY COUNTRY
ON TABLE HOLD AS TMP FORMAT FOCUS
END
-RUN

GRAPH FILE TMP
SUM SEATS
BY BIDON NOPRINT
BY COUNTRY
ON GRAPH PCHOLD FORMAT PNG
ON GRAPH SET HTMLENCODE ON
ON GRAPH SET GRAPHDEFAULT OFF
ON GRAPH SET VZERO OFF
ON GRAPH SET HAXIS 770
ON GRAPH SET VAXIS 405
ON GRAPH SET UNITS PIXELS
ON GRAPH SET LOOKGRAPH VBAR
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);
setDepthRadius(5);
setTransparentBorderColor(getChartBackground(),true);
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);
setPlace(true);
ENDSTYLE
ON GRAPH SET STYLE *
DEFMACRO=COL1,
    MACTYPE=RULE,
    WHEN=BIDON EQ 1,
$
DEFMACRO=COL3,
    MACTYPE=RULE,
    WHEN=BIDON EQ 3,
$
TYPE=DATA,
     MACRO=COL1,
     COLUMN=SEATS,
     FOCEXEC=app/z2(CNTRY=COUNTRY), TARGET='_blank',
$
TYPE=DATA,
     MACRO=COL3,
     COLUMN=SEATS,
     FOCEXEC=app/z2(CNTRY=COUNTRY), TARGET='_blank',
$
ENDSTYLE
END

-* File z2.fex
-*************
TABLE FILE CAR
PRINT *
WHERE COUNTRY EQ '&CNTRY';
END

MartinY,

Thanks for the idea of using the values of the X-Axis dimension in a DECODE for easier use in the macro conditionals. I tried doing what you suggested, but I get the following error:

0 NUMBER OF RECORDS IN GRAPH=       35  PLOT POINTS=      4
 (FOC32466) MERGE DIMENSIONS EXCEED THE NUMBER OF SORT FIELDS

I commented that COMPUTE line out for now, and tried the following:

Since the values for the X-Axis BY field will always differ due to user input of &WE, I thought I could set up macro conditions that test for equality with the 4 different possible week ending values that will be in the graph by storing them in their own variables and then testing against them. For example, I would create the other possible graph X-Axis values with some -SETs:

-* Catch the user selected value for week end date:
-DEFAULTH &WEEKENDING = '&WE.EVAL';

-* Assign the correct value for the week end date 1 week previous to the user selected one:
-SET &ONEWEEKPREV = AYMD(&WEEKENDING.QUOTEDSTRING, -7, YYMD);

-* Assign the correct value for the week end date 2 weeks previous to the user selected one:
-SET &TWOWKSPREV = AYMD(&WEEKENDING.QUOTEDSTRING, -14, YYMD);

-* Assign the correct value for the week end date 3 weeks previous to the user selected one:
-SET &THREEWKSPREV = AYMD(&WEEKENDING.QUOTEDSTRING, -21, YYMD);

-* Then in the macro conditions below, use these newly created values to ensure drills only populate if the week end date matches:
DEFMACRO=SAMEWEEKRULE,
     MACTYPE=RULE,
     WHEN=WKEND EQ '&WEEKENDING',
$
DEFMACRO=ONEWEEKRULE,
     MACTYPE=RULE,
     WHEN=WKEND EQ '&ONEWKPREV',
$
DEFMACRO=TWOWEEKRULE,
     MACTYPE=RULE,
     WHEN=WKEND EQ '&TWOWKSPREV',
$
DEFMACRO=THREEWEEKRULE,
     MACTYPE=RULE,
     WHEN=WKEND EQ '&THREEWKSPREV',
$

-* I then try to apply the above macros to the drill-downs, but it doesn't work when I add the macro rule like so:
TYPE=DATA,
     COLUMN=N2,
     MACRO=TWOWEEKRULE,
     TARGET= '_blank',
     DRILLMENUITEM='Cashier LP drill',
     FOCEXEC=IBFS:/WFC/Repository/Retail/Cashier_Productivity/Reports/Cashier_Drilldown.fex(SRPDCASHIERNAME='Sally' DSACCTNO='10980' WEEKENDING='20151017'),
$

-* If I comment out the MACRO=TWOWEEKRULE, line, the graph renders and the drill is usable on all 4 bars present.  If I don't have it commented out, the 
-* graph renders, but no drills are present.

Here's my full code for the graph procedure (Can you see where I might have gone wrong?):

-* Test graph for Void Amt per Customer Counts by Cashier and Week End Date - 4 week trend.

-SET &ECHO = 'ALL';

-DEFAULTH &CASHIER = 'Sally';
-DEFAULTH &STORENUM = '10980';
-DEFAULTH &WE = '20151017';

-DEFAULTH &WEEKENDING = '&WE.EVAL';

-SET &ONEWKPREV = AYMD(&WEEKENDING.QUOTEDSTRING, -7, YYMD);
-SET &TWOWKSPREV = AYMD(&WEEKENDING.QUOTEDSTRING, -14, YYMD);
-SET &THREEWKSPREV = AYMD(&WEEKENDING.QUOTEDSTRING, -21, YYMD);

-*IA_GRAPH_BEGIN
-*Do not delete or modify the comments below
-* I took this jumbled code out for easier readability.
-*Do not delete or modify the comments above
ENGINE INT CACHE SET ON
-DEFAULTH &WF_STYLE_UNITS='PIXELS';
-DEFAULTH &WF_STYLE_HEIGHT='405.0';
-DEFAULTH &WF_STYLE_WIDTH='770.0';
-DEFAULTH &WF_TITLE='WebFOCUS Report';

GRAPH FILE srprod
SUM
	COMPUTE VOIDAMT_PER_CUST/P11.2M = ( SRPDVOIDAMT / SRPDCUSTCNT ); AS 'Void Amt,per Cstmr'
-*BY TOTAL COMPUTE COND/D1=DECODE WKEND ('&WEEKENDING' 0 '&ONEWKPREV' 1 '&TWOWKSPREV' 2 '&THREEWKSPREV' 3 ELSE 4); NOPRINT
BY SRPROD.DIMDATE.WKEND
WHERE SRPROD.SRPROD.SRPDCASHIERNAME EQ '&CASHIER'
WHERE SRPROD.DIMSTORE.DSACCTNO EQ '&STORENUM'
WHERE (SRPROD.DIMDATE.WEEKENDING GE &THREEWKSPREV. (|FORMAT=YY|M|D).QUOTEDSTRING);
WHERE SRPROD.DIMDATE.WEEKENDING LE '&WE'
HEADING
"Void Amt per Customer for &CASHIER - Weekly Trends"
ON GRAPH PCHOLD FORMAT JSCHART
ON GRAPH SET VZERO OFF
ON GRAPH SET HTMLENCODE ON
ON GRAPH SET GRAPHDEFAULT OFF
ON GRAPH SET GRWIDTH 1
ON GRAPH SET EMBEDHEADING ON
ON GRAPH SET UNITS &WF_STYLE_UNITS
ON GRAPH SET HAXIS &WF_STYLE_WIDTH
ON GRAPH SET VAXIS &WF_STYLE_HEIGHT
ON GRAPH SET GRMERGE ADVANCED
ON GRAPH SET GRMULTIGRAPH 0
ON GRAPH SET GRLEGEND 0
ON GRAPH SET GRXAXIS 1
ON GRAPH SET LOOKGRAPH VBAR
ON GRAPH SET AUTOFIT ON
ON GRAPH SET STYLE *
*GRAPH_SCRIPT
setPieDepth(0);
setPieTilt(0);
setDepthRadius(0);
setCurveFitEquationDisplay(false);
setPlace(true);
*END
INCLUDE=IBFS:/FILE/IBI_HTML_DIR/javaassist/intl/EN/combine_templates/ENFlat.sty,$
TYPE=REPORT, TITLETEXT=&WF_TITLE.QUOTEDSTRING, $
TYPE=HEADING, JUSTIFY=CENTER, FONT='Trebuchet MS', SIZE=12, COLOR=RGB(66 70 73), STYLE=BOLD, $

-* Test to get conditional ability to drill for each bar individually in graph:

DEFMACRO=TWOWEEKRULE,
     MACTYPE=RULE,
     WHEN=WKEND EQ '&TWOWKSPREV',
$
TYPE=DATA,
     COLUMN=N2,
     MACRO=TWOWEEKRULE,
     TARGET= '_blank',
     DRILLMENUITEM='Cashier LP drill',
     FOCEXEC=IBFS:/WFC/Repository/Retail/Cashier_Productivity/Reports/Cashier_LP_Drilldown.fex(SRPDCASHIERNAME='&CASHIER' DSACCTNO='&STORENUM' WEEKENDING='&WEEKENDING'),
$

*GRAPH_SCRIPT
setReportParsingErrors(false);
setSelectionEnableMove(false);
setTextString(getY1Title(),"Void Amt per Cstmr");
setDisplay(getY1Title(),true);
setDisplay(getO1Title(),false);
setDisplay(getLegendArea(),false);
setFillColor(getSeries(0),new Color(73,139,182));
*END
ENDSTYLE
END
-RUN

-*IA_GRAPH_FINISH

First, to be able to use your BY TOTAL COMPUTE line, you need (GRXAXIS is now 2). This will fix your error FOC32466:

ON GRAPH SET GRXAXIS 2

instead of

ON GRAPH SET GRXAXIS 1

Also, what I can see is that with your BY TOTAL COMPUTE active:

TYPE=DATA,
     COLUMN=N2,
     MACRO=TWOWEEKRULE,
...

As to becomes (column is changing, it's now the third one)

TYPE=DATA,
     COLUMN=N3,
     MACRO=TWOWEEKRULE,
...

I will finally suggest you to try comparing with the value of the variable instead of the variable. Because it sounds to me that your condition definition may not be properly evaluated

DEFMACRO=SAMEWEEKRULE,
     MACTYPE=RULE,
     WHEN=WKEND EQ '&WEEKENDING.EVAL',
$

This message has been edited. Last edited by: MartinY,

MartinY,

Thanks for your help and tips! So, what exactly is GRXAXIS doing? I'm not understanding.

That does make sense to switch the column from N2 to N3. I didn't think to do that. Appreciate you pointing that out for me.

I did finally get the drill-down working for just one bar on the graph after realizing I was incorrectly comparing 2 differently formatted dates by accident. I fixed them to match and now have a conditional drill that only appears if the values for WKEND equal the value of the given week ending variables I've created.

Again, really appreciate your help with figuring this out for me.

Take care!

Hi CoolGuy,

Here what I found :
GRMULTIGRAPH, which specifies how many sort fields to use to create multiple graphs.
GRLEGEND, which specifies how many sort fields to place on the graph legend.
GRXAXIS, which specifies how many sort fields to display on the X-axis (even if some are NOPRINT). GRXAXIS must be at least 1 in order to plot the graph. A value greater than one creates nested X-axes.

Note: The sum of the sort fields used by GRMULTIGRAPH, GRLEGEND, and GRXAXIS must equal the number of sort fields in the graph request.

And glad that my suggestions/tips helps you have your report work as you whish.

MartinY,

That would do it! Thanks for the explanations on different graph SETs and what they actually are doing. I had 2 BY fields, so I needed to set the GRAXIS to 2. Good to know! Sort of new to all this graphing. I've been focused on reports and custom HTML widgets and haven't spent as much time in the graphing space as of yet. Thanks again!