3.) Designer Quick Start

This section contains a series of short exercises designed to illustrate some of the basic features of the Report Designer.  For more detail about any of the features described in this section, please see the Designer Guide portion of the documentation.

3.1.) Start Report Designer

This example assumes that you will be running Report Designer locally on the machine on which it is installed.  For details about running remotely please see section 2.4 of the Designer Guide.

Before Report Designer can start, EspressManager must be running.  EspressManager is the back-end component that manages data access, and file I/O for Report Designer (this allows it to run both locally and remotely).  To start EspressManager execute the EspressManager.bat file in the root directory of your installation (EspressManager.sh for Unix installations).  By default, when EspressManager starts the monitor will open in a new window.


EspressManager Monitor

With EspressManager running you can then start Report Designer by executing the ReportDesigner.bat file in the root directory of your installation (ReportDesigner.sh for Unix installations).  A dialog box will then appear prompting you to log in.  To log in as the default user, use the user name "guest" with no password (see section 2.2.1 of the Designer Guide for more on configuring users).


Designer Login Window

After you have entered the user name and password, click 'Start Report Designer' and the Report Designer will open in a new window.

3.2.) Set Up Data Sources

Data sources are maintained within a data registry.  To create a new data registry, first select to start a new report by selecting 'New' from the File menu.  This will bring up a dialog prompting you to use an existing registry, or create a new one.


Registry Selection Dialog

Select to start a new data registry, and click 'Next'.  This will open a dialog prompting you to specify a name for the registry.  Enter any name you would like for the data registry, and click 'Ok'.  The data registry will open in a new window.


Data Registry Window

3.2.1.) Setup Database Connections

EspressReport allows you to connect to JDBC and ODBC compliant data sources.  Examples of each are included with your installation.  The examples in this guide will use the JDBC source.  If you prefer, you can use the ODBC versions, by using the templates under help/quickstart/templates/Access

3.2.1.1.) Setup a JDBC connection

In this exercise, 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).  To have EspressManager make a connection to a JDBC data source, you will need to modify the EspressManager .bat or .sh file so that it picks up the classes for the JDBC driver.

Open the EspressManager .bat or .sh file in a text editor or at the command line, and modify the "-classpath" argument to point to the hsqldb.jar file which is under the help/examples/DataSources/database directory of your EspressReport installation.  Be sure to put the path to the .jar file in the argument as well.  In the .bat file separate files in the classpath with a semi-colon, and in the .sh file use a colon.

*Note - If you're running EspressManager and/or Report Designer, you'll want to shut them down before modifying the .bat or .sh files, as you'll need to re-start them in order for the changes to take effect.

The modified EspressManager.bat file should look like this:

The modified EspressManager.sh file should look like this:

Once you have finished the modifications, save the changes you've made to the .bat or .sh file, and re-start EspressManager.

*Note - If you're running on Mac OS X and you elected to create aliases when installing, you will need to modify the espressmanager.app package to add the hsqldb.jar to the classpath.  To do this right-click (CTRL+Click) on the espressmanager.app and select 'Show Package Contents' from the pop-up menu.  Then navigate to the Contents where you will see a file called 'Info.plist'.  Open this file and add the .jar file to the classpath argument.

With the modified EspressManager running, re-start Report Designer, and open your data registry again.  From the Data Source Manager, click on the "Databases" note in the left-hand frame, and click the 'Add' button.  A dialog will then appear prompting you to enter the connection information for the new database.  Enter "Woodview" as the name of the Database, 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 user name 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've entered the information correctly.  Then click 'OK' to bring back up the data source manager window, where there will be a new node under "Databases" for Woodview.

3.2.1.2.) Setup an ODBC connection

For Users running on a Windows platform, EspressReport can also make a connection to an ODBC source, using Sun's ODBC-JDBC bridge. 

In this exercise, we will set up an ODBC connection to the Woodview Access database that comes with the EspressReport installation.  To do this, you must first set up the Woodview database as a data source in your system.  To do this, launch the ODBC data sources window from the Windows control panel.  Then add a user or system DSN.  Select Microsoft Access as the database driver, and specify the data source name as "Woodview".  Then click the 'Select' button under 'Database'.  Browse to the Woodview.mdb file, which is in the EspressReport installation under help/examples/DataSources/database.  Select this file.  You should now see a new entry under the "System DSN" or "User DSN" tab called Woodview.


Windows ODBC Data Source Administrator

