EspressReport allows you to connect to JDBC compliant data sources. Examples are included within your installation guide.

Q.3.2.1.1. Setup a JDBC Connection

In this tutorial, we will set up a JDBC connection to the Woodview HSQL database that comes with the EspressReport Installation. (HSQL is an open source Java application database).

From the Data Source Manager, click on the Databases node in the left frame and then click the Add button. A dialog will appear prompting you to enter connection information for the new database. Enter Woodview as the name of the Database, select HSQL from the Driver List, enter jdbc:hsqldb:help/examples/DataSources/database/woodview for the URL and enter org.hsqldb.jdbcDriver as the driver. Click on both the Require Login and Save Password boxes. Then enter sa for the username and leave the password blank.

Add Database Dialog

Leave the Auto Join and table name properties alone and click the Test Connection button to make sure you have entered the information correctly. Then click the OK button to add this database connection to the Data Source Manager window, where there will be a new node under "Databases" for Woodview.

EspressReport provides a number of different interfaces to query a database to retrieve the report data. You can execute an SQL statement, use the Query Builder, or use data views to create a query interface that insulates the end user from the database structure. In this example, we will use the Query Builder to create a query.

To create a new query, click on the Woodview node in the left frame of the Data Source Manager to expand it. Two sub-nodes will appear, one called Queries and one called Data Views. Select the Queries node and click the Add button. A dialog will appear prompting you to specify a name for the query and to select whether to launch the Query Builder or to enter an SQL statement.

Query Name Dialog

Enter any name you would like, select Open Query Builder and click on the Ok button. The Query Builder will launch. You will see a separate window containing all of the tables for Woodview sitting over the top of the main Query Builder window.

Query Builder Dialog

To add a table to the query, select a table in the Tables window and click the Add button. You can also double click on the table name. Using one of the two methods, add the following tables to the query:

CATEGORIES
CUSTOMERS 
ORDER_DETAILS
ORDERS
PRODUCTS
		

When you are done, click the Close button to close the Database Tables window. The tables will appear in the top half of the Query Builder window. You will see join lines connecting various fields in the tables.

Query Builder with Tables

To add a field to the query, you can double click on the field in the table window. You can also double click on the Table and Field fields in the lower (QBE) portion of the Query Builder window and then select the table and field from the drop-down menus. Using either method, add the following fields to the query:

In the eighth column, which should be blank, right click in the Field field and select Build from the pop-up menu. This will open the Formula Builder interface allowing you to create a computed column.

Formula Builder Window

First, click the left parenthesis button to build a column. Then double click on the Tables folder to expand it into five nodes, one for each of the tables that you selected for the query. Opening a table folder will list all of the column fields for that table. Open the PRODUCTS folder, select UNITPRICE and click the INSERT button. Then click the add (+) button. Next, insert STAINCOST from the ORDER_DETAILS table. Then click the right parenthesis button followed by the multiply (*) button. Finally, insert QUANTITY from the ORDER_DETAILS table. The finished formula should look like this:

( PRODUCTS.UNITPRICE + ORDER_DETAILS.STAINCOST ) * ORDER_DETAILS.QUANTITY

Formula Builder Window with Formula

Click the Ok button and the built column will be added to the query. Next, we will give the column an alias. Right click on the column and select Alias from the pop-up menu. A window will appear asking you to enter a column alias. Enter "SALES" (without quotation marks) and click the OK button.

Column Alias Dialog

Click the Ok button and you will see the column name change in the Query Builder. Now click on the Datasheet View tab in the Query Builder. Your query will run and you should see the first thirty records of the query results.

Query Builder Datasheet View

Now that you have finished designing the query, select Done from the File menu to save the changes. This will close the Query Builder window and return you to the Data Registry Manager window. There will now be a node under "Queries" for the query you have just designed.

Data Source Manager With Query

Q.3.2.2.1. Add Query Parameters

