To create a new report, you must specify the report type, the input data source information, and a mapping of the data columns to the respective columns of the report. In this appendix, we look at the various report types and the methods used to map the columns. There are also a number of fully functional examples in this appendix.
Please note that unless otherwise noted, all examples use the Woodview HSQL database, which is located in the <EspressReportInstall>/help/examples/DataSources/database directory. In order to run the examples, you will need to add database HSQL JDBC driver (hsqldb.jar) to your classpath. The driver is located in the <EspressReportInstall>/lib directory.
There are five basic report types: Simple Columnar, Summary Break, Cross Tab, Master & Details, and Mailing Labels reports. Every report has a column mapping defined as well as properties specific to the type of the report.
The column mapping is done in the API using the ColInfo class (located in the quadbase.reportdesigner.util package). You define the column mapping by declaring a ColInfo array object. Each element in the array represents the column's respective column. You can also assign specific properties for each element of the ColInfo array object, depending on the type of the report desired, and create the report.
The QbReport class contains numerous constructors. However, most constructors can be broken down into three sets of parameters. The first set is only the parent object. The parent object must be set if the report is to be used in an applet context, it can be null otherwise. The second set of parameters are always required and they determine the type of report you are constructing, the data source, and the mapping. There is a great deal of information in the two Appendices regarding this group of parameters. Finally, the third set of parameters are options and properties. For example, most constructors have the option to specify a template, although it is not necessary. Some options are specific to certain report types, for example the boolean parameter sideBySideLayout is only relevant if you are creating a Master & Details report. Some constructors contain a Properties parameter that allows you to set multiple properties and group them into one properties object. For more information on the various options and properties, please see the APIDocs.
![[Caution]](../../../images/caution.png) | Caution |
|---|---|
As you may know, creating objects in java is a resource intensive operation. It is always recommended that you do not create too many objects. One way to conserve resource is to reuse |
The Simple Columnar report is the most basic of all the types supported by EspressReport. It presents columnar data in a single table without any groupings, or breaks.
Here is an example:
| Product Type | Product Name | Price |
|---|---|---|
| 12 | Chair | $20 |
| 12 | Table | $30 |
| 14 | Cabinet | $20 |
| 14 | Table | $50 |
With Simple Columnar reports, all you need to do is define the ColInfo array object with respect to the data source. For instance, given the dataset below:
String dataType[] = {"String", "String", "String"};
String fieldName[] = {"Product Type", "Product Name", "Price"};
String records[][] = {{"12", "Chair", "$20"},
{"12", "Table", "$30"},
{"14", "Cabinet", "$20"},
{"14", "Table", "$50"}};
The column mapping is set so that the columns of the data source are mapped to the report. Thus Column Zero of the report corresponds to Column Zero of the data source, Column One of the report to Column One of the data source and so on and so forth. Thus, the following report is created:
| Product Type | Product Name | Product Price |
|---|---|---|
| 12 | Chair | $20 |
| 12 | Table | $30 |
| 14 | Cabinet | $20 |
| 14 | Table | $50 |
Report after mapping and setting the Table Header Names
To generate the aforementioned report, the following column mapping has to be set using the API:
ColInfo[] colInfo = new ColInfo[3];
colInfo[0] = new ColInfo(0);
colInfo[0].setName("Product Type");
colInfo[1] = new ColInfo(1);
colInfo[1].setName("Product Name");
colInfo[2] = new ColInfo(2);
colInfo[2].setName("Product Price");
In the above code, the names for the columns are also changed to Product Type, Product Name and Product Price respectively rather than keeping the Header names defined in the original data.
The column mapping need not be in order or follow the same order as the data. You can also selectively choose the columns you desire. For instance, given a data source that has multiple columns, you can write the following code:
ColInfo[] colInfo = new ColInfo[3];
colInfo[0] = new ColInfo(2);
colInfo[0].setName("Product Name");
colInfo[1] = new ColInfo(7);
colInfo[1].setName("Units Sold");
colInfo[2] = new ColInfo(4);
colInfo[2].setName("Unit Price");
Here, the Column 0 of the report is mapped to column 2 of the data source, Column 1 of the report is mapped to column 7 of the data source and Column 2 of the report is mapped to column 4 of the data source. In addition to setting the column mappings, the above example also sets the Table Header for each column.
Constructing a Simple Columnar report is relatively straight forward. We have already discussed how to set the ColInfo array and how to obtain the data in previous sections. The following code demonstrates how to create a Simple Columnar report.
// Data passed in an array in memory
DbData data = new DbData(dataType, fieldName, records);
// Set Column Mapping
ColInfo colInfo[] = new ColInfo[3];
colInfo[0] = new ColInfo(0);
colInfo[0].setName("Product Type");
colInfo[1] = new ColInfo(1);
colInfo[1].setName("Product Name");
colInfo[2] = new ColInfo(2);
colInfo[2].setName("Product Price");
// Create Report
QbReport report = new QbReport
(parent, // Parent
QbReport.COLUMNAR, // Type of Report
data, // Data
colInfo, // Column Mapping - use the column mapping in the template
"SimpleColumnar.rpt"); // Template Name
The Exported Unformatted Results link shows how the report appears when it was first created without any formatting or templates. The Exported Formatted Results presents the outcome after applying the template.
Even though this example creates a report from scratch, we still recommend that you apply a template. If you look at the source code, notice the large commented section after the QbReport constructor. This section modifies the visual appearance of the report in exactly the same way as applying the template. As you can imagine, writing the code takes much more effort since there is no visual aid to help you gauge position and dimensions.
When you apply a template, you have the option of using the column mapping stored in the template. In the above example, you could replace the colInfo in the constructor with null and it would still generate the same report. This is because the column mapping in the template is exactly the same. If you set the ColInfo and apply a template, the constructed report will use the ColInfo you supplied and ignore the column mapping in the template.
For a Summary Break report, you need to define a Row Break. The Row Break defines when to break the report and insert column summaries. The report essentially gets “broken” everytime the data in the “break” column changes. An aggregation must be set for any columns that are not specified as Row Breaks, even if the columns are non-numeric (set the aggregation to NONE). This is so that column summaries can be generated based on the aggregation. For instance, given the dataset below:
| Order # | Product | Quantity |
|---|---|---|
| 12 | Chair | 2 |
| 12 | Table | 3 |
| 14 | Cabinet | 2 |
| 14 | Table | 5 |
Original Data
If Column 0 (Order #) is assigned as Row Breaks, the Aggregation for Column 1 (Product) is set to NONE, and the Aggregation for Column 2 (Quantity) is set to SUM, we get the following report.
| Order # | Product | Quantity |
|---|---|---|
| 12 | Chair | 2 |
| Table | 3 | |
| 5 | ||
| 14 | Cabinet | 2 |
| Table | 5 | |
| 7 |
Report after applying Row Break and Aggregation
Setting the column mapping for Summary Break reports requires that two more properties to be set than the Simple Columnar report. First, the row break columns must be specified for row group columns. Second, columns that are not row break columns must specify the aggregation type. For example, to generate the report in the previous section, you need the set the following lines of code:
ColInfo[] colInfo = new ColInfo[3];
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);
Again, the first step is to map the column in the report with the column from the data source. Then, Column Zero is set to be a row break column. Since Columns One and Two are not set as row break columns, you must set an aggregation for them even if it is NONE. There are two parameters in the setAggregation method, the first parameter determines if the report uses column aggregation. If column aggregation is used, the individual rows of data will be hidden and only the aggregation will be displayed. If you set the first parameter to true, the other non row break column must also be set to use column aggregation. The second parameter determines the type of aggregation. Here is a list of all available aggregations in the ColInfo class:
| AVG | MAX | STDDEV |
| COUNT | MEDIAN | SUM |
| COUNTDISTINCT | MIN | SUMSQUARE |
| FIRST | NONE | VARIANCE |
| LAST |
The following example shows how to create a Summary Break report:
ColInfo[] colInfo = new ColInfo[5];
colInfo[0] = new ColInfo(1);
colInfo[0].setRowBreak(true);
colInfo[1] = new ColInfo(9);
colInfo[1].setRowBreak(true);
colInfo[2] = new ColInfo(4);
colInfo[2].setAggregation(true, ColInfo.SUM);
colInfo[3] = new ColInfo(5);
colInfo[3].setAggregation(true, ColInfo.SUM);
colInfo[4] = new ColInfo(6);
colInfo[4].setAggregation(true, ColInfo.SUM);
QbReport report = new QbReport
(parent, // parent
QbReport.SUMMARY, // Sumamry Break Report
"sample.dat", // filename
colInfo, // column information
null);
try {
// Apply template including scripts and formula
report.applyTemplate("SummaryBreak.rpt", true);
} catch (Exception e) {
e.printStackTrace();
}
The Exported Unformatted Results link shows how the report appears when it was first created without any formatting or templates. The Exported Formatted Results presents the outcome after applying the template.
First thing you may notice is that the column mapping is different from the previous examples. This is often the case when using a text file as the data source. In this example, we are mapping column 0 from the report with column 1 in the text file, column 1 from the report with column 9 in the text file, and so on.
Also, notice that the example uses a template to format the appearance of the report eliminating the need to write excessive amounts of code. In this example, we cannot include the template in the constructor because by default, adding the template to the constructor will not import the formula and scripts. The way to import both the formula and the scripts is by calling the method applyTemplate after constructing the QbReport object. The two arguments that you will need to pass in are the file path of the template and a boolean specifying whether you want to import formula and scripts.
An alternate way to create a summary break report is by specifying that your data source is already sorted. That way, EspressReport will build a summary break report in the same manner as for database data. This feature is available for all data sources including text, XML, and class data. To do this, simply call the QbReport constructor with the boolean parameter isDataSorted set to true:
new QbReport(parent, reportType, data, mapping, template, sideBySideLayout, isDataSorted);
A Crosstab report is a report format that shows and summarizes columnar data in a matrix-like form. Crosstab reports often resemble spreadsheets. Both rows and columns are summarized, allowing multi-dimensional data to be displayed in a 2 dimensional format.
In addition to defining Row Breaks, you will also need to specify a column that serves as the Column Break as well as a column that serves as the Column Break Value. The Column Break column gets included in the Table Header with a separate column for each unique entry in the selected column while the Column Break Value column represents the field that is summarized in the report. You will also need to specify the type of Aggregation for the Column Break Value column. For instance, given the dataset below:
| Region | Product | Total Sales |
|---|---|---|
| East | Chair | 14500 |
| Midwest | Chair | 13250 |
| South | Chair | 15252 |
| East | Table | 10550 |
| Midwest | Table | 9150 |
| South | Table | 11250 |
Original Data
If Column 0 (Region) is assigned as Row Break, Column 1 (Product) is assigned as the Column Break, and Column 2 (Sales) assigned as the Column Break Value with a summation aggregation, we get the following report:
| Region | Chair | Table | Total Sales |
|---|---|---|---|
| East | 14500 | 10550 | 25050 |
| Midwest | 13250 | 9150 | 22400 |
| South | 15252 | 11250 | 26502 |
| 43002 | 30950 | 73952 |
Report after applying Column Break and Column Break Value with Aggregation
This example demonstrates several qualities frequently exhibited by CrossTab reports. Notice that the distinct Product names are transformed to become column headers. This means that the number of columns in the report will vary based on the data source. Also, the numeric values represent Total Sales for a particular combination of Product and Region. The cells to the right and bottom of the data are aggregations cells. The Total Sales at the end of each row sums up the Total Sales for each region, while the values at the bottom of each column sums up the Total Sales of each product.
To generate the aforementioned report, the following column mapping has to be set using the API:
ColInfo[] colInfo = new ColInfo[3];
colInfo[0] = new ColInfo(0);
// use this column as the row break. The number of
// new groups would be equal to the number of unique
// fields present.
colInfo[0].setRowBreak(true);
colInfo[1]=new ColInfo(1);
// use this column as the column break. The number of
// new columns would be equal to the number of unique
// column fields present.
colInfo[1].setColumnBreak(true);
colInfo[2] = new ColInfo(2);
// use this column as the value fields for the
// newly created chair and table columns
colInfo[2].setColumnBreakValue(true);
// aggregate by adding up the individual fields to
// get the summary field.
colInfo[2].setAggregation(ColInfo.SUM);
In the above code, the names for the columns are not set as the original names of the columns from the data source sufficed.
Note that in the example given above, only a single Column Break and a single Column Break Value was specified. However, just like Row Breaks, multiple Column Breaks and Column Break Values can be specified.
For example:
ColInfo[] colInfo = new ColInfo[7];
colInfo[0] = new ColInfo(0);
colInfo[0].setRowBreak(true);
colInfo[1]=new ColInfo(1);
colInfo[1].setRowBreak(true);
colInfo[2]=new ColInfo(2);
colInfo.setColumnBreak(true);
colInfo[3]=new ColInfo(3);
colInfo[3].setColumnBreak(true);
colInfo[4]=new ColInfo(4);
colInfo[4].setColumnBreakValue(true);
colInfo[4].setAggregation(ColInfo.SUM);
colInfo[5]=new ColInfo(5);
colInfo[5].setColumnBreakValue(true);
colInfo[5].setAggregation(ColInfo.SUM);
colInfo[6]=new ColInfo(6);
colInfo[6].setColumnBreakValue(true);
colInfo[6].setAggregation(ColInfo.SUM);
In the above code, Columns 2 and 3 of the data source are set to be Column Break columns while Columns 4, 5 and 6 are set to be Column Break Value columns.
The following example shows how to create a CrossTab report.
ColInfo[] colInfo = new ColInfo[4];
colInfo[0] = new ColInfo(0);
colInfo[0].setRowBreak(true);
colInfo[1] = new ColInfo(1);
colInfo[1].setColumnBreak(true);
colInfo[2] = new ColInfo(2);
colInfo[2].setColumnBreakValue(true);
colInfo[2].setAggregation(ColInfo.AVG);
QbReport report = new QbReport
(parent, // Parent
QbReport.CROSSTAB, // Type of Report
QbReport.CLASSFILE, // Data
"ClassFile",
colInfo, // Column Mapping
"CrossTab"); // Template Name
The Exported Unformatted Results link shows how the report appears when it was first created without any formatting or templates. The Exported Formatted Results presents the outcome after applying the template.
The example uses a class file as the data source. The class file generates the temperatures everytime you run the program so the results will vary. For more information on class file data sources, see Appendix 2.B.5 - Data passed in a Custom Implementation.
The fixed-field crosstab option improves some of the limitations of the free-form crosstab implementation, by making it easier for users to design crosstab reports that can expand and contract with changing data.
The key difference between a fixed field and a free-form crosstab is that in the design view, you are not able to position or control the individual columns for the crosstab report. Instead you can set formats for groups of elements (row break, headers, footers and formulas). The actual report is only constructed during running/preview. Since the crosstab table is essentially constructed from scratch every time the report is run it is easier to create a smoothly contracting and expanding crosstab table.
The following example shows how to create a multi-dimensional (extra row/column breaks) fixed-field crosstab report with 3 column breaks.
ColInfo colInfo[] = new ColInfo[4];
colInfo[0] = new ColInfo(0);
colInfo[1] = new ColInfo(1);
colInfo[2] = new ColInfo(2);
colInfo[3] = new ColInfo(3);
colInfo[0].setRowBreak(true);
colInfo[1].setColumnBreak(true);
colInfo[2].setColumnBreak(true);
colInfo[3].setColumnBreakValue(true);
colInfo[3].setAggregation(ColInfo.SUM);
Properties props = new Properties();
props.put("crossTabFreeForm", "false"); // Using fixed-field form
props.put("crossTabSummaryPositionL", "false"); // Draw summary column to the right of the details matrix
props.put("crossTabFormulaOnHeader", "true"); // Draw formula column in the header section
props.put("crossTabColBkValAlignH", "false"); // Align Column Break Value vertically
QbReport report = new QbReport
(parent, // Parent
QbReport.CROSSTAB, // Report Type
QbReport.DATAFILE, // Data Type
"sample.dat", // Filename
colInfo, // Column Mapping
"field.rpt", // Template
props); // Properties
In this example, we are mapping column 0 from the report with column 0 in the text file, column 1 from the report with column 1 in the text file, and so on. In the above code, columns 1, 2 and 3 of the data source are set to be Column Break columns and column 4 is set to be Column Break Value column with an sum aggregation. There are different crosstab options, because you cannot individually format or position crosstab options in the fixed-field layout. In this example the summary column is set to be drawn to the right of the report data, the formula column is set to be drawn in the header section and the column break value is set to be aligned vertically.
For more information about fixed-field crosstab, see Section 1.4.3.3 - Fixed-Field Crosstab Report.
A Master & Details report is a set of tabular data that is grouped according to a Master field. This report type is most commonly used when some fields in your data table are related to many other fields. A good example of this is an invoice. For each order number, there will be customer information, and several items with pricing information. A Master & Details report would be used to group the information according to order number.
In a Master & Details report, the data is grouped based on a “Primary” and/or “Master” column(s). Here, you must define a Primary Key column. The Primary Key column (order number in the invoice example) determines the grouping of the report. A new group will be created every time there is a value in the selected column (i.e. the Primary Key column). Please note that only one column can serve as the Primary Key. A Master field or several Master fields can also be selected in addition. Any column selected as the Master field (customer information) will result in the placing of the column value in the Group Header instead of the Data Section of the report. By definition, the Primary Key value must be unique for each grouping of the Master field column(s). For instance, given the dataset below:
| Order # | Customer Name | Product | Unit Price | Quantity |
|---|---|---|---|---|
| 12 | Paul Campbell | Chair | $24.95 | 4 |
| 12 | Paul Campbell | Table | $127.50 | 1 |
| 14 | Sally Hayes | Cabinet | $227.25 | 2 |
| 14 | Sally Hayes | Chair | $24.95 | 2 |
| 14 | Sally Hayes | Table | $127.50 | 1 |
Original Data
If Column 0 (Order #) is assigned as the Primary Key and Column 1 (Customer Name) is assigned as the Master field, we get the following report:
| Order # | 12 | |
| Customer Name | Paul Campbell | |
| Product | Unit Price | Quantity |
| Chair | $24.95 | 4 |
| Table | $127.50 | 1 |
| Order # | 14 | |
| Customer Name | Sally Hayes | |
| Product | Unit Price | Quantity |
| Cabinet | $227.25 | 2 |
| Chair | $24.95 | 2 |
| Table | $127.50 | 1 |
Report after applying Primary Key and Master field
To generate the aforementioned report, the following column mapping has to be set using the API:
ColInfo[] colInfo = new ColInfo[5];
colInfo[0] = new ColInfo(0);
colInfo[0].setPrimaryKey(true);
colInfo[1]=new ColInfo(1);
colInfo[1].setMaster(true);
colInfo[2] = new ColInfo(2);
colInfo[3] = new ColInfo(3);
colInfo[4] = new ColInfo(4);
In the above code, the names for the columns are not set as the original names of the columns from the data source sufficed.
You can select any number of columns to be in the master section. The only restriction is that the primary key is automatically placed in the master section. If this is undesired, you can set that column to be invisible.
The following example shows how to create a Master & Details report.
String url = "jdbc:hsqldb:database/woodview";
String driver = "org.hsqldb.jdbcDriver";
String query = "...";
DBInfo dbInfo = new DBInfo(url, driver, "sa", "", query);
ColInfo[] colInfo = new ColInfo[16];
for(int i = 0; i < colInfo.length; i++){
colInfo[i] = new ColInfo(i);
if(i < 8)
colInfo[i].setMaster(true);
}
colInfo[0].setPrimaryKey(true);
colInfo[15].setVisible(false);
QbReport report = new QbReport
(parent, // Parent
QbReport.MASTERDETAILS, // Type of Report
dbInfo, // Database Info
colInfo, // Column Mapping
null); // Template Name
try {
// Apply Template with Formula and Script
report.applyTemplate("MasterDetails", true);
} catch( Exception e ) {
e.printStackTrace();
}
Please see the full source code for the query details. The Exported Unformatted Results link shows how the report appears when it was first created without any formatting or templates. The Exported Formatted Results presents the outcome after applying the template.
This example uses the WoodView HSQL, so you will need to add database HSQL JDBC driver (hsqldb.jar) to your classpath. The driver is located in the <EspressReportInstall>/lib directory. For more information on using a database data source, see Appendix 2.B.1 - Data from a Database.
Since there are a total of 16 columns in this example, we use a for-loop to set the column info. The first eight columns are set as master columns and will be shown in the Group Header section. Column 0, OrderID, is set as the primary key.
We apply the template after constructing the QbReport object in order to import the formula and scripts stored in the template.
You can also position the master section to be side by side with the rest of the columns. To do so, use the following QbReport constructor and pass in true for the sideBySideLayout parameter.
QbReport(java.lang.Object parent, int reportType, IDatabaseInfo dbinfo, ColInfo[] mapping, java.lang.String template, boolean sideBySideLayout)
The Mailing Labels report presents data in a columnar format without any groupings or breaks. It is similar to the Simple Columnar report, all you need to do is merely define the ColInfo array object with respect to the data source. For instance, given the dataset below:
| Name | Company | Region |
|---|---|---|
| Mary | ABC Inc. | New York |
| Peter | GHI Ltd | London |
Original Data
The column mapping is set so that the columns of the data source are mapped to the report. Thus Column 0 of the report corresponds to column 0 of the data source, Column 1 of the report to column 1 of the data source and so on and so forth. Thus the following report is created:
| Name: | Mary |
| Company: | ABC Inc. |
| City: | New York |
| Name: | Peter |
| Company: | GHI Ltd |
| City: | London |
Report after mapping and setting the Table Header Names
To generate the aforementioned report, the following column mapping has to be set using the API:
ColInfo[] colInfo = new ColInfo[3];
colInfo[0] = new ColInfo(0);
colInfo[1] = new ColInfo(1);
colInfo[2] = new ColInfo(2);
colInfo[2].setName("City");
In the above code, the name for the third column was also changed to City rather than keeping the Header names defined in the original data.
The column mapping need not be in order or follow the same order as the data. You can also selectively choose the columns you desire. For instance, given a data source that has multiple columns, you can write the following code:
ColInfo[] colInfo = new ColInfo[3];
colInfo[0] = new ColInfo(2);
colInfo[1] = new ColInfo(7);
colInfo[2] = new ColInfo(4);
Here, the Column 0 of the report is mapped to column 2 of the data source, Column 1 of the report is mapped to column 7 of the data source, and Column 2 of the report is mapped to column 4 of the data source.
The following example shows how to create a Mailing Labels report.
String xmlfilename = "Inventory.xml";
String dtdfilename = "Inventory.dtd";
String xmlcondition = "/Inventory/Category/Product/UnitsInStock < 15";
XMLFieldInfo[] fields = new XMLFieldInfo[8];
fields[0] = new XMLFieldInfo(new String[] { "Inventory", "Category" }, "CategoryName");
fields[1] = new XMLFieldInfo(new String[] { "Inventory", "Category", "Product" },
"ProductID");
fields[2] = new XMLFieldInfo(new String[] { "Inventory", "Category", "Product",
"ProductName" });
fields[3] = new XMLFieldInfo(
new String[] { "Inventory", "Category", "Product", "UnitPrice" });
fields[3].setElementDataType(DTDDataType.DOUBLE);
fields[4] = new XMLFieldInfo(
new String[] { "Inventory", "Category", "Product", "Material" });
fields[5] = new XMLFieldInfo(new String[] { "Inventory", "Category", "Product", "Material",
"Units" });
fields[5].setElementDataType(DTDDataType.INT);
fields[6] = new XMLFieldInfo(new String[] { "Inventory", "Category", "Product", "Material",
"CostPerUnit" });
fields[6].setElementDataType(DTDDataType.DOUBLE);
fields[7] = new XMLFieldInfo(new String[] { "Inventory", "Category", "Product",
"UnitsInStock" });
fields[7].setElementDataType(DTDDataType.INT);
XMLFileQueryInfo xmlInfo = new XMLFileQueryInfo(xmlfilename, fields, xmlcondition, fields,
dtdfilename, false, null);
ColInfo[] colInfo = new ColInfo[8];
for (int i = 0; i < 8; i++) {
colInfo[i] = new ColInfo(i);
}
QbReport report = new QbReport
(parent, // Parent
QbReport.MAILINGLABELS, // Type of Report
xmlInfo, // XML Info
colInfo, // Column Mapping
null, // Template Name
false); // Side By Side
try {
// Apply Template with Formula and Script
report.applyTemplate("MailingLabels.rpt", true);
} catch (Exception e) {
e.printStackTrace();
}
The Exported Unformatted Results link shows how the report appears when it was first created without any formatting or templates. The Exported Formatted Results presents the outcome after applying the template. This example uses a XML file as the data source, for more information on using an XML data source, see Appendix 2.B.3 - Data from an XML Data Source.
We apply the template after constructing the QbReport object in order to import the formula and scripts stored in the template.
You are not restricted to just using the original data in generating the columns for the report. You may create your own columns with your own value or use formula to calculate over one or more columns. For instance, given the dataset below:
| Units Sold | Product | Unit Price |
|---|---|---|
| 12 | Chair | $25 |
| 2 | Table | $38 |
| 18 | Cabinet | $21 |
| 4 | Sofa | $57 |
Original Data
If Column 0 (Units Sold) and Column 2 (Unit Price) were multiplied, you can get a new column, which would have the total revenue for that product, thus producing the following report:
| Product | Units Sold | Unit Price | Total Revenue |
|---|---|---|---|
| Chair | 12 | $25 | $300.00 |
| Table | 2 | $38 | $76.00 |
| Cabinet | 18 | $21 | $378.00 |
| Sofa | 4 | $57 | $228.00 |
Report after applying formula and creating a new column
To generate the aforementioned report, the following column mapping has to be set using the API:
ColInfo[] colInfo = new ColInfo[4];
colInfo[0] = new ColInfo(1);
colInfo[1] = new ColInfo(0);
colInfo[2] = new ColInfo(2);
IObject salesColumn = NumericObject.multiply(NumericObject.getColumnValue(1),
NumericObject.getColumnValue(2));
colInfo[3] = new ColInfo(salesColumn, "Total Revenue", Types.DOUBLE);
In the above code, the names for the columns are not set as the original names of the columns from the data source sufficed. For the newly created column, Total Revenue was specified as the header.
To learn more about formula, please see Section 1.8 - Using Formulas & the Formula Builder.
In addition to regular queries, you can pass in queries that have parameters and have the report prompt the user for values for the parameters, before generating the report. There is no limit to the number of parameters allowed in each query.
To use a parameterized query as your data source for your report, you must use the SimpleQueryFileInfo class, which implements IQueryFileInfo, to pass in the query information. Since SimpleQueryFileInfo is a subclass of DBInfo, you can use the same parameters to create a SimpleQueryFileInfo object.
SimpleQueryFileInfo(java.lang.String url, java.lang.String driver, java.lang.String username, java.lang.String password, java.lang.String query)
In addition to creating the query object, you must also create the parameters using either an array of SimpleQueryInParam for single valued parameters or an array of SimpleQueryMultiValueInParam for multi valued parameters. Finally, you must pass an instance of the parameter set to the query object using the method setInParam(ParamSet).
The constructors for the single valued parameter and multi valued parameter are identical since one is a subclass of the other.
SimpleQueryInParam(java.lang.String paramName, java.lang.String promptName, boolean mapToColumn, java.lang.String tableName, java.lang.String columnName, int sqlType, java.lang.Object defaultValue, java.lang.Object value)
The paramName is the name for the parameter, having two parameters with the same name is permitted provided that they are created in separate parameter instances. The promptName is the message the user will see when they are asked to input the value for the parameter.
The parameters mapToColumn, tableName and columnName determines if you would like to specify a column from the database whose values will be used for the parameter input. Selecting true modifies the parameter prompt that the end user will see when previewing or running the report in the Report Viewer. If you map the parameter to a database column, the user will be prompted with a drop-down list of distinct values from which to select a parameter value. If you do not map, set the tableName and columnName to null, the user will have to type in the specific parameter value. If you are querying the database from a parameterized class file and using the class file as the datasource, keep in mind that you can not set mapToColumn to true. For an alternative method of mapping to a column in a parameterized class file, see Appendix 2.B.5 - Data passed in a Custom Implementation.
The sqlType determines the datatype of the parameter. Select the constant that matches the datatype from the java.sql.Types class. The defaultValue is displayed in the input box when the prompt first appears. If you selected to map to column and provide a default value that does not exist, the first element of the drop down list will be displayed. The last parameter is of type object and if this parameter is not null, the parameter prompt will not be displayed. Instead of allowing the user to specify the value, it will use the value in this parameter for the query.
The following example shows how to create a report with a single value parameter.
SimpleQueryInParam inParam = new SimpleQueryInParam(
"param", "Please select", true, "Categories",
"CategoryName", Types.VARCHAR, "Arm Chairs", null);
SimpleQueryInParam[] paramSet = { inParam };
SimpleQueryFileInfo reportInfo = new SimpleQueryFileInfo(
"jdbc:hsqldb:woodview",
"org.hsqldb.jdbcDriver",
"sa", "",
"...");
reportInfo.setInParam(paramSet);
// End Code : Adding Parameter Info
// Begin Code : Setting up Column Mapping
ColInfo colInfo[] = new ColInfo[4];
for (int i = 0; i < colInfo.length; i++) {
colInfo[i] = new ColInfo(i);
}
colInfo[0].setPrimaryKey(true);
colInfo[1].setName("Product Name");
colInfo[2].setName("Price");
colInfo[3].setName("Units In Stock");
// End Code : Setting up Column Mapping
QbReport report = new QbReport(parent,
QbReport.MASTERDETAILS,
reportInfo,
colInfo,
"SingleValueParameter.rpt");
This example uses the WoodView HSQL database, so you will need to add database HSQL JDBC driver (hsqldb.jar) to your classpath. The driver is located in the <EspressReportInstall>/lib directory. For more information on using a database data source, see Appendix 2.B.1 - Data from a Database.
Here the parameter is mapped to the CategoryName column of the Category table. The prompt will display “Please Select” and the values will be presented in a drop down list for the user to select.
You can also assign a parameter to have multiple values, for example, in the case where a user wants to check against a range of values rather than just a single value. The range of values is usually specified within the IN clause of a SQL query. Note that EspressReport only considers parameters within the IN clause to be multi-value.
The following example shows how to create a report with a multi-value parameter.
SimpleQueryMultiValueInParam inParam = new SimpleQueryMultiValueInParam("param",
"Please select", true, "products", "productid", Types.INTEGER, new Integer(1), null);
SimpleQueryMultiValueInParam[] paramSet = { inParam };
SimpleQueryFileInfo rootInfo = new SimpleQueryFileInfo(
"jdbc:hsqldb:woodview",
"org.hsqldb.jdbcDriver",
"sa", "",
"select productid, productname " +
"from Products where productid in (:param)");
rootInfo.setInParam(paramSet);
// End Code : Adding Parameter Info
// Begin Code : Setting up Column Mapping
ColInfo rootColInfo[] = new ColInfo[2];
rootColInfo[0] = new ColInfo(0);
rootColInfo[0].setName("Product ID");
rootColInfo[1] = new ColInfo(1);
rootColInfo[1].setName("Product Name");
// End Code : Setting up Column Mapping
QbReport rootReport = new QbReport(
parent,
QbReport.MAILINGLABELS,
rootInfo,
rootColInfo,
"MultiValueParameter.rpt");
You can also add sub-reports to the main report. This helps in presenting more data as two reports can be shown in one. For more information on sub-reports, please refer to Section 1.11 - Sub-Reports.
To create a report containing a sub-report, you need to create the sub-report object and then insert that sub-report object as a report cell object in the main report. The sub-report can be placed anywhere in the main report. To create a SubReportObject, use the following static method.
SubReport.createSubReport(QbReport parentReport, int reportType, IDatabaseInfo dbinfo, ColInfo[] mapping, java.lang.String template)
Similar to the QbReport constructors, there are numerous variations for the parameters to compensate for different data sources. The above method retrieves the data from a database. The sub-report can also contain parameters and you can set up parameter sharing from the API as well. Once the SubReportObject is created, it can be inserted into the report using the addData(subReportObject) method.
The following example shows how to create a report that contains a sub-report.
// Begin Code : Creating the sub report
DBInfo subReportInfo = new DBInfo(
"jdbc:hsqldb:woodview",
"org.hsqldb.jdbcDriver",
"sa",
"",
"SELECT Employees.FirstName + ' ' + Employees.LastName AS Employee, "
+
"Count(Order_Details.OrderID) AS Orders, "
+
"Sum( ( Products.UnitPrice + Order_Details.StainCost ) * Order_Details.Quantity) AS Sales "
+
"FROM Employees, Products, Orders, Order_Details " +
"WHERE ((Orders.EmployeeID = Employees.EmployeeID) " +
"AND (Orders.OrderID = Order_Details.OrderID) " +
"AND (Products.ProductID = Order_Details.ProductID)) " +
"AND (((Orders.OrderDate BETWEEN '2003-01-10' AND '2003-31-12'))) " +
"GROUP BY Employees.FirstName + ' ' + Employees.LastName;");
ColInfo subReportColInfo[] = new ColInfo[3];
for (int i = 0; i < subReportColInfo.length; i++) {
subReportColInfo[i] = new ColInfo(i);
}
try {
// createSubReport(root report, report type, sub report data source, sub report column mapping, template)
SubReportObject reportCell = quadbase.reportdesigner.ReportAPI.SubReport
.createSubReport(
rootReport,
QbReport.COLUMNAR,
subReportInfo,
subReportColInfo,
"SubReportSub.rpt");
reportCell.setWidth(rootReport.getTable().getHeader().getWidth() - 2);
reportCell.setHeight(2);
// Or use the following method if the height of the subreport may change
// reportCell.setResizeToFitContent(true);
reportCell.setY(0);
reportCell.setX(2);
// Add SubReport to the Table Header
rootReport.getTable().getHeader().addData(reportCell);
rootReport.getTable().getHeader().setHeight(1.8);
} catch (Exception ex) {
ex.printStackTrace();
}
// End Code : Creating the sub report
The main report in this example obtains the data using a DBInfo object to create a summary break report. Both reports apply a template to reduce the amount of code in the example. The sub-report is created and inserted to the table header section of the main report. Notice that several lines of code are necessary to adjusts the dimensions of the SubReportObject and the table header section in order to see the entire sub-report.
You can also include sub-reports that take a parameter from the main report and use it to populate the data within the sub-report. This helps to put in relevant data in sections within the main report. A parameterized sub-report is created by passing in a parameterized query for the sub-report and then specifying the column from the main report that provides the parameter for the sub-report's query. The parameter passed to the sub-report is the first value of a given column.
Depending on the placement of the Sub-Report, you can have a linked sub-report for each distinct value of the column. For example, in a Summary Break report, you can have a sub-report whose parameter is linked to the row break column. If this sub-report is then placed in the Group Header section, a sub-report is generated in the main report for each distinct value in the row break column (since the column breaks on each distinct value).
A linked sub-report will always take the first value in the column mapped to its parameter. However, depending on the placement of the sub-report, it can appear multiple times within the report, each time taking the first value of the column within its grouping.
Given below is an example of a sub-report placed in the group header that takes in a parameter from the main report.
QbReport.setEspressManagerUsed(false);
// Begin Code : Creating the root Report
DBInfo rootInfo = new DBInfo("jdbc:hsqldb:woodview",
"org.hsqldb.jdbcDriver",
"sa",
"",
"SELECT Customers.Company, Orders.OrderID, " +
"Products.ProductName, Products.UnitPrice, " +
"Order_Details.Quantity " +
"FROM Order_Details, Products, Orders, Customers " +
"WHERE (Orders.CustomerID = Customers.CustomerID) " +
"AND (Order_Details.OrderID = Orders.OrderID) " +
"AND (Products.ProductID = Order_Details.ProductID);");
ColInfo rootColInfo[] = new ColInfo[5];
rootColInfo[0] = new ColInfo(0);
rootColInfo[0].setRowBreak(true);
ootColInfo[1] = new ColInfo(1);
rootColInfo[1].setRowBreak(true);
rootColInfo[2] = new ColInfo(2);
rootColInfo[2].setAggregation(false, ColInfo.NONE);
rootColInfo[3] = new ColInfo(3);
rootColInfo[3].setAggregation(false, ColInfo.AVG);
rootColInfo[4] = new ColInfo(4);
rootColInfo[4].setAggregation(false, ColInfo.SUM);
QbReport rootReport = new QbReport(
parent,
QbReport.SUMMARY,
rootInfo,
rootColInfo,
"ParameterizedSubReport.rpt");
// End Code : Creating the root Report
int numberOfTableHeaderCells = rootReport.getTable().getHeader().getData().length;
// Transferring Table Header cells to Row Break Zero Header
for (int i = 0; i < numberOfTableHeaderCells; i++)
{
rootReport.getTable().getRowBreakHeader(0)
.addData(rootReport.getTable().getHeader().getData(i));
rootReport.getTable().getRowBreakHeader(0).getData(i).setY(1);
}
// Deleting Table Header cells
for (int i = 0; i < numberOfTableHeaderCells; i++) {
rootReport.getTable().getHeader().removeData(0);
}
// Begin Code : Creating the SubReport
// SimpleQueryInParam(name of Parameter, String to be displayed, MapToColumn?, tableName, ColumnName, SQL Type, DefaultValue, value)
SimpleQueryInParam inParam = new SimpleQueryInParam(
"Company", "Please select", true, "Customers",
"Company", Types.VARCHAR, "All Unfinished Furniture",
"All Unfinished Furniture");
SimpleQueryInParam[] paramSet = { inParam };
SimpleQueryFileInfo subReportInfo = new SimpleQueryFileInfo(
"jdbc:hsqldb:woodview",
"org.hsqldb.jdbcDriver",
"sa", "",
"SELECT Customers.CustomerID, Customers.ContactName, " +
"Customers.Address, Customers.City, " +
"Customers.State, Customers.Zip " +
"FROM Customers " +
"WHERE (Customers.Company =:Company);");
subReportInfo.setInParam(paramSet);
// Begin Code : Setting up Column Mapping
ColInfo subReportColInfo[] = new ColInfo[6];
for (int i = 0; i < subReportColInfo.length; i++) {
subReportColInfo[i] = new ColInfo(i);
}
// End Code : Setting up Column Mapping
try {
// createSubReport(root report, report type, sub report data source, sub report column mapping, template)
SubReportObject reportCell = SubReport.createSubReport(
rootReport,
QbReport.COLUMNAR,
subReportInfo,
subReportColInfo,
null);
((QbReport) reportCell.getSubReport()).applyTemplate(
"ParameterizedSubReportSub.rpt", true);
// Get Parameter from root report column
reportCell.setParameterMap(new String[] { rootReport.getTable().getColumn(0).getID() });
// Set SubReport cell's height and width
reportCell.setWidth(7.2);
reportCell.setResizeToFitContent(true);
reportCell.setY(.3);
reportCell.setX(0);
// Add SubReport to the Group Zero Header
rootReport.getTable().getRowBreakHeader(0).addData(reportCell);
} catch (Exception ex) {
ex.printStackTrace();
}
// End Code : Creating the sub report
return (new Viewer().getComponent(rootReport));
You do not need to link a column from the main report to a sub-report's parameter if both the main report and the sub-report have a parameter with the same name.
With the help of parameterized queries or classes, Drill Down reports can be created. Instead of having reports with large amounts of data in it, you can show a top level report showing the minimum data required and then delve deeper on the selected data. For more information on Drill Down reports, please refer to Section 1.10 - Drill Down.
To create a Drill Down report, you need to create the various QbReport objects (please note that all QbReport objects, other than the root report object, will have parameterized queries as their data source) and then specify the order of the drill-down as well as the column of the report to attach the next level of the Drill Down report. To create a Drill Down report use the following method:
rootReport.createDrillDownReport(java.lang.String name, int reportType, IDatabaseInfo dbInfo, ColInfo[] mapping, java.lang.String template, int[] columnMapping)
Unlike Sub-Reports, Drill Down reports must be parameterized queries, so there are fewer construction methods for Drill Down reports.
The following example shows how to create a report that contains a Drill Down report.
Component doDrillDownReport(Object parent) {
QbReport.setEspressManagerUsed(false);
// Begin Code : Creating Report 1 - the Root Report
DBInfo rootInfo = new DBInfo("jdbc:hsqldb:woodview",
"org.hsqldb.jdbcDriver",
"sa",
"",
"select Categories.CategoryName, sum(Products.UnitsInStock) " +
"from Categories, Products " +
"where Categories.CategoryID = Products.CategoryID " +
"group by Categories.CategoryName " +
"order by Categories.CategoryName;"
);
ColInfo rootColInfo[] = new ColInfo[2];
for (int i = 0; i < rootColInfo.length; i++) {
rootColInfo[i] = new ColInfo(i);
}
rootColInfo[0].setName("Product Name");
rootColInfo[1].setName("Total Units In Stock");
QbReport rootReport = new QbReport(
parent,
QbReport.COLUMNAR,
rootInfo,
rootColInfo,
"DrillDown.rpt");
// End Code : Creating Report 1 - the Root Report
// Begin Code : Creating Report 2 - the drill down Report
SimpleQueryInParam inParam = new SimpleQueryInParam("param", "Please select", true,
"Categories", "CategoryName", Types.VARCHAR, "Arm Chairs", null);
SimpleQueryInParam[] paramSet = { inParam };
SimpleQueryFileInfo levelOneReportInfo = new SimpleQueryFileInfo(
"jdbc:hsqldb:woodview",
"org.hsqldb.jdbcDriver",
"sa",
"",
"select Categories.CategoryName, Products.ProductName, Products.UnitPrice, Products.UnitsInStock from Categories, Products where Categories.CategoryID=Products.CategoryID and Categories.CategoryName=:param order by Categories.CategoryName, Products.ProductName;");
levelOneReportInfo.setInParam(paramSet);
ColInfo levelOneReportColInfo[] = new ColInfo[4];
for (int i = 0; i < levelOneReportColInfo.length; i++) {
levelOneReportColInfo[i] = new ColInfo(i);
}
levelOneReportColInfo[0].setName("Product Type");
levelOneReportColInfo[1].setName("Product Name");
levelOneReportColInfo[2].setName("Price");
levelOneReportColInfo[3].setName("Units In Stock");
try {
// createDrillDownReport(Name, Type of Report, database information, column mapping, template, column of root report to be mapped to)
rootReport.createDrillDownReport("TestDrillDownReport",
QbReport.COLUMNAR,
levelOneReportInfo,
levelOneReportColInfo,
"lvl1.rpt", new int[] { 0 });
} catch (Exception ex) {
ex.printStackTrace();
}
rootReport.getTable().getColumn(0).setDrillDownName("TestDrillDownReport");
// End Code : Creating Report 2 - the drill down Report
return (new Viewer().getComponent(rootReport));
When you generate drill-down reports without using the EspressManager, you MUST have a sub directory called DrillDown under the working directory of the .class file.
The above exported results, only contain the root report and the Drill Down report for Oval Tables.
When you export Drill Down reports, to either DHTML or PDF format, you must have the DrillDownReportServlet servlet provided. This servlet provides the link in the report to the next level. Depending on the link clicked, this servlet delivers the appropriate next level report. To use this servlet, you must make it accessible from your servlet runner/application server. The URL to access this servlet MUST be http://<machine name>:<port number>/servlet/DrillDownReportServlet. Then in your application code, you must use the following method:
QbReport.setDynamicExport(boolean state, String serverName, int servletRunnerPort);
For example, given a Drill Down report and the following method call:
report.setDynamicExport(true, "Aphrodite", 8080);
When the report is exported, the above code sees to it that the links are pointing to machine Aphrodite and the port 8080. The DrillDownReportServlet servlet gets called using the URL, http://Aphrodite:8080/servlet/DrillDownReportServlet and thus computes the next level based on the link clicked and shows the next level.
You can also change the url for this servlet by using the setServletDirectory(String) method in the QbReport class. For instance, given the example above, if the following line of code were added:
report.setServletDirectory("ER/Test/");
then the links will show http://Aphrodite:8080/ER/Test/DrillDownReportServlet instead of the default servlet/ in the link.
Note that Drill Down reports exported to formats other than HTML, DHTML, or PDF will only show the current level.