Once you have set up the DSN for Woodview, go back to the Data Source Manager.  Click on the "Databases" note in the left-hand frame, and click the 'Add' button.  A dialog will then appear prompting you to enter the connection information for the new database.  If you are using Sun's JVM (i.e. JRE 1.2 or higher) then the default driver specified will work.  Enter "WoodviewODBC" as the name of the Database, and enter "jdbc:odbc:Woodview" for the URL.  Click on both the 'Require Login' and 'Save Password' boxes.  Then enter 'admin' for both the user name and password.


Setup Database Dialog

Leave the auto-join and table name properties alone, and click the 'Test Connection' button to make sure you've entered the information correctly.  Then click 'OK' to bring back up the data source manager window, where there will be a new node under "Databases" for WoodviewODBC.

3.2.2.) Create a Query

EspressReport provides a number of different interfaces to query a database to retrieve the report data.  You can type a 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.

*Note - You can use either the JDBC or ODBC data source for this example, the data is the same.  The screen shots are from the Access Database.

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


Query name Dialog

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


Query Builder Dialog

To add a table to the query, select the 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:

The tables will appear in the top half of the Query Builder window.  You will see the 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, or double click on the "Table" and "Field" fields in the lower (QBE) portion of the Query Builder window, and 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

To construct the column, first click on the left parenthesis button.  Then double-click on the "Tables" folder.  It will open up into five nodes, one for each table you selected for the query.  Opening a table folder will list all of the column fields in that table.  Open up the "Products" folder, select "UnitPrice" and click 'Insert'.  Then click the add ('+') button.  Next insert "StainCost" from the "Order Details" table.  Then click on the right parenthesis button.  Click the multiply ('*') button, and finally insert "Quantity" from the Order Details column.  The finished formula should look like this: (Products.UnitPrice + [Order Details].StainCost) * [Order Details].Quantity.


Formula Builder Window with Formula

Click 'OK' and the computed column will be added to the query.  Next, we will give the column you built an alias.  Right-click on the column and select 'Alias' from the pop-up menu.  A dialog will appear prompting you to specify a column alias.


Column Alias Dialog

Click, 'Ok' 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've 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 source manager window.  There will now be a node under "Queries" for the query you've just designed.


Data Source Manager With Query

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 exercise we will add parameters to the query created in section 3.2.2.

To open the query that you created, select it and click the 'Edit' button in the data source manager.  Your query will re-open in the query builder.  The Tables window will open on top of the query builder.  Click 'Close' to close the tables window, and scroll down in the lower (QBE) portion of the query builder window, until you see the "Condition" field.  Right-click in the "Condition" field under the OrderID column, and select 'Build' from the pop-up menu.  This will bring up the formula builder allowing 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" for the parameter name, and click 'OK'.  The parameter will be added to the query.  Then click the 'And' button.  Click the 'Parameter' button again.  Enter "EndDate" as the 2nd parameter name.  The finished condition should now appear as Orders.OrderDate BETWEEN :StartDate AND :EndDate.


Formula Builder with Conditions

Click 'Ok' to close the formula builder and 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 some properties for the query parameters.


Parameter Initialization Dialog

From this dialog, select to map the parameter to a database column by clicking the option under "Attributes".  Select "Orders.OrderDate" from the drop-down menu.  This will automatically fill in the default value and data type options.  Next, modify the "Prompt name" to read "Start Date".  Click the 'Next Parameter' button, and map the "EndDate" parameter to the same column.  Click on the drop-down menu to select an end date.  Select a date far enough from the start date that by default you'll have more than a couple records to work with (this makes report design easier).  Change it's prompt to read "End Date".

Click 'Ok' to close the initialization dialog 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 'OK'.  You will now see the filtered result in the datasheet window.  Now, click 'Done' from the File menu to save the changes that you've made to the query.

3.2.3.) Create a Data View

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

*Note - You can use either the JDBC or ODBC data source for this example, the data is the same.  The screen shots are from the Access Database.

This will open a new dialog asking you to select database tables that you would like to use.  Select the following tables:


Data View Tables Dialog

Next, click on the 'Joins' tab.  You'll 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 modify the auto-joins if necessary.  Click 'Ok' 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.  Call the data view "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:

Now add fields from the other tables as follows:

Next click the 'Add Heading' button.  At the prompt specify the name "Customer Information".  Add two more headings in this manner, one called "Shipping Information" and one called "Order Information".  Once the headings have been created, select the following fields (Using CTRL+Click or SHIFT+Click for multiple selection):

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