EspressReport allows you to easily parameterize report queries, allowing report data to be dynamically filtered at run-time. In this tutorial we will add parameters to the query created in Section Q.3.2.2 - Create a Query

To open the query you created, select it and click the Edit button in the Data Source Manager. Then click the OK button to confirm the name. Your query will re-open in the query builder. The Tables window will open on top of the Query Builder. Click the Close button to close the Database Tables window and right click in the Condition field under the ORDERID column in the lower part of the Query Builder and select Build from the pop-up menu. This will bring up the Formula Builder which will allow you to construct a condition for the query.

Within the Formula Builder, double click on the Tables folder to expand it. Then expand the ORDERS node and double click on the ORDERDATE field. Next, click the BETWEEN button and then click the PARAMETER button. This will bring up a dialog prompting you specify a name for the query parameter.

Parameter Name Dialog

Enter StartDate as the parameter name and click the OK button. The parameter will be added to the query. Then click the AND button followed by the PARAMETER button again. Enter EndDate as the second parameter name. The finished condition should look like this

ORDERS.ORDERDATE BETWEEN :StartDate AND :EndDate

Formula Builder with Conditions

Click the Ok button to close the Formula Builder and to return to the Query Builder window. Now click on the Datasheet View tab. Because you have just added two parameters to the query, an initialization dialog will appear, asking you to specify a few properties for the query parameters.

Parameter Initialization Dialog

From this window, click on Map to a database column and select ORDERS.ORDERDATE from the drop-down menu. This will automatically fill the Default Value and Data Type options. Next, enter Start Date into the Prompt Name, then click the Next Parameter button and map the EndDate parameter to the same column. Click on the Define Value drop-down menu to select an end date. Select a date far enough from the start date that by default you will have more than a couple records to work with (this makes report design easier). Change its Prompt Name to End Date.

Click the Ok button to close the initialization window once you have specified all the options. A new dialog will appear prompting you to select a date range by which to filter the result set.

Parameter Selection Dialog

Select the Start and End date that you would like and click the OK button. You will now see the filtered result in the datasheet window. Now, click Done from the File menu to save the changes you have made to the query.

A unique feature in EspressReport is an ability to create data views. Data views are local schemas/views that allow an administrator to pre-configure a group of tables and fields. This allows end users to only select fields and define simple conditions to create a query. To create a data view, select the Data Views node under Woodview and click Add.

This will open a new dialog asking you to select database tables you want to use. Select the following tables and click the ADD>> button to add them to the Selected Tables window:

CUSTOMERS 
ORDERS 
ORDER_DETAILS 
PRODUCTS
		

Data View Tables Dialog

Next, click on the Joins tab. You will see a representation of the tables like in the Query Builder. You can see the auto-join lines between the tables. This window can be used to join the tables or to modify the auto-joins, if necessary. Click the Ok button to finalize the table selection. The next window allows you to select and group fields for the view. At the top of the window you can specify a name for the view. Name it Invoicing.

Next, double click on the CUSTOMERS folder to reveal the fields for that table. Add the following fields by selecting them and clicking the ADD>> button:

CONTACTNAME
COMPANY 
ADDRESS 
CITY 
STATE 
ZIP
		

Now add fields from other tables as follows:

Now click the Add Heading button. At the prompt, specify the name Customer Info. Add two more headings in the same way, one called Shipping Info and one called Order Info. Once they are created, select the following fields (Using CTRL+Click or SHIFT+Click for multiple selection):

CONTACTNAME
COMPANY 
ADDRESS 
CITY 
STATE 
ZIP
		

Once the fields are selected, click the Group Fields button and select Customer Info from the drop-down list. The fields will be moved under that heading. Next, select the following fields in the same way:

SHIPDATE
SHIPTO 
SHIPADDRESS 
SHIPCITY 
SHIPSTATE 
SHIPZIP
		

Add these fields to the Shipping Info group the same way you did before. Next, select the following fields:

