Not sure why I'm having this problem. I have a hold file that I would like to graph to merely calculate the percent of total orders that are web based, with the 2 series' being current year and previous year. It won't even let me open in Advanced Graph. Is there something in this code that you see is causing a problem?

-SET &ECHO = ALL;

SET NODATA = 0
SET HOLDLIST=PRINTONLY
SET ASNAMES=ON
SET SHOWBLANKS = ON
SET TRACEOFF = ALL
SET TRACEON = SQLAGGR//CLIENT
SET TRACEON = SQLAGGR//CLIENT

SET TRACEUSER = ON
-RUN


DEFINE FILE SALES_CURR_PREV_FY_US
TODAY/YYMD=&YYMD;
END_DAY/YYMD=DATEADD(TODAY, 'D', -1);
PREV_TODAY/YYMD=DATEADD(END_DAY, 'Y', -1);
END_YEAR/YY=END_DAY;
END_MONTH/M=END_DAY;
BEGIN_CURR_FISC_DATE/YYMD=IF END_MONTH = 11 OR 12 THEN DATEADD(END_DAY, 'Y', 1) ELSE END_DAY;
BEGIN_CURR_CLDR_DATE/YYMD=DATEMOV(BEGIN_CURR_FISC_DATE, 'BOY');
BEGIN_CURR_FY/YYMD=DATEADD(BEGIN_CURR_CLDR_DATE, 'M', -2);
CURR_FY/YY=IF END_MONTH = 11 OR 12 THEN DATEADD(END_DAY, 'Y', 1) ELSE END_YEAR;
PREV_FY/YY=DATEADD(END_DAY, 'Y', -1);
BEGIN_PREV_FY/YYMD=DATEADD(BEGIN_CURR_FY, 'Y', -1);
ONE/I1=1;
WOE/A10=IF SALES_CURR_PREV_FY_US.SALES_CURR_PREV_FY_US.REMOTE_DATA EQ 'IX' THEN 'WOE' ELSE 'NON-WOE';
END
TABLE FILE SALES_CURR_PREV_FY_US

SUM
     'CNT.DST.SALES_CURR_PREV_FY_US.SALES_CURR_PREV_FY_US.ORDER_NUM' AS 'CNT_ORDERS'
BY 'SALES_CURR_PREV_FY_US.SALES_CURR_PREV_FY_US.PRICING_BRANCH' AS 'BRANCH'
BY 'SALES_CURR_PREV_FY_US.SALES_CURR_PREV_FY_US.GL_DT_FISCAL_YEAR' AS 'FY'
BY 'SALES_CURR_PREV_FY_US.SALES_CURR_PREV_FY_US.WOE'
HEADING
""
FOOTING
""
WHERE PRICING_BRANCH NE '31' OR 'CR' OR '1' OR '36';
WHERE (( SALES_CURR_PREV_FY_US.SALES_CURR_PREV_FY_US.GL_DATE GE BEGIN_CURR_FY ) AND ( SALES_CURR_PREV_FY_US.SALES_CURR_PREV_FY_US.GL_DATE LE END_DAY )) OR (( SALES_CURR_PREV_FY_US.SALES_CURR_PREV_FY_US.GL_DATE GE BEGIN_PREV_FY ) AND ( SALES_CURR_PREV_FY_US.SALES_CURR_PREV_FY_US.GL_DATE LE PREV_TODAY));
ON TABLE SET PAGE-NUM OFF
ON TABLE SET BYDISPLAY ON
ON TABLE NOTOTAL
-*ON TABLE PCHOLD FORMAT HTML
ON TABLE HOLD AS WOEHOLD FORMAT ALPHA
ON TABLE SET HTMLCSS ON
END


DEFINE FILE WOEHOLD
ORDERS/D10.2=CNT_ORDERS;
END
TABLE FILE WOEHOLD
SUM 
     'RPCT.WOEHOLD.WOEHOLD.ORDERS' 
BY 'WOEHOLD.WOEHOLD.BRANCH'
BY 'WOEHOLD.WOEHOLD.FY'
ACROSS 'WOEHOLD.WOEHOLD.WOE'
ON TABLE SET PAGE-NUM OFF
ON TABLE SET BYDISPLAY ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
-*ON TABLE HOLD AS WOEHOLD2 FORMAT ALPHA
ON TABLE SET HTMLCSS ON
END
  

Thank you!

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

Read up on APP HOLD

Same happens with me. Adv Graph Assistant somehow doesn't like HOLD files. Graph assistant opens OK though..!!?!

How bout that..it does open in Graph assist! But, it doesn't show me my SUM or ACROSS fields to use as values.

Tom,

