3.) Working with Data Sources

The first step in designing a report is to retrieve the data that you would like to use.  Within Report Designer you can draw data from JDBC/ODBC compliant databases, text files, XML files, EJBs, and even bring in object/array data through class files.  All of the data source information is stored within the Data Source Manager.

3.1.) The Data Source Manager

The Data Source Manager is an integrated utility that stores location and connection information for the data sources employed by a particular user.  The information is stored in an XML registry file.  You can set up as many different data registry files as you like, and there is no limit to the number of data sources that can be stored within a single registry.  The registry is used as an aid at design time, and is not required to deploy reports, since the data or the data source information is stored with the report file.

*Note - The XML data source repository only stores location and connection information, and not the actual data.  It is not an XML file that contains data to be used in a report.

3.1.1.) Using Data Source Manager

When you start a new report (by selecting 'New' from the File menu, or by clicking the 'New' button on the toolbar), or when you first launch Report Designer, the report wizard will launch.  The Wizard is a step-by-step process that will guide you through the basic construction of your report.  The first dialog in the report wizard will prompt you to specify the data registry file that you would like to use, or to create a new registry.  By default, data source registry files are saved in the DataRegistry directory.  Once you have either opened an existing registry or started a new one, the Data Source Manager window will open.


Data Source Manager Window

The left-hand side of the window contains a tree listing all of the data sources in the registry file.  Grouped under "Databases" are the individual databases and their associated queries and data views.  Grouped under "JNDIDataSources" are database sources that use JNDI (Java Naming and Directory Interface) name to connect instead of JDBC.  Grouped under "XMLFiles" are all the XML files and their associated queries, grouped under "TXTFiles" are all of the specified Text files, grouped under "ClassFiles" are all of the specified class files, grouped under "EJBs" are all the specified EJB connections, and grouped under "SOAP" are all the SOAP data sources.

The right-hand side of the window contains a series of buttons controlling all of the functions of the Data Source manager.  Each button performs the following functions.

3.2.) Data From a Database

EspressReport can draw data from any JDBC/ODBC compliant database.  In order to connect to a database via a 3rd party driver (other than the ODBC-JDBC bridge), you will need to modify the EspressManager so that it will pick up the classes for the driver.  You can add the classes using the configuration interface (see section 2.3.1), or by manually editing the EspressManager.bat/.sh file.  In the file, add the appropriate classes or archives to the "-classpath" argument.  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 JDBC driver to the classpath.  To do this righ-click (CTRL+Click) on espressmanager.app and select 'Show Package Contents' from the pop-up menu.  Then navigate to the Contents folder where you will see a file called 'Info.plist'.  Open this file and add the appropriate classes or archives to the classpath argument.  Note that JDBC drivers for MS SQL Server, MySQL, Oracle, Informix and PostgreSQL databases are included as a convenience.  Other database JDBC jar files were not included because of licensing, multiple drivers and/or other concerns although support for those databases exist and the jar files can be explicitly added.

The first step in using a database as the data source, is to set up the database in the registry, and specify the connection information.  To add a database, click on the "Databases" node, and click the 'Add' button.  This will bring up a window prompting you to specify the connection information for that database.  You can choose a database to connect to from the Driver List or specify the information directly.  Fields to enter are database name, URL, and driver.  You can also select whether the database requires a login, and if so whether you want to save username and password information by using the check boxes at the bottom of the window.  If you select to save login and password information, you can then enter that information in the last two spaces.  Click on 'Ok' and the new database will be added to the Data Source Manager window.


Add Database Dialog

In order for EspressReport to make a connection to the database, the following information must be provided.

Once you've specified the connection information, you can test the database connection by clicking the 'Test Connection' button.  This will test the connection using the information you've provided, and report any problems.

The "Default Options" portion of the dialog allows you to specify some properties for queries generated through the Query Builder interface or data views.  You can specify whether to auto-join selected tables.  Auto-join will attempt to join primary and foreign keys defined in the database.  You can specify the table name format that should be used for queries either unqualified (only table name), or 2-part or 3-part qualified.  Properties specified here will become the defaults for new queries and data views.  They can also be modified for individual queries.