ORDERDATE 
ORDERID 
STAIN 
STAINCOLOR 
QUANTITY 
PRODUCTNAME 
UNITPRICE 
STAINPRICE
		

Add these fields to the Order Info Info group. Next, select the CONTACTNAME field on the right side and click the Rename button. In the dialog, specify the name Contact name. Repeat this for every field in order to give it proper names.

Next, select the Order ID field on the right side and click the up arrow button to move the field to the top of the "Order Info" heading. Use the arrows to arrange the items in the "Order Info" heading in the following order:

Order ID 
Order Date 
Product name 
Unit Price 
Stain 
Stain Color 
Stain Price 
Quantity
		

Data View Fields Window

Now click the OK button in the fields window to save the view. It will be saved as a new node under Data Views in the Data Source Manager.

Q.3.2.3.1. Query a Data View

Now that the data view has been created, you can write queries against the view. This allows users to develop queries without knowing the underlying structure of the database. It also allows administrators to limit which database elements users has access to. In this tutorial we will create a query for the data view you created in Section Q.3.2.3 - Create a Data View

In the Data Source Manager, select the Invoicing data view. Then click the Next button. This will open a dialog prompting you to select fields from the view. To select fields, first double click on a heading to expand it. Add the following fields to the query by selecting them and clicking the Add button:

Order ID 
Order Date 
Product name 
Unit Price 
Quantity
		

Data View Query Field Selection Dialog

Once you finish adding the fields, click the OK button. This will bring up a new window which enables you to set conditions, grouping, and ordering for the query. Like the Query Builder, this window also allows you to preview the query result with the Datasheet View tab.

Data View Conditions Dialog

First, specify a name for the query in the space provided at the top. Then double click on the Condition field for the Order Date column. This will bring up a dialog allowing you to specify a condition for the field.

Specify Condition Dialog

Click the Between button. A new dialog will appear prompting you to specify a start and end date with which to filter the results. Select 2001-01-14 as the first date, and 2003-12-09 as the second.

Specify Condition Fields Dialog

Click OK to close the dialog and add the condition. You will be taken back to the conditions window. Now you can click on the Datasheet View tab to preview the query.

Click OK in the main window and the query will be saved using the name you provided. You will then be taken to the first step in the Report Wizard. Click Cancel in this dialog to close the interface and then return to the Data Source Manager by clicking the New File button. There will now be a new node for your query under the Invoicing data view.

The simple columnar layout is the most straight-forward type of mapping. Columns from the data source are drawn as a straight table in the report, without any grouping or breaks.

To begin mapping a report, first select the query you created before in Queries node in the Data Source Manager and click the Next button. A new window will open with a table containing the results of the query (the first 20 records only). Notice that because your query contains a parameter, it initially runs with the default values that were specified when the parameters were initialized.

Query Result Screen

To continue with the Report Wizard, click the Next button. This will bring up a dialog asking you which report layout option you want to use.

Select Report Layout Dialog

From this dialog, select Simple Columnar as the layout and click the Next button. The next step in the Report Wizard allows you to select which columns from the query you want to use in the report, as well as re-arrange the column order. (Although the column order is not particularly important for the simple columnar layout, it can have a significant impact on other layouts depending on the mapping options selected).

Column Selection/Ordering Dialog

In this dialog, select the following fields for the report:

ORDERID 
CATEGORYNAME 
PRODUCTNAME 
QUANTITY 
SALES
		

Once you select the fields, click the Next button to continue on with the Wizard. The next dialog is the data mapping dialog. This is where you can select how to map fields from the data source into the selected report layout. Because this is a simple columnar layout, the only options are whether to set columns visible or not, or to generate a top N presentation.

Data Mapping Dialog

In this dialog, select to leave all the columns visible and do not select the top N option. (For more about top N presentations, please see Section 1.4.1.1.1 - Top N Report of the Designer Guide). Next, click the Done button. (There are some optional additional steps in the Wizard which will be explained later in this section). This will bring up the Report Designer interface with an unformatted version of your report.