Add these fields to the "Shipping Information" group in the same manner as you did before.  Next select the following fields:

Add these fields to the "Order Information" group.  Next, select the "Contactname" field on the right-hand (view) side and click the 'Rename' button.  In the dialog, specify the name "Contact name".  Repeat this for each field, giving it a clear name rather than a single word.

Next, select the "Order ID" field on the right-hand (view) 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:


Data View Fields Window

Now the data view has been completed.  Click 'OK' in the fields dialog to save the view.  It will be saved as a new node under "Data Views" in the data source manager.

3.2.3.1.) Query a Data View

Now that a 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 the user has access to.  In this exercise we will create a query for the data view you created in section 3.2.3.

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 'Add':


Data View Query Field Selection Dialog

Once you've finished adding the fields, click 'OK'.  This will bring up a new window allowing 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 11/2/2000 as the first date, and 12/2/2000 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 return to the data source manager.  There will now be a new node for your query under the "Invoicing" data view.

3.3.) Report Mapping

This section looks at the different ways data from the data source can be mapped into a report structure.  In this section we will take the result set from the query created in section 3.2.2, and use it to generate reports in the different layouts supported in EspressReport.  For detailed information about all the different layout and mapping options, see Chapter 4 of the Designer Guide.

3.3.1.) Simple Columnar Layout

The simple columnar layout is the most straightforward mapping.  Columns from the data source are drawn in a straight table in the report without any grouping and breaks.

To begin mapping a report, first select the query 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 (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 would like 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 would like to use in the report, as well as re-arrange the column order.  (Although the column order isn't 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:

Once you have selected the fields, click 'Next' to continue on in the wizard.  The next dialog is the data mapping dialog.  Here is where you can select how to map the 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, elect to leave all the columns visible and do not select the top N option. (For more about top N presentations, please see section 4.1.1.1 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 hand corner of the window.  A parameter selection dialog will appear prompting you to select a start and end date by which to filter the report.  Specify a large enough range that you'll get more than a couple of records, and click 'OK'.  You will then see the report output.  Notice how the simple columnar layout just places the columns from the result set in the report directly, without any grouping/sorting/summaries.


Simple Columnar Report Preview

3.3.2.) Summary Break Layout

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 3.3.1, we will convert it into a summary break layout.

Go to the design window, 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 the last data mapping dialog, hit the 'Back' button until you get back to the report layout dialog.

Change the report layout type to summary break, and click 'Next'.  In the next screen, keep the column selection the same and click 'Next' again to go to the data mapping window.  You'll notice that there are more options in this window than there were 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 have the report group by those two columns.  Then from the drop down menus under "Aggregation" select to sum the Quantity and Sales columns.  Also, un-check the 'Apply Template' option, as you do not need to carry over the formatting from the simple columnar layout.  Once you have finished specifying options, again click the 'Done' button.  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'll notice that because there are two levels of nested grouping in the report there are now corresponding Group Header and Footer sections for each.  For more about report sections and their behavior, please see section 5.1 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

3.3.3.) Crosstab Layout

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

Go to the design window and select 'Change Data Mapping'.  When the report wizard re-opens click the 'Back' button until you get back to the layout selection screen.  From this screen, select to use a crosstab layout and click 'Next'.  At the column selection/ordering screen (next in the wizard), change the selection to be the following:


Column Selection for Crosstab Report

Once you have specified the columns in the 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, and click on the 'Aggregation' menu to select 'SUM'.  This will give you the total sales for each category and region in the report.  Once you have finished specifying the options, click the 'Done' button to go to the Report Designer.


Crosstab Report in Design Window

As you can see a report column has been generated for each region, and 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.

3.3.4.) Master & Details Layout

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 a one to many layout that can also be configured in a side-by-side layout.

Go back to the design window and select 'Change Data Mapping' from the Data menu.  Again, navigate back to the layout selection screen.  This time select the master & details layout.  Click 'Next' to get to the column selection screen.  In the column selection screen, change the selection to include the following columns:


Column Selection for Master & Details Report

Once you have specified 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 hand 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 the additional pre-formatting options.  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 the text you would like 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 have finished setting the options, click 'Next'.  A new dialog will open allowing you to specify a style for the new report.


Report Style Selection

Select the 'Block Left-Align' style, and click the 'Done' button.  A warning will pop-up that you can ignore, and then you will be taken to the Report Designer window, where the elements have been added, and now there is some default formatting applied to the report.


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