The "Multiple Database Options" portion of the dialog allows you to specify additional databases (i.e., additional database URL's) to obtain data from within the query.  This option is only available when the database (original and any additional database) is MS SQL Server and 3-Part Qualified Table Name option is chosen.  Note that the same login details as well as the same driver (as defined in the original connection) is used to connect to the specified additional databases as well.  The query can obtain data by referencing a column in the additional database using a 3-Part table nomenclature.

There are two sample databases included with the EspressReport installation.  One is an HSQL (a pure Java application database) database, and the other is an MS Access database.  Both contain the same data, and are located in the help/examples/DataSources/database directory.  For details about how to set up connections to these sample databases, please see section 3.2.1 of the Quick Start Guide.

3.2.1.) JNDI Data Sources

In addition to connecting to databases via JDBC, EspressReport lets you use the JNDI (Java Naming and Directory Interface) to connect to data sources.  In EspressReport JNDI data sources are treated just like database data sources and support the same functionality (queries, parameters, data views, etc.).  The advantage to using a JNDI data source is that it potentially makes it easier to migrate reports between environments.  If data sources in both environments are setup with the same lookup name, reports can be migrated without any changes.

To connect to a JNDI data source in the Report Designer you must have a data source deployed in your Web application environment, and you must have EspressManager running as a servlet in the same environment.  For more information about running EspressManager as a servlet, see section 2.3.2.

To setup a JNDI data source, select the "JNDIDataSources" node in the data source manager, and click the 'Add' button.  This will bring up a dialog allowing you to specify the connection information.


JNDI Setup Dialog

The first option allows you to specify a display name for the data source.  The second option allows you to specify the JNDI lookup name for the data source.  The third allows you to specify the initial context factory for the data source, and the last option allows you to specify the provider URL.  This information will vary depending on the application server you're using as different vendors implement JNDI data sources differently.  You can test the connection by clicking the 'Test Connection' button.

3.2.2.) Queries

Once you have added a database, a new node for your database will appear in the Data Source Manager window.  When you expand the node you will see two more nodes, one called "Queries" and one called "Data Views".  These are the two ways to retrieve data from your database.  To create a new query, select the "Queries" node and click the 'Add' button.  A dialog will come up prompting you to specify a query name, and select whether you would like to enter the SQL statement as text, or launch the Query Builder. 

If you select to enter an SQL statement, a dialog box will come up allowing you to type in your SQL statement.  From this dialog, you can also load a QRY or text file containing SQL text, or execute a stored procedure.  If you select to launch the Query Builder, the Query Builder will open in a new window, allowing you to construct the query visually.  After you have finished building or entering the query, you will return to the Data Source Manager window and the query will appear as a new entry under the "Queries" node for your database.

3.2.2.1.) Using Query Builder

The Query Builder is an integrated utility that allows you to construct queries against relational databases in a visual environment. To launch the query builder, add a new query within the Data Source Manager, and select the 'Open Query Builder' option.  The Query Builder will then open in a new window.  You can also launch the query builder to modify an existing query by double clicking the query name in the Data Source Manager.

The main Query Builder window consists of two parts.  The top half of the window contains all of the database tables selected for the queries, and their associated columns.  The top window also shows what joins have been set up between column fields.  The lower half of the main window or QBE (query by example) window contains the columns that have been selected or built for the query, and their associated conditions.


Query Builder Window

There are three tabs at the top of the Query Builder window.  These allow you to toggle between different views.  The "Design View" tab is the main designer window described above.  The "SQL View" tab shows the SQL statement that is generated by the current query.  The "Datasheet View" tab shows the query result.

When you have finished constructing the query, select 'Done' from the File menu to return to the Data Source Manager.

3.2.2.1.1.) Tables

When the Query Builder first launches, a tabbed window will appear, containing a list of all the tables within the database.  A second tab contains a list of all the views in the database, and a third tab contains a list of other queries you have designed for the database under a heading called "Queries".  From this window, you can select the tables/views/queries from which you would like to build the query.  You can also load a previously designed query as a table.  To add a table, select it and click the 'Add' button, or double click on the table name.  When a table is added it will appear in the main Query Builder window, and will show all of the columns within that table.  To remove a table, right click within the table and select 'Delete' from the pop-up menu.  You can also specify a table alias, and sort the fields alphabetically from this menu.  You can close the tables window, by clicking on the 'Close' button.  To re-open it, select 'Show Tables' from the 'Query' menu.

*Note - By default, the tables will appear using the name format you specified when setting up the database connection.  You can change the naming by selecting 'Table Name Format' from the Query menu.