Simple Columnar Report in Design Window

Now click on the Preview Tab in the upper-left corner of the window and select Use Live Data. A parameter selection dialog will now appear prompting you to select a start and end date by which to filter the report. Specify a large enough range that you will get more than a couple of records and click the OK button. You will then see the report output. Notice how the simple columnar layout places the columns directly into the report, without any grouping, sorting, or summaries.

Simple Columnar Report Preview

The summary break layout is similar to the columnar layout, except it adds the ability to group and aggregate the report columns. A summary break report must be grouped by at least one column. Using the report created in Section Q.3.3.1 - Simple Columnar Layout, we will convert it into a summary break layout.

Go to the design tab and select the Change Data Mapping icon on the toolbar: . This will return you to the Report Wizard, where we will select a different report layout. From this dialog, hit the Back button twice until you get back to the Select Report Format window.

Change the report layout type to Summary Break and click the Next button. On the next screen, keep the same column selection and click the Next button again to go to the data mapping window. You can notice there are more options in this window than for the columnar layout.

Data Mapping Screen for Summary Break Layout

In the data mapping dialog, check the option called Row Break for the first two columns. This will group the report by those two columns. From the drop down menus under Aggregation, select SUM for QUANTITY and SALES columns. Also, uncheck the Apply Template option, as you do not need to carry over the formatting from the simple columnar layout. Once you finish specifying the options, click the Done button and then Yes on the following warning message. You will be taken back to the Report Designer where the new mapping has taken effect.

Summary Break Report in Design Window

In the Designer, you will notice that there are two levels of nested grouping in the report; therefore, there are now corresponding Group Header and Footer sections for each. For more about report sections and their behavior, please see Section 1.5.1 - Report Sections of the Designer Guide. Now click on the Preview tab to preview the report. Again, you will be prompted to specify parameter values. Once you see the report in the Preview window, notice how the data is grouped by Category Name and Order ID, and that intermediate summaries are calculated for each group.

Summary Break Report Preview

A crosstab report shows data in a matrix-like form, allowing multi-dimensional data to be displayed in a two-dimensional layout. In the current example, we will use the crosstab layout to break-down the sales column by product category and by region.

Go to the Design tab and click on the Change Data Mapping icon. When the Report Wizard re-opens, click the Back button until you get back to the layout selection screen. From this screen, select CrossTab layout and click the Next button. At the column selection/ordering screen (next in the Wizard), change the selection to the following:

CATEGORYNAME 
REGION
SALES
		

Column Selection for Crosstab Report

Once you specify the columns in correct order, click the Next button to bring up the data mapping option for the crosstab layout.

Data Mapping Screen for Crosstab Layout

For the CATEGORYNAME column, check the option marked Row Break. This will create a row in the report data for each distinct category name. Next, select the Column Break option for the Region column. This will create a column in the report data for each distinct region. Leave the Order option as not sorted. Finally, select the Column Break Value option for the Sales column, then click on the Aggregation menu and select SUM. This will give you total sales for each category and region in the report. Once you finish specifying the options, click the Done button, then Yes on the warning message to go back to the Report Designer.

Crosstab Report in Design Window

As you can see, a report column has been generated for each region and it has been automatically totaled both vertically (columns) and horizontally (rows). Now, click on the Preview tab to preview the crosstab layout.

Crosstab Report Preview

Try previewing again (by going to the design window and then clicking Preview again), this time specifying a much smaller time range. Notice how the number of columns decreases because the values no longer exist in the data. When you expand the date range again the columns will re-appear.

Like the summary break layout, the Master & Details layout also allows you to group the data. It also allows you to automatically add column fields to the group header section, creating many unique layouts that can be configured in a side-by-side layout.

Go back to the Design tab and select Change Data Mapping from the toolbar. Again, navigate back to the layout selection screen. This time, select the Master & Details layout, then click the Next button to get to the column selection screen. In the column selection screen, select the following columns:

