Transcription
Pete Helgrenpete@valadd.comOpen Source Report Writing Tools for IBM iValue Added Software, Inc801.581.115418027 Cougar BluffSan Antonio, TX 78258 2014 Value Added Software, Incwww.opensource4i.com1
DisclaimerThe original presentation lookedat both Jasper Reports andBIRT. This presentation willfocus on Jasper Reports.Anyone interested in BIRT? 2014 Value Added Software, Incwww.opensource4i.com2
AgendaReview the current “report writing” optionsTake a look at “traditional” RPG approachesTake a look at designing reports using reportwriting toolsTake a look at integrating Open Source andRPGTake a look at deployment strategies 2014 Value Added Software, Incwww.opensource4i.com
Report writing optionsQuery/400 (or whatever it is called now) DB2 Web Query Sequel NGS - IQ Crystal Reports Business Objects (SAP) Jasper Reports BIRT *Roll your own – Open Source 2014 Value Added Software, Incwww.opensource4i.com
Why use Open Source?Affordable (like, um, free)IBM i is the perfect environment for Open SourceILE RPG easily accommodates FOSSLow investment (time, human CPU cycles andmoney) 2014 Value Added Software, Incwww.opensource4i.com
Why NOT use Open Source?Learning curve ( for some, - for others)Licensing Issues – Need to clearly understand what youcan and can't do with FOSS.Support Issues – Most support is by user forum butmany commercial offerings are available.Possible security issues like “Heartbleed”Performance and “stack” stability – not really an issue.(maybe upgrade from that model 270/800!) 2014 Value Added Software, Incwww.opensource4i.com
Traditional RPG Approaches RPG writing to an External Print File With CL Wrapper -- PDF (V6R1) Add the iText wrapper 2014 Value Added Software, Incwww.opensource4i.com
“Traditional” RPG(code example – Sample1.rpgle)FOEMPRPTOED OverflowD SQLStmntD PrepSQLGetRSD FetchNextRowD CloseSQLCursorD WriteLineD WriteHeaderD SQLSuccessC/freesSPRPRPRPRPRSPRINTER / Prep the SQL statementIf PrepSQLGetRS() SQLSuccess;// Read the employee fileWriteHeader();Dow FetchNextRow() SQLSuccess;WriteLine();enddo;// Close the cursor opened in prepCloseSQLCursor();endif;*inlr *on;/end-free 2014 Value Added Software, Incwww.opensource4i.com
Sample1.rpgle **************p* Prepare and open the SQL *****************P PrepSQLGetRSBD PrepSQLGetRSPI5AD/freeSQLStmnt 'select ' 'emfnam '' '' substr(emmnam,1,1) '' '' emlnam as ename, ' 'emadd1,emcity, emst, emzip1,emzip2 from employee ' 'order by emlnam,emfnam';EXEC SQLPREPARE S1 FROM :SQLStmnt;EXEC SQLDECLARE C1 CURSOR FOR S1;EXEC SQLOPEN C1;RETURN SQLSTT;/end-freeP PrepSQLGetRSE 2014 Value Added Software, Incwww.opensource4i.com
Sample1.rpgle **************p* Fetch a row at a ************P FetchNextRowBD FetchNextRowPI5A/freeEXEC SQLFETCH FROM C1 INTO :ename, :emadd1, :emcity, :emst, :emzip1, :emzip2 ;RETURN SQLSTT;/end-freeP FetchNextRowE 2014 Value Added Software, Incwww.opensource4i.com
Sample1.rpgle **************C* Write the **************P WriteHeaderBD WriteHeaderPI/freewrite header;/end-freeP WriteHeaderE 2014 Value Added Software, Incwww.opensource4i.com
Sample1.rpgle **************C* Write the **************P WriteLineBD WriteLinePI/freewrite detail;if Overflow ;write header;reset Overflow;endif;/end-freeP WriteLineE 2014 Value Added Software, Incwww.opensource4i.com
Sample1.rpgle **************C* Close the Open **************P CloseSQLCursor BD CloseSQLCursor PI5A/freeEXEC SQLCLOSE C1;RETURN SQLSTT;/end-freeP CloseSQLCursorE 2014 Value Added Software, Incwww.opensource4i.com
Sample1 spooled file (cont) 2014 Value Added Software, Incwww.opensource4i.com
Sample1C (override to PDF)PGMOVRPRTFFILE(OEMPRPT) DEVTYPE(*AFPDS) SPLFNAME(SAMPLE) TOSTMF('\Reports\output') WSCST(*PDF)callsample1endpgm 2014 Value Added Software, Incwww.opensource4i.com
Sample1C output 2014 Value Added Software, Incwww.opensource4i.com
Sample2.rpgle (using A5A5A10I tColumnView)40A30A25A2A5S 05S 0N10I 0 2014 Value Added Software, m
Sample2.rpgle (cont)C/freerre begin object group(16);// Initial Object creationlHeading new String('Sample Report using iText and RPG');lColumns new jArrayList();lReportName new String('Sample iText Report (Sample 2) ');lFileName new String('/reports/output/Sample 2 iText Report.pdf');WriteHeader();iTextReport new ame);rreRV initialize(iTextReport); // Creates the report shellcounter 1;// Prep the SQL statementIf PrepSQLGetRS() SQLSuccess;// Read the employee fileDow FetchNextRow() SQLSuccess;counter counter 1;WriteLine();enddo;// Close the cursor opened in prepCloseSQLCursor();lrows rreRV finalize(iTextReport:counter);endif;*inlr *on;/end-free 2014 Value Added Software, Incwww.opensource4i.com
Sample2.rpgle **************C* Write the **************P WriteHeaderBD WriteHeaderPI/free// Not so much a write as it is a CREATE// Before creating the ReportView, add heading columns to the array// The pattern here is to construct and then add to the ArrayList//Column Headings MUST Total to 100%colEmp new String('Employee Name');colAddr new String('Address');colCity new String('City');colState new String('State');colZip1 new String('Zip');colZip2 new String('Zip 4');lColView new RREReportColumnView(colEmp:30);rre jArrayList add(lColumns:lColView);lColView new RREReportColumnView(colAddr:20);rre jArrayList add(lColumns:lColView);lColView new RREReportColumnView(colCity:20);rre jArrayList add(lColumns:lColView);lColView new RREReportColumnView(colState:10);rre jArrayList add(lColumns:lColView);lColView new RREReportColumnView(colZip1:10);rre jArrayList add(lColumns:lColView);lColView new RREReportColumnView(colZip2:10);rre jArrayList add(lColumns:lColView);/end-freeP WriteHeaderE 2014 Value Added Software, Incwww.opensource4i.com
Sample2.rpgle **************C* Write the **************P WriteLineBD WriteLinePI/freeif %REM(counter: 2) 1;RRERV SetGrayFill(iTextReport:.9);endif;rreRV addCell(iTextReport:ename);rreRV addCell(iTextReport:emadd1);rreRV addCell(iTextReport:emcity);rreRV addCell(iTextReport:emst);rreRV addCell(iTextReport:%char(emzip1));rreRV addCell(iTextReport:%char(emzip2));if %REM(counter: 2) 1;RRERV SetGrayFill(iTextReport:1);endif;/end-freeP WriteLineE 2014 Value Added Software, Incwww.opensource4i.com
Sample2 output - pdf 2014 Value Added Software, Incwww.opensource4i.com
Questions?Three “native i” approaches:“Traditional” spoolfile“Traditional” spoolfile with override to PDFUse iText (Java) through wrapping with RPG 2014 Value Added Software, Incwww.opensource4i.com
Designing ReportsThe purpose of this session was not to introduce you toreport design, just how to run those designed reports fromRPG. However, a quick tutorial will get you oriented.There are MANY tutorials and design guides on the web forJasper and iReport.Remember: RRE can handle overriding your connection toDB2 for i. So you can develop with one DB and deploy onanother. You could even use a MySQL or MSSQL DB todevelop and then deploy to I as long as your table/columnreferences don't change. 2014 Value Added Software, Incwww.opensource4i.com
Designing Reports (cont)Jasper uses iReport for design (the Eclipse plugin is underre-development as Jasper Studio).By the way: RRE is *currently* using JasperReports version4.5.0 (iReport 4.5.0). Make sure you have designers thatare compatible with the correct version otherwise runningthe reports can get ugly. 2014 Value Added Software, Incwww.opensource4i.com
Basic SetupDownload and install iReportInstall the jt400.jar (JDBC driver) into any location.(get it from http://jt400.sourceforge.net/)Then from within iReport choose the Tools-- options and thenpatiently wait for the all the tabs to appear. Select the “classpath”tab and then “Add” the jar location to the classpath.You'll need the JDBC drivers so you can access the DB2 for Idatabases 2014 Value Added Software, Incwww.opensource4i.com
Start iReport and create adata sourceClick on the database connection icon (highlighted above) 2014 Value Added Software, Incwww.opensource4i.com
Start iReport and create adata sourceIn the Connections/Datasources window click on “New” and thenselect “Database JDBC Connection” 2014 Value Added Software, Incwww.opensource4i.com
Start iReport and create adatasourceComplete the dialog as indicated – try the test button tomake sure all is well 2014 Value Added Software, Incwww.opensource4i.com
Jasper – Report WizardGenerally the best approach to get started is touse the wizard in Jasper Reports. It'll step youthrough the options. SQL comes first.If you are proficient at SQL or already have theSQL in some form, you can cut and paste theSQL Statement into the SQL window. Or youcan use the “Design Query” button which willstep you through creating the SQL: 2014 Value Added Software, Incwww.opensource4i.com
Report WizardFile-- new will start the wizard. The frame may take a few seconds to loadBut it will then display the available templates. Choose one and then click onLaunch Report Wizard 2014 Value Added Software, Incwww.opensource4i.com
Report WizardGive the report a file name and a folder to save it in. Then click next. 2014 Value Added Software, Incwww.opensource4i.com
Build a queryThe basic challenge of any report writer/designeris that the end user usually needs to knowsomething about the database schema andrelationships in order to create valid queries.JasperReports is no exception. You still need toknow your SQL! 2014 Value Added Software, Incwww.opensource4i.com
Design a QueryIf you need to design a query click the “Design Query” button. If you havealready designed a query then select it from the “Load query” button. 2014 Value Added Software, Incwww.opensource4i.com
Design Query Wizard 2014 Value Added Software, Incwww.opensource4i.com
Report Wizard - SQL 2014 Value Added Software, Incwww.opensource4i.com
Report Wizard – Select Fields 2014 Value Added Software, Incwww.opensource4i.com
Report Wizard – GroupingIf you have aggregation (count, min, max, etc)functions or want to break at certain groups, thenadd a group by clause to the SQL 2014 Value Added Software, Incwww.opensource4i.com
Report Wizard – ChooselayoutThere are several to choose from. The “classic”columnar report (more like a “form”) or a tabularlayout (multiple rows) 2014 Value Added Software, Incwww.opensource4i.com
Report Wizard - FinishOne useful option is to save the report as a “template” whichyou can use to retrieve settings in the first step of using thewizard in the future. This is handy for saving complexqueries that may be reused. 2014 Value Added Software, Incwww.opensource4i.com
Jasper Report - modifyThe wizard will give you a basic form so you will probablywant to modify the report title and column headings ifnothing else. 2014 Value Added Software, Incwww.opensource4i.com
Jasper Reports - ExecuteYou will need to save the jrxml file before you runthe report primarily because Jasper will compilethe report to a .jasper file. BOTH compiled anduncompiled reports can be used by RRE.Jasper finally compiles down to a java objectwhich is executed and produces the requiredoutput. 2014 Value Added Software, Incwww.opensource4i.com
Jasper Output OptionsJasper currently supports:PDFXLS (ugly)TextCSVRTFODF (Open Office et al)HTML 2014 Value Added Software, Incwww.opensource4i.com
Employee ListingJasper - PDFIsn't that lovely. 2014 Value Added Software, Incwww.opensource4i.com
Deployment ApproachesiText wrapper (as previously seen)(limited to currently wrapped methods)iReport directlyJasper Reports ServerCall from RPG:Jasper wrapper(RPG Report Engine – RRE) 2014 Value Added Software, Incwww.opensource4i.com
JasperReports Server Web Based Centralized Individual and group security Easy Deployment User selected output options Runs under Tomcat (so it runs on i!) Download the free community edition at thewww.jasperforge.org site 2014 Value Added Software, Incwww.opensource4i.com
Jasper Reports ServerDeploymentSelect the JasperServer Repository view from the “Window” menu item.Navigate the repository tree to the location you want to add the report to.Right click and choose “Add” and select “JasperServer report 2014 Value Added Software, Incwww.opensource4i.com
Jasper Reports ServerDeployment Name the reportSelect the source from the current report 2014 Value Added Software, Incwww.opensource4i.com
Jasper Reports ServerDeploymentChoose a datasource by importing the datasource from current environmentJNDI would be a better choice. 2014 Value Added Software, Incwww.opensource4i.com
Jasper Reports ServerDeployment 2014 Value Added Software, Incwww.opensource4i.com
Demo Let's deploy and run our demo report on theJasperReports Server. 2014 Value Added Software, Incwww.opensource4i.com
Deployment via RPG Since JasperReports is a Java library, you'llneed to wrap the java with RPG. Fortunately this has been done for you withthe RPG Report Generator project. 2014 Value Added Software, Incwww.opensource4i.com
Open Source Java(for me at least)Modernizing RPG applicationsLearn ILE RPG techniquesDon't have to learn JNI techniques(unless you need a method that hasn't been wrapped)Do you have to learn Java? No! But, being familiar with it canmake your life easier. 2014 Value Added Software, Incwww.opensource4i.com
It's a wrap!“Wrapping” is a method for interfacing betweenmethods in API's. Either to simplify them or tomake them more accessible in a specificlanguage.I use both approaches. I built an API withseveral wrappers in Java and then wrapped thewrappers in RPG. 2014 Value Added Software, Incwww.opensource4i.com
So is the RPG report generator aJava program or RPG?Both. I am wrapping Java with RPG.Scott Klement does this with HSSFR(POI)Aaron Bartell does this with the RPG ChartEngine(which inspired me to do the Report Engine)Kudos to both of them for sharing their code! 2014 Value Added Software, Incwww.opensource4i.com
Java Basics(a short side trip)Dealing with objects.Objects have both a way to store information andthey have ways of acting on that information,both internally and externally.Information is stored in “fields” (variables) in theobject.Fields are acted upon by “methods” 2014 Value Added Software, Incwww.opensource4i.com
Java Basics(a short side trip - cont)For example:An “ bank account” object might have fields tostore account type, account number and abalance.You might want to be able to add money(deposit), take money (withdraw) or just get abalance (these would be your methods) 2014 Value Added Software, Incwww.opensource4i.com
Java Basics(a short side trip - cont)// We have a list of the fields and one method (a constructor) shown herepublic class Account {/****/String type;String account;private BigDecimal balance;public Account(String type, String account) {// TODO Auto-generated constructor stub}this.type type;this.account account;this.balance new BigDecimal("0"); 2014 Value Added Software, Incwww.opensource4i.com
Java Basics(a short side trip - cont)public boolean addMoney(BigDecimal deposit){boolean success false;this.balance.add(deposit);// Maybe some DB I/O to update a tablereturn success;}public boolean takeMoney(BigDecimal withdrawal){boolean success false;this.balance.subtract(withdrawal);// Maybe some DB I/O to update a tablereturn success;}public BigDecimal getBalance(String type, String number){return this.balance;} 2014 Value Added Software, Incwww.opensource4i.com
Java Basics(a short side trip - cont)Generally, the class represents a “blueprint” forhow the object works in the world. Rarely do weact on the classes themselves. In most cases wecreate and “instance” of the class which will beunique during it's lifetime. 2014 Value Added Software, Incwww.opensource4i.com
Java Basics(a short side trip - cont)So, rather than acting on the account class itself,we create an instance of it with the “new”operator which returns us a fresh object, built onour blueprint. e.g.Account pete new Account(“checking”,”123345”)This constructs an instance of a checking account with account #12345 and a balance of zero (my usual balance.)Basically the thing is built in memory and referenced in ourprograms with the variable name “pete”. 2014 Value Added Software, Incwww.opensource4i.com
Java Basics(a short side trip - cont)Now that we have an object, we can act on it.Account pete new Account(“checking”,”123345”);BigDecimal mydeposit new BigDecimal(“20.00”);boolean OK pete.addMoney(mydeposit);BigDecimal mybalance pete.getBalance(); 2014 Value Added Software, Incwww.opensource4i.com
Example (Java – Java)(sometimes called a “convenience method”)// Main print routine for both Jasper within class Generatorpublic boolean printReport(Connection pConnection, String reportName, StringreportOutput, HashMap reportParams, String outputFormat, boolean compileFirst,String engine ) {boolean success perPrint jasperPrint returnReportPrint(pConnection, reportName,reportParams, compileFirst);.}File out new File(reportOutput);return success; 2014 Value Added Software, Incwww.opensource4i.com
Example (Java to Java)public boolean iEmailReport(String sender, String recipient, String reportName,StringreportOutput,HashMap reportParameters, String outputFormat, boolean compileFirst, String engine){Connection aConn null;boolean success true;aConn setConnect();success printReport(aConn, reportName, reportOutput, reportParameters,outputFormat, compileFirst, engine);if(success){try {smtpHost props.getProperty(SMTP HOST);EzMailer.sendMessageAttach( smtpHost,sender, recipient,"Your report completed normally", "Your report " reportName " ran and completed normally and is located here: " reportOutput "." outputFormat,reportOutput "." outputFormat);} catch (MessagingException e) {// TODO Auto-generated catch rn success; 2014 Value Added Software, Incwww.opensource4i.com
Example (Java to Java)public boolean iEmailCompiledReport(String sender, String recipient, StringreportName,String reportOutput,HashMap reportParameters, String outputFormat,String engine){boolean success true;success iEmailReport(sender, recipient, reportName, reportOutput,reportParameters, outputFormat, false, engine );}return success; 2014 Value Added Software, Incwww.opensource4i.com
OK. So how do we call thisfrom RPG?All Java programs run within a Java VirtualMachine. IBM has built an interface between theJava world and the RPG world so when an RPGprogram references a Java program the JVM isinvoked (if the current job doesn't have onerunning). The RPG program invokes Javaconstructors to build the objects within the JVMand then provide pointers to the objects in theJVM so that they can be found and used by theRPG programs. 2014 Value Added Software, Incwww.opensource4i.com
Drawbacks?The construction of the JVM is I/O and memoryintensive so there is a “warm up” period.The two worlds are basically oblivious to eachother so normal garbage collection of objectsthat are no longer in use in the Java world doesNOT happen automatically. 2014 Value Added Software, Incwww.opensource4i.com
Fixes to JVM drawbacksJVM warm up can be minimized by running theprocess in a server job. But then a mechanismfor communicating with the server job needs tobe implemented (like with a data queue).There are some callable routines that can “cleanup” after calls to Java objects are made. Theseare already part of RRE(but you need to code additional ones if you decide to wrap moreJava methods) 2014 Value Added Software, Incwww.opensource4i.com
Example RPG to Java* D RREGenerator .engineD.Generator'D:'iPrintReport')D hostlike(jString)D libraryListlike(jString)D userIDlike(jString)D passwordlike(jString)D reportNamelike(jString)D reportOutputlike(jString)D reportParamlike(jMap)D outputFormatlike(jString)D compFirstNvalueD enginelike(jString) 2014 Value Added Software, Incwww.opensource4i.com
Plus an RPG --------------------------------**RPG Wrapper Prototype for RRE ----------------------------D rre iPrintReport.DPRNDprHost1024Aconst varyingDprLibList1024Aconst varyingDprUserID1024Aconst varyingDprPassword1024Aconst varyingDprReportName1024Aconst varyingDprReportOut1024Aconst varyingDprRepParamlike(jMap)DprOutFormat1024Aconst varyingDprCompileNvalueDprEngine1024Aconst varyingBasically you wouldn't know this was calling a java programexcept for this. You might be able to find an alternative. 2014 Value Added Software, Incwww.opensource4i.com
RPG Report Engine BasicsBoth Jasper and BIRT are supported.Same API for both reports to keep it simple (fornow)The only difference? How each report isdesigned. 2014 Value Added Software, Incwww.opensource4i.com
RRE APIVariations on a theme:Two basic api's:1. Print the report with output going to the IFS2. Print the report and email it.Two different report types for Jasper:(ignored for BIRT)Compiled (.jasper)Uncompiled(.jrxml)The result is 4 “flavors” of api's:Email or not. Compiled or not 2014 Value Added Software, Incwww.opensource4i.com
Parameters passed to RREAPIFor the most basic API, printReport we need thefollowing:An SQL Connection object (handled for youautomatically if you want)The full path and name of the report file (withextension)Full path and file name for the output (extensionwill be added automatically)Report parameters (as a Java HashMap)Compile indicator (true/false boolean)Report type: Jasper or BIRT 2014 Value Added Software, Incwww.opensource4i.com
Convenience methodsIn RPG the easiest approach is to let RREhandle your connection to DB2 using the RREproperties file. Then you can use the conviencemethods to run each report. 2014 Value Added Software, Incwww.opensource4i.com
----------------------------------**RPG Wrapper Prototype for RRE ------------------------------------D rre iPrintCompiledReport.DPRNDprReportName1024Aconst varyingDprReportOut1024Aconst varyingDprRepParamlike(jMap)DprOutFormat1024Aconst varyingDprEngine1024Aconst varying* 2014 Value Added Software, Incwww.opensource4i.com
RRE componentsPrototypes are in RRE H.rpgleActual procedure interfaces are coded inRRE.rpgle 2014 Value Added Software, Incwww.opensource4i.com
Example of RPG wrapper forJava procedurePPDDDDDDDDDDrre iPrintCompiledReport.Brre 024A1024A1024ANconst varyingconst varyinglike(jMap)const varyingconst varyinglike(RREGenerator)/freegen new RREGenerator();// For convience convert RPG strings to string objects to passlReportName new String(peReportName);lReportOut new String(peReportOut);lOutFormat new String(%trim(peOutFormat));lEngine new String(peEngine);success RREGenerator iPrintCompiledReport(gen: lReportName:lReportOut: peRepParam:lOutFormat: lEngine);rre freeLocalRef(gen);return success;/end-freePE 2014 Value Added Software, Incwww.opensource4i.com
Java equivalent// This wrapper assumes that connection from properties settings will be usedand that file is// is a compiled .jasper file (needs no compiling)public boolean iPrintCompiledReport(String reportName,String reportOutput,HashMapreportParameters, String outputFormat, String engine){Connection aConn null;boolean success true;aConn setConnect();success printReport(aConn, reportName, reportOutput, reportParameters,outputFormat, false, engine );disconnect(aConn);}return success; 2014 Value Added Software, Incwww.opensource4i.com
RPG Example/freerre begin object group(100);ReportName '/rre/reports/templates/employee listing with Parms.jasper';ReportOut '/rre/reports/output/employee listing with parms 2 test8';lReParam new jMap();lTempMap new jMap();lkey new String('selectZip');// Maybe this should be a BigDecimallvalue new jInteger(84078);lTempMap rre jmap put(lReParam:lkey:lvalue);OutFormat %trim(OutputType);Engine 'jasper';success rre iPrintCompiledReport(ReportName :ReportOut:lReParam:OutFormat :Engine);rre end object group();*inlr *on;/end-free 2014 Value Added Software, Incwww.opensource4i.com
What we didn't coverMost reports have parameters that are passed toit. RRE does accommodate the passing ofparameters to the reports. You could have aweb front end or a green screen front end thatcaptures the values and passes them RRE.The challenge in using parameters in reportsisn't in the running of them, it is in the design. 2014 Value Added Software, Incwww.opensource4i.com
What we didn't coverSub reportsPerhaps a future session is needed dealing onlywith the iReport report designer. 2014 Value Added Software, Incwww.opensource4i.com
Thanks! Questions?Pete HelgrenValue Added Software, Inc.pete@valadd.comCode samples and the complete RRE package isavailable here:http://www.opensource4i.com 2014 Value Added Software, Incwww.opensource4i.com
Designing Reports (cont) Jasper uses iReport for design (the Eclipse plugin is under re-development as Jasper Studio). By the way: RRE is *currently* using JasperReports version 4.5.0 (iReport 4.5.0). Make sure you have designers that are compatible with the correct version otherwise running the reports can get ugly.