Query Builder Tables Window

3.2.2.1.2.) Joins

When you select database tables for the query, the Query Builder can auto-detect joins between column fields, based on primary key-foreign key relationships in the database.  Auto-joins will be added depending on which option you selected when setting up the database connection.  Auto-joins will create a standard join between tables.  A join is represented by a line drawn between two fields in the top half of the design window.  To remove a join or edit join properties, right click on the line and select your choice from the pop-up menu.  To add a join, click and drag one column field to another in a different table.  A join will then appear.  You can change the auto-join settings by selecting 'Auto Join' from the Query menu.

Join Properties: Selecting 'Join Properties' from the pop-up menu will bring up three options allowing you to select the type of join used between the column fields.  Query Builder only supports equi-joins.  Inequality joins can be easily accomplished using the "conditions" field.  You can specify inner joins, left outer joins, and right outer joins.  See the examples below for an explanation of the different join types.

Suppose you have the following two tables: Customers and Orders

CustomerID CustomerName
1 Bob
2 Ivan
3 Sarah
4 Randy
5 Jennifer
OrderID CustomerID Sales
1 4 $2,224
2 3 $1,224
3 4 $3,115
4 2 $1,221

An inner join on CustomerID on the two tables will result in combining rows from the Customers table and those from the Orders table such that each row from the Customers table will be "joined" with all the rows in the Orders table with the matching CustomerID value.  Rows from the Customers table with no matching CustomerID fields from the Orders table will not be included in the query result set.

Now suppose you create a query by selecting the OrderID, CustomerName, and Sales fields with an inner join on the CustomerID field.  The select statement generated by the Query Builder would look like this:

The result of the query is shown below:

OrderID CustomerName Sales
1 Randy $2,224
2 Sarah $1,224
3 Randy $3,115
4 Ivan $1,221

As you can see, the CustomerName entries "Bob", and "Jennifer" do not appear in the result set.  This is because neither customer has placed an order.  There are situations where you may want to include all the records (in this example customer names) regardless whether matching records exist in the related tables(s) (in this case the Orders table).  You can achieve this result using outer joins.

The Query Builder gives you the option of either right or left outer joins.  The keywords "right" and "left" are not significant.  It is determined by the order that the tables are selected in the Query Builder.  If the outer table (the one that will have all records included regardless of matching join condition) is selected first, then Query Builder will use a right outer join.  If the outer table is selected after the other join table, a left outer join is used. In our example, the Customers table has been selected before the Orders table, hence to select all of the records from the CustomerName field, the Query Builder will use a right outer join on the CustomerID fields.


Join Properties Dialog

Now, using the previous example, suppose you create the same query as before, except this time specifying to include all of the records from Customers.  The select statement generated by the Query Builder would look like this:

The result of the new query is shown below:

OrderID CustomerName Sales
  Jennifer  
  Bob  
1 Randy $2,224
2 Sarah $1,224
3 Randy $3,115
4 Ivan $1,221

As you can see, all of the customer names have now been selected, and null values have been inserted into the result set where there are no corresponding records.  If you specify an outer join, the join line connecting the two tables in the Query Builder will become an arrow in the direction of the join.

3.2.2.1.3.) Columns

The QBE window contains information on column fields selected for the query, as well as any conditions for the selection.


Formula Builder Window

3.2.2.1.4.) Using Database Functions

The formula builder component in the query builder allows you to use database specific functions when building a column or condition for the query.  You can use the functions that are supplied or add your own to the interface.

EspressReport comes with the function libraries for Oracle, Access, MS SQL, and DB2 pre-loaded.  They are stored in XML format in the DatabaseFunctions.xml file in the userdb directory.  For databases with functions not stored in XML, EspressReport will use default ones.  You can specify different database functions by editing the XML file, or creating a new one based on the DatabaseFunctions.dtd file in the userdb directory.  A sample database functions file might look like the following:

3.2.2.1.5.) Adding Extra SQL

Sometimes it is necessary to add extra SQL statements to run before or after a query.  For example, you may need to set a transaction level or call a stored procedure before executing a query, and/or commit a transaction or drop a temporary table after executing a query.  The query builder allows you to specify these extra SQL statements by selecting 'Extra SQL' from the Query menu.  This will bring up a window allowing you to write statements to execute before and/or after a query.


Extra SQL Dialog