ORDERID 
COMPANY 
QUANTITY 
PRODUCTNAME 
UNITPRICE 
SALES
		

Column Selection for Master & Details Report

Once you specify the columns in the correct order, click the Next button to bring up the data mapping dialog for the master & details layout.

Data Mapping Screen for Master & Details Layouts

Select the ORDERID field as the Primary Key from the drop-down menu in the lower-left portion of the screen. This will group the data by the OrderID field. Next, check the Master Field option for the Company column. This will place the Company field in the Group Header section of the report.

Instead of clicking Done to get to the Report Designer from this screen, click the Next button to invoke additional pre-formatting options. Once again, click the Yes button on the warning message. The first dialog allows you to add several elements to the report.

Add Report Elements Dialog

Check the boxes to add a report title, page number, and date. Enter a text you want to use as the report title and specify the format and location of the date and time using the drop-down boxes for each option. Once you finish setting the options, click the Next button. A new dialog will open which will enable you to specify a style for the new report.

Report Style Selection

Select the Block Left-Align style and then click the Done button. You will be taken back to the Report Designer window, where new elements have been added and the report will also have default formatting applied.

Master & Details Report in Design Window (with pre-formatting)

As you can see the OrderID and Company fields have been generated in the Group Header section. Now preview the report and you will see a group with each order, as well as the page number and date in the section (header or footer) in which they were placed.

Master & Details Report in Preview Window

Report elements can be moved one by one by clicking and dragging the cell. Report elements can also be moved as groups. To move a group of elements, you must first select the group using the selection box. To activate the selection box, click and drag to draw a box around the report columns. When you release your mouse, they will become highlighted. To add more elements to your current selection, press CTRL key and draw another selection box (while still holding the CTRL key).

Drawing a Selection Box in Report Designer

Once the fields in the report are selected, click and drag to indent them about half an inch. Next, click the left alignment button on the toolbar: selection box button on the toolbar: . This will align all of the cell text to the left edge of a cell.

There are number of options available that allows you to control how the data are displayed (date format, decimal places, rounding, etc). Using the selection box again, select the UnitPrice and StainPrice columns (just the column fields not the headers).

Two Column Selection

Once the columns are selected, select the Data Format option from the Format menu. This will open a dialog prompting you to select which data type you would like to set the format for. Select Numeric Format and click Select.

Data Type for Formatting Dialog

This will open a new dialog. From this dialog, select Fixed Point and click the Format button. At the next dialog, specify 2 decimal points and select the dollar sign as the Units symbol.

Set Format Dialog

Numeric Format Dialogs

Click Ok and Ok again at the previous dialog and the selected fields will be converted to currency format.

Numeric Data in Currency Format

Next, select the Discontinued column by clicking on it (the border outline will appear). Again, click the Data Format button. This time a dialog will appear allowing you to select a format for the Boolean column. Select Yes/No and click Ok. The data in the column will now change to No.

Data Format Dialog for Boolean Data

Now we will edit the label text. By default, column headers will display column names from the database. However, you can override these headers with a custom one. To do so, double click on the ProductID cell and a dialog will appear allowing you to modify the column header. Insert a space between "Product" and "ID" and click OK. The change will appear in the Design window.

Edit Column Header Dialog

Repeat this for each column label (except "Discontinued") in order to have proper names for all of them.

The dual colors feature in EspressReport allows users to differentiate different rows or groups of data by changing the background color and/or font. To turn on dual colors, use the group selection tool to select all columns in the report (not headers).

Next, select the Dual Colors button on the toolbar: . A dialog will appear prompting you to set dual colors for the columns. Click the check box labeled Enable Dual Colors. Then select the Row Index radio button to indicate that you want to change color based on a row value. Enter 1 for the row index value.

Dual Colors Dialog

Next, click the Background Color button. A dialog will appear, giving you the option to set the background transparent and showing the current background color.

Background Transparency Dialog

