1 / 53

ASE102: Generic Reporting Engine based on T-SQL Stored Procedures

ASE102: Generic Reporting Engine based on T-SQL Stored Procedures. Timo Kuisma Senior Systems Analyst timo.kuisma@wmdata.fi August 15-19, 2004. Options for reporting in customized solutions. Third party reporting application (Business Objects, Cognos, Crystal Reports etc.)

talmai
Download Presentation

ASE102: Generic Reporting Engine based on T-SQL Stored Procedures

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ASE102: Generic Reporting Engine based on T-SQL Stored Procedures Timo Kuisma Senior Systems Analyst timo.kuisma@wmdata.fi August 15-19, 2004

  2. Options for reporting in customized solutions • Third party reporting application (Business Objects, Cognos, Crystal Reports etc.) • Pros: highly customizable and visual layout • Pros: different ”levels” of complexity (e.g. designer, developer, end user) • Cons: licensing cost, learning curve, repository, separate server • Individual reports for application • Pros: you can program and visualize ”everything” • Cons: you must program and visualize everything! • Cons: new report usually means re-deployment • isql • Pros: you can report everything • Cons: requires good sql and database schema knowledge • Cons: no developer vs. end user distinction • Cons: not extremely visual user interface

  3. Generic Reporting Engine • Based on Transact-SQL stored procedures • reports can be added and modified ”on the fly” • no redeployment or even restart needed for the application • reports can contain anything, as long as it’s accessible by select -statement • one report can contain multiple result sets • Cons: only two "levels of complexity" -> developer and end user • Functionality extensible by custom commands • aggregate calculations (sum, min, max, avg, count) with break levels • formatting (e.g. font, size, colors, column widths) • url links to internal application pages or external websites • drilling to other reports • Example implementation based on Java Servlets • can be implemented basically in any programming language • JFreeChart for generating charts • free Java class library • open source (GNU Lesser General Public Licence) • http://www.jfree.org/jfreechart/

  4. Simple database schema

  5. What one report consists of • A stored procedure in the database • name starts with sp_E, for example sp_EMyCustomerReport • naming convention can be anything though • A row in EQMProcedure table • procName = “dbo.sp_EMyCustomerReport” • visibleName = “My extremely useful customer report” • groupId = 0 (main level in folder hierarchy) • procType = 0 (normal) • landscape = 0 • allowGet = 1 (can be bookmarked) • Appropriate privileges for execution • That’s all

  6. My extremely useful customer report create proc sp_EMyCustomerReport as select c.name as 'Customer', o.name as 'Main office', b.nameEn as 'Branch' from Customer c inner join Office o on o.id = c.mainOfficeId inner join Branch b on b.code = c.branchCode where c.customerStateId = 0 /* only active customers */ and c.salesBranchCode = 'U03' and c.name like 's%' order by c.name

  7. Report output looks like

  8. Main loop for ResultSet in Java private void showReport(Connection con, PrintWriter out, HttpServletRequest request, HttpServletResponse response) throws SQLException, ServletException, java.io.IOException, Exception { ResultSet rs = null; ReportInfo info = new ReportInfo(); String procedure = getStringParameter(request, "-name", ""); CallableStatement stmt = con.prepareCall(procedure); boolean gotResultSet = stmt.execute(); boolean runLoop = true; boolean bFirst = true; while (runLoop) { if (gotResultSet) { rs = stmt.getResultSet(); } else { rs = null; if (stmt.getUpdateCount() == -1) runLoop = false; } if (rs != null) { // Handle ResultSet } gotResultSet = stmt.getMoreResults(); } }

  9. Handle the ResultSet ResultSetMetaData rsmd = rs.getMetaData(); for (int i = 1; i <= rsmd.getColumnCount(); i++) { ReportColumn column = new ReportColumn(i, rsmd.getColumnName(i), rsmd.getColumnType(i)); column.setVisible(i <= (rsmd.getColumnCount() - info.getHiddenColumnCount())); info.addColumn(column); } if (bFirst) { out.println("<table border=0 class='reportengine' cellpadding='2'>"); bFirst=false; } out.println("<tr>"); out.println("<td colspan='" + info.getMaxColumnCount(true) + "'>&nbsp;</td>"); out.println("</tr>"); out.println("<tr class='table-header'>"); for (Iterator it = info.getColumns(); it.hasNext(); ) { ReportColumn column = (ReportColumn)it.next(); if (column.getVisible()) { out.print("<td align="+column.getAlignment() + "><b>" + formatCellValue(column.getName()) + "</b></td>"); } } out.println("</tr>");

  10. Loop the ResultSet row by row if (rs.next()) { RowBuffer prevRow = new RowBuffer(rsmd.getColumnCount()); RowBuffer currRow = new RowBuffer(rsmd.getColumnCount()); do { RowBuffer temp = prevRow; prevRow = currRow; currRow = temp; currRow.setValues(rs); int groupIndex = info.getGroupChange(prevRow, currRow); if (groupIndex >= 0) { if (!rs.isFirst()) { info.printGroupFooter(out, groupIndex); info.resetFunctions(groupIndex); } info.printGroupHeader(out, groupIndex, currRow); } info.printRow(request, out, currRow, params, isPrintVersion); info.processFunctions(currRow); } while (rs.next()); }

  11. info.printRow method prints HTML tablerow public void printRow(HttpServletRequest request, PrintWriter out, RowBuffer row, Vector params, boolean isPrintVersion) throws IOException { out.println("<tr class='detail'>"); for (Iterator it = getColumns(); it.hasNext(); ) { ReportColumn column = (ReportColumn)it.next(); int colIndex = column.getDataIndex(); if (column.getVisible()) { Object value = row.getObject(colIndex); String alignAttr = isNumericType(value) ? " align='right'" : ""; out.println("<td" + alignAttr + " class='detail'>" + Formatter.format(value, true) + "</td>"); } } out.println("</tr>"); }

  12. Report data is gathered in ReportInfo class public class ReportInfo { private SortedMap columns = new TreeMap(); private Vector groups = new Vector(); private int hiddenColumnCount = 0; private String reportTitle = ""; private Vector reportLinks = new Vector(); private Vector urlLinks = new Vector(); private Chart chart = null; public ReportInfo(); public String getReportTitle(); public void setReportTitle(String value); public void addColumn(ReportColumn column); public int getColumnCount(); public Iterator getColumns(); public ReportColumn getColumnByDataIndex(int dataIndex); public void addGroup(ReportGroup group); public int getGroupCount(); public ReportGroup getGroup(int index); public ReportColumn getGroupColumnByDataIndex(int dataIndex); ... }

  13. Each column is in ReportColumn class public class ReportColumn { private int dataIndex; private String name; private int dataType; private boolean visible; private AggregateFunction function; public ReportColumn(int dataIndex); public ReportColumn(int dataIndex, String name, int dataType); public ReportColumn(int dataIndex, String name, AggregateFunction function); public int getDataIndex(); public void setDataIndex(int value); public String getName(); public void setName(String value); public AggregateFunction getFunction(); public void setFunction(AggregateFunction value); public boolean getVisible(); public void setVisible(boolean value); public int getDataType(); }

  14. RowBuffer class holds data for one row public class RowBuffer { private Object[] values; public RowBuffer(int columnCount) { values = new Object[columnCount]; } public void clear() { for (int i = 0; i < values.length; i++) { values[i] = null; } } public void setValues(ResultSet rs) throws SQLException { ResultSetMetaData md = rs.getMetaData(); for (int i = 1; i <= md.getColumnCount(); i++) { values[i - 1] = rs.getObject(i); } } public int getCount() { return values.length; } public Object getObject(int index); public String getString(int index); public double getDouble(int index); public int getInt(int index); }

  15. Report parameters • Any stored procedure parameter is rendered as input param • Parameters starting with @EQM are inserted automatically • e.g. @EQM_loginId can be used to get logged user’s internal id in application • Pop-up menus can be rendered with EQMParameter row • insert into EQMParameter values (”salesBranchCode",”Sales branch", "select code, name from SalesBranchView order by sortOrder", null) • Insert into EQMParameter values (“reportYear”,”Year”, “select 2004,’2004’ union select 2003,’2003’ order by 1 desc”, null) • insert into EQMParameter values ("AMpersonId","Account manager", null, "sp_EPGetTeamAccountManagers") • Automatic @EQM parameters can be used with param procedures to e.g. filter the pop-up menu • One parameter definition can be linked to many reports • insert into EQMProcedureParameter values ( "dbo.sp_EMySalesReport","salesBranchCode")

  16. Example parameter form

  17. Parsing of stored procedure parameters private Vector getProcedureParameters(DatabaseMetaData dbmd, String schema, String procName) throws SQLException { Vector result = new Vector(); ResultSet rs = null; try { rs = dbmd.getProcedureColumns(null, schema, procName, null); while (rs.next()) { int type = rs.getInt("COLUMN_TYPE"); switch (type) { case DatabaseMetaData.procedureColumnIn: case DatabaseMetaData.procedureColumnUnknown: ParameterItem param = new ParameterItem( rs.getString("COLUMN_NAME").substring(1), rs.getInt("DATA_TYPE") ); result.add(param); break; } } } finally { closeResultSet(rs); } return result; }

  18. ParameterItem class public class ParameterItem { private String name; private int dataType; private String text; private boolean visible; private String visibleName; private Vector choiceList; public ParameterItem(String name, int dataType) { this.name = name; this.dataType = dataType; this.text = ""; this.visible = !name.startsWith("EQM"); } public String getName() {return name; } public int getDataType() {return dataType; } public boolean isVisible() {return visible; } public void setText(String value) {text = ((value == null) ? "" : value.trim()); } public String getText() {return text; } public int getInt() {return Integer.parseInt(text); } public java.util.Date getDate() throws ParseException { DateFormat dateFormat = DateFormat.getDateInstance(DateFormat.SHORT, new Locale("fi", "FI")); return dateFormat.parse(text); } ... }

  19. Input form for parameters out.println("<form method='GET' action='reportengine'>"); out.println("<input type='hidden' id='-name' name='-name' value='" + schemaName + "'>"); out.println("<table border=0>"); for (int i = 0; i < params.size(); i++) { ParameterItem param = (ParameterItem)params.get(i); if (param.isVisible()) { String paramName = param.getName(); String visibleName = param.getVisibleName()==null ? paramName : param.getVisibleName(); Vector choiceList = param.getChoiceList(); out.println("<tr>"); out.println("<td><label for='" + paramName + "'>" + visibleName + "</label></td>"); if (choiceList == null) { out.println("<td><input type='text' id='"+paramName+"' name='"+paramName+"' value='"+param.getText()+"'></td>"); } else { out.println("<td><select name='" + paramName + "' style=''>\n"); for (int vi = 0; vi < choiceList.size(); vi++) { out.println("<option"); out.println(" value=\"" + ((ParameterValue)choiceList.get(vi)).getId() + "\">\n"); out.println(((ParameterValue)choiceList.get(vi)).getName() + "\n"); out.println("</option>\n"); } out.println("</select>\n</td>"); } out.println("</tr>"); } } out.println("<td colspan='3'><input type='submit' id='-run' name='-run' value='Show report'></td>"); out.println("</tr></table></form>");

  20. Report procedure privileges • If application uses Sybase logins for user authentication • If application uses database connection pool • permissions have to be handled in a customized way, e.g. • category = "report" • action = "dbo.sp_EYearlySalesByBranch" • role = "Manager" • permission = Manager has the right to execute sp_EYearlySalesByBranch -report select e.procName from EQMProcedure e, sysobjects o where e.groupId = @reportGroupId and o.id = object_id(e.procName) and (o.uid = user_id() or user_id()=1 or exists ( select 1 from sysprotects p, sysusers s where s.uid = user_id() and p.id = o.id and p.action = 224 /* execute */ and p.protecttype=1 /* for grant */ and p.uid in (0, user_id(), s.gid) ))

  21. Extending functionality • Generic Command class • Headers and breaklevels • Aggregate functions • Report drilling • URL links • Creating charts • Editing • XML output and XSLT transformations

  22. Extending functionality with commands • Command is a simple select statement • e.g. "select db.hdr" for creating breaklevels • "select db.sum" for calculating sums for numeric columns • "select db.chart" for creating charts from column data • All commands can have 1-n parameters • e.g. "select db.sum, 4, 5, 6" for calculating sums for three columns • Each command needs an implementing class • e.g. db.sum command implementation is in FunctionCommand class • Each command is registered to a HashTable

  23. Command abstract class public abstract class Command { private String name; private static final Hashtable commands = new Hashtable(); public abstract void run(ReportInfo info, ResultSet rs, ResultSetMetaData md) throws Exception; public Command(String name) {this.name = name;} public String getName() {return name;} public static void register(Command command) {commands.put(command.getName(), command);} public static Command getInstance(String name) { Command command = (Command)commands.get(name); return command; } static { register(new HdrCommand()); register(new FunctionCommand("db.sum")); register(new FunctionCommand("db.avg")); register(new FunctionCommand("db.min")); register(new FunctionCommand("db.max")); register(new FunctionCommand("db.cnt")); register(new ListHideColumnsCommand()); register(new TitleCommand()); register(new ReportCommand()); register(new UrlCommand()); register(new ChartCommand()); } }

  24. Header break levels • Report data can be devided to break levels • create header break level from 1st column • select "db.hdr", 1 • Break levels can be nested • create break level from 1st column and then another one from 2nd column • select "db.hdr", 1 • select "db.hdr", 2 • And they can consist of multiple columns • create break level from 1st and 2nd columns • select "db.hdr", 1, 2 • Order by must be according to break level columns

  25. sp_EMyCustomersByCountry procedure create proc sp_EMyCustomersByCountry as select "db.hdr",1 select co.name as 'Country', c.name as 'Customer', o.name as 'Main office', p.firstName +' '+ p.lastName as 'Account manager' from Customer c inner join Office o on o.id = c.mainOfficeId inner join Address a on a.officeId = o.id and a.addressTypeId = 1 inner join Country co on co.code = a.countryCode inner join GroupMember gm on gm.personGroupId=c.teamPersonGroupId and gm.groupRoleTypeId=-1 inner join Person p on p.id = gm.personId and p.id != 10013420 where c.customerStateId = 0 /* only active customers */ and c.salesBranchCode = 'U03' order by co.name, c.name

  26. My customers by country / select "db.hdr",1

  27. HdrCommand class public class HdrCommand extends Command { public HdrCommand() { super("db.hdr"); } public void run(ReportInfo info, ResultSet rs, ResultSetMetaData md) throws SQLException { ReportGroup group = new ReportGroup(); for (int i = 2; i <= md.getColumnCount(); i++) { group.addColumn(new ReportColumn(rs.getInt(i))); } info.addGroup(group); } }

  28. ReportGroup class public class ReportGroup { private Vector columns = new Vector(); private Hashtable functions = new Hashtable(); public ReportGroup() {} public void addColumn(ReportColumn column) {columns.add(column);} public int getColumnCount() {return columns.size();} public ReportColumn getColumn(int index) {return (ReportColumn)columns.get(index);} public ReportColumn getColumnByDataIndex(int dataIndex) { for (int i = 0; i < getColumnCount(); i++) { ReportColumn column = getColumn(i); if (column.getDataIndex() == dataIndex) return column; } return null; } public void setFunction(int dataIndex, AggregateFunction function) { if (function == null) functions.remove(String.valueOf(dataIndex)); else functions.put(String.valueOf(dataIndex), function); } public AggregateFunction getFunction(int dataIndex) { return (AggregateFunction)functions.get(String.valueOf(dataIndex));} public void processFunctions(RowBuffer row) { for (Enumeration e = functions.keys(); e.hasMoreElements(); ) { String key = (String)e.nextElement(); AggregateFunction function = (AggregateFunction)functions.get(key); function.addValue(row.getDouble(Integer.parseInt(key))); } } }

  29. Aggregate functions • Numeric columns can be calculated by aggregates • sum, minimum, maximum, average and count • Command is simple: select "db.sum", 3, 4 • calculates sum for 3rd and 4th column to the end of result set • Aggregates are calculated for each break level • select "db.hdr", 1 (first column e.g. Customer name) • select "db.sum", 3, 4 • Aggregates could be calculated in procedure itself… • …but • result sets with break levels and compute -clause are complex to parse • report engine calculates them quickly when looping the result set • formatting can be customized

  30. My customer sales create proc sp_EMyCustomerSales as select "db.sum", 3 select c.name as 'Customer', co.name as 'Country', convert(numeric(12,2),round(sum(pre.amount),2)) as '2004 sales' from Customer c inner join Office o on o.id = c.mainOfficeId inner join Address a on a.officeId = o.id and a.addressTypeId = 1 inner join Country co on co.code = a.countryCode inner join Project p on p.customerId = c.id inner join ProjectRow pr on pr.projectId = p.id inner join ProjectRowEstimate pre on pre.projectRowId = pr.id where c.customerStateId = 0 /* only active customers */ and c.salesBranchCode like 'U%' group by c.name, co.name having sum(pre.amount) > 1000000 order by c.name

  31. Report looks like

  32. By adding select "db.hdr",2 it looks like

  33. Aggregate functions public class FunctionCommand extends Command { public FunctionCommand(String name) {super(name);} public void run(ReportInfo info, ResultSet rs, ResultSetMetaData md) throws Exception { for (int i = 2; i <= md.getColumnCount(); i++) { // select "db.sum", 4, 5, 6 AggregateFunction function = AggregateFunction.createInstance(getName()); info.getGroup(0).setFunction(rs.getInt(i), function); } } } public abstract class AggregateFunction { private static final Hashtable functions = new Hashtable(); public abstract void reset(); public abstract void addValue(double value); public abstract double getValue(); public abstract Object clone(); public static void register(String name, Class cls) {functions.put(name, cls);} public static AggregateFunction createInstance(String name) throws Exception, InstantiationException { Class cls = (Class)functions.get(name); if (cls == null) return null; return (AggregateFunction)cls.newInstance(); } static { AggregateFunction.register("db.sum", SumFunction.class); AggregateFunction.register("db.avg", AvgFunction.class); AggregateFunction.register("db.min", MinFunction.class); AggregateFunction.register("db.max", MaxFunction.class); AggregateFunction.register("db.cnt", CntFunction.class); } }

  34. Implementation of Sum aggregate public class SumFunction extends AggregateFunction { private double result; public SumFunction() { } private SumFunction(double result) { this.result = result; } public Object clone() { return new SumFunction(result); } public void reset() { result = 0; } public void addValue(double value) { result += value; } public double getValue() { return result; } }

  35. Linking of reports • Reports can be linked to other reports (drilling etc.) • select "db.report", "dbo.sp_EMyDrillingReportProc", 1 • Linked reports can have dynamic or static parameters • select "db.report", "dbo.sp_ESalesDrill", 1, "month=5", "&year=2004" public class ReportCommand extends Command { public ReportCommand () {super("db.report"); } public void run(ReportInfo info, ResultSet rs, ResultSetMetaData md) throws SQLException { ResultSetMetaData rsmd = rs.getMetaData(); int numberOfColumns = rsmd.getColumnCount(); Vector params = new Vector(); for (int i = 4; i <= numberOfColumns; i++) {params.add(rs.getString(i)); } ReportLink link = new ReportLink(rs.getString(2), rs.getInt(3), params); info.addReportLink(link); } }

  36. ReportLink class public class ReportLink { private String report = ""; private int column = 0; private Vector parameters = new Vector(); public ReportLink(String report, int column, Vector parameters) { this.report = report; this.column = column; this.parameters = parameters; } public String getReport() { return report; } public int getColumn() { return column; } public Vector getParameters() { return parameters; } }

  37. Linking to internal or external URLs • Any column of the result set can be linked to a URL • URL can point to application’s internal page • select 'db.url', 'customer', 2, 'id=10' • add link to column #2 and take customer id parameter (&id) from column #10 • or it can point to an external web page • select 'db.url', 'http://', 7, '9' • add link to column #7 and take the url from column #9 • Internal ID columns can be hided with command • select 'db.list.hidecolumns', 2 • UrlCommand and UrlLink are similar to ReportCommand and ReportLink

  38. Rendering URL links in HTML String urlLink = null; UrlLink url = getUrlLinkByColumnIndex(colIndex); if (url != null && !isPrintVersion) { boolean addLink = true; urlLink = "<a href='" + url.getUrl(); for (int i = 0; i < url.getParameters().size(); i++) { String paramValue = null; String para = (String)url.getParameters().get(i); int ampersandIndex = para.indexOf("&"); int equalsIndex = para.indexOf("="); if (ampersandIndex >= 0) {urlLink += para; } else if (equalsIndex >= 0) { int endOfPar = Integer.parseInt(para.substring(equalsIndex + 1)); if (endOfPar < 0){ ParameterItem param = (ParameterItem)params.get(Math.abs(endOfPar) - 1); paramValue = param.getText(); } else { Object rowObject = row.getObject(endOfPar); if (rowObject != null) paramValue = rowObject.toString(); else addLink = false; } if (addLink) { if (i>0) urlLink += "&"; else urlLink += "?"; urlLink += para.substring(0, equalsIndex) + "=" + paramValue; } else urlLink = ""; } else { if (url.getUrl().startsWith("http")) { Object rowObject = row.getObject(Integer.parseInt(para)); if (rowObject != null) paramValue = rowObject.toString(); if (value != null) urlLink += paramValue; else urlLink = ""; } } } if ((urlLink != null) && (urlLink != "")) urlLink += "' target='_blank'>"; }

  39. Creating charts with JFreeChart • Free Java class library for generating charts • pie charts (2D and 3D) • bar charts (regular and stacked, with an optional 3D effect) • line and area charts • scatter plots and bubble charts • time series • Gantt charts • Other features • direct export to PNG and JPEG • export to PDF via iText and SVG via Batik • support for Servlets, JSP, Applets or client applications • Licensed under GNU Lesser General Public Licence • http://www.jfree.org/jfreechart/

  40. Example report with db.chart Command create proc sp_ESalesBranchPie as select 'db.chart', 'Pie3D' type, 'Sales by branch from beginning of year '+ convert(char(4),datepart(year, getdate())) title, 'dbo.sp_ESalesBranchPieDRL' reportlink, 0 printdata select sbv.nameEn, convert(numeric(9,0),round(sum(pre.amount)/1000,0)) from Project p inner join ProjectRow pr on pr.projectId = p.id inner join ProjectRowEstimate pre on pre.projectRowId = pr.id and pre.year = datepart(year, getdate()) inner join Customer c on p.customerId = c.id inner join SalesBranchView sbv on substring(c.salesBranchCode,1,1) = sbv.code where p.stateDate >= convert(datetime,'1.1.'+convert(char(4),datepart(year,getdate()))) group by sbv.nameEn order by 2 desc

  41. And the report looks like

  42. ChartCommand class public class ChartCommand extends Command { public ChartCommand () {super("db.chart");} public void run(ReportInfo info, ResultSet rs, ResultSetMetaData md) throws SQLException { int numberOfColumns = md.getColumnCount(); String type = rs.getString(2); String title = rs.getString(3); String reportLink = ""; int width = 0; int height = 0; for (int i = 4; i <= numberOfColumns; i++) { String colname = md.getColumnName(i); if (colname.equalsIgnoreCase("reportlink")) {reportLink = rs.getString("reportlink"); } else if (colname.equalsIgnoreCase("width")) {width = rs.getInt(i); } else if (colname.equalsIgnoreCase("height")) {height = rs.getInt(i); } else if (colname.equalsIgnoreCase("printdata")) {info.setPrintTable(rs.getInt(i) == 1); } } if (type.equalsIgnoreCase("Pie3D")) { ChartPie3D myChart = new ChartPie3D(type, title, reportLink); if (width != 0) myChart.setWidth(width); if (height != 0) myChart.setHeight(height); info.setChart(myChart); } else if (type.equalsIgnoreCase("Bar3D")) { ... } } }

  43. Creating the chart with JFreeChart public class ChartPie extends Chart { private DefaultPieDataset dataset = new DefaultPieDataset(); public ChartPie(String type, String title, String reportLink) {super(type, title, reportLink); } public void addValue(ResultSet rs) { try {dataset.setValue(rs.getString(1), new Double(rs.getDouble(2))); } catch (SQLException ex) {System.out.println(ex); } } public DefaultPieDataset getDataset() {return dataset;} public void createChart(javax.servlet.http.HttpSession session) { PiePlot plot = new PiePlot(getDataset()); plot.setInsets(new Insets(0, 5, 5, 5)); StandardPieItemLabelGenerator lg = new StandardPieItemLabelGenerator( "{0} = {1}", NumberFormat.getInstance(new Locale("fi", "FI")), NumberFormat.getPercentInstance() ); plot.setLabelGenerator(lg); if (super.getReportLink() != "" && super.getReportLink() != null) { StandardPieURLGenerator urlg = new StandardPieURLGenerator( "reportengine?-name="+super.getReportLink(), "EQM_category" ); plot.setURLGenerator(urlg); super.setImageMap("chart"); } super.setChart (new JFreeChart(getTitle(), JFreeChart.DEFAULT_TITLE_FONT, plot, false)); super.saveFile(session); } }

  44. Chart abstract Class abstract public class Chart { private JFreeChart myChart; private String type = ""; private String title = ""; private String path = ""; private Vector parameters = new Vector(); private ChartRenderingInfo renderInfo = null; private String imageMap = ""; private String reportLink = ""; public Chart(String type, String title, String reportLink) { this.type = type; this.title = title; this.reportLink = reportLink; } abstract public void createChart(javax.servlet.http.HttpSession session); public void saveFile(HttpSession session) { String filename = null; myChart.setBackgroundPaint(java.awt.Color.white); try { renderInfo = new ChartRenderingInfo(new StandardEntityCollection()); filename = ServletUtilities.saveChartAsPNG(myChart, width, height, renderInfo, session); } catch (IOException e) { System.out.println(e.toString()); } path = "chart?filename=" + filename; } }

  45. Rendering the chart in Java Servlet Chart chart = info.getChart(); if (chart != null) { chart.createChart(request.getSession()); String imageMap = chart.getImageMap(); if (imageMap == "") { out.println("<IMG SRC=\"" + chart.getPath() + "\" WIDTH=\"" + chart.getWidth() + "\" HEIGHT=\"" + chart.getHeight() + "\" BORDER=\"0\" >" ); } else { PrintWriter pw = new PrintWriter(out); ChartUtilities.writeImageMap(pw, imageMap, chart.getRenderInfo(), false); pw.flush(); out.println("<IMG SRC=\"" + chart.getPath() + "\" WIDTH=\"" + chart.getWidth() + "\" HEIGHT=\"" + chart.getHeight() + "\" BORDER=\"0\" USEMAP=\"#"+ imageMap +"\" >" ); } }

  46. Editing of reports

  47. Using XML for report output • Writing report output as XML gives more flexibility • XML can be created with e.g. JDOM (http://www.jdom.org/) • Java based Document Object Model for XML files • Resulting XML data can be transformed with XSLT • to Word or Text document • to Excel document (tab separated data) • to PDF with Formatting Objects Processor (http://xml.apache.org/fop/) • and to HTML obviously

  48. Example XML output (My customer report) <?xml version="1.0" encoding="ISO-8859-1" ?> <report> <title>My extremely useful customer report</title> <section> <heading> <title>Customer</title> <title>Main office</title> <title>Branch</title> </heading> <rows> <row> <cell> <string>Stonesoft Espana SA</string> </cell> <cell> <string>Madrid</string> </cell> <cell> <string>Software consultancy and supply</string> </cell> </row> </rows> </section> </report>

  49. Building XML Document with JDOM import org.jdom.*; import org.jdom.output.XMLOutputter; ... org.jdom.Document doc = null; org.jdom.Element root = null; if (!pageHeaderWritten) { root = new Element ("report"); doc = new Document (root); Element title = new Element("title"); title.setText(info.getReportTitle()); root.addContent(title); pageHeaderWritten = true; } Element section = new Element("section"); if (info.getColumnCount()>0) { Element heading = new Element("heading"); for (Iterator it = info.getColumns(); it.hasNext(); ) { ReportColumn column = (ReportColumn)it.next(); if (column.getVisible()) { Element columnTitle = new Element("title"); columnTitle.setText(column.getName()); heading.addContent(columnTitle); } } section.addContent(heading); } Element rows = new Element("rows"); section.addContent(rows);

  50. Simple DTD for report XML output <?xml version="1.0" encoding="ISO-8859-1"?> <!ELEMENT report (title, section+, imageurl?)> <!ELEMENT section (title?, heading, rows)> <!ELEMENT rows (row | breaklevel)*> <!ELEMENT heading (title*)> <!ELEMENT breaklevel (title, row*)> <!ELEMENT row (cell)*> <!ELEMENT cell ((null | string | numeric | date | reference | aggregate), colindex?) > <!ELEMENT version (#PCDATA)> <!ELEMENT title (#PCDATA)> <!ELEMENT null EMPTY> <!ELEMENT string (#PCDATA)> <!ELEMENT numeric (#PCDATA)> <!ELEMENT date (#PCDATA)> <!ELEMENT reference (title, url)> <!ELEMENT url (#PCDATA)> <!ELEMENT aggregate (type, value)> <!ELEMENT type (average | sum | min | max | count)> <!ELEMENT value (numeric | date)> <!ELEMENT average (#PCDATA)> <!ELEMENT sum (#PCDATA)> <!ELEMENT min (#PCDATA)> <!ELEMENT max (#PCDATA)> <!ELEMENT count (#PCDATA)> <!ELEMENT rowindex (#PCDATA)> <!ELEMENT colindex (#PCDATA)> <!ELEMENT imageurl (#PCDATA)>

More Related