You can enter any SQL statements you would like to run before and/or after the query in the appropriate boxes.  When you have finished, click 'Ok' and the statements will be added to the query. 

3.2.2.1.6.) Query Output

The "SQL View" and "Datasheet View" tabs let you see two different views of the query.

3.2.2.2.) Parameterized Queries

You can also use the Query Builder to design parameterized queries.  This feature allows the user to filter the data at run-time. Parameterized queries are also used for drill-down reports.  For more on drill-down reports, please see Chapter 10.

Query parameters can be defined when typing an SQL statement or using the Query Builder.  They can also be defined when running data views (this is covered in the next section).  A parameter is specified within an SQL statement by the ":" character.  Generally the parameter is placed in the WHERE clause of an SQL Select statement.  For example, the following SQL statement

specifies a parameter called "Name".  The user would then be able to enter a product name at run-time, and only retrieve data for that product.

Within the Query Builder, you can specify a query parameter by right clicking on the "Condition" field, and selecting 'Build' from the pop-up menu.  The Formula Builder will open, allowing you to place a condition on the column.


Specifying a Parameter in the Formula Builder

You can insert a parameter by clicking the 'PARAMETER' button.  A second dialog will appear prompting you to specify a name for the parameter.  Type the parameter name, click 'OK' and then click 'OK' again to close the formula builder.  You can specify as many different parameters as you would like for a query.

3.2.2.2.1.) Multi-Value Parameters

EspressReport supports a special kind of parameter that takes an array of values as the input rather than a single value.  Multi-value parameters are useful when you want to have the user filter the result set based on an unknown number of values.  For example, say a report is run to return a list customers for a specific state/province.  Users could select as many different states/provinces as they wanted and return the relevant information.

To create a multi-value parameter, place a parameter within an IN clause in an SQL statement.  For example the following query

will create a multi-value parameter named "State".  Multi-value parameters will only be created when there is only one parameter in the IN clause.  If you place more than one parameter in the IN clause i.e. Customers.State IN (:State1, :State2, :State3).  This will create three single value parameters instead.

3.2.2.2.2.) Initializing Query Parameters

When you attempt to save (by selecting 'Done' from the File menu), or preview (by clicking the "Datasheet View" tab) a parameterized query, you will first be prompted to initialize the parameter.  You can also initialize it by selecting 'Initialize Parameters...' from the Query menu, or by clicking the 'Initialize Parameters' button in the Enter SQL Dialog.


Initialize Parameter Dialog

From this dialog you can specify the following options:


Custom Parameter List Dialog

If you map the parameter, the user will see either a drop down box (single value parameter) or a list box (multi-value parameter) containing the various options.  If you choose not to map the parameter, the user will see a text box to enter their own value.  In the case of a multi-value parameter, it is recommended to let the user know in the parameter prompt that this parameter accepts multiple values.  Users can separate multiple values using a comma (e.g. ARC, DOD, TRD).  If the text requires the user of a comma, the user can use quotes to include the comma within the filter string (e.g. "Doe, John", "Smith, Mike").

Clicking on the 'Previous Parameter' and the 'Next Parameter' buttons allow you to initialize each of the parameters that have been defined in the query.

When you select to use a parameterized query to design a report, or open a report that uses a parameterized query, the report will load/start with the default values.  You will be prompted to provide parameter values when you preview the report.

3.2.2.2.3.) Cascading Parameters

By default, the user is prompted to enter all of the report parameters at once in the prompt dialog.  This configuration, however, may not be the best approach if some parameters are mapped to database columns with a significant number of distinct values.  It can be difficult to select from a very large list, and depending on the parameter combination, users may be able to select sets that don't return any data.

To assist with these problems, EspressReport provides a feature that allows the user to configure the order in which the parameters should be entered.  With this feature enabled, the user enters parameters in the dialog in a pre-defined order.  As such each selection will be applied as a filter to the next parameter prompt(s).  Using cascading parameters can limit the number of distinct values presented to the user, and can prevent the user from selecting invalid parameter combinations.

To enable cascading parameters, check the option marked "Prompt parameter in sequence" in the parameter initialization dialog.  Then click the 'Config' button to set the order of the parameter prompts.  A dialog will open showing all the parameters defined in the query.


Parameter Sequence Dialog