Click the button labeled Click and a new dialog with color swatches will appear, allowing you to change background color.

Choose Color Dialog

Select a new background color you want to use and click the OK button. You will be returned to the first dialog, where the color selection will be reflected. Click the OK button again and you will be returned to the Dual Colors dialog. Once you are back at the Dual colors dialog, click the Font Style and Size button. This will bring up a dialog allowing you to specify the font, font size, and font style for the alternating rows.

Font Style and Size Dialog

From this dialog, set Name to Dialog, Style to Plain and Size to 9. This will match the fonts for the alternating rows. Click the Ok button to go back to the dual colors dialog and once more to return to the Report Designer. Now when you preview the report, you will see alternating bands of color for each row.

Dual Colors in Preview

To further customize reports, many different types of elements can be added to a report template.

Q.3.4.4.1. Insert an Image

To insert an image into the report header, you first need to resize the report section to fit the image. In the Design window, place the mouse over the lower section divider of the Report Header section, then click and drag down making the section about an inch taller.

Resizing a Report Section

Once the section is resized, click the Image button on the toolbar: . A small rectangle will follow your mouse pointer around the Design window. Position the rectangle in the upper left corner of the Report Header section and click. A dialog will appear prompting you to select an image to insert. Click on Browse and navigate to help\examples\DataSources\database\Woodview.gif. You should now see an image in the preview panel.

Insert Image Dialog

Click OK and the image will be inserted into the report. The image will be represented by a gray rectangle in the Design view. You can see the image when you preview the report.

Q.3.4.4.2. Insert a Title

To insert a title, click the insert label button on the toolbar: . A small rectangle will now follow your mouse pointer around the Design window. Position the rectangle next to the image you inserted and click. A dialog will appear prompting you to enter the label text. Type in the text of your desired title.

Insert Label Dialog

Click OK and the title will be added to the report. As you can see, by default the text is fairly small. To change this, change the font size dialog on the toolbar to be 18pt font.

Toolbar Font Options

When you do this, you will notice that some of the text in the title cell has now disappeared. This is because the text is now larger than the defined space. To resize the cell, click and drag on the resizing handles until you can see the report title again.

Then click the Left Alignment button on the toolbar to set the text alignment to the left as you did for the other report elements. Move and position the title cell so it is next to the inserted image.

Report Header with Image and Title

Q.3.4.4.3. Insert a Line

Next, we will add a horizontal line below the column headers. To do this, first resize the Table Header section slightly to provide some more space below the column headers. Next, select the Insert Horizontal Line button on the toolbar: . Your cursor will then change into a cross. Click below the ProductID header and drag across to the last column to draw a line.

Drawing a Line in Report Designer

While selecting report elements, you may have noticed the Report Explorer panel on the left side of the Report Designer, showing the report elements in a tree format. Click to expand some of the sections and you will see all the elements in the report represented in the tree. If you select one of the elements in the tree, the corresponding element in the report will be selected (and vice-versa).

Designer with Explorer Open

You can close the report explorer by selecting Option → Report Explorer, or by clicking on the X button in the top-right corner of the Report Explorer panel.

In EspressReport, each of the report sections has a number of configurable options allowing you to display data in sections in a number of different ways. To invoke the options menu for a section, click the button for that section on the left side of the design window. In this example, bring up the options menu for the Table Header section by clicking on the corresponding button.

From the pop-up menu, select Repeat On Every Page. This will cause the Table Header to be drawn on each report page instead of only once (which is the default). For more about report section options, see Section 1.5.3 - Section Options of the Designer Guide.

Now that you have finished formatting the report, preview it to see the results.

Finished Report

To insert a formula, click the Insert Formula button on the toolbar: . This will bring up a dialog containing all the formulas within the report. Notice that the template has several existing formulas. Click the NEW button to create a new formula and enter name ItemTotal at the prompt.

Report Formula List

