Focal Point
Combine tables and graphs

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

September 18, 2007, 06:13 AM
Frans
Combine tables and graphs
Hi all,

I'm making a table report and a graph where I make a break on YEAR.

When I exectute the procedure I now first see all the tables and after that all the graphs for each year.

I want a procedure where I see table 2006, graph 2006, table 2005, graph 2005 etc.

I've been thinking about making a loop with DO WHILE ELSE, but I'm not sure how to make it. Anybody got an idea?


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
September 18, 2007, 07:03 AM
FrankDutch
Frans

Is this supposed to be done in PDF or HTML

In 7.6 you can build a great compound report where you get what you want.

In the compound report builder let you include the graph and the table.




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

September 19, 2007, 05:18 AM
Frans
Hi Frank,

This is indeed supposed to be done in HTML and PDF. I have tried to make a compound report of it. Only problem is that I make a new table for each year and a new graph for each year.

So i I only take one year (= 1 table and 1 graph) then it works fine. If I take 3 years, then WF has to make 3 graph files and 3 tables, this makes the report server crash.

I'm now thinking of making a loop for each year.


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
September 19, 2007, 07:47 AM
FrankDutch
Frans

I'm going out for a short holiday. so I can not help you at this moment.
I would however see how you solved it.
Did you contact the IBI helpdesk?




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

September 19, 2007, 09:44 AM
Frans
OK i'm planning to work on it friday again with someone of IBI, I'll let you know if I resolved it.

Enjoy your holiday!


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
November 08, 2007, 04:36 AM
Frans
For all who are interested in the solution;

Since we're using webservices to feed another application with reports, html lay-out painter was no option because this produces technically incorrect html ouput (multiple HTML blocks).

So for HTML ouput we've solved it to let the front-end application consume HTMTABLE format and let the frontend do the lay-out.

For PDF the PDF lauout painter is sufficient, this produces 1 one and works very nicely to combine everything in WebFOCUS.


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
November 08, 2007, 09:37 AM
GinnyJakes
Frans,

The PDF Layout Painter works great for this. Here is a model that I created using one of the sample files. The graph program was created with the Advanced Graph Assistant (in a previous release as it is not working very well in a Unix environment in 762) and was referenced in the compound focexec.