Using the spin boxes, you can set the sequence for the query parameters.  The user will be prompted starting with the lowest numbered parameter, and working up through the highest.  If two or more parameters share the same number, the user will be prompted to enter those parameters at the same time (in the same dialog).

By default, the parameter values for the next level are generated by rerunning the entire query with the previously prompted parameters filled in.  If the original query is slow to execute, you can improve performance by mapping higher order parameters to SQL Queries.  You can even include previously selected parameter values in the mapped query.  For more information, please see Section 3.2.2.2.2.

3.2.2.3.) Entering SQL Statements

Typically, the Query Builder is recommended for creating queries. However, there are times when it is necessary to enter SQL statements directly, for example, if the query is already created in a QRY file, if the query is built into a stored procedure/function, or if the query requires commands not supported by the Query Builder. In these situations, select "Enter SQL statement" to open the Set SQL Statement window. Here, you can enter SQL statements directly into the text area as shown below or you can load an existing QRY File.


Enter SQL Statement Dialog

To preview the result set, click on the Datasheet View tab.

3.2.2.3.1.) Calling Oracle Stored Procedures

Compared to other database systems, Oracle uses a different approach when it comes to stored procedures and functions. For example, on MS SQL Server, using the EXEC command will return a result set. However, Oracle requires the use of an OUT parameter with a REF CURSOR type to return the result set. In addition, Oracle will not accept multiple statements from a single query. Therefore, it is necessary to store the query within a stored function and use special syntax to access the existing Oracle stored procedures.

To access your Oracle stored procedures the first step is to define a weakly typed REF CURSOR using the following PL/SQL statement.

This ref_cursor type will be used to store the query result set and return as an OUT parameter. The next step is to create a function, which calls your stored procedure and executes your query. The following skeleton code will return a simple query using the ref_cursor type.

Now that the Oracle stored function is setup, it can be easily called from Report Designer using a special PL/SQL like syntax. In the Set SQL Statement window enter the following syntax to call the Oracle stored function:


Calling simple Oracle stored function

The 'BEGIN ... END;' syntax alerts the system that the user is trying to access an Oracle stored function. And the '?' notifies the report designer that a variable is reserved for the OUT parameter. The JDBC syntax for calling Oracle stored procedures is as follows:

However, EspressReport does not support this format. Preview the results by clicking the Datasheet View tab.

Here is a more practical example to illustrate how stored procedures can be used with EspressReport to develop useful solutions. Suppose you have a table called employee_table that stores an organization's location hierarchy such as the one shown here:

ID

NAME

PARENT

EMPLOYEE

1

All

NULL

0

2

America

1

0

3

Europe

1

0

4

New York

2

20

5

Santa Clara

2

30

6

Dallas

2

12

7

London

3

14

8

Paris

3

11

The table lists the various corporate locations in a tree structure. The numbers of employees are stored in the leaf nodes (e.g. New York, London, etc.) and each node contains information about its immediate parent. Suppose you want to create a report that displays the number of employees in a certain region and information about the separate branches within that region. For example, if the user inputs ID = 2 (America), you want the report to display the total number of employees in America along with the branch locations. Using Oracle's CONNECT BY and START WITH clauses, the problem is solved with two simple Oracle Stored Functions:

The function sum_employees takes the starting node as an argument and finds the sum of all leaf nodes that are descendents of that node. For example, sum_employees(3) returns 25 because there are 25 employees in Europe (14 in London, 11 in Paris). The second function, regional_employees, traverses through the tree structure starting with the locID and builds a result set from the ID, Name and the result from the sum_employees function. Then, the result set is returned through a REF CURSOR.

To call a stored function that requires an argument, enter the following statements in the Set SQL Statement window:


Calling regional_employees function

Preview the results by clicking the Datasheet View tab.


Result set from regional_employees

As seen from the results, the CONNECT BY clause traverses the tree recursively listing the American nodes together before listing the European nodes. If the user is only interested in the European locations, they can enter 3 for the parameter and the following result set would return.


Result set from regional_employees in Europe

To create a parameterized report, use the ':param_name' syntax. The SQL parser in EspressReport will be able to differentiate between the colon used for parameters and the one used for the assignment operator (':='). Here is an example using parameters.


Calling Oracle Stored Function using Parameter