I did do a search in the Technical Doc on APP HOLD but it's not very clear to me. I've also read through the "Creating Reports using Graphical Tools...." book to see if there is something I am consistently missing. If you can guide me a little more on what I should be looking for, I'm more than happy to go find it.

Thank you!

js,

I'll make 4 assumptions:

1. You are using the GUI tool
2. the fille you want to Graph is WOEHOLD
3. The graph will be added above/next to/under the HTML report.
4. This will be for multiple users


1. After you have completed the GUI part, EDIT the fex in text, and, at the top of the fex, add:

APP HOLD BASEAPP
-RUN

APP HOLD saves the file to the Reporting Server, with the Master.

after this code:

ON TABLE HOLD AS WOEHOLD FORMAT ALPHA
ON TABLE SET HTMLCSS ON
END
-EXIT

put a -EXIT, as above and hit the RUN button.

Now WOEHOLD wiil be availble to the Advance Graph Engine with the Master in Baseapp.

Start Graphing with a new fex. When done, save and add the Graph code under your HTML report and remove APP HOLD and -EXIT.

hth

Hi Tom,

1 - Yes
2 - I'm actually trying to graph WOEHOLD2 because it contains the percent of total calculation that I need to graph.
3 - The graph will replace the HTML report - I was using the HTML format for testing the output, then trying to graph that.
4 - Yes

I inserted the code per your suggestion and ran it. So I'm trying to create the graph off of the WOEHOLD file and it does open for me now, but I get "no data to plot", plot points = 0 when I try to run this.

DEFINE FILE WOEHOLD
ORDERS/D10.2=CNT_ORDERS;
END
GRAPH FILE WOEHOLD
-* Created by Advanced Graph Assistant
SUM 'RPCT.WOEHOLD.WOEHOLD.ORDERS'
BY WOEHOLD.WOEHOLD.BRANCH
BY WOEHOLD.WOEHOLD.FY
WHERE WOEHOLD.WOEHOLD.WOE EQ 'WOE';
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 VBAR
ON GRAPH SET GRMERGE ADVANCED
ON GRAPH SET GRMULTIGRAPH 0
ON GRAPH SET GRLEGEND 0
ON GRAPH SET GRXAXIS 3
ON GRAPH SET GRAPHSTYLE *
.
.
.
  

See changes below. Use the Graph tool to display as a % of orders instead of the Count.

Look at the Master for WOEHOLD, or, do
?FF WOHOLD
-EXIT

This gives you the Fields and Formats...

quote:

DEFINE FILE WOEHOLD
ORDERS/D10.2=CNT_ORDERS;
END
GRAPH FILE WOEHOLD
-* Created by Advanced Graph Assistant
SUM ORDERS
BY BRANCH
BY FY
WHERE WOE EQ 'WOE';
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 VBAR
ON GRAPH SET GRMERGE ADVANCED
ON GRAPH SET GRMULTIGRAPH 0
ON GRAPH SET GRLEGEND 0
ON GRAPH SET GRXAXIS 3
ON GRAPH SET GRAPHSTYLE *

Hmm - same results. So I just created a small fex to look at the data that is in WOEHOLD and it gives me zilch - 0 lines. Even though when I ran the APP HOLD and -EXIT, it told me it created about 450000+ rows. So, it seems I'm missing something else.

I appreciate your patience with me.

Do this at the top of any fex:

? PATH
-EXIT

And make sure BASEAPP is in your path...

If not, do this:

APP PREPENDPATH BASEAPP
-RUN

Thank you, Tom. BASEAPP is in my path.

I was able to get it working by creating a 2nd hold file and following your suggestion to create a new fex and format my graph in there. When I run it all in 1 .fex, I get results, but not when I create the graph off the WOEHOLD or WOEHOLD2 hold files from baseapp. Both give me the 0 data points error.

Thank you!

Well, for WOEHOLD2 you are HOLDing an ACROSS column. Do the ?FF and see what you get. Using ACROSS with HOLD gives you new column names.

If going to Summit, sign up for an Internals class, if offered; very informative...

If not offered, let IBI know; it should be an annual class at Summit...

Tom

This message has been edited. Last edited by: Tom Flynn,

I will definitely have to do that if one is offered. I don't recall seeing it on the agenda but I'll check. Thank you for the help!

I had the same issue recently. Solved by creating a procedure that calculates what you need, no across, that messes up the hold file for the graph.

Just some FYI, I learned recently that if you are using amper variables in your report that you want to build a graph off of, you have to have DM -DEFAULT(s) for each variable for Advance Graph to work. Works like a charm then. And if you want PDF output then you have to have the graph in a separate fex then the report. I think this sucks! Unless, someone has a solution to this. I would be very interested in it.