The Formula Builder window will then open. Double click on the Columns folder on the right side to expand it. Then double click on the UnitPrice column to add it to the formula. Next, click the multiply * button, then double click on the Quantity column to add it. The finished formula should look like this:

{UnitPrice}*{Quantity}
		

Formula Builder Window

Click the Test button to ensure that the formula is entered correctly. Then click OK. You will be taken back to the formula list where your new formula has been added. From the formula list, select the ItemTotal formula that you just created and click the Insert button. The dialog will close and a small dotted rectangle will follow your pointer around the design window. Position the formula below the Item Total label and between the lines in the Table Data section, then click. The formula will then be added to the report.

Now preview the report and choose Use Live Data at the next dialog. Notice that because the formula was added to the Table data section, it now computes for each row of data. You also may have noticed that the formula data format was automatically set to currency.

Notice that the formula you added in the previous section does not correctly calculate the line total for the invoice. By only multiplying the unit price and the quantity, the formula is ignoring whether an item was stained (which incurs an additional cost). To take care of this, we will use cell scripting.

To add a script, select the ItemTotal formula that you created in Section Q.3.5.1 - Add a Formula and click the scripting button on the toolbar: . This will bring up a dialog containing all the scripts in the report. Since no scripts have been previously added, the dialog will be blank.

Script List Window

Click NEW to create a new script. In the prompt, type the name StainCheck for the script. Click Ok and the formula builder will open allowing you to add the script. Enter the following script:

if ({Stain} == True) { 
value=({UnitPrice}+{StainPrice})*{Quantity}; 
 }else { 
value={UnitPrice}*{Quantity}; 
 }
	    

The script dynamically modifies the ItemTotal price depending on whether the item has been stained or not. If the item has been stained

{Stain} == True

, then the ItemTotal value includes the stain price. If the item hasn't been stained, then the price is calculated in the same manner as before.

Formula Builder for Cell Scripts

Click Test to ensure that the script has been entered correctly. Then click Ok. You will return to the script list where the new script has been added. In the script list, select the script that you just created and click the APPLY button. The script will be applied to the column. Notice that a check mark now appears in the upper left corner of the Item Total cell.

Cell with Applied Script in Design Window

In EspressReport, formulas can also be used to aggregate report columns. Adding an aggregation is the same as adding a formula like in Section Q.3.5.1 - Add a Formula. Click the Insert Formula button on the toolbar: to add a new formula. Name this formula SubTotal.

In the formula builder, double click on the Numeric Functions folder to expand it. Double click on the Sum function to insert it into the formula. Next, use the cursor to highlight the "field" portion of the sum function. Then double click on the Columns folder to expand it. At the end of the list there is "ItemTotal" that you created in Section Q.3.5.1 - Add a Formula. Double click on it to add it to the formula. The finished formula should look like this:

sum({ItemTotal})

Formula Builder with Aggregation Formula

Click Test to ensure that the formula is entered correctly. Then click OK to return to the formula list. In the formula list, select the formula that you have created and click the INSERT button. The dialog will close and a small dotted rectangle will follow your pointer around the design window. Position the formula in the Group Footer section of the report, below the Item Total column and next to Sub-Total, then click to add it.

Because the formula is in the Group Footer section, it will not calculate until the report is run and only displays the text of the formula. Now preview the report. Notice that the aggregation reflects the values that are modified by the cell script.

Report With Formulas

For additional tutorial, add two more formulas to the Group Footer section. One to calculate the sales tax and one to calculate a grand total for the order.

In this report, we will add a chart below the summaries in the Table Footer. First, you need to make some room for the chart. Right click on the Table Footer and select Insert Section from the pop-up menu to insert a new section. Then enlarge it to add about four inches of space. Next, click the Insert Chart button on the toolbar: . A small dotted rectangle will follow your pointer around the Design window. Position it below the "Total" label in the Table Footer section and click. The Chart Designer will then load and guide you through the steps of generating the chart.

The first screen that you see prompts you to specify information about the chart's data source. Charts can retrieve their data from the report or they can have an independent data source.