When using IN parameters, it is necessary to initialize the parameters prior to executing the query. It is especially important to set the correct default data type for executing stored procedures because the parameters cannot be mapped to existing columns. More information on initializing parameters can be found in Section 3.2.2.2. To try this example, <EspressReportInstall>\help\examples\data\locationHierarchyExample.sql contains the SQL commands to create employee_table as well as the two stored functions.

3.2.3.) Data Views

In addition to the query interfaces, EspressReport provides another means of retrieving database data - data views.  Data views provide a simplified view of the database, in which users can design queries by simply selecting fields, without using the Query Builder, or having any knowledge of the underlying database structure.  Using data views administrators can pre-define tables, joins, and fields, creating in effect a local schema for the user to select from.

For example, an administrator could set up a data view for the sales department.  The appropriate database tables and fields are pre-selected, and grouped in a manner congruent with business users' logic.  For example a group called 'invoices' would have the appropriate customer and order fields.  End users would then select this data view, pick the pertinent fields, specify a date range, and then begin designing a report.

To create a data view select the "Data Views" node in the Data Source Manager window and click 'Add'.  A new window will open allowing you to select the database tables that you would like to use for the data view.


Data View Choose Tables Dialog

The left-hand window contains all of the available database tables and views.  You can add a table by selecting it in the left-hand window, and clicking the 'ADD>>' button.  By default, the data view will use the name format you specified when setting up the database connection.  You can change the naming by clicking the 'Table Name Format' button, or specify a table alias by clicking on the 'Rename' button.  You can also import selected tables and joins from another data view by clicking the 'Import Joins...' button.

The 'Joins' tab of this window allows you to specify the joins between the selected tables.


Data View Joins Dialog

The 'Joins' tab shows all of the selected tables and their associated fields.  The tables will be auto-joined depending on which option you selected when setting up the database connection.  These auto-joins create a standard join between tables.  A line drawn between two table fields represents a join.  To remove a join or edit join properties, right click on the line and select your choice from the pop-up menu.  To add a join, click and drag one column field to another in a different table.  A join will then appear.  Data views use the same join properties as the Query Builder.  For more about join properties, please see section 3.2.2.1.2.

After you have finished selecting and joining tables, click 'OK' and a new window will open allowing you to construct the data view.


Create Data View Dialog

The left-hand window contains a list of tables you have selected, and their associated fields.  Each folder signifies a table, and can be opened and closed by double clicking.  The right-hand window contains fields that have been selected for the data view.  To add a field to a data view, select it in the left-hand window, and click the 'ADD >>' button.  Fields can be removed from the data view in the same manner by selecting a field in the right-hand window, and clicking the '<< REMOVE' button.  You can create a calculated column by clicking the 'Build Formula'' button.  This will open the formula builder allowing you to build the column.  You can also define an alias by selecting any of the view fields in the right-hand window and clicking the 'Rename' button.

You can also group fields within the data view by adding headings.  This allows you to create your own organizational structure of "virtual tables" that group data from different database tables under one heading.  To create a heading, click the 'Add Heading' button.  You will then be prompted to specify a name for the heading.  The new heading will then appear as a folder in the right-hand window.  To add fields under a heading, first select the fields you would like to add from the right-hand window, and click the 'Group Fields' button.  You will then be presented with a drop-down menu, allowing you to select the heading under which you would like to add the fields.

You can add a description to any of the fields by first selecting the field and then clicking the 'Description' button, or by double clicking on a field in the right-hand side of the window. This will bring up a new dialog allowing you to specify a description for the field. End users can then access these descriptions when running the data view.

The "Conditions" tab contains a formula builder window that allows you to specify certain filtering criteria for end users. Anything added in this window, will be added to the Where clause of the generated SQL. For more on using the formula builder, please see section 3.2.2.1.3.

When you have finished creating the data view, click the 'OK' button, and the data view will be added to the Data Source Manager.  Users can now use this view to construct ad-hoc queries.

When you design a report using a data view as the data source (by selecting the data view and clicking the 'Next' button) a window will open allowing you to select which fields in the view you would like to use for the report. From this dialog, you can also build computed fields based on the available view columns.

After you have selected the fields, click 'OK' and a new window will open allowing you to specify sorting, aggregation, and  filtering conditions for the data view.


Data View Choose Fields Dialog


Data View Conditions Window

