This section contains a series of short exercises designed to illustrate some of the basic features of the Report API and Chart API. For more details about any of the features described in this section, please see the Programming guide portion of the documentation.
The code, given in this guide, has been created with the following in mind:
ERES Server is up and running;
Tomcat has been installed (with default configuration) along with ERES and is being used as the servlet container and the web-server;
Any code (both source and compiled version) given in this guide is also under the <ERES installation directory>/WEB-INF/classes/help/quickstart directory. Any HTML and jsp files will be under the <ERES installation directory>/help/quickstart directory. Similarly, any templates would be under the help/quickstart/templates directory.
Most of the files need not be moved in order to run the exercises. In the few instances where the files have to be moved and/or modified, instructions will be given.
The templates (referenced in this guide), which uses a database as a data source, uses the HSQL java database. For windows users, there are alternate templates available, which use Access. These templates can be found under the <ERES installation directory>/help/quickstart/templates/Access directory. The templates will again follow the naming convention specified above along with “_Acc” (without the double quotes) before the “.rpt” (without the double quotes) extension.
You can run the API QuickStart by going to http://<machineName>:<portNumber>/<ERES context>/help/quickstart/index.jsp (or https://<machineName>:<portNumber>/<ERES context>/help/quickstart/index.jsp if you are using HTTPS). Make sure that ERES Server is running before running the examples. Once you connect to the URL, you should see the following document in the browser:
API QuickStart Index Page
Please note that while some of the API can be used without ERES Server and in other application servers, the code in this guide was designed with ERES Server and Tomcat in mind. Please refer to the Programming guide and Configuration guide to switch to other configurations.
The following sections show how to run a pre-existing template (ExportDHTMLReport.pak located in the <ERES installation directory>/help/quickstart/templates directory) in a jsp application using the Report API and also using the LookupServlet servlet.
Each section shows the code to generate the report and any steps necessary to deploy.
The following code shows how to display an existing report template (in this case ExportDHTMLReport.pak) in a jsp application:
// Connect to the ERES Server QbReport.setEspressManagerUsed(true); QbReport.useServlet(true); QbReport.setServletRunner(protocol + :// + host + ":" + port); QbReport.setServletContext(quadbase.common.client.ServerMessage.getServletContext()) // Open the report and export it as DHTML, return the result as a String QbReport report = new QbReport(null, help/quickstart/templates/ExportDHTMLReport.pak); ByteArrayOutputStream data = new ByteArrayOutputStream(2048); OutputStream out = new BufferedOutputStream(data); report.export(QbReport.DHTML, out); out.flush();
![[Note]](../../../images/note.png) | Note |
|---|---|
The above piece of code is not complete. The code above is the core ERES Report API code needed to open a report template, export it to DHTML and stream the DHTML content back to the client browser. |
The class file for the above source is located in the <ERES installation directory>/WEB-INF/classes/help/quickstart directory.
When the jsp application is run, by selecting the appropriate link from the main QuickStart API example page, the following report is shown:
Report Generated
The main part of the code is in the getReport method in the ExportDHTMLReport bean. There, a QbReport object called report is created using the ExportDHTMLReport.pak template. The QbReport is then exported, as DHTML content, to a OutputStream. The following constructor is used to create the QbReport object:
QbReport(Object parent, String reportTemplateName);
In addition to the above approach, you can simply pass in the name of the template and the export format desired to the LookupServlet and let it do the work.
The following code shows how to display an existing report template (in this case ExportDHTMLReport.pak) using the LookupServlet:
String contextPath = quadbase.common.client.ServerMessage.getServletContext(); // Based on the ERES context, get the http location of the files used in this example int lastSlash = contextPath.lastIndexOf(/); String eresPath = contextPath.substring(0, lastSlash); String domain = protocol + "://" + host + ":" + port;</p> return domain + contextPath + "/LookupServlet?USESESSION=TRUE& URLTYPE=FORREPORT&" + "TemplatePath=" + domain + eresPath + "/help/quickstart/templates/ExportDHTMLReport.pak&MultiPageExport=false";
| Note |
|---|---|
The above piece of code is not complete. The code above is the core ERES API code needed to pass an existing report template and the export format to the LookupServlet servlet. |
The class file for the above source is located in the <ERES installation directory>/WEB-INF/classes/help/quickstart directory.
When the jsp application is run, by selecting the appropriate link from the main QuickStart API example page, the following report is shown:
Report Generated
The main part of the code is in the getReportUrl method in the ExportDHTMLReport bean. There, the full path to the report template is created and passed to the LookupServlet servlet. The export format is also passed and the LookupServlet will then return the DHTML content.
The following sections show how to create a report programmatically and apply a template, ApplyTemplate.pak (located in the <ERES installation directory>/help/quickstart/templates directory) to the report in an application.
Each section shows the code to generate the report and any steps necessary to deploy.
The following code shows how to create a Summary Break report programmatically (using MapSummaryBreakColInfo.txt as the data source):
// Connect to ERES Server QbReport.setEspressManagerUsed(true); QbReport.useServlet(true); QbReport.setServletRunner(protocol + :// + host + ":" + port); QbReport.setServletContext(quadbase.common.client.ServerMessage.getServletContext()); // Specify Column Mapping ColInfo colInfo[] = new ColInfo[4]; colInfo[0] = new ColInfo(0); colInfo[0].setRowBreak(true); colInfo[1] = new ColInfo(1); colInfo[1].setAggregation(false, ColInfo.NONE); colInfo[2] = new ColInfo(2); colInfo[2].setAggregation(false, ColInfo.SUM); colInfo[3] = new ColInfo(3); colInfo[3].setAggregation(false, ColInfo.SUM); // Create the report and export it as DHTML, return the result as a String QbReport report = new QbReport(null, QbReport.SUMMARY, "help/quickstart/data/MapSummaryBreakColInfo.txt", colInfo, null); ByteArrayOutputStream data = new ByteArrayOutputStream(2048); OutputStream out = new BufferedOutputStream(data); report.export(QbReport.DHTML, out); out.flush();
| Note |
|---|---|
The above piece of code is not complete. The code above is the core ERES Report API code needed to create a Summary Break report, export it to DHTML, and stream the DHTML content back to the client browser. |
The class file for the above source is located in the <ERES installation directory>/WEB-INF/classes/help/quickstart directory.
When the jsp application is run, by selecting the appropriate link from the main QuickStart API example page, the following report is shown:
Report Generated
Please note that when the above code is run, the report generated is of default look and feel without any additional formatting.
The main part of the code is in the getReport method in the MapSummaryBreakColInfo bean. There, a QbReport object called report is created using the specified Column Mapping, the specified Report Type, and the specified Data Source. The following constructor is used:
QbReport(Object parent, int reportType, String dataSource, ColInfo[] columnMapping, String reportTemplate);
You can pass in a different formatting (i.e. different from the default look and feel) by specifying a template during the creation of the QbReport object.
The following code shows how to create a Summary Break report programmatically (using MapSummaryBreakColInfo.txt as the data source and ApplyTemplate.pak as the template):
// Connect to ERES Server QbReport.setEspressManagerUsed(true); QbReport.useServlet(true); QbReport.setServletRunner(protocol + :// + host + ":" + port); QbReport.setServletContext(quadbase.common.client.ServerMessage.getServletContext()); // Specify Column Mapping ColInfo colInfo[] = new ColInfo[4]; colInfo[0] = new ColInfo(0); colInfo[0].setRowBreak(true); colInfo[1] = new ColInfo(1); colInfo[1].setAggregation(false, ColInfo.NONE); colInfo[2] = new ColInfo(2); colInfo[2].setAggregation(false, ColInfo.SUM); colInfo[3] = new ColInfo(3); colInfo[3].setAggregation(false, ColInfo.SUM); // Create the report and export it as DHTML, return the result as a String QbReport report = new QbReport(null, QbReport.SUMMARY, "help/quickstart/data/MapSummaryBreakColInfo.txt", colInfo, "help/quickstart/templates/ApplyTemplate.pak"); ByteArrayOutputStream data = new ByteArrayOutputStream(2048); OutputStream out = new BufferedOutputStream(data); report.export(QbReport.DHTML, out); out.flush();
| Note |
|---|---|
The above piece of code is not complete. The code above is the core ERES Report API code needed to create a Summary Break report, apply a template, export it to DHTML, and stream the DHTML content back to the client browser. |
The class file for the above source is located in the <ERES installation directory>/WEB-INF/classes/help/quickstart directory.
When the jsp application is run by selecting the appropriate link from the main QuickStart API example page, the following report is shown:
Report Generated
The main part of the code is in the getReport method in the ApplyTemplate bean. There, a QbReport object called report is created using the specified Column Mapping, the specified Report Type, the specified Data Source, and the specified Report Template. The following constructor is used:
QbReport(Object parent, int reportType, String dataSource, ColInfo[] columnMapping, String reportTemplate);
The following sections show how to create a chart programmatically and apply a template, ApplyChartTemplate.tpl (located in the <ERES installation directory>/help/quickstart/templates directory) to the report in an application.
Each section shows the code to generate the chart and any steps necessary to deploy.
The following code shows how to create a two-dimensional Column chart programmatically (using CreateColumnChart.txt as the data source):
// Connect to ERES Server QbChart.setEspressManagerUsed(true); QbChart.useServlet(true); QbChart.setServletRunner(protocol + :// + host + ":" + port); QbChart.setServletContext(quadbase.common.client.ServerMessage.getServletContext()); // Specify Column Mapping ColInfo colInfo = new ColInfo(); colInfo.series = 1; colInfo.category = 0; colInfo.value = 2; // Create the chart and export it as PNG QbChart chart = new QbChart(null, QbChart.VIEW2D, QbChart.COL, "help/quickstart/data/CreateColumnChart.txt", false, null, colInfo, null); chart.export(QbChart.PNG, out, 450, 350); out.flush();
| Note |
|---|---|
The above piece of code is not complete. The code above is the core ERES Chart API code needed to create a two-dimensional Column chart, export it to PNG, and stream the PNG content back to the client browser. |
The class file for the above source is located in the <ERES installation directory>/WEB-INF/classes/help/quickstart directory.
When the jsp application is run, by selecting the appropriate link from the main QuickStart API example page, the following chart is shown:
Chart Generated
Please note that when the above code is run, the chart generated is of default look and feel without any additional formatting.
The main part of the code is in the streamChart method in the CreateColumnChart bean. There, a QbChart object called chart is created using the specified Column Mapping, the specified Chart Type and the specified Data Source. The following constructor is used:
QbChart(Applet applet, int dimensionType, int chartType, String dataSource, boolean doTranspose, int[] transposedColumns, ColInfo[] columnMapping, String reportTemplate);
You can pass in a different formatting (i.e. different from the default look and feel) by specifying a template during the creation of the QbChart object.
The following code shows how to create a two-dimensional Column chart programmatically (using CreateColumnChart.txt as the data source and ApplyChartTemplate.tpl as the template):
// Connect to ERES Server QbChart.setEspressManagerUsed(true); QbChart.useServlet(true); QbChart.setServletRunner(protocol + :// + host + ":" + port); QbChart.setServletContext(quadbase.common.client.ServerMessage.getServletContext()); // Specify Column Mapping ColInfo colInfo = new ColInfo(); colInfo.series = 1; colInfo.category = 0; colInfo.value = 2; // Create the chart and export it as PNG QbChart chart = new QbChart(null, QbChart.VIEW2D, QbChart.COL, "help/quickstart/data/CreateColumnChart.txt", false, null, colInfo, "help/quickstart/templates/ApplyChartTemplate.tpl"); chart.export(QbChart.PNG, out, 450, 350); out.flush();
| Note |
|---|---|
The above piece of code is not complete. The code above is the core ERES Chart API code needed to create a two-dimensional column chart, apply a template to it, export it to PNG, and stream the PNG content back to the client browser. |
The class file for the above source is located in the <ERES installation directory>/WEB-INF/classes/help/quickstart directory.
When the jsp application is run by selecting the appropriate link from the main QuickStart API example page, the following chart is shown:
Chart Generated
The main part of the code is in the streamChart method in the QuickStart1042 bean. There, a QbChart object called report is created using the specified Column Mapping, the specified Chart Type, the specified Data Source, and the specified Chart Template. The following constructor is used:
QbChart(Applet applet, int dimensionType, int chartType, String dataSource, boolean doTranspose, int[] transposedColumns, ColInfo[] columnMapping, String reportTemplate);
The following code shows how to modify a report's (and its accompanying sub-report's, drill-down's, and independent chart's) data source, without having to create a new QbReport object. The QbReport object (created from ModifyDataSource.pak) is opened with backup data (this is so that the database is not hit unnecessarily). The data source is then changed to the Access Woodview database.
QbReport.setEspressManagerUsed(true); QbReport.useServlet(true); QbReport.setServletRunner(protocol + :// + host + ":" + port); QbReport.setServletContext(quadbase.common.client.ServerMessage.getServletContext()); // Create the report using the two rows of back-up data QbReport report = new QbReport(object, "help/quickstart/templates/ModifyDataSource.pak", false, false, false, true); modifyDataSource(report); ByteArrayOutputStream data = new ByteArrayOutputStream(2048); OutputStream out = new BufferedOutputStream(data); report.setServletDirectory(quadbase.common.client.ServerMessage.getServletContext()); if (protocol.equalsIgnoreCase("https")) report.setHttpsDynamicExport(true, host, port); else report.setDynamicExport(true, host, port); report.export(QbReport.DHTML, out); out.flush(); static void modifyDataSource(QbReport report) throws Exception { // Begin Code : Specification for new Database String newDatabaseURL = "jdbc:odbc:Woodview";</br> String newDatabaseDriver = "sun.jdbc.odbc.JdbcOdbcDriver";</br> String newDatabaseUserid = "";</br> String newDatabasePassword = "";</br>// End Code : Specification for new Database // Begin Code : Change the data source of the main report and all ancillary templates report.getInputData().setAllDatabaseInfo(newDatabaseURL, newDatabaseDriver, newDatabaseUserid, newDatabasePassword); // End Code : Change the data source of the main report and all ancillary templates }
| Note |
|---|---|
The above piece of code is not complete. The code above is the core ERES Report API code needed to modify the datasource of a report, export it to DHTML, and stream the DHTML content back to the client browser. |
The class file for the above source is located in the <ERES installation directory>/WEB-INF/classes/help/quickstart/classes directory.
When the jsp application is run, by selecting the appropriate link from the main QuickStart API example page, the following report is shown:
Report Generated
The main part of the code is in the getReport method in the ModifyDataSource bean. A QbReport object called report is created there. The data source for the report, sub-report, drill-down, and chart (with independent data source) is changed using the following method in the quadbase.reportdesigner.util.IInputData interface:
setAllDatabaseInfo(String url, String driver, String userid, String password);
The following code shows how to modify a report's (and its accompanying sub-report's) data source, without having to create a new QbReport object. The QbReport object (created from ModifyDataSourceWithSubReport_Acc.pak) is opened with backup data (this is so that the database is not hit unnecessarily). The datasource is then changed from the Access Woodview database to the HSQLDB Woodview database.
// Connect to ERES Server QbReport.setEspressManagerUsed(true); QbReport.useServlet(true); QbReport.setServletRunner(protocol + :// + host + ":" + port); QbReport.setServletContext(quadbase.common.client.ServerMessage.getServletContext()); // Create the report using the two rows of back-up data QbReport report = new QbReport(null, "help/quickstart/templates/Access/ModifyDataSourceWithSubReport_Acc.pak", false, false, false, true); modifyDataSource(report); ByteArrayOutputStream data = new ByteArrayOutputStream(2048); OutputStream out = new BufferedOutputStream(data); report.setServletDirectory(quadbase.common.client.ServerMessage.getServletContext()); if (protocol.equalsIgnoreCase("https")) report.setHttpsDynamicExport(true, host, port); else report.setDynamicExport(true, host, port); report.export(QbReport.DHTML, out); out.flush(); static void modifyDataSource(QbReport report) throws Exception { // Specification for new Database connection String newDatabaseURL = "jdbc:hsqldb:help/examples/DataSources/database/woodview"; String newDatabaseDriver = "org.hsqldb.jdbcDriver"; String newDatabaseUserid = "sa"; String newDatabasePassword = ""; String newDatabaseReportQuery = "select year(o.orderdate) as \"Year\", month(o.orderdate) as \"Month\", count(o.orderid) as \"Orders\", sum(od.quantity) as \"Units Sold\", sum((p.unitprice + od.staincost) * od.quantity) as \"Total Sales\" from orders o, order_details od, products p where o.orderid = od.orderid and p.productid = od.productid group by year(o.orderdate), month(o.orderdate) order by year(o.orderdate), month(o.orderdate);"; String newDatabaseSubReportQuery = "select c.region as \"Region\", year(o.orderdate) as \"Year\", sum((p.unitprice + od.staincost) * od.quantity) as \"Total Sales\" from customers c, orders o, products p, order_details od where c.customerid = o.customerid and o.orderid = od.orderid and od.productid = p.productid group by c.region, year(o.orderdate);"; // Get a handle to the Sub-Report and change its data source SubReportObject subReportObject = (SubReportObject)report.getTable().getHeader().getData("TBL0_HDR_LB0"); QbReport subReport = (QbReport)subReportObject.getSubReport(false, false, true, report); // Get the query and pass in new database info DBInfo newSubReportDatabaseInfo = new DBInfo(newDatabaseURL, newDatabaseDriver, newDatabaseUserid, newDatabasePassword, newDatabaseSubReportQuery); subReport.getInputData().setDatabaseInfo(newSubReportDatabaseInfo); // Change the data source of the main report // Get the query and pass in new database info DBInfo newReportDatabaseInfo = new DBInfo(newDatabaseURL, newDatabaseDriver, newDatabaseUserid, newDatabasePassword, newDatabaseReportQuery); report.getInputData().setDatabaseInfo(newReportDatabaseInfo); }
| Note |
|---|---|
The above piece of code is not complete. The code above is the core ERES Report API code needed to modify the datasource of a report (and its subreport), export it to DHTML, and stream the DHTML content back to the client browser. |
The class file for the above source is located in the <ERES installation directory>/WEB-INF/classes/help/quickstart directory.
When the jsp application is run, by selecting the appropriate link from the main QuickStart API example page, the following report is shown:
Report Generated
The main part of the code is in the getReport method in the ModifyDataSourceWithSubReport bean. There, a QbReport object called report is created. The data source for the report and the sub-report is changed using the following method in the quadbase.reportdesigner.util.IInputData interface:
setDatabaseInfo(IDatabaseInfo db);
The sub-report is obtained by getting a handle to the cell containing the sub-report and then calling the sub-report. The sub-report is also opened using its backup data. This is done by:
SubReportObject subReportObject = (SubReportObject)<;Handle to desired Report Section>;.getData(String ID); (QbReport)subreport = (QbReport)subReportObject.getSubReport( boolean isEnterpriseServer, boolean optimizeMemory, boolean useBackupData, IReport report);
Please note that if you wish to change the data source from the HSQLDB Woodview database to the Access Woodview database, you will need to change the following lines of code from:
QbReport report = new QbReport(object, help/quickstart/templates/Access/ModifyDataSourceWithSubReport_Acc.pak, false, false, false, true); // Begin Code : Specification for new Database String newDatabaseURL = "jdbc:hsqldb:help/examples/DataSources/database/woodview"; String newDatabaseDriver = "org.hsqldb.jdbcDriver"; String newDatabaseUserid = "sa"; String newDatabasePassword = ""; String newDatabaseReportQuery = "select year(o.orderdate) as \"Year\", month(o.orderdate) as \"Month\", count(o.orderid) as \"Orders\", sum(od.quantity) as \"Units Sold\", sum((p.unitprice + od.staincost) * od.quantity) as \"Total Sales\" from orders o, order_details od, products p where o.orderid = od.orderid and p.productid = od.productid group by year(o.orderdate), month(o.orderdate) order by year(o.orderdate), month(o.orderdate);"; String newDatabaseSubReportQuery = "select c.region as \"Region\", year(o.orderdate) as \"Year\", sum((p.unitprice + od.staincost) * od.quantity) as \"Total Sales\" from customers c, orders o, products p, order_details od where c.customerid = o.customerid and o.orderid = od.orderid and od.productid = p.productid group by c.region, year(o.orderdate);";
to
QbReport report = new QbReport(object, help/quickstart/templates/ModifyDataSourceWithSubReport.pak, false, false, false, true); // Begin Code : Specification for new Database String newDatabaseURL = "jdbc:odbc:Woodview"; String newDatabaseDriver = "sun.jdbc.odbc.JdbcOdbcDriver"; String newDatabaseUserid = ""; String newDatabasePassword = ""; String newDatabaseReportQuery = "select year(o.orderdate) as \"Year\", month(o.orderdate) as \"Month\", count(o.orderid) as \"Orders\", sum(od.quantity) as \"Units Sold\", sum((p.unitprice + od.staincost) * od.quantity) as \"Total Sales\" from orders o, [order details] od, products p where o.orderid = od.orderid and p.productid = od.productid group by year(o.orderdate), month(o.orderdate) order by year(o.orderdate), month(o.orderdate);"; String newDatabaseSubReportQuery = "select c.region as \"Region\", year(o.orderdate) as \"Year\", sum((p.unitprice + od.staincost) * od.quantity) as \"Total Sales\" from customers c, orders o, products p, [order details] od where c.customerid = o.customerid and o.orderid = od.orderid and od.productid = p.productid group by c.region, year(o.orderdate);";
The following code shows how to modify a report's and its accompanying sub-report's parameterized data sources, without having to create a new QbReport object. The QbReport object (created from ModifyDataSourceParameterized_Acc.pak) is opened with backup data (this is so that the database is not hit unnecessarily). The datasource is then changed from the Access Woodview database to the HSQLDB Woodview database.
// Connect to ERES Server QbReport.setEspressManagerUsed(true); QbReport.useServlet(true); QbReport.setServletRunner(protocol + :// + host + ":" + port); QbReport.setServletContext(quadbase.common.client.ServerMessage.getServletContext()); // Open the report using the two rows of backup data QbReport report = new QbReport(null, "help/quickstart/templates/Access/ModifyDataSourceParameterized_Acc.pak", false, false, false, true); changeDataSource(report); report.setServletDirectory (quadbase.common.client.ServerMessage.getServletContext()); if (protocol.equalsIgnoreCase("https")) report.setHttpsDynamicExport(true, host, port); else report.setDynamicExport(true, host, port); ByteArrayOutputStream data = new ByteArrayOutputStream(2048); OutputStream out = new BufferedOutputStream(data); report.export(QbReport.DHTML, out); out.flush(); static void changeDataSource(QbReport report) throws Exception { <div class="dir"> // Specification for the new Database String URL = "jdbc:hsqldb:help/examples/DataSources/database/woodview"; String driver = "org.hsqldb.jdbcDriver"; String userid = "sa"; String passwd = ""; String newDatabaseSubReportQuery = "select c.categoryname as \"Category\", p.productname as \"Product\", cu.region as \"Region\", sum((od.staincost + p.unitprice) * od.quantity) as \"Sales\" from categories c, products p, customers cu, orders o, order_details od where c.categoryid = p.categoryid and p.productid = od.productid and cu.customerid = o.customerid and o.orderid = od.orderid and c.categoryname IN (:category) group by c.categoryname, p.productname, cu.region"; // Get a handle to the SubReport and change its datasource SubReportObject subReportObject = (SubReportObject)report.getTable().getHeader().getData("TBL0_HDR_SRPT0"); QbReport subReport = (QbReport)subReportObject.getSubReport(false, false, true, report); // Get the parameter information of the subreport // and pass in the new database information along with the parameter information IQueryInParam[] subReportParameters = ((IQueryFileInfo)subReport.getInputData().getDatabaseInfo()).getInParam(); SimpleQueryFileInfo subReportInfo = new SimpleQueryFileInfo(URL, driver, userid, passwd, newDatabaseSubReportQuery); subReportInfo.setInParam(subReportParameters); subReport.getInputData().setDatabaseInfo(subReportInfo); // Get the parameter information of the main report and pass // in the new database information along with the parameter information // Pass in the parameter values for the main report (which is // then picked up by the subreport) Vector paramValues = new Vector(); paramValues.addElement(new String("Arm Chairs")); paramValues.addElement(new String("Double Dressers")); paramValues.addElement(new String("Round Tables")); // Get the parameter properties information and pass in the // value of the parameter IQueryInParam[] reportParameters = ((IQueryFileInfo)report.getInputData().getDatabaseInfo()).getInParam(); ((IQueryMultiValueInParam)reportParameters[0]).setValues(paramValues); // Get the query for the main report from the report template itself // instead of passing in a new query SimpleQueryFileInfo reportInfo = new SimpleQueryFileInfo(URL, driver, userid, passwd, report.getInputData().getDatabaseInfo().getQuery()); reportInfo.setInParam(reportParameters); report.getInputData().setDatabaseInfo(reportInfo); </div> }
| Note |
|---|---|
The above piece of code is not complete. The code above is the core ERES Report API code needed to modify the datasource of a parameterized report (and its parameterized subreport), export it to DHTML, and stream the DHTML content back to the client browser. |
The class file for the above source is located in the <ERES installation directory>/WEB-INF/classes/help/quickstart directory.
When the jsp application is run, by selecting the appropriate link from the main QuickStart API example page, the following report is shown:
Report Generated
The main part of the code is in the getReport method in the ModifyDataSourceParameterized bean. There, a QbReport object called report is created. The data source for the report and the sub-report is changed using the following method in the quadbase.reportdesigner.util.IInputData interface:
setDatabaseInfo(IDatabaseInfo db);
However, because both reports use a parameterized report, SimpleQueryFileInfo was used to pass in the database connection information, as well as the parameter properties information. An object of the class SimpleQueryFileInfo (with the required information) is then passed as the argument for the setDatabaseInfo() method.
The parameter information is obtained by getting the database connection information (by called the getDatabaseInfo() method), casting it to IQueryFileInfo and using the following method in IQueryFileInfo:
public IQueryInParam[] getInParam();The above method call returns the complete parameter properties information for all the parameters defined in the report.
The code also passes in the values for the parameters directly into the report, rather than have the user be prompted to specify the values. The values are entered by going to each parameter and specifying them before passing them to the report object. The following method, in IQueryInParam, is used to specify the value:
public void setValue(Object value);
The sub-report is obtained by getting a handle to the cell containing the sub-report and then calling the sub-report. The sub-report is also opened using its backup data. This is done by:
SubReportObject subReportObject = (SubReportObject)<Handle to desired Report Section>.getData(String ID); (QbReport)subreport = (QbReport)subReportObject.getSubReport(boolean isEnterpriseServer, boolean optimizeMemory, boolean useBackupData, IReport report);
Please note that if you wish to change the data source from the HSQLDB Woodview database to the Access Woodview database, you will need to change the following lines of code from:
QbReport report = new QbReport(object, help/quickstart/templates/Access/ModifyDataSourceParameterized_Acc.pak, false, false, false, true); // Specification for the new Database String newDatabaseURL = "jdbc:hsqldb:help/examples/DataSources/database/woodview"; String newDatabaseDriver = "org.hsqldb.jdbcDriver"; String newDatabaseUserid = "sa"; String newDatabasePassword = ""; String newDatabaseSubReportQuery = "select c.categoryname as \"Category\", p.productname as \"Product\", cu.region as \"Region\", sum((od.staincost + p.unitprice) * od.quantity) as \"Sales\" from categories c, products p, customers cu, orders o, order_details od where c.categoryid = p.categoryid and p.productid = od.productid and cu.customerid = o.customerid and o.orderid = od.orderid and c.categoryname IN (:category) group by c.categoryname, p.productname, cu.region;";
to
QbReport report = new QbReport(object, help/quickstart/templates/ModifyDataSourceParameterized.pak, false, false, false, true); // Specification for the new Database String newDatabaseURL = "jdbc:odbc:Woodview"; String newDatabaseDriver = "sun.jdbc.odbc.JdbcOdbcDriver"; String newDatabaseUserid = ""; String newDatabasePassword = ""; String newDatabaseSubReportQuery = "select c.categoryname as \"Category\", p.productname as \"Product\", cu.region as \"Region\", sum((od.staincost + p.unitprice) * od.quantity) as \"Sales\" from categories c, products p, customers cu, orders o, [order details] od where c.categoryid = p.categoryid and p.productid = od.productid and cu.customerid = o.customerid and o.orderid = od.orderid and c.categoryname IN (:category) group by c.categoryname, p.productname, cu.region;";
You can also pass in a java.sql.Connection object, instead of passing in the URL, driver, userid and password of the database. For example, if you wish to pass in a Connection object, conn, you would need to change the following lines of code:
SimpleQueryFileInfo subReportInfo = new SimpleQueryFileInfo(URL, driver, userid, passwd, newDatabaseSubReportQuery); SimpleQueryFileInfo reportInfo = new SimpleQueryFileInfo(URL, driver, userid, passwd, report.getInputData().getDatabaseInfo().getQuery());
to
SimpleQueryFileInfo subReportInfo = new SimpleQueryFileInfo(conn, newDatabaseSubReportQuery); SimpleQueryFileInfo reportInfo = new SimpleQueryFileInfo(conn, report.getInputData().getDatabaseInfo().getQuery());
The following code shows how to modify certain elements of the report programmatically. The QbReport object is created from ModifyReportElements.pak.
// Connect to ERES Server QbReport.setEspressManagerUsed(true); QbReport.useServlet(true); QbReport.setServletRunner(protocol + :// + host + ":" + port); QbReport.setServletContext(quadbase.common.client.ServerMessage.getServletContext()); QbReport report = new QbReport(null, "help/quickstart/templates/ModifyReportElements.pak"); modifyElements(report); ByteArrayOutputStream data = new ByteArrayOutputStream(2048); OutputStream out = new BufferedOutputStream(data); report.export(QbReport.DHTML, out); out.flush(); static void modifyElements(QbReport report) { // Set Dual colors int numberOfColumns = report.getTable().getColumnCount(); for (int i = 0; i < numberOfColumns; i++) { <div class="dir"> ReportColumn column = report.getTable().getColumn(i); column.setAlternateRow(1); column.setBgColor2(new Color(245,245,238)); column.setFontColor2(new Color(0,0,0)); column.setFont2(new Font("Dialog", Font.PLAIN, 8)); </div> } // Add title to Report Header ReportCell title = new ReportCell(); title.setText("Top 10 Customers"); title.setBgColor(new Color(255,255,255)); title.setFontColor(new Color(0,54,100)); title.setFont(new Font("Dialog", Font.BOLD, 14)); title.setAlign(IAlignConstants.ALIGN_LEFT); title.setWidth(2.1); title.setHeight(0.4); title.setX(0); title.setY(0); report.getReportHeader().addData(title); // Add a formula ReportCell formulaCell = new ReportCell(); Formula formula = new Formula("totalSales", "sum({Total Sales})"); report.addFormula(formula); formulaCell.setFormulaObj(formula); formulaCell.setBgColor(new Color(255,255,255)); formulaCell.setFontColor(new Color(0,0,0)); formulaCell.setFont(new Font("Dialog", Font.BOLD, 8)); formulaCell.setAlign(IAlignConstants.ALIGN_LEFT); formulaCell.setWidth(1.0); formulaCell.setHeight(0.25); formulaCell.setX(4.1); formulaCell.setY(0); report.getReportFooter().addData(formulaCell); // Add a label ReportCell label = new ReportCell(); label.setText("Total sales for top 10 customers:"); label.setBgColor(new Color(255,255,255)); label.setFontColor(new Color(0,54,100)); label.setFont(new Font("Dialog", Font.BOLD, 8)); label.setAlign(IAlignConstants.ALIGN_RIGHT); label.setWidth(2.1); label.setHeight(0.4); label.setX(1.9); label.setY(0); report.getReportFooter().addData(label);
| Note |
|---|---|
The above piece of code is not complete. The code above is the core ERES Report API code needed to modify the various elements of a report, export it to DHTML, and stream the DHTML content back to the client browser. |
The class file for the above source is located in the <ERES installation directory>/WEB-INF/classes/help/quickstart directory.
When the jsp application is run, by selecting the appropriate link from the main QuickStart API example page, the following report is shown:
Report Generated
The main part of the code is in getReport method in the ModifyReportElements bean. Report properties such as Dual Color are turned on and a formula, a label and a title are added to the QbReport object.
Dual colors are set using the following code. The dual color property is set for each table column and the alternate background color, font color and font are specified.
<Desired Report Column>.setAlternateRow(int numberOfRowsBeforeAlternateColor);
<Desired Report Column>.setBgColor2(Color alternateBackgroundColor);
<Desired Report Column>.setFontColor2(Color alternateFontCOlor);
<Desired Report Column>.setFont2(Font alternateFont);Adding a title to the Report Header is done using the following code. The ReportCell object is first created, the title text then set and the ReportCell properties such as height, width, xPosition and yPosition are specified before adding it to the Report Header section.
ReportCell title = new ReportCell(); title.setText(String text); title.setBgColor(Color backgroundColor); title.setFontColor(Color fontColor); title.setFont(Font font); title.setAlign(int alignment); title.setWidth(double width); title.setHeight(double height); title.setX(double xPosition); title.setY(double yPosition); <Handle to desired Report Section>.addData(title);
A formula and a label are specified likewise. A ReportCell object is first created, the formula or label set and the ReportCell properties specified before adding the newly created formula or label ReportCell object to the appropriate section.
ReportCell formulaCell = new ReportCell(); Formula formula = new Formula(String formulaName, String formulaText); report.addFormula(formula); formulaCell.setFormulaObj(formula); formulaCell.setBgColor(Color backgroundColor); formulaCell.setFontColor(Color fontColor); formulaCell.setFont(Font font); formulaCell.setAlign(int alignment); formulaCell.setWidth(double width); formulaCell.setHeight(double height); formulaCell.setX(double xPosition); formulaCell.setY(double yPosition); <Handle to desired Report Section>.addData(formulaCell); ReportCell label = new ReportCell(); label.setText(String text); label.setBgColor(Color backgroundColor); label.setFontColor(Color fontColor);<br /> label.setFont(Font font); label.setAlign(int alignment); label.setWidth(double width); label.setHeight(double height); label.setX(double xPosition); label.setY(double yPosition); <Handle to desired Report Section>.addData(label);
The following sections show how to run a pre-existing template (PassInParameterValues.pak located in the <ERES installation directory>/help/quickstart/templates directory) which uses a parameterized query as the data source.
Each section shows the code to generate the report and any steps necessary to deploy.
The following code shows how to display an existing report template (in this case PassInParameterValues.pak), which uses a parameterized query. The parameter values are passed when creating the report.
// Connect to ERES Server QbReport.setEspressManagerUsed(true); QbReport.useServlet(true); QbReport.setServletRunner(protocol + :// + host + ":" + port); QbReport.setServletContext(quadbase.common.client.ServerMessage.getServletContext()); // Set query parameters Vector vec = new Vector(); vec.addElement("CA"); vec.addElement("NY"); Object queryParams[] = new Object[3]; queryParams[0] = vec; queryParams[1] = new Date(101, 0, 4); queryParams[2] = new Date(103, 01, 12); // Set formula parameter Object formulaParams[] = new Object[1]; formulaParams[0] = "Ivan"; // Create new Report object using specified report and parameter values QbReport report = new QbReport (null, "help/quickstart/templates/PassInParameterValues.pak", queryParams, formulaParams); ByteArrayOutputStream data = new ByteArrayOutputStream(2048); OutputStream out = new BufferedOutputStream(data); report.export(QbReport.DHTML, out); out.flush();
| Note |
|---|---|
The above piece of code is not complete. The code above is the core ERES Report API code needed to open a parameterized report, pass the parameter values to the report. Export it to DHTML and stream the DHTML content back to the client browser. |
The class file for the above source is located in the <ERES installation directory>/WEB-INF/classes/help/quickstart directory.
When the jsp application is run by selecting the appropriate link from the main QuickStart API example page, the following report is shown:
Report Generated
The main part of the code is in the getReport method in the PassInParameterValues bean. There, a QbReport object called report is created using the PassInParameterValues.pak template. The parameter values are passed using the following constructor:
QbReport(Object parent, String reportTemplateName, Object[] queryParameterValues, Qbject[] formulaParamterValues);
Both query parameter and formula parameter values are declared in the same order as the parameters were defined. Query parameters which take in multiple values are declared as a Vector object which then contains the different multiple values.
In addition to the above approach, you can simply pass in the name of the template and the parameter values, then the export format desired to the LookupServlet and let it do the work.
The following code shows how to pass in parameter values to an existing parameterized report template (in this case PassInParameterValues.pak) using the LookupServlet:
String contextPath = quadbase.common.client.ServerMessage.getServletContext(); // Based on the ERES context, get the http location of the files used in this example int lastSlash = contextPath.lastIndexOf(/); String eresPath = contextPath.substring(0, lastSlash); String domain = protocol + "://" + host + ":" + port; return domain + contextPath + "/LookupServlet?USESESSION=TRUE&URLTYPE=FORREPORT&" + "TemplatePath=" + domain + eresPath + "/help/quickstart/templates/PassInParameterValues.pak&MultiPageExport=false" + "&QueryParamName=State&QueryParamSize=2&QueryParamValue=CA&QueryParamValue=NY &QueryParamName=StartDate&QueryParamSize=1&QueryParamValue=2001-01-04 &QueryParamName=EndDate&QueryParamSize=1&QueryParamValue=2003-02-12 &FormulaParamName=Name&FormulaParamValue=Ivan";
| Note |
|---|---|
The above piece of code is not complete. The code above is the core ERES API code needed to pass an existing report template and the export format to the LookupServlet servlet. |
The class file for the above source is located in the <ERES installation directory>/WEB-INF/classes/help/quickstart directory.
The main part of the code is in the getReportUrl method in the PassInParameterValues bean. There, the full path to the report template is created and passed to the LookupServlet servlet. The export format is also passed and the LookupServlet will then return the DHTML content.
Please note that if you are using the Access templates, you will need to change the following lines of code from:
queryParams[1] = new Date(101, 0, 4); queryParams[2] = new Date(103, 01, 12);
to
queryParams[1] = new Timestamp(101, 0, 4, 0, 0, 0, 0); queryParams[2] = new Timestamp(103, 01, 12, 0, 0, 0, 0);
In addition to the above, you can also pass in parameter values using the getAllParameters method in QbReport. The following code shows how to display an existing report template (in this case PassInParameterValues.pak), which uses a parameterized query, in an application. The report is opened with backup data (to avoid the initial hit to the database) and the parameter values are set. The report is then refreshed with the data.
// Connect to ERES Server QbReport.setEspressManagerUsed(true); QbReport.useServlet(true); QbReport.setServletRunner(protocol + :// + host + ":" + port); QbReport.setServletContext(quadbase.common.client.ServerMessage.getServletContext()); // Set query parameters Vector vec = new Vector(); vec.addElement("CA"); vec.addElement("NY"); Object queryParams[] = new Object[3]; queryParams[0] = vec; queryParams[1] = new Date(101, 0, 4); queryParams[2] = new Date(103, 01, 12); // Set formula parameter Object formulaParams[] = new Object[1]; formulaParams[0] = "Ivan"; // Create new Report object using backup data QbReport report = new QbReport (null, "help/quickstart/templates/PassInParameterValues.pak", false, false, false, true); report.getAllParameters().get(0).setValues((Vector)queryParams[0]); report.getAllParameters().get(1).setValue(queryParams[1]); report.getAllParameters().get(2).setValue(queryParams[2]); report.getAllParameters().get(3).setValue(formulaParams[0]); report.refreshWithOriginalData(); ByteArrayOutputStream data = new ByteArrayOutputStream(2048); OutputStream out = new BufferedOutputStream(data); report.export(QbReport.DHTML, out); out.flush();
| Note |
|---|---|
The above piece of code is not complete. The code above is the core ERES Report API code needed to open a parameterized report and to pass the parameter values to the report. Export it to DHTML and stream the DHTML content back to the client browser. |
The class file for the above source is located in the <ERES installation directory>/WEB-INF/classes/help/quickstart directory.
When the jsp application is run, by selecting the appropriate link from the main QuickStart API example page, the following report is shown:
Report Generated
The main part of the code is in the getReport method in the QuickStart1072 bean. There, a QbReport object called report is created using the PassInParameterValues.pak template. The parameter values are passed using the following interface in QbReport:
getAllParameters()
Both query parameter and formula parameter values are declared in the same order as the parameters were defined. Query parameters which take in multiple values are declared as a Vector object which then contains the different multiple values.
Please note that if you are using the Access templates, you will need to change the following lines of code from:
queryParams[1] = new Date(99, 0, 4); queryParams[2] = new Date(101, 01, 12);
to
queryParams[1] = new Timestamp(99, 0, 4, 0, 0, 0, 0); queryParams[2] = new Timestamp(101, 01, 12, 0, 0, 0, 0);
The following code shows how to display an existing report template (in this case PassInParameterValues.pak), which uses a parameterized query, in a servlet. The servlet creates the QbReport object by opening the template using back-up data. An HTML page is then streamed asking for the parameter values. These values are passed to another servlet (the ParamReportGeneratorServlet servlet) and the QbReport object is created, from the given template, with the specified parameter values.
// Connect to ERES Server QbReport.setEspressManagerUsed(true); QbReport.useServlet(true); QbReport.setServletRunner(protocol + :// + host + ":" + port); QbReport.setServletContext(quadbase.common.client.ServerMessage.getServletContext()); String reportLocation = "help/quickstart/templates/PassInParameterValues.pak"; // Create the ObReport object using back-up data QbReport report = new QbReport(null, reportLocation, false, false, false, true, false); // Specify the parameters for connecting to ParamReportGeneratorServlet if (protocol.equalsIgnoreCase("https")) report.setHttpsDynamicExport(true, host, port); else report.setDynamicExport(true, host, port); report.setServletDirectory (quadbase.common.client.ServerMessage.getServletContext()); // Specify report template location and export format desired ParameterPage paramPage = report.getParameterPage(reportLocation, null, QbReport.DHTML, null); return paramPage.toHtmlString();
| Note |
|---|---|
The above piece of code is not complete. The code above is the core ERES Report API code to open a parameterized report template, create a dynamic HTML page that asks for the input parameter values and stream that HTML page back to the client. |
The class file for the above source is located in the <ERES installation directory>/WEB-INF/classes/help/quickstart directory.
When the jsp application is run, by selecting the appropriate link from the main QuickStart API example page, the following HTML page is shown:
HTML Prompt Page Generated
Depending on what parameter values you pass in, a report similar to the following will be shown:
Report Genererated
The main part of the code is in the getReport method in the QuickStart1073 bean. There, a QbReport object called report is created using the PassInParameterValues.pak template. The dynamic export is then called to use the ParamReportGeneratorServlet (provided with ERES) using the following lines:
<QbReport object>.setDynamicExport( boolean isDynamicExport,String serverName, int servletRunnerPort); String htmlParamPage = <QbReport object>.getHTMLParamPage( String reportLocation, int exportFormat);
| Note |
|---|---|
If you are using HTTPS protocol, you have to call |
The HTML file asking for the parameter values is then generated and passed to the client browser.
In addition to the above approach, you can simply pass in the name of the template and the export format desired to the LookupServlet and let it do the work.
The following code shows how to pass in an existing parameterized report template (in this case PassInParameterValues.pak) and have a HTML page asking for the input parameter values be generated, using the LookupServlet:
String contextPath = quadbase.common.client.ServerMessage.getServletContext(); // Based on the ERES context, get the http location of the files used in this example int lastSlash = contextPath.lastIndexOf(/); String eresPath = contextPath.substring(0, lastSlash); String domain = protocol + "://" + host + ":" + port; return domain + contextPath + "/LookupServlet?USESESSION=TRUE&URLTYPE=FORREPORT&" + "TemplatePath=" + domain + eresPath + "/help/quickstart/templates/QuickStart54.rpt&MultiPageExport=false" + "&ForHTMLParamPage=TRUE";
| Note |
|---|---|
The above piece of code is not complete. The code above is the core ERES API code needed to pass an existing report template and the export format to the LookupServlet servlet. |
The class file for the above source is located in the <ERES installation directory>/WEB-INF/classes/help/quickstart directory.
The main part of the code is in the getReportUrl method in the QuickStart1073 bean. There, the full path to the report template is created and passed to the LookupServlet servlet. The export format is also passed and the LookupServlet will then return the DHTML content.
The following code shows how to display an existing report template (in this case DeployExportDrillDown.pak), which is a drill-down report, in a servlet. The servlet creates the QbReport object by opening the template. A DHTML page, showing the contents of the first level, is then streamed. The next level report is obtained by clicking on the links. These links point to the DrillDownReportServlet. The values of the link (clicked on) are passed to the DrillDownReportServlet and the next level report (based on those values) is then generated and then streamed to the client.
// Connect to ERES Server QbReport.setEspressManagerUsed(true); QbReport.useServlet(true); QbReport.setServletRunner(protocol + :// + host + ":" + port); QbReport.setServletContext(quadbase.common.client.ServerMessage.getServletContext()); //Specify the template location String reportLocation = "help/quickstart/templates/DeployExportDrillDown.pak"; // Create the QbReport object using the template QbReport report = new QbReport (null, reportLocation); // Specify the parameters for connecting to DrillDownReportServlet report.setServletDirectory(quadbase.common.client.ServerMessage.getServletContext()); if (protocol.equalsIgnoreCase("https")) report.setHttpsDynamicExport(true, host, port); else report.setDynamicExport(true, host, port); // Export the report ByteArrayOutputStream data = new ByteArrayOutputStream(2048); OutputStream out = new BufferedOutputStream(data); report.export(QbReport.DHTML, out);
| Note |
|---|---|
The above piece of code is not complete. The code above is the core ERES Report API code needed to open a drill-down report, export it to DHTML, and stream the DHTML content back to the client browser. |
The class file for the above source is located in the <ERES installation directory>/WEB-INF/classes/help/quickstart directory.
When the jsp application is run by selecting the appropriate link from the main QuickStart API example page, the following report is shown:
Report generated
Depending on what link you click, a report similar to the following will be shown:
Report Generated After Clicking on a Link
Again, depending on what link you click, a report similar to the following will be shown:
Report Generated After Clicking on a Link
The main part of the code is in the getReport method in the DeployExportDrillDown bean. There, a QbReport object called report is created using the DeployExportDrillDown.pak template. The dynamic export is then called to use the DrillDownReportServlet (provided with ERES) using the following lines:
<QbReport object>.setDynamicExport( boolean isDynamicExport, String serverName, int servletRunnerPort);
| Note |
|---|---|
If you are using HTTPS protocol, you have to call |
The DHTML file for the top level report is then generated and passed
to the OutputStream
<QbReport object>.export(int exportFormat, OutputStream out);
In addition to the above approach, you can simply pass in the name of the template and the export format desired to the LookupServlet and let it do the work.
The following code shows how to pass in an existing drill-down report template (in this case DeployExportDrillDown.pak) and have a DHTML page be generated, using the LookupServlet:
String contextPath = quadbase.common.client.ServerMessage.getServletContext(); // Based on the ERES context, get the http location of the files used in this example int lastSlash = contextPath.lastIndexOf(/); String eresPath = contextPath.substring(0, lastSlash); String domain = protocol + "://" + host + ":" + port; return domain + contextPath + "/LookupServlet?USESESSION=TRUE&URLTYPE=FORREPORT&" + "TemplatePath=" + domain + eresPath + "/help/quickstart/templates/DeployExportDrillDown.pak&MultiPageExport=false";
| Note |
|---|---|
The above piece of code is not complete. The code aboveis the core ERES API code needed to pass an existing report template and the export format to the LookupServlet servlet. |
The class file for the above source is located in the <ERES installation directory>/WEB-INF/classes/help/quickstart directory.
The main part of the code is in the getReportUrl method in the DeployExportDrillDown bean. There, the full path to the report template is created and passed to the LookupServlet servlet. The export format is also passed and the LookupServlet will then return the DHTML content.
The following code shows how to launch the Report Designer (in default mode) via the API:
String host = getParameter(host); int port = Integer.parseInt(getParameter("port")); String eresPath = getParameter("servletContext"); // Specify the connection information for Report Designer to connect to ERES server. QbReportDesigner.useServlet(true); QbReportDesigner.setServletRunner(protocol + "://" + host + ":" + port); QbReportDesigner.setServletContext(eresPath); // Use toolbar icons in the JAR file QbReportDesigner.setUseSysResourceImages(true); // Create and display the Report Designer object QbReportDesigner designer = new QbReportDesigner(this); designer.setVisible(true);
| Note |
|---|---|
The above piece of code is not complete. The code above is the core ERES Report API code needed to open the Report Designer. |
The class file for the above source is located in the <ERES installation directory>/help/quickstart/classes directory.
When the jsp application is run, by selecting the appropriate link from the main QuickStart API example page, Report Designer (in default mode) will start.
The main part of the code is in the LaunchReportDesigner java code. There, a QbReportDesigner object called designer is created and shown:
QbReportDesigner(Object parent); <QbReportDesigner object>.setVisible(boolean b);