I did all of this with the GUI and no hand-coding. (I know, I know, I'm going to hear from a bunch of you on this.)

Anyway, here is my code and I hope it helps you out.

This first program is the compound report that has a page master with an image. You'll have to change it to something on your site or remove it altogether.
 
-* File centsales_compound_pdf_sortmerge
-* Default Mode: ResourceLayout
SET HTMLARCHIVE=ON
COMPOUND LAYOUT PCHOLD FORMAT PDF
UNITS=IN, $
SECTION=section1, LAYOUT=ON, METADATA='0.5^0.5^0.5^0.5', MERGE=OFF, ORIENTATION=PORTRAIT, PAGESIZE=Letter, $
PAGELAYOUT=ALL, NAME='BNSF Page Master', $
OBJECT=BOX, NAME='line1', POSITION=(0.417 1.250), DIMENSION=(7.417 0.042), BACKCOLOR=RGB(0 0 0), BORDER-COLOR=RGB(0 0 0), $
OBJECT=IMAGE, NAME='image1', [B]IMAGE=bnsf_logo.gif[/B], POSITION=(0.667 0.667), DIMENSION=(2.125 0.417), $
OBJECT=STRING, NAME='text1', TEXT='&DATEtrMDYY', POSITION=(6.125 0.802), MARKUP=ON, WRAP=ON, DIMENSION=(1.417 0.208), font='ARIAL', color=RGB(0 0 0),  size=10, $
OBJECT=STRING, NAME='text2', TEXT='Page <ibi-page-number/> of <ibi-total-pages/>', POSITION=(3.667 10.167), MARKUP=ON, WRAP=ON, DIMENSION=(1.417 0.250), font='ARIAL', color=RGB(0 0 0),  size=10, $
COMPONENT='DfltCmpt1', POSITION=(0 0), DIMENSION=(0 0), $
PAGELAYOUT=1, NAME='Page layout 1', text='Page layout 1', TOC-LEVEL=1, BOTTOMMARGIN=0.5, TOPMARGIN=0.5, METADATA='0^0.5^0^0.5', $
COMPONENT='report1', TEXT='report1', TOC-LEVEL=2, POSITION=(0.861 1.663), DIMENSION=(6.802 5.235), METADATA='Z-INDEX: 100; LEFT: 0.861in; WIDTH: 6.802in; POSITION: absolute; TOP: 1.663in; HEIGHT: 5.235in', $
COMPONENT='graph1', TEXT='graph1', TOC-LEVEL=2, POSITION=(0.861 6.956), DIMENSION=(6.819 2.564), METADATA='Z-INDEX: 100; LEFT: 0.861in; WIDTH: 6.819in; POSITION: absolute; TOP: 6.956in; HEIGHT: 2.564in', $
OBJECT=STRING, NAME='text1', TEXT='PDF Layout Painter - Compound Bursted Report - Master IBIDEMO/CENTURYSALES on Plant Name', POSITION=(0.875 1.292), MARKUP=OFF, WRAP=ON, DIMENSION=(6.542 0.292), font='ARIAL', color=RGB(0 0 0), style=bold, size=10, $
END
SET COMPONENT='DfltCmpt1'
TABLE FILE SYSCOLUM
" "
SUM TBNAME NOPRINT
IF READLIMIT EQ 1
ON TABLE SET PREVIEW ON
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET STYLE *
END
SET COMPONENT='report1'
-*component_type report
-INCLUDE centsales_pdf
SET COMPONENT='graph1'
-*component_type graph
-INCLUDE advanced_merge_centsales
TABLE FILE CENTURYSALES
" "
SUM
COMPUTE CNTR/I4 = CNTR + 1; NOPRINT
COMPUTE CNTR2/A4 = IF &FOCGRAPHCNT EQ 1 THEN ' ' ELSE FTOA(CNTR-1,'(F4)','A4'); NOPRINT
COMPUTE IMG/A16 = 'HOLD'||LJUST(4,CNTR2,'A4')|| '.svg';NOPRINT
BY CENTURYSALES.PLANTSEG.PLANTNAME NOPRINT
ON CENTURYSALES.PLANTSEG.PLANTNAME PAGE-BREAK
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET STYLE *
type=HEADING, IMAGE=(IMG), position=(0 0), $
END
COMPOUND END


This is the report program:
 -* File centsales_pdf.fex
DEFINE FILE CENTURYSALES
PROD_GROUP/A30=IF PRODUCTCATEGORY CONTAINS 'Camcorders' OR 'Cameras' THEN 'Digital Photography' ELSE
               IF PRODUCTCATEGORY CONTAINS 'PDA' OR 'Organizers'     THEN 'Organizers'          ELSE
               IF PRODUCTCATEGORY EQ 'TV' OR 'VCR' OR 'DVD'          THEN 'Video Systems'       ELSE
      'Audio Systems';
END
TABLE FILE CENTURYSALES
SUM
     CNT.ORDERNUMBER AS 'Number,of Orders'
     QUANTITY
     LINEPRICE
BY PLANTNAME NOPRINT
BY PROD_GROUP AS 'Product Group'
BY PRODUCTCATEGORY
ON PLANTNAME PAGE-BREAK
ON PROD_GROUP SUBTOTAL AS '*TOTAL'
HEADING
"Plant Order Report for <PLANTNAME "
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='ARIAL',
     SIZE=9,
     RIGHTGAP=0.125000,
$
TYPE=TITLE,
     STYLE=BOLD,
$
TYPE=TABHEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=TABFOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=HEADING,
     SIZE=12,
     BACKCOLOR='SILVER',
     STYLE=BOLD,
$
TYPE=HEADING,
     LINE=1,
     JUSTIFY=CENTER,
$
TYPE=FOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=SUBHEAD,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBFOOT,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBTOTAL,
     BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
     SIZE=9,
$
TYPE=ACROSSTITLE,
     STYLE=BOLD,
$
TYPE=GRANDTOTAL,
     BACKCOLOR=RGB(210 210 210),
     STYLE=BOLD,
$
ENDSTYLE
END
 


And the graph:
 -* File advanced_merge_sample.fex
-*INTERNAL_PROPERTIES$SampleData=false;OBJECTID=GLOBAL
-*INTERNAL_PROPERTIES$GlobalRecordLimit=500;OBJECTID=GLOBAL
-*INTERNAL_PROPERTIES$SampleData=false;OBJECTID=GLOBAL
-*INTERNAL_PROPERTIES$GlobalRecordLimit=500;OBJECTID=GLOBAL
-*INTERNAL_PROPERTIES$SampleData=false;OBJECTID=GLOBAL
-*INTERNAL_PROPERTIES$GlobalRecordLimit=500;OBJECTID=GLOBAL
-*INTERNAL_PROPERTIES$SampleData=false;OBJECTID=GLOBAL
-*INTERNAL_PROPERTIES$GlobalRecordLimit=500;OBJECTID=GLOBAL
DEFINE FILE CENTURYSALES
PROD_GROUP/A30=IF PRODUCTCATEGORY CONTAINS 'Camcorders' OR 'Cameras' THEN 'Digital Photography' ELSE
               IF PRODUCTCATEGORY CONTAINS 'PDA' OR 'Organizers'     THEN 'Organizers'          ELSE
               IF PRODUCTCATEGORY EQ 'TV' OR 'VCR' OR 'DVD'          THEN 'Video Systems'       ELSE
      'Audio Systems';
END
-*INTERNAL_PROPERTIES$SampleData=false;OBJECTID=GLOBAL
-*INTERNAL_PROPERTIES$GlobalRecordLimit=500;OBJECTID=GLOBAL
-*INTERNAL_PROPERTIES$SampleData=false;OBJECTID=GLOBAL
-*INTERNAL_PROPERTIES$GlobalRecordLimit=500;OBJECTID=GLOBAL
GRAPH FILE CENTURYSALES
-* Created by Advanced Graph Assistant
SUM CNT.CENTURYSALES.ORDERS.ORDERNUMBER AS 'Number of Orders'
BY CENTURYSALES.PLANTSEG.PLANTNAME NOPRINT
BY CENTURYSALES.PRODSEG.PROD_GROUP AS 'Product Group'
ON GRAPH SET STYLE *
    TYPE=REPORT, TITLETEXT='Boston', $
ENDSTYLE
ON GRAPH SET GRAPHDEFAULT OFF
ON GRAPH SET GRMERGE ADVANCED
ON GRAPH SET GRMULTIGRAPH 1
ON GRAPH SET GRLEGEND 0
ON GRAPH SET GRXAXIS 1
ON GRAPH SET HAXIS 6
ON GRAPH SET VAXIS 3
ON GRAPH SET UNITS INCHES
ON GRAPH SET LOOKGRAPH VBAR
ON GRAPH HOLD AS HOLD FORMAT SVG
ON GRAPH SET GRAPHSTYLE *
setTemplateFile("/images/tdg/template/IBISouthWestern.txt");
setReportParsingErrors(false);
setSelectionEnableMove(false);
setPieDepth(10);
setPlace(true);
setSeriesType(1,0);
setConnectLineStep(getSeries(1),false);
setSeriesType(2,0);
setConnectLineStep(getSeries(2),false);
setSeriesType(3,0);
setConnectLineStep(getSeries(3),false);
setSeriesType(4,0);
setConnectLineStep(getSeries(4),false);
setSeriesType(5,0);
setConnectLineStep(getSeries(5),false);
setSeriesType(6,0);
setConnectLineStep(getSeries(6),false);
setSeriesType(7,0);
setConnectLineStep(getSeries(7),false);
setSeriesType(8,0);
setConnectLineStep(getSeries(8),false);
setSeriesType(9,0);
setConnectLineStep(getSeries(9),false);
setSeriesType(10,0);
setConnectLineStep(getSeries(10),false);
setSeriesType(11,0);
setConnectLineStep(getSeries(11),false);
setSeriesType(12,0);
setConnectLineStep(getSeries(12),false);
setSeriesType(13,0);
setConnectLineStep(getSeries(13),false);
setSeriesType(14,0);
setConnectLineStep(getSeries(14),false);
setSeriesType(0,0);
setConnectLineStep(getSeries(0),false);
setSmoothLines(false);
setSmoothAreas(false);
setUseTimeScaleAxis(false);
setDepthRadius(0);
setDepthAngle(0);
setTextString(getSubtitle(),"Orders by Product Group");
setDisplay(getSubtitle(),true);
ENDSTYLE
END
 


You should be able to copy all this code into your environment and with a couple of changes, I hope it will work for you and you'll be able to use it as a model.

I have found you do have to read the doc to use this tool.

Have fun!


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
November 09, 2007, 11:13 AM
Frans
Hi Ginny,

Thanks for your reply, but I allready mentiond that we use the PDF layout painter to combine everything in one file. Wink This works great!

Another option if you're using BID is HTML layout painter. But if you're using webservices you can't combine tables and graphs. I'm not sure what the possibilities are in open portal services.

Frans


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
November 12, 2007, 08:55 AM
linus
I'm not sure I completely following what your attempting to do, but I have a program where I create a report and lay a graph image next to the report. I do this with looping. The program is an online program but it generates pdf output. If you contact me personally I'd be happy to share my code with you.


WF 7.7.05
HP-UX - Reporting Server, Windows 2008 - Client, MSSQL 2008, FOCUS Databases, Flat Files
HTML, Excel, PDF