For each of the fields in the data view, you can specify sorting, aggregation, and conditions, by double clicking on the respective field. Sorting and aggregation can be selected from drop-down menus. Double clicking on the 'Conditions' field brings up a new window that allows you to specify simple selection criteria like '>', '<', '=', and 'between'. Users can build more advanced filtering criteria by right clicking on the 'Conditions' field and selecting 'Build' from the pop-up menu.  This will open the Formula Builder window allowing you to build a condition. You can also display all of the unique values in the column by double clicking on the 'View Column' button.

The Option menu in the upper left hand corner of the conditions window allows you to select a vertical/horizontal view for the conditions window, initialize any parameters in the data view, or save the query.

The selection set and conditions that you specify will be saved as a data view query with the name that you specify in the name field.  Data view queries are saved under the node for the data view.  A report created from the data view will reference the data view query for updating/modification.

3.2.3.1.) Data View Parameters

As with Query Builder, users can specify query parameters in Data Views.  To add a parameter to a data view, select a data view in the Data Source Manager, and click 'Next' to run the data view.  After you have selected fields for the data view, and you are in the conditions window, right-click in the "Condition" field for a column, and select 'Build' from the pop-up menu.  This will bring up the formula builder, allowing you to specify a parameter in the same way as in Query Builder.  For more on this, please see section 3.2.2.2.

Once, you have entered the parameter, you will be prompted to initialize it if you go to the "Datasheet View" tab, you click 'Ok' to continue on with the report wizard, or if you save the selections as a query.  You can also initialize the parameter by selecting 'Initialize Parameters' from the Option menu.

3.2.3.2.) Updating Data View Queries

Often times you will need to make changes to the structure/make-up of the data view as your data model, or requirements change.  Changes could include adding/removing fields or re-naming them.  You can propagate changes from the data view to its associated queries by selecting it in the data source manager, and selecting 'Data View Queries' from the Update menu.

All of the queries associated with the view will be scanned, and any inconsistencies in fields or field names will be presented for you to update.


Update Query Fields Dialog

For each query, you will be prompted to change any fields that no longer match the data view structure.  For each field, you can select a field from the data view to map it to, or elect to remove the field from the query.  If you want to leave the query alone, you can click the 'Skip' button.  The query will continue to run, but it will reference the old data view structure.  Click 'Apply' to save the changes to the data view query.

3.2.4.) Editing Queries

If you have selected to build a report using database data, either by designing a query in the Query Builder, by writing an SQL statement, or by running a data view, you can modify the query directly from the Report Designer without having to go back to the Data Source Manager.

To modify a report's query, select 'Modify Query' from the Data menu in Report Designer, or click the 'Modify Query' button on the toolbar.  If you have designed a query in the Query Builder, then the Query Builder interface will re-open allowing you to modify the query.  If you have entered an SQL statement, a text box will open allowing you to modify the SQL.  If you have used a data view, the data view conditions window will re-open allowing you to change the filters, or pick additional fields.

Once you have specified the changes, you will be given the option to modify the query in the data registry, save a new query in the data registry, or modify only the query in the template.


Saving Query Options

Once you have specified the save options, the modified query will be applied to the report.  Note that if you have made significant changes to the query you may have to again perform data mapping for the report.  For more on data mapping please see Chapter 4.

3.2.5.) Editing Database Connections

If you have selected to build a report using database data, you can also directly edit a template's database connection information from within the Report Designer.  To modify the connection information in the report, select 'Modify Database' from the Data menu.  This will bring up a dialog allowing you to specify a different URL, Driver, Username, and/or password for the report to use when connecting to the database. :


Change Database Connection Dialog

In addition to manually entering the database information, you can retrieve the database connection information from a data registry.  To do this click the 'Select' button on the Database Connection dialog.  This will allow you to browse to the XML registry file from which you would like to pull the database connection.  When you select a registry file, you will be presented with a list of databases defined in the registry.


Select Database from Registry

Select the database that you would like to use, and click 'Ok'.  The connection information for that database will be automatically applied to the connection dialog.  After you have set the connection information for the template, click 'Ok' to apply the changes.  A dialog will open asking you if you would like to verify the new connection information.


Verify Connection Dialog

Unless you know the data source isn't present, it's generally a good idea to check that the supplied connection information is correct.  If you're changing the database connection information in a report, all the sub-reports, charts, and drill-down layers in the report that use the same connection information will be automatically updated as well.

Unlike the modify query feature, changes to a template's database connection are only saved in the template.  They cannot be saved back to the data registry.