Chart Data Options

In this case, select to use Report Data for the chart and click OK. This will bring up a table showing the report data from which the chart will be drawn.

Chart Data Dialog

Click the Next >> button to continue to the next dialog in the Chart Wizard. The next screen allows you to select a chart type. You can toggle between two-dimensional and three-dimensional chart types using the radio buttons. Select a two-dimensional column chart and click the Next >> button.

Chart Types Dialog

The next screen allows you to set data mapping for the chart. Data mapping is the process by which the columns of data from your data source are mapped to the elements of the chart. Select the Multi Selection option next to the Data Series field. The field should change from single-value to multi-value selection box. Use Ctrl+click method to select the following four columns as the data series:

East 
Midwest 
South 
West
		

Now set the Category (X) option to “Product Category”. The dialog should now look like this:

Data Mapping Dialog

Once you finish setting up the mapping options, click the Done button and you will go to the Chart Designer window where you can customize the chart.

The first thing we will modify in the chart is the canvas size. This controls the size of the finished chart. To do this, select Format → Canvas. This will bring up a dialog allowing you to modify the chart canvas.

Chart Canvas Dialog

Select the button to specify the measurements in inches, un-select Maintain Ratio option and set the new chart canvas size to 5 inches by 4 inches. Click OK. This will resize the canvas in the view part of the Chart Designer.

Now that the chart canvas has been resized, the chart plot will appear small and portions of the X-Axis labels may be truncated. This can be adjusted by resizing the chart. You can click and drag on the chart plot to move it or right click and drag to resize it. You can also click and drag to move the legend. Use these options to position the chart plot and legend on the canvas.

Chart Designer with Positioned Chart and Legend

Next, you can modify the format of the axis labels. To do this, click the Axis Elements button on the toolbar. This will bring up a tabbed dialog allowing you to set different options for each chart axis. Click on the "Y Axis" tab to bring up options for the value axis.

Axis Elements Dialog

Check the box marked Show grid to add grid lines to the Y-Axis. Then select Fixed point for the data format and click the Format button. This will bring up an additional dialog allowing you to set format options for numeric data. Enter number 2 as decimals and click OK.

Value Axis Fixed Point Formatting Dialog

Click OK again to dismiss the axis elements dialog and you will see the specified changes reflected in the chart.

Chart After Axis Element Formatting

To modify colors of the chart elements, you can use one of the preset “Color Sets”. Click on the Color Set tab in the right side of the Chart Designer window and choose any color set. This will apply the color set for the data elements. You can also change the color for any element by clicking on it to select it (hint: the Design window will indicate the currently selected element at the lower left corner) and picking a new color from the color panel.

Picking a color set

Next, you can add titles to the chart. To do this, select Insert → Titles. This will bring up a dialog allowing you to enter titles for the chart, as well as for each of the axes.

Chart Titles Dialog

Leave the main title blank and enter titles for both the X and Y axes. The titles will then be added to the chart. Titles are placed automatically but you may need to manually adjust their positions by clicking and dragging the text on the chart canvas.

Chart with Axis Titles

Finally, you can customize the appearance of the plot area by adding a background and border to the plot. To do this, Format → Plot Area. This will bring up a dialog allowing you to set display options for the chart plot.

Plot Area Dialog

Select to draw both the plot area and the border with a thickness of 1. Specify None for the appearance. Choose the Enable Gradient option. Once you specify all the options, click OK and the plot area for the chart will be modified. You can change the background color of the plot area by clicking on it to select it and then modifying the color in the color panel.

Chart with Plot Area

Next, save the changes you have made by clicking the Save button on the toolbar (because the chart uses report data, it will be saved automatically in the /chart/ directory). Then exit the Chart Designer by selecting File → Exit. This will return you to the Design window where a gray rectangle now represents your chart.

Chart in Report Designer

Now preview the report. You will see the chart displayed below the table.

Report with Chart in Preview