Focal Point
Multiple graphs on BY field?

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/1191008331

October 26, 2004, 07:33 PM
<kvn>
Multiple graphs on BY field?
Is there a way to do this?

I have a hold report with 2 BY fields and a PRINT. I want the first BY field (ACCOUNT) to be a graph and the 2nd BY field (DATE) and PRINT (PCT) axises on that graph.

TABLE FILE TREND
SUM PCT
BY ACCOUNT
BY DATE
ON TABLE HOLD AS ACCTHOLD FORMAT FOCUS
END
October 26, 2004, 10:07 PM
<kvn>
I actually need to make 2 different graphs side by side with the same 2 graphs under for the BY field.

Example

ACCOUNT 1
Graph 1 | Graph 2
--------------------
ACCOUNT 2
Graph 1 | Graph 2
--------------------
ACCOUNT 3
Graph 1 | Graph 2
October 27, 2004, 12:47 AM
Piipster
Have you tried GRAPH FILE TREND.
Remove the HOLD FORMAT FOCUS.
October 27, 2004, 12:48 AM
Piipster
BTW. Using 2 BY fields should give you multiple graphs.
October 27, 2004, 11:54 AM
<kvn>
I know changing the TABLE to graph will work and using 2 BY fields will give me multiple graphs. I'm using that hold file to graph off of.

I'll try to explain myself better. I just realized I didn't make much sense in my post.

Graph 1
TABLE FILE TREND1
SUM PCTAVG
BY DATE
ON TABLE HOLD AS ACCT1HOLD FORMAT FOCUS
END

Graph 2
TABLE FILE TREND2
SUM PCTTOTAL
BY DATE
ON TABLE HOLD AS ACCT2HOLD FORMAT FOCUS
END

Report
ACCOUNT 1
Graph 1 | Graph 2
--------------------
ACCOUNT 2
Graph 1 | Graph 2
--------------------
ACCOUNT 3
Graph 1 | Graph 2
--------------------
ETC...


So what I need to do is create a report with 2 side-by-side graphs, created from 2 different HOLD files, for each ACCOUNT.
October 27, 2004, 12:35 PM
<Grzegorz>
Below is the example which might be helpful for you. The COUNTRY is used as ACCOUNT, and the CAR as DATE.
It is not the most elegant solution, but it is working at least for the HTML output.

-* First, dump all countries to the temporary table:
TABLE FILE CAR
BY COUNTRY
ON TABLE HOLD AS CTRYHLD FORMAT ALPHA
END
-RUN
-*--------------------------------------------------
-* Read the list of dumped countries:
-SET &CTRYLIST = '';
-SET &CTRYLIST0 = &LINES;
-REPEAT :LISTLOOP FOR &CNT FROM 1 TO &CTRYLIST0;
-READ CTRYHLD &TMPVAR.A10 NOCLOSE
-SET &CTRYLIST.&CNT = &TMPVAR;
-:LISTLOOP
-SET &CTRYLIST = &CTRYLIST1;
-*-------------------------------------------------
-* For each country within the list, create two graphs
-* one for the total, and one for the average.
-* Hold the created graphs as HTML tables.
-REPEAT :HOLDLOOP FOR &CNT FROM 1 TO &CTRYLIST0;
GRAPH FILE CAR
SUM SALES
BY CAR
WHERE COUNTRY EQ '&CTRYLIST.&CNT'
ON TABLE HOLD AS TOTHLD&CNT FORMAT HTMTABLE
END
-RUN
GRAPH FILE CAR
SUM AVE.SALES
BY CAR
WHERE COUNTRY EQ '&CTRYLIST.&CNT'
ON TABLE HOLD AS AVEHLD&CNT FORMAT HTMTABLE
END
-RUN
-:HOLDLOOP
-*-------------------------------------------------
-* Graphs will be displayed on the HTML page:
-HTMLFORM BEGIN

Multiple Graphs


-* For each country display country name,
-* and two graphs for this country:
-REPEAT :HTMLOOP FOR &CNT FROM 1 TO &CTRYLIST0;







-:HTMLOOP
&CTRYLIST.&CNT|
!IBI.FIL.TOTHLD&CNT|;!IBI.FIL.AVEHLD&CNT|;



-HTMLFORM END
Regards
Grzegorz

This message has been edited. Last edited by: <Mabel>,
October 27, 2004, 01:22 PM
<kvn>
Grzegorz your the BEST! Big Grin
October 27, 2004, 04:43 PM
<kvn>
Is there a limit to how many variables you can have in a fex? My graphs are stopping at '!IBI.FIL.TOTHLD100;' using Grzegorz solution.
October 28, 2004, 02:03 PM
<Grzegorz>
I suppose that it is rather a limitation on the size of the generated FOCUS code.

The solution I proposed in previous post is not suitable for the "large number of COUNTRYies in the CAR table".
If you have a large number of ACCOUNTs in the data source, you can try to split the request in order to make it smaller (if possible). For example:


Such a kind of report is also a good candidate for scheduling (although simply schedule it, does not solve the problem).

Regards
Grzegorz