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:

To generate the aforementioned report, the following column mapping has to be set using the ERES 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
      

Full Source Code

Exported Unformatted Results

Exported Formatted Results

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.

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:

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();
}
        

Full Source Code

Exported Unformatted Results

Exported Formatted Results

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, ERES 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);
        

To generate the aforementioned report, the following column mapping has to be set using the ERES 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
        

Full Source Code

Exported Unformatted Results

Exported Formatted Results

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 7.A.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.

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
            

To generate the aforementioned report, the following column mapping has to be set using the ERES 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();
}
        

Full Source Code

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 <ERESInstall>/WEB-INF/lib directory. For more information on using a database data source, see Appendix 7.A.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)
        

To generate the aforementioned report, the following column mapping has to be set using the ERES 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();
}
        

Full Source Code

Exported Unformatted Results

Exported Formatted Results

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 7.A.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.

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");
        

Full Source Code

Exported Results

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 <ERESInstall>/lib directory. For more information on using a database data source, see Appendix 7.A.1 - Data from a Database.

This example uses the Woodview ODBC database, for more information on using a database data source, see Appendix 7.A.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 ERES 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");
        

Full Source Code

Exported Results

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
        

Full Source Code

Exported Results

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));
        

Full Source Code

Exported Results

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.

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));
        

Full Source Code

Exported Results

When you generate Drill Down reports without using the ERES Server, 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("ERES/Test/");
        

then the links will show http://Aphrodite:8080/ERES/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.