To edit or create a data registry, you can either select the Manage Data Sources option from File menu, or click on the Manage Data Sources icon on the toolbar. This will launch the Data Registry Manager dialog.

Data Registry Manager Dialog

From this dialog, you can add, remove, and edit registries, as well as assign user privileges for data registries.

To add a registry, click the Add button and a dialog will appear prompting you to specify a filename for the new registry. Click the OK button and a new Data Source Manager window will open, allowing you to set up data sources within the registry.

To edit a registry, first select it from the list on the left side and click the Edit button. This will bring up the Data Source Manager window for the selected registry file.

3.1.1.1. Exporting/Importing Data Registries

A data registry consists of several files such as database queries, text files, xml files, etc. If you export a data registry, all files will be packed in a single rpak file that can be archived or imported to another ERES installation/server.

To export a data registry, select it and then click the Export Pack button. Enter a file name and click the OK button.

	Note
	Only administrators can export or import data registries.

To import a data registry, click the Import Pack button and then select a rpak file. You can insert the data registry using its original name or you can change it in the New registry name field. If you want to choose which files should be unpacked or change their path or file name, click the Details button.

Unpack dialog

In this dialog, you can choose which files you want to unpack and you can also change their names and paths.

If you don't want to unpack any file, uncheck 'unpack file to disk' checkbox.

	Note
	File paths of some type of files (for example: sql queries) can't be changed. In such cases, the New file path field and buttons will be disabled.

Click the Cancel button to get back to the Import Data Registry dialog.

Click the Unpack button. The data registry will now unpack all contained files. It is possible that some files from the rpak file might have the same file names and paths as other existing files on your hard drive. If there are any filename conflicts, the following dialog will pop up.

Data Registry Manager Dialog

If you click Yes, the conflicting files on your hard drive will be overwritten with the files from the rpak file.

If you click No, the Unpack dialog will open again allowing you to solve the filename conflict.

3.1.1.2. Data Registry Privileges

As with other objects in EspressReport ES, the administrator can also assign privileges to Data Registries. These include access permissions (read/write) as well as content filtering (restricting which sources in the registry users or groups can see).

To set privileges for a registry, select it in the Data Registry Manager and click the Privilege button. This will bring up a dialog listing permissions for the file.

Registry Privileges Dialog

To give users or groups access to the registry, click the Add button. This will bring up a list of all the defined users and groups. From this dialog, select the groups and users you want to add. You can set access rights for each user or group.

For each user or group that has access to the data registry, you can also customize their view of the registry. This allows you to restrict access of a group of users to specific data sources or types of data sources. To customize the registry display, select one of the users or groups in the registry privileges dialog and click the Customize button. This will bring up a dialog showing all the data sources that have been defined in the registry.

Customize Registry Dialog

Each node (data source) in the registry can be set to visible or invisible by selecting it and checking the option at the bottom of the dialog. Invisible nodes will be grayed out. Note that if you render a parent node invisible, all the sub-nodes will also be invisible, so you can't make Queries node invisible and individual queries visible at the same time.

Once you finish, click the Ok button and the settings will be associated with the user or group. When they access the registry, only the visible nodes will appear.

When you edit or create a new data registry, the Data Source Manager window will open allowing you to add and edit data sources. The Data Source Manager is a visual representation of the registry XML file.

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

3.1.2.1. Using Data Source Manager

Data Source Manager

The left side of the window contains a tree listing all of the data sources in the registry file. Grouped under Databases are 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 XML files and their associated queries. Grouped under TXTFiles are all specified Text files. Grouped under ClassFiles are all specified class files, and grouped under EJBs are all specified EJB connections.

Right side of the window contains a series of buttons controlling all of the functions of the Data Source manager. Each button performs the following function:

Edit:

This option allows you to modify attributes of a data source. For a database, it allows you to change the connection information and modify queries/data views. For XML files, it allows you to change the file and its location, and modify XML queries. For text files, it allows you to change the display name and file location. For class files, it allows you to change the display name and modify the location. And for EJBs, it allows you to change the display name as well as parameter values.

Copy:

This option is not only available for database nodes and bigdata nodes but also for the specified query or data view.

Add:

This option allows you to add a data source. It will create a new source depending on which node is selected on the left side of the window. Hence, if you select TXTFiles and click the Add button, you will be prompted to add a new text file data source.

Remove:

This option will remove the selected data source.

View:

This will open a new window showing the tabular data from the selected data source, either query, XML file, text file, class file or EJB. For data views, the data view query interface will start. From this window, you can select to create a new report or a new chart by selecting the appropriate button.

View Data Source Dialog

Cancel:

This will cancel the wizard process.

Done:

This will save any changes you've made in the registry and return you to the registry list.

3.1.2.2. Data Source Node Locking

The data registry has an automatic node locking system designed to block multiple users from editing related nodes at the same time. The system utilizes your IP address, username, and a special security token to determine whether you are allowed to add, remove, or edit a specific node. If you are currently editing a node, other users will not be able to edit it. However, if you disconnect from the server or move to another computer and try to open the same node from the same username, an option box will appear allowing you to override your previous lock.

Please note that administrator is capable of unlocking any node, even if they did not open it in the first place.

The data registry is a tree where data type is the root and queries and files are the leaves. Changing any node within a particular branch will lock all parent nodes and all child nodes in that branch. Here are some examples of how the node locking system works:

Locking Query

In the above image, you can see that the database Woodview (HSQL) and query Orders are part of the same branch. So if you edit Orders query, it will lock both Orders and database Woodview (HSQL). If you edit Woodview (HSQL), the database will be locked and all child nodes connected to this database will be locked as well, which includes Orders, Products, State, and Region.

Locking Data Views

In the case of data views, there are three levels in the tree. In the above image, editing Orders data view query will lock the query, data view Invoicing, and database Woodview (HSQL). If the data view Invoicing is being edited, both queries Orders and Sales will be locked, as well as database Woodview (HSQL). However, in both of these scenarios, other data views and queries can still be edited. If the database Woodview (HSQL) is being edited, all data views as well as their queries will be locked.

ERES can draw data from any JDBC compliant database. In order to connect to a database via a 3rd party driver (other than the JDBC bridge), you will need to add the classes for that driver to the classpath of the application server/servlet runner where you have deployed the ERES server. If you installed ERES with Tomcat, then you need to modify setclasspath.bat/.sh file in /bin/ directory of your Tomcat installation to add the driver classes, or copy them to <ERESInstallDir>/WEB-INF/lib directory. Note that JDBC drivers for MS SQL Server, MySQL, Oracle, Informix and PostgreSQL databases are included as a convenience. Other database JDBC jar files are not included due to licensing, multiple drivers, and/or other concerns, although support for these 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 a 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 manually. Fields to enter are database name, URL, and driver. You can also select whether the database requires a login and whether you want to save username and password information. If you select to save login and password information, you can then enter these informations in User Name and Password textboxes. Then click the Ok button and the new database will be added to the Data Source Manager window.

Add Database Dialog

In order for ERES to create a connection to the database, the following information must be provided:

Once you specify 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 default setting 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 URLs) 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) are 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 within the ERES installation. One is a HSQL (a pure Java application database) database and the other one is a MS Access database. Both contain the same data and are located in help/examples/DataSources/database directory. For details about how to set up connections to these sample databases, please see Section Q.3.1.1 - Setup Database Connections of the Quick Start.

3.1.3.1. JNDI Data Sources

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

To connect to a JNDI data source in ERES, you must have a data source deployed in the same Web application environment as you are running the ERES server. To set up 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 option 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.1.3.2. Queries

Once you add 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 SQL statement 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 finish 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.1.3.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 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 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 and the Datasheet View tab shows the query result.

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

3.1.3.2.1.1. Tables

When the Query Builder launches for the first time, 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 the 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 display all 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 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.1.3.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 its 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 and Orders tables in such a way 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:

Select Orders.OrderID, Customers.CustomerName, Orders.Sales
From Customers, Orders
Where Customers.CustomerID = Orders.CustomerID
Order by Orders.OrderID;
                    

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 you specify to include all records from the Customers table. The select statement generated by the Query Builder would look like this:

Select Orders.OrderID, Customers.CustomerName, Orders.Sales
From Orders right outer join Customers on Orders.CustomerID = Customers.CustomerID
Order by Orders.OrderID;
                    

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.1.3.2.1.3. Columns

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

Selecting Column Fields:

You can add column fields to the query from any table that has been selected in one of two ways. You can double-click on a field name within a table to add it to the query, or you can double-click on the Table or Field fields to bring up a drop-down menu with field choices. You can remove a column from the query by right clicking in the lower window and selecting Delete Column from the pop-up menu, or by selecting Delete Column from the Edit menu. Once you select a column field, you can specify how you want to sort the column, in ascending or descending order, by double clicking on the Sort field. You can also specify group by or column aggregation by double clicking on the Aggregation field. Aggregation options include: Group By, Sum, Average, Min, Max, Count, Standard Deviation, Variance, First, Last, and Where. If you select group by for one column, then you are required to specify group by (or aggregation) for all of the other columns. To specify a column alias, right click on the column and select Alias from the pop-up menu. You can perform a SELECT DISTINCT operation by selecting the Select Distinct option from the query menu.

Building Columns:

To build your own column, right click on a blank column in the QBE window and select Build from the pop-up menu. This will launch the Formula Builder. The Formula Builder allows you to construct columns in a visual environment using the tables that you have selected and the formula library for the database that you are using. You can click the Browse Data Field button to see the first few records of data for any field in your query.

Formula Builder Window

Conditions:

You can place conditions on the query selection by entering them in the Condition or Or fields. A condition placed in the Condition field creates an AND clause within the generated SQL. A condition placed in the Or field creates an OR clause within the SQL. Right clicking in either field and selecting Build from the pop-up menu will bring up the Formula Builder. In the Formula Builder, you can specify standard conditions, =, <, >, BETWEEN, LIKE, NOT, etc., as well as construct formulas to filter the query. You can also specify a query parameter here.

Note

ERES can auto-correct items entered as query conditions by appropriately appending the field name and encasing string arguments in quotes. For example, if you enter = ARC, ERES will change the query condition to Categories.CategoryName='ARC'. If you're using complex functions (i.e. database functions that take multiple string arguments), ERES may not be able to properly parse the function. You can turn off the auto-correct feature by un-checking the box at the bottom of the formula builder window.

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

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

<DatabaseFunctions>
      <Database ProductName="ACCESS">
            <FunctionSet Name="Numeric Functions">
                  <Function>Abs(number)</Function>
                  <Function>Atn(number)</Function>
            </FunctionSet>
      </Database>
</DatabaseFunctions>
                    

3.1.3.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. Once you finish, click the Ok button and the statements will be added to the query.

3.1.3.2.1.6. Query Output

The SQL View and Datasheet View tabs allow you to see two different views of the query.

SQL View:

The SQL View tab shows you the SQL statement generated by the query in the design view. It allows you to see how the Query Builder is translating different operations into SQL. You can edit the generated SQL if you want, however, if you change the SQL and then return to the Design View, all changes you made will be lost. If you save a query after changing the SQL, the query will re-open in the SQL View tab if you select to edit it.

Datasheet View:

The Datasheet View tab shows you the query result in data table form (this tab is also available in the Enter SQL dialog). The datasheet view will show you all the data that is drawn as a result of executing the query. Going to the datasheet view will also test the query to check for design errors. You can navigate the query result by using the toolbar at the bottom of the window.

• Go to the first page of the data table

• Go to the previous page of the data table

• Go to a specific row of data

• Go to the next page of the data table

• Go to the last page of the data table

• Set number of rows to display per page (default is 30)

Exporting Queries:

You can export queries in one of two ways. You can output the SQL statement as a text or you can output the query result as CSV file. To export a query, select Export from the File menu. A second menu will appear giving you the option to Generate SQL or Generate CSV. Select the desired option and a dialog box will appear prompting you to specify the filename and location.

	Note
	To save the query and exit the Query Builder, select Done from the File menu.

3.1.3.2.2. Parameterized Queries

You can also use the Query Builder to design parameterized queries. This feature allows you to filter the data at run-time.

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

Select * From Products Where ProductName = :Name
                

specifies a parameter called Name. You can then 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 for query as you like.

3.1.3.2.2.1. Multi-Value Parameters

ERES supports a special kind of parameter that takes an array of values as input rather than a single value. Multi-value parameters are useful when you want to filter the result set based on an unknown number of values. For example, say a report is run to return a list of 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

Select Customers.Company, Customers.Address, Customers.City, Customers.State, Customers.Zip
From Customers
Where Customers.State IN (:State);
                    

will create a multi-value parameter named State. Multi-value parameters will only be created if 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), it will create three single value parameters instead.

3.1.3.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:

Map to database column:

This option allows you to specify a column from the database whose values will be used for the parameter input. Selecting this option modifies the parameter prompt that the end user will get when previewing or running the report in the Report Viewer. If you map the parameter to a database column, then the user will be prompted with a drop-down list of distinct values from which to select a parameter value. If you do not map, the user will have to type in a specific parameter value.

Tip

Normally, this drop-down list is populated by running a select distinct on the column while applying the joins and conditions from the query. If you prefer to get all data from the column without constraints (sometimes this can improve performance of the parameter prompts), you can set the Distinct Parameter List Selection option in the Admin Console. This will result in selecting the distinct values of the mapped column from the table that contains the column. For more information about ERES server options, see Section 1.4.1.3 - Server Options.

Map to database function:

The map to database column feature is very handy if you want to enter a valid value for a parameter from a list box, but sometimes you rather want a computed value or a derived value from a database column. For example, you want to find all orders from year 2007. However, OrderDate is a date. What you want is to apply the Year function to the OrderDate column. This is the impetus behind this feature. Mapping a parameter to a database function is very similar to mapping to a column. In the formula builder, enter a condition comparing a function result to a parameter as shown below:

Condition for Mapping to Database Function

In the initialize parameter dialog, check the Map to database function checkbox and the values will be automatically filled in.

Map Parameter to Database Function

The list of custom functions is extracted from DatabaseFunctions.xml file located in <ERES Install>/userdb/ directory. Modify the .xml file if you wish to add a new database or custom functions. The new functions will appear in this list after you restart the program.

If your database is not listed in the .xml file, the function list will be populated with functions listed in the JDBC driver. However, the function parameters are not provided. For example, the HSQL database will not be listed in the .xml file.

An interesting example using the HSQL database is as follows. Suppose you would like to create a report for orders that were delayed. You can utilize the HSQL DateDiff function to find the number of days for the order to ship.

DATEDIFF('dd', ORDERS.ORDERDATE, ORDERS.SHIPDATE) >= :ShipDelay
                                

This function finds the difference between the order date and the ship date and displays the result in terms of days. If you initialize the parameter and check the map to database function, the following window will be displayed:

No Parameter Types for HSQL Function

The DateDiff function requires a string and two date values for the parameters. Enter these parameter types in the parentheses. This will bring up three set parameter value lists. Enter dd (day) for the first parameter, select Orders.OrderDate from the list for the second parameter, and select Orders.ShipDate from the list for the third parameter. The default values will be updated with the function results.

Map Parameter to HSQL Function

Map to SQL ResultSet:

A parameter mapped to a database column will give you a list of distinct values in a drop-down list box for the user to choose from when running the report. However, to produce the list of values, a select distinct on the column with the joins and conditions from the query will be run. In some cases, this can be a time-consuming process. To obviate this problem, and in fact gain complete control as to what and how to populate the drop-down list box, you can write your own select statement to populate the drop-down list. An added bonus is that parameters that are in the query can be included in this query. With proper joins and parameters included, you can use this feature to facilitate cascading parameters (See Section 3.1.3.2.2.3 - Cascading Parameters). Below is an example:

Suppose you have two parameters in the query. So, your query is as follows:

SELECT CATEGORIES.CATEGORYID, PRODUCTS.PRODUCTNAME, PRODUCTS.UNITPRICE, PRODUCTS.UNITSINSTOCK
FROM PRODUCTS, CATEGORIES
WHERE ((PRODUCTS.CATEGORYID = CATEGORIES.CATEGORYID))
AND (((CATEGORIES.CATEGORYID =:category) AND (PRODUCTS.PRODUCTNAME =:product)))
                                

In the config prompt in initialize parameter, set the order for parameter prompting to category first, then product.

The select statement for parameter category can be the following:

SELECT DISTINCT CATEGORIES.CATEGORYID
FROM CATEGORIES
                                

The select statement for parameter product will be as shown below:

SELECT DISTINCT PRODUCTS.PRODUCTNAME
FROM CATEGORIES, PRODUCTS
WHERE CATEGORIES.CATEGORYID = PRODUCTS.CATEGORYID
AND CATEGORIES.CATEGORYID = :category
                                

Select Statement for Product

When the user runs the template, category will be prompted first. Then the value of category chosen will be used to filter for product.

The select statement mapped to a parameter can have either one or two columns in the select list. It is clear that if one column is in the select list, it must be the column that supplies list of distinct values for the parameter. Another useful feature provided here is that you can actually select two columns in the select list such that the first one of the columns will supply values for the drop-down list while the second column will be the actual parameter value for the filter condition. Consider the following example.

Suppose your database has a table with product ID as the primary key. When your end user wants to search for products from the database, they want to use the product name as parameter (therefore it is listed in the query as first) since a product ID could be just a cryptic code (therefore it is listed in the query as second). Using this feature, you can choose product name for the values in the drop-down list and product ID as the actual value filter condition.

Select Statement with Two Columns

Use custom selection choices:

Rather than having a drop-down menu with all the distinct column values, you can also create a custom list of parameter values. To create this list, select this option and click the Setup Choices button. This will launch a new dialog allowing you to create a list of choices.

Custom Parameter List Dialog

In this dialog, you can either enter custom values or select values from the distinct values of a column in the database. Once you finish specifying the values for the list, click the OK button and the choices will be saved.

Default Value:

This allows you to specify a default value for the parameter. Although you don't have to specify a default value, it is recommended that you do so. If you do not supply a default value, you cannot open or manipulate the report template without the data source present.

You can either select a single value manually (either choose it from a list or type it manually, it depends on the mapping method you chose) or map the default value to a SQL query.

For multi-value parameters (see Section 3.1.3.2.2.1 - Multi-Value Parameters), the SQL query can return more than one value. In such case, several values will be chosen as default parameter values.

Date Variable:

This option is only available when the parameter is not mapped to a database column or function, or it's mapped to a SQL resultset and not set to a custom selection choice. This option is only intended for parameters with variable type date/time. When you click this button, the following panel will pop up, listing all the supported keywords.

Enter Date Variable Dialog

This dialog allows you to select one of the three keywords: CurrentDate, CurrentTime, and CurrentDateTime. You can add or subtract units of time from the current date/time, allowing you to have a dynamic date range. For example, a report can have the following default values:

StartDate: CurrentDate - 1 WEEK
EndDate: CurrentDate
                                

This would indicate that every time the report is run, the default prompt should be one week ago to the current date. Other supported time units are YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. This feature only supports a single addition or subtraction, it does not support multi-value parameters.

You can also use functions to define the parameter value:

FirstOfYear()

Argument format: CurrentDate, CurrentDateTime, e.g. FirstOfYear(CurrentDate)

This function returns a date of the first day of the year from the argument. For example, when the argument evaluates to 2012-08-14, the function returns 2012-01-01.

LastOfYear()

Argument format: CurrentDate, CurrentDateTime, e.g. LastOfYear(CurrentDate)

This function returns a date of the last day of the year from the argument. For example, when the argument evaluates to 2012-08-14, the function returns 2012-12-31.

FirstOfQuarter()

Argument format: CurrentDate, CurrentDateTime, e.g. FirstOfQuarter(CurrentDate)

This function returns a date of the first day of the quarter which includes the date from the argument. For example, when the argument evaluates to 2012-08-14, the function returns 2012-07-01.

LastOfQuarter()

Argument format: CurrentDate, CurrentDateTime, e.g. LastOfQuarter(CurrentDate)

This function returns a date of the last day of the quarter which includes the date from the argument. For example, when the argument evaluates to 2012-08-14, the function returns 2012-09-30.

FirstOfMonth()

Argument format: CurrentDate, CurrentDateTime, e.g. FirstOfMonth(CurrentDate)

This function returns a date of the first day of the month from the argument. For example, when the argument evaluates to 2012-08-14, the function returns 2012-08-01.

LastOfMonth()

Argument format: CurrentDate, CurrentDateTime, e.g. LastOfMonth(CurrentDate)

This function returns a date of the last day of the month from the argument. For example, when the argument evaluates to 2012-08-14, the function returns 2012-08-31.

FirstOfWeek()

Argument format: CurrentDate, CurrentDateTime, e.g. FirstOfWeek(CurrentDate)

This function returns a date of the first day of the week which includes the date from the argument. For example, when the argument evaluates to 2012-08-14, the function returns 2012-08-12 (Sunday is considered as the beginning of the week).

LastOfWeek()

Argument format: CurrentDate, CurrentDateTime, e.g. LastOfWeek(CurrentDate)

This function returns a date of the last day of the week which includes the date from the argument. For example, when the argument evaluates to 2012-08-14, the function returns 2012-08-18 (Saturday is considered as the end of the week).

StartOfDay()

Argument format: CurrentTime, CurrentDateTime, e.g. StartOfDay(CurrentDateTime)

This function returns a time of the start of the day from the argument. For example, when the argument evaluates to 2012-08-14 12:15:03, the function returns 2012-08-14 00:00:00.0.

EndOfDay()

Argument format: CurrentTime, CurrentDateTime, e.g. EndOfDay(CurrentDateTime)

This function returns a time of the end of the day from the argument. For example, when the argument evaluates to 2012-08-14 12:15:03, the function returns 2012-08-14 23:59:59:999.

Data Type:

This allows you to specify data type for the parameter value(s). If you mapped the parameter to a column, the data type is set automatically.

Allow Select All Option:

Use this to add an option to the parameter prompt dialog that will allow users to select all parameter values even for single-value parameters. See the Section 3.1.3.2.2.4 - All Parameters for more details.

Custom Date Format:

This allows you to set the format in which the date parameter should be entered. This option is only available when the data type is either date, time, or timestamp and the parameter is either mapped to column or not mapped at all.

When you check this option, the default custom date format is shown. These defaults can be changed in the admin console (See Section 1.4.1.3 - Server Options). Please note that the default date format only applies to database data sources. The date format is built using a combination of characters that represent date/time elements. You can build the format easily using the date format builder by clicking the Build button.

Date/Time Format Builder

The builder contains a list of elements available on the right. You can mouse over the elements to see an example of each presentation. The bottom section contains a set of separators available for use.

You can also type in the format by hand either in the builder or directly in the Custom Date Format input box. For more information about characters and their formatting, please refer to the documentation for the printDate() report function in Section 4.1.6.2.8.3 - Date/Time Functions. Formatting for this option is the same as for the format argument of the function.

Prompt Name:

This allows you to specify the prompt that is given to the user in the parameter 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 various options. If you choose not to map the parameter, the user will see a textbox to enter their own value. In case of a multi-value parameter, it is recommended to let the user inform 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 use of comma, the user can use quotes to include the comma within the filter string (e.g. "Doe, John", "Smith, Mike").

Clicking the Previous Parameter and the Next Parameter buttons allows 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.1.3.2.2.3. Cascading Parameters

By default, the user is prompted to enter all 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 parameters that do not return any data.

To assist with these problems, ERES 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. User will be prompted to start with the lowest numbered parameter and work his/her way up to the highest one. 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.1.3.2.2.2 - Initializing Query Parameters.

Please note that this feature can only be used with reports.

3.1.3.2.2.4. All Parameters

Sometimes you want to select all parameter values at once. The All Parameters feature allows you to do so.

Single-value parameters

It is possible to select all parameter values at once, even for parameters that do not allow multi-value selection.

	Note
	There is a difference between multi-value selection and all-value selection. See the Inner Workings chapter to learn more.

For example: Let's assume you have a condition like this:

WHERE column = :Parameter
                                

In such case, the parameter prompt dialog will not allow you to select more than one value.

Typical single-value parameter

But you can use the Select All Values feature to add an option to the parameter value list that will allow viewers to select all parameter values at once (even if the parameter does not allow multi-value selection).

Single-value parameter with the Select All functions enabled

The Select all feature can be enabled in the Initialize Parameters dialog (see Section 3.1.3.2.2.2 - Initializing Query Parameters for more details) by selecting the Allow Select All Option checkbox.

This option is only available for parameters that meet the following requirements:

1. The parameter uses one of the following operators. If there are multiple occurrences of this parameter in the query, all parameter comparison operators have to be one of these:

   <	less than
   <=	less than or equal to
   >	greater than
   >=	greater than or equal to
   =	equal to

2. The parameter is mapped to the same column as the column from the parameter condition or the parameter isn't mapped to anything.

After you select the Allow Select All option, the Select All Label field activates, allowing you to enter a text that will be used for selecting all data from the query. For parameters that are mapped to a column, this text will be displayed in the parameter value list in 1st place. For parameters that aren't mapped to anything, entering this text as the parameter value will result in selecting all data.

Multi-value parameters

Unlike single-value parameters, the Select All feature is enabled for all multi-value parameters by default (in fact, it can't be disabled, because disabling it for multi-value parameters would make no sense). All you have to do to use this feature is to click on the Select All icon in the parameter prompt.

However, multi-value parameters can work in two modes:

1. If the parameter meets the conditions from the previous paragraph and the parameter is on the first cascading level (i.e. parameter cascading is disabled or the parameter is on the first cascading level), it is parsed by the SQL parser and the parameter condition is nullified. Nullifying the parameter optimizes the query and prevents it from causing performace issues or even errors. See the Inner Workings to learn more about how it works.

2. If the parameter doesn't meet the conditions from the previous paragraph or if it's not on the first cascading level, selected values will be injected to the query as a list of values separated by comma. If there is a large amount of values injected to the query as a list, the query can become quite long. Long queries can cause performance issues or even errors, so it is not recommended to use this option for parameters with many values.

Inner Workings

If a report/chart/map viewer chooses to select all parameter values for a single-value parameter or for a multi-value parameter that meets the conditions for parameter disabling, the query is then automatically parsed and a special condition is added to the parameter which basically disables the parameter.

For example: The following query

select *
from table
where column > parameter_value
                                

Would be parsed and passed to the database as:

select *
from table
where ((column > parameter_value) OR (1 = 1))
                                

This example also demonstrates another important thing: selecting all values for the < (less than) or > (greater than) operators returns all values from the table (if there are no other conditions) rather than returning no data at all (because condition like WHERE <all data from the Date column> > Date would return no data...).

Because ERES allows you to use many database systems, parsing may fail for certain complex queries in certain databases. In such case a warning dialog will be displayed.

In such situations, you have the following three options:

1. Try to modify the query so it can be parsed by our parser.

2. Add your own Select all parameters condition to the query.

   For example:

   WHERE ((column = :Parameter) OR (:Parameter LIKE 'selectall'))
                                           

   	Note
   	If you embed all parameters directly to the query, leave the Allow Select All Option option disabled.

3. Contact Quadbase support.

3.1.3.2.3. Entering SQL Statements

Typically, the Query Builder is recommended for creating queries. However, there are cases 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.1.3.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:

CREATE OR REPLACE PACKAGE types
AS

      TYPE ref_cursor IS REF CURSOR; 

END;
                    

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.

CREATE OR REPLACE FUNCTION my_function()

      RETURN types.ref_cursor

AS

      result_cursor types.ref_cursor;

BEGIN

      do_stored_procedure();
      OPEN result_cursor FOR
            SELECT * FROM Categories

      RETURN result_cursor;

END;
                    

Now that the Oracle stored function is set up, it can be easily called from ReportDesigner 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 ReportDesigner that a variable is reserved for the OUT parameter. The JDBC syntax for calling Oracle stored procedures is as follows:

( call ? := my_function() )
                    

However, ERES 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 ERES to develop useful solutions. Suppose you have a table called employee_table that stores an organization's location hierarchy such as the one shown below:

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 can be solved with two simple Oracle Stored Functions:

CREATE OR REPLACE FUNCTION sum_employees(locID IN NUMBER)

      RETURN NUMBER 

AS

      sum_emp NUMBER; 

BEGIN

      SELECT sum(employee) INTO sum_emp
      FROM employee_table
      CONNECT BY PRIOR id = parent
      START WITH id = locID;

      RETURN sum_emp; 

END;

CREATE OR REPLACE FUNCTION regional_employees (locID IN NUMBER)

      RETURN types.ref_cursor 

AS

      result_cursor types.ref_cursor; 

BEGIN

      OPEN result_cursor FOR
            SELECT id, name, sumEmployees(id) AS Employees
            FROM employee_table
            CONNECT BY prior id = parent
            START WITH id = locID; 

      RETURN result_cursor;

END;
                    

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. The result set is then 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 in 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 European locations, they can enter 3 for the parameter and the following result set would return this:

Result set from regional_employees in Europe

To create a parameterized report, use the :param_name syntax. The SQL parser in ERES will be able to differentiate between the colon used for parameters and the one used for the assignment operator ( := ). Here is an example of using the 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. You can find more information about initializing parameters in Section 3.1.3.2.2 - Parameterized Queries.

To try this example, <ERESInstall>\help\examples\DataSources\database\locationHierarchyExample.sql contains SQL commands to create employee_table as well as two stored functions.

3.1.3.3. Data Views

In addition to the query interfaces, ERES provides another way 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 predefine tables, joins, and fields, creating 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 or chart.

To create a data view, select the Data Views node in the Data Source Manager window and click the Add button. A new window will open allowing you to select the database tables you want to use for the data view.

Data View Choose Tables Dialog

Left window contains all available database tables and views. You can add a table by selecting it in the left 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 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 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 represented by a line. 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 information about join properties, please see Section 3.1.3.2.1.2 - Joins.

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

Create Data View Dialog

The left window contains a list of tables you have selected and their associated fields. Each folder represents a table and can be opened and closed by double clicking. The right window contains fields that have been selected for the data view. To add a field to a data view, select it in the left window and click the ADD>> button. Fields can be removed from the data view in the same way by selecting a field in the right 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 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 window. To add fields under a heading, first select the fields you want to add from the right 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.

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 information about using the formula builder, please see Section 3.1.3.2.1.3 - Columns.

Once you finish 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 or chart using a data view as the data source (by selecting the data view and clicking the Create Report or Create Chart button), a window will open allowing you to select which fields in the view you want to use for the report. From this dialog, you can also build computed fields based on the available view columns.

After you select the fields, click the OK button 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

You can specify sorting, aggregation, and conditions for every field in the data view 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 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 refer to the data view query for updating/modification.

3.1.3.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 View to run the data view. After you select 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 information about this, please see Section 3.1.3.2.2 - Parameterized Queries.

Once you enter the parameter, you will be prompted to initialize it if you go to the Datasheet View tab and then click Ok to continue 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.1.3.3.2. Updating Data View Queries

Sometimes you may 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 remove the field from the query. If you do not want to change anything in the query, you can click the Skip button. The query will continue to run, but it will refer to the old data view structure. Click the Apply button to save the changes to the data view query.

3.1.3.3.3. Data View Security

EspressReport ES allows you to filter data based on a user's security level when viewing a pre-built report/chart. But what if the user is doing ad hoc querying and reporting and you want to restrict data access based on user's security level? For this, EspressReport ES allows you to apply security filters to data views. This feature will automatically apply preset filters to data views and data view queries when certain users run them through QuickDesigner Reports/QuickDesigner Charts. These options can be configured via XML file and tie into the report-based secured parameter features. For more information about secured report parameters, see Section 4.1.10.2.1 - Security Parameters.

To illustrate the concept, here is a simple example. A sample data view security XML file is displayed here:

<?xml version="1.0"?>
<!DOCTYPE SecuredParamInfoList SYSTEM "QuickDesignerSecuredParameters.dtd">
<SecuredParamInfoList>

      <SecuredParamInfo>
            <Driver>org.hsqldb.jdbcDriver</Driver>
            <URL>jdbc:hsqldb:<ERESInstallDir>\help\examples\DataSources\database\woodview</URL>
            <TableName>CUSTOMERS</TableName>
            <ColumnName>REGION</ColumnName>
            <DataType>VARCHAR</DataType>
            <IsMulti state="true" />
            <SecLevel Name="EastSouth" GrantAll="false">
                  <Data>East</Data>
                  <Data>South</Data> </SecLevel>
            <SecLevel Name="Midwest" GrantAll="false">
                  <Data>Midwest</Data> </SecLevel>
            <SecLevel Name="West" GrantAll="false">
                  <Data>West</Data> </SecLevel>
            <SecLevel Name="East" GrantAll="false">
                  <Data>East</Data> </SecLevel>
            <SecLevel Name="South" GrantAll="false">
                  <Data>South</Data> </SecLevel>
            <SecLevel Name="Executive" GrantAll="true">
            </SecLevel> </SecuredParamInfo> 

</SecuredParamInfoList>
                

This XML file defines a filter for the REGION column from the CUSTOMERS table in the Woodview sample database. It specifies behavior of the filter for six different security levels. For level East, the query will be automatically filtered so that only rows where region is East will be returned. For level South, the query will be automatically filtered so that only rows where region is South will be returned. For the level Midwest, only rows where the region is Midwest will be returned. For the level West, only rows for West region will be returned. The IsMulti attribute allows you to indicate whether multiple parameter values can be specified for the filter, like in the case of the EastSouth level. For level Executive, all values from the region column will be available.

The security levels specified in the XML file should match those that are defined in the Organizer. For more information about setting up security levels, see Section 2.3.3 - Security Levels. The sample XML file — SampleQDSecuredParameters.xml and the DTD file - QuickDesignerSecuredParameters.dtd that defines all of the specifications for the XML file can be found under <ERESInstallDir>/qDesigner.

To deploy a security filter XML file, you will need to modify QB.properties file under <ERESInstallDir>/WEB-INF/classes directory and add a new server option (add it to the ServerCommands= line) -QuickDesignerSecuredParameters: followed by the relative or absolute path to your XML file. For more information about setting server options, see Section 1.4.1.3.1 - Secured Parameter. For this tutorial, please add -QuickDesignerSecuredParameters:qDesigner/SampleQDSecuredParameters.xml to QB.properties.

With the XML file setup, any time a user creates or runs a data view or data view query that uses the field/table defined in the XML file, the filter defined for their security level will be applied. If the user then creates and saves a report/chart in QuickDesigner Reports/QuickDesigner Charts, this filter will be converted to a secured query parameter in the finished report/chart. Any changes to the security permissions after this time can be made by administrator through the Report Designer/Chart Designer. For more information about report-level security, see Section 4.1.10 - Template Security. If the user does not belong to a security level, or their security level is not defined in the XML file, the user will not be allowed to run the query. Therefore, it is important to have security levels assigned to all groups or users that are allowed to use QuickDesigner Reports/QuickDesigner Charts.

Sign in as admin and create 6 users, namely, exec,easternmanager,southernmanager,midwesternmanager,westernmanager, and eastsouthmanager. Assign them with Design privilege. Then open the data registry and highlight sample.xml. Click the Privilege button to assign privilege to allow read and write access to all these users.

Create 6 security levels in organizer (Note: security levels are case-sensitive):

Executive (assigned to the user exec)
East (assigned to easternmanager)
South (assigned to southernmanager)
Midwest (assigned to midwesternmanager)
West (assigned to westernmanager)
EastSouth (assigned to eastSouthmanager)
                

If you are running this example with HSQL, open SampleQDSecuredParameters.xml and change <ERESInstallDir> in the line

<URL>jdbc:hsqldb:<ERESInstallDir>\help\examples\DataSources\database\woodview</URL>
                

to your install directory. For example, if you installed ERES under c:\ERES, the line between the <URL> tags would read jdbc:hsqldb:c:\ERES\help\examples\DataSources\database\woodview. Make sure that under Manage Data Sources in the Organizer, the URL for this database reads the same as the above.

To prepare a data view for our example, open the Organizer, click the Manage Data Sources button on the toolbar, select Sample.xml data registry and click the Edit button. Select Databases/Woodview/Data Views node and click the ADD button. Choose CUSTOMERS table in the Choose Tables dialog, click the ADD button to add it to the Selected Tables pane and then click OK to close this dialog. You are in the Create Data View dialog now. Enter Customers name for the view, select the CUSTOMERS table on the left, click ADD to add it to the View Fields pane and click OK to close the dialog. The data view is prepared. Close the Data Source Manager and the Organizer.

To create your own report, shut down and restart Tomcat, then sign in as one of the Manager logins you just created (not the executive level). Open Quick Designer Reports and click the New button on the toolbar to open the Data Source Dialog. Select Databases/Woodview/DataView/Customers data view. In the DataView Builder, select columns REGION, COMPANY, ADDRESS, CITY, STATE, ZIP and click the Next button. Do not add any conditions in the Conditions dialog and click OK. Finally, add all columns to the report. Save your report to make sure that all your users have access to the file and the database (see Section 2.3.2 - Setting User Privileges).

• Log out and login again as easternmanager:

  • From Published Files, open the file you have just created.

  • You should see the report with all Eastern values.

• Log out and login again as eastsouthmanager:

  • Open the file you have just created from Published Files.

  • You should see the report with both values for East and South.

• Log out and login again as exec:

  • Click on the report you have created from Published Files.

  • You will only see one value, but if you click on the filter, you can change between the different security levels by typing in the region you want to see.

  • When viewing this report from QuickDesigner Reports, you will be automatically prompted for the value you want to see and prompted again every time you refresh, as you would be with any other parameter.

If you were to create a report using this query in QuickDesigner Reports while logged in as a user with GrantAll privileges (the Executive level in the example), the report would be saved by default without any secured parameters and all users would be able to view all parameter values from Published Files, QuickDesigner Reports and Report Designer. Any security of a report created from a user with GrantAll privileges must be done from Report Designer with template security (see Section 4.1.10 - Template Security).

If you want to create your own XML file, please follow the DTD spec in <ERESInstallDir>/qDesigner/QuickDesignerSecuredParameters.dtd.

	Tip
	In case you think your query is not being filtered properly, you can add the flag -debug:DVW_SEC_PARAM to the ServerCommands= line of the QB.properties file; shut down the ERES Server, shut down your Application server (Tomcat), and run again. The Applications server's console or log file should show diagnostic statements for you to see possible problems.

3.1.3.4. Editing Queries

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

To modify a report or chart's query, select Modify Query from the Data menu in Report Designer or Chart Designer. 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 specify all the changes, you will be given an 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 specify the save options, the modified query will be applied to the report or chart. Note that if you made significant changes to the query, you may need to perform the data mapping again.

3.1.3.5. Editing Database Connections

If you have selected to build a report or chart using database data, you can also directly edit a template's database connection information from within the Report Designer or the Chart Designer. To modify the connection information in the report or chart, 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 XML registry file from which you want 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 want to use and click the Ok button. The connection information for that database will be automatically applied to the connection dialog. After you set the connection information for the template, click the Ok button 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 that 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 will only saved be to the template. It will not be saved to the data registry.

In addition to relational databases, ERES allows you to retrieve data and query XML files. XML data can be in virtually any format, but you need to specify a DTD file or an XML Schema (XSD) along with the XML data. To set up an XML data source, select XMLFiles node in the Data Source Manager and click the Add button. A dialog will open prompting you to specify options for the new XML source.

Setup XML Data Source Dialog

The first option allows you to specify a display name for the XML data source. The second option allows you to specify the location of the XML file from which you want to retrieve data. Note that you can also specify an XBRL file in this field. You can set up a data source that retrieves XML data from an HTTP server here as well, by adding the appropriate URL as the file location. The third option allows you to specify the location of a valid DTD or XML schema file for the XML file.

The Quadbase Format checkbox allows you to indicate whether the XML file is in the form of an XML export from ERES. For example, if you choose to export a report's data in XML format, you can read it back in using this format. When you use a file in this format, you do not have to specify a DTD or XML Schema.

The Verify XML against DTD/XML Schema checkbox will make sure that the supplied XML file/source complies with the layout specified in the DTD or XML schema file. Because queries are designed based on the structure of the DTD/XML Schema file, a non-conforming XML source could produce unexpected results. If the XML does not conform to the DTD or XML schema, you will be given a warning. You can, however, continue setting up the data source.

The Refresh Schema checkbox will only appear if you choose to edit an existing XML data source. Checking this option will reload the schema or DTD definition to incorporate any changes to the structure in the XML data source in the registry. This option is only necessary if the DTD or schema definition has changed since the data source was first created.

Once you finish setting up the XML file and the DTD/XML Schema, a new dialog will open allowing you to specify the data type for all the selectable elements in the XML data source. The dialog will be different depending on whether you are using a DTD file or XML Schema.

DTD Data Type Selection Dialog

Because DTD files do not specify a correct data type for elements, all elements are considered to be Strings by default. To change the data type of an element, you have to select the element and pick a data type from the drop-down window at the bottom of the dialog. To ensure proper results when you query the XML file, you should set the data type for all selectable elements. This includes leaf nodes, parent nodes that contain data, and attribute elements. The following data types are supported:

Once you finish specifying the data types, click the OK button and the XML source will be added to the Data Source Manager.

If you're using an XML schema, a different data types dialog will open.

XML Schema Data Type Selection Dialog

Generally, the data types should already be defined in the XML schema file, but you can make any changes in this dialog. Once you finish specifying the data types, click the OK button and the XML source will be added to the Data Source Manager.

3.1.4.1. XMLQueries

Once you set up an XML data source, you can then create queries to select nodes, specify filtering conditions, and transform the tree structure into the tabular form used by ERES. To add a query, select the node for your XML source and click the Add button. This will launch the XML query builder interface that allows you to construct a query.

XML Query Field Selection Tab

The first tab in the XML query builder allows you to select the fields/nodes from the XML file that you would like to use in the report. The left side of the window contains the tree structure from the DTD or XML schema file. You can pick any selectable elements and add them to the query by clicking the Add button. Selected fields will appear in the right side of the window. You can specify an alias for any field by double clicking the Alias field for a column and typing the new column alias.

	Note
	Each selected element will become a column in the report or chart data. For results where a one-to-one relationship cannot be determined, the tabular structure is built using all available permutations in the data (similar to a cross-join in SQL). For best results, it is recommended that you select fields for a query where a clear hierarchical relationship is present.

The Conditions tab of the XML query builder allows you to specify some basic filtering criteria for your selection.

XML Query Conditions Tab

You can specify an equal, not equal, greater than, less than, less or equal to, or greater or equal to condition for any selectable element in the XML file. You can also use the AND and OR operators to build compound conditions. Fields are specified using a direct path down the XML tree. Currently, only direct path is supported. You cannot use more complex XPath expressions. To add a field, you can double click on it in the left side, or you can select it and click the Insert button.

After you finish writing the conditions, click the Test button to verify that the syntax is correct.

The DataSheet tab allows you to preview the query result and see how the XML data is converted to tabular form. You can navigate through the result set the same way as in Query Builder (Section 3.1.3.2.1.5 - Adding Extra SQL).

Once you select the fields and specify the appropriate conditions, click the OK button. The query will then be added as a new node under your XML source in the Data Source Manager and can now be used to create a report or chart.

There is a sample XML file with DTD and XML schema descriptions included in the ERES installation. The files are located in help/examples/DataSources/XML directory of your installation and are called Inventory.xml, Inventory.dtd and Inventory_XSD.xml, Inventory.xsd for the XML schema example. There is also a sample servlet that allows you to stream XML data to the Chart Designer. The servlet code and instructions are located in help/examples/DataSources/XML/servlet directory.

3.1.4.1.1. XML Parameters

As with database queries, you can also specify parameters for XML queries. The same syntax “:” is used to denote a parameter in the XML condition as it is in a query condition. So the following XML condition:

/Inventory/Category/@CategoryName = :category
                

would place a dynamic filter on the query for the CategoryName attribute. XML parameters are initialized in the same way as query parameters. The initialization dialog will appear if you try to preview or close the query, or you can trigger it by clicking the Initialize Parameters button. The only difference is that instead of mapping to a database field, the parameter prompt can be mapped to a node in the XML file.

XML Parameter Initialization Dialog

ERES also allows you to retrieve data from flat text files. To add a text file as a data source, select the TXTFiles node in the Data Source Manager and click the Add button. A dialog will open prompting you to specify a display name and the location of the text file you want to use.

Add Text Data Source Dialog

	Tip
	The text file can be also retrieved from URL. To do so, enter the URL to the File text field. You have to enter a full URL with protocol etc. (e.g. http://www.quadbase.com/textfile.txt).

After you specify the information, click the OK button and the text source will appear under the TXTFiles node of the Data Source Manager window.

String,date,decimal
Name,day,Volume
"John","1997-10-3",32.3
"John","1997-4-3",20.2
"Mary","1997-9-3",10.2
"Mary","1997-10-04",18.6
            

For maximum flexibility, ERES allows you to design reports and charts using object or array data by providing an interface to pass data to the Report Designer as an argument. Using the API, you can implement IDataSource to return an IResultSet object similar to the java.sql.ResultSet interface used for JDBC result sets. Users can provide their own implementation of IResultSet or use one provided by ERES.

For more information about this feature, please see Appendix 8.A.5 - Data passed in a Custom Implementation.

To add a class file as a data source, select the ClassFiles node in the Data Source Manager and click the Add button. A dialog will open prompting you to specify a display name and the location of the class file you want to use.

Add Class File Dialog

There is sample code available in help/examples/DataSources/classes directory of the installation. The complied code will generate a class file that passes the data array into Report Designer or Chart Designer. Note that in order to use a class file as a data source, you must have the file or directory containing the package in the classpath of your application server/servlet runner.

Here is a simple guide to use SampleData.class file available as datasource in ERES Data Registry:

The most simple solution is to copy SampleData.class file from <ERES install dir>/help/examples/DataSources/classes into <ERES install dir>/WEB-INF/classes/help/examples/DataSources/classes directory (you will have to create help/examples/DataSources/classes under <ERES install dir>/WEB-INF/classes/ before copying the file).

	Note
	You can also change the app server CLASSPATH to include <ERES install dir> instead. (Note that in Tomcat, you have to include all the jars, as setting the CLASSPATH in Tomcat will not pick up the jars under ERES/WEB-INF/lib.)

Start or restart your Tomcat server and open ERES Organizer - DataRegistry

Select ClassFiles and add the new classfile by filling in the dialog (as showed in the picture above):

Now you can view your ClassFile data and use it as datasource for reports and charts.

Using Enterprise JavaBeans™ technology, developers can simplify the development of large enterprise applications. With EJB technologies, developers can rely on building business logic and allow the application server (EJB container) to manage all system level services.

When working in the Java EE™ environment, persistent data interfaces are provided by entity beans. Although the underlying storage mechanism might be a relational database, the application data model is the EJB and it may not be desirable to have a reporting tool making redundant database connections. For this situation, ERES allows users to query data directly from an entity bean.

To add an EJB as a data source, the EJB must first be deployed in the application server and the client JAR file containing the appropriate stub classes must be added to your application server/servlet runner's classpath. Select the EJBs node in the Data Source Manager and click the Add button. This will bring up a dialog allowing you to specify a display name and the connection information for the bean.

Add EJB Dialog

To connect to an EJB data source, you must provide the JNDI lookup name for the bean (this is specified when you deploy the bean). For EJB 1.1 users, specify the name for the home interface and the remote interface. For EJB 2.0 users, specify the local home interface and the local interface. Once you specify all the information, click the Import button, which will analyze the home interface and retrieve all of the finder methods. Any methods found will be populated in the List of Methods section in the dialog.

The same dialog can be used to filter the data being retrieved based on parameters that are present in the finder methods. When you select a method in the left dialog, any parameters present will appear in the Parameter List section. You can then click on a parameter to set its value.

Specifying EJB Parameter Values Dialog

When you select a parameter, the data type of the parameter will appear in the lower portion of the window. Below that you will be able to specify a value for the parameter. Be sure to enter a correct value for the data type, and then click the Set Value button. This will fix the parameter values. Once you finish setting up all parameter values, click the Environment button. This will bring up a new dialog allowing you to specify environment properties for your application server. This information is necessary for the ERES Server to connect to the EJB.

EJB Environment Setup

The fields here are the available environment properties for the JNDI context interface. You don't have to specify all values, only the information necessary for your environment (application server). Once you finish specifying the environment variables, click the OK button. You will be returned to the EJB setup window. Click OK again to finish setting up the EJB data source. A new node will appear under EJBs with your EJB.

You can modify the parameter values by selecting your EJB source, and clicking the Edit button.

There is a sample EJB data source included in the installation in help/examples/DataSources/EJB directory. The directory contains sales.ear and salesClient.jar files, as well as the source code for the Sales entity bean. The sales.ear file is designed to be deployed in Java 2 Reference Implementation and uses the Cloudscape database as the underlying storage mechanism. You can use SalesClient.java program to populate the Cloudscape database. The salesClient.jar file contains the stub classes to connect to the deployed Sales EJB and needs to be in the classpath.

ERES also allows you to retrieve data using SOAP (Service Oriented Architecture Protocol). To connect to a SOAP data source using WSDL, you don't need to know any URLs for the services, SOAP actions, operation names or parameters. All you need to know is a location of WSDL file, which contains all the necessary information.

To set up a SOAP data source, select the SOAPServices node in the Data Source Manager and click the Add button. A dialog will open prompting you to specify options for the new SOAP data source.

SOAP data source setup

The first option allows you to specify a display name for the data source. The second option allows you to specify a location of the WSDL file. The location can be either absolute path on the server or path relative to your ERES installation directory or URL. Once you specify the connection information, you can test the connection to the WSDL file by clicking the Test button. This will test retrieving the WSDL file from the URI you've provided, check the file for any supported SOAP operations and report any problems. After clicking OK, a new SOAP data source node will be added to the data registry.

To add a new SOAP View, select an existing SOAP data source and click the Add button. A dialog will open prompting you for all the parameters necessary to make a SOAP query.

Setup SOAP View dialog

The first option in the dialog allows you to specify a display name in the Data Source Manager. Next, there are three drop-down menus in the dialog. The first drop-down menu contains all the SOAP services described in the WSDL file. Once you specify a service, the second drop-down list will be populated with all the ports of this service. Selecting a port will populate the last drop-down list with all the operations of this port. If you move the mouse over any drop-down list, a hint will appear with documentation for the service/port/operation (if the documentation is provided in the WSDL file). After specifying the service, port and operation, all the parameters of the operation will be read. If there are some parameters, they will be displayed in a table. The first two columns of the table are not editable. They are read from the WSDL file. The next 2 columns are editable and allow you to specify parameter prompts and default values. The last column contains a checkbox which allows you to choose whether the default parameter value will always be used or not. This means that this parameter value will be fixed and you will not be prompted for it. All the parameters that don’t have this checkbox checked will be used as report/chart parameters.

The Setup Data Types button is only available when editing the SOAP View from the Data Source Manager and allows you to adjust data types when necessary. In order to verify result from the SOAP response, click the Preview Result button. All the default values will then be tested to see if they have proper data type or not. In case they do not match, you will be prompted to adjust them. After that, you will get the setup data types dialog (the same as for XML data source). If the data source is parameterized, you will get the parameter prompt dialog before specifying data types. Please note that in order to generate the XML schema properly, you have to specify existing parameter values.

Setup XML Schema Data Types dialog

You can setup data types from this dialog. The behavior is exactly the same as for XML data source with DTD schema (see Section 3.1.4 - Data from XML and XBRL Files). Once you finis specifying data types, click the OK button. A dialog will open showing you the result preview.

Query Result Preview dialog

Clicking the OK button in this dialog will take you back to the Setup SOAP View dialog. Once you finish specifying all necessary information, click the OK button in the dialog. A new node will be added under your SOAP data source in the Data Source Manager and can now be used to create a report or chart.

The Salesforce data source is designed for existing Salesforce users who want to display their Salesforce data in ERES. The connection to the Salesforce server is established via SOAP using Salesforce Partner WSDL (version 13.0). Users communicate with Salesforce server by SOQL (Salesforce Object Query Language) queries. Please note that users must have valid Salesforce accounts with username and password to work with this data source. Moreover, users who use the ERES Salesforce data source must have access to Salesforce account from trusted networks. To add your IP address to the trusted IP list, you have to activate your computer as described below.

For more information about SOQL queries and activating Salesforce user's accounts from trusted networks, please visit the following Salesforce sites:

SOQL queries

https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_sosl_intro.htm

Activating Salesforce user's accounts

https://help.salesforce.com/s/articleView?id=sf.security_networkaccess.htm

To set up a Salesforce data source, select the SalesForce node in the Data Source Manager and click the Add button. A dialog will open prompting you to specify a display name for the data source, user name and password to your Salesforce account. Once you specify the connection information, you can test the connection to your Salesforce account by clicking the Test Connection button. This will test the connection using the information you've provided and report any problems.

Setup SalesForce Data Source Dialog

Once you add a Salesforce data source, a new node will appear in the Data Source Manager window. To add a new Salesforce query, click the Add button. A new dialog will open prompting you to specify a query name and SOQL query.

Setup SalesForce Query Dialog

Please note that only child-to-parent relationship queries are supported in the current ERES version. You cannot use parent-to-child queries (using nested SOQL queries). For more information about Salesforce relationship queries and their syntaxes, please visit the following Salesforce site: https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_relationships.htm

Moreover, this dialog allows you to initialize query parameters in case that your query contains single value or multi value parameters. A parameter is specified within an SOQL statement using the ":" character. Generally the parameter is placed in WHERE clause of an SOQL Select statement. For example, the following SOQL statement

Select Name, Type, Status, ActualCost From Campaign Where Name = :CampaignName
        

specifies a single value parameter called CampaignName. You would then be able to enter a campaign name at run-time and only retrieve data for that campaign.

Another example of SOQL statement shows using of multi value parameters that take an array of values as the input rather than single values.

Select Name, Description, Type, LeadSource, Probability From Opportunity Where Type IN (:OpportunityType) And LeadSource IN (:OppLeadSource)
        

The statement specifies two multi value parameters called OpportunityType and OppLeadSource. You would then be able to specify opportunity types and lead sources at run-time and you will only retrieve data according to specified parameters values.

In order to initialize SOQL query parameters, click the Initialize Parameters button. The initialize parameters dialog will then appear allowing you to specify parameters mapping.

Initialize Parameters Dialog

From this dialog, you can specify the following options:

The rest of the options are basically same as for database query parameters. For further information about initializing database query parameters, see Section 3.1.3.2.2.2 - Initializing Query Parameters. Once you specify mapping for all available parameters, click the OK button and you will be taken back to the Setup Salesforce Query dialog.

From the Setup SalesForce Query dialog, you can also preview the query result using the Preview Result button to verify output from your query. In case you have a parameterized query, the parameter prompt dialog will appear prompting you to specify parameter values. Once you specify the parameter values, click the OK button and the query result preview dialog will appear.

Parameter Prompt

Query Result Preview Dialog

From this dialog, you can verify the query output. Clicking the OK button will take you back to the Setup SalesForce Query Dialog.

Once you specify the query, click the OK button. The query will then be added as a new node under your Salesforce data source in the Data Source Manager and can now be used to create a report or chart.

ERES also allows you to design reports and charts using data retrieved from Excel files. To add an Excel file as a data source, select the ExcelFiles node in the Data Source Manager and click the Add button. A dialog will open prompting you to specify the data source name and to select the Excel file from which the data should be imported.

Setup Excel Data Source dialog - Data

After selecting the Excel file, the imported data will be previewed in the dialog. If the checkbox Use Relative Path is checked, the file path to the selected Excel file will be set as relative to the ERES installation directory. Otherwise the full path will be used. In case the Excel file is stored on a different disk drive from the one which ERES is installed on, this option is not available. You can select the sheet (if there is more than one in the file) and the cells which are relevant for the data source being designed using your mouse or by specifying the range in the Range box (for instance, you can specify that your source will use the data from the columns A and B and from the rows 2 to 12 by typing A2:B12 in the Range box; this is the format which is also used for ranges in MS Excel). You can also select the data by clicking the row header or the column header. Hold Ctrl or Shift to select more rows or columns. Click the top left corner to select all cells. Again, this behavior is similar to MS Excel.

Please note that ERES can process both *.xls files and *.xlsx files. The *.xlsx files are used in Microsoft Excel 2007 and newer and they are based on Open XML.

ERES can also process basic Excel formulas. If it is not able to process the entered formula, an error message will be displayed.

Empty rows (in case the data are in columns) or columns (in case the data are in rows) at the end of the sheet are automatically removed from the data source, even in case they have been selected.

Click on the Options tab to specify whether the data are in columns or in rows to get the data structure correctly. You can also specify whether table headers are included in your data selection. The option Include additional rows or Include additional columns (depending on whether the data are in columns or rows) allows you to automatically include data rows or columns into the Excel file after the data source has been created without the need to change the data source in the Data Source Manager manually.

The bottom part of the dialog on the Options tab shows you the data source content following the current configuration. You can change the data type for each column of the data source there, if desired. The data types are detected automatically, therefore changing the type should not be necessary in most cases.

Setup Excel Data Source dialog - Options

Click OK to save the data source when the configuration is finished.

Once you set up a data registry, the sources defined in the registry can be used for charts and reports. The first time you select to start a new report or chart, you will be prompted to select a registry you want to use. If there are currently no available registries (meaning that you haven’t created any, or you don’t have privileges to view any), you will be prompted to go the data registry manager to create one.

Once you select a registry, a data source manager window will open allowing you to select, add, or modify a data source that you want to use for the chart or report.

Data Source Manager Window for Chart Designer

After you first select a registry, the next time you design a report or chart, it will automatically connect to that registry and open the data source manager. You can change registries by clicking the Change Data button.

3.1.11.1. Using Multiple Data Sources

Once you select the data source you want to use for a report or chart and click the Next button, the next screen will display first twenty records from the data source (With the exception of data views, which will require you to select fields and set conditions first). You can display all of the records by checking the Show All Records box.

ERES allows you to construct reports and charts from multiple data sources. Once you have selected your first source, and click Next from the data table window, you will be presented with a dialog asking if you would like to process the current data or select another data source.

Additional Data Source Dialog

If you select Process Data and click the Next button, you will continue to the next step in the report or chart wizard. If you select Get Other Data Source, you will return to the Data Source Manager to select another data source for the report. You can repeat this process to select as many sources as you want.

Multiple data sources are combined sideways in the data table. This means that the columns from the second (or third, fourth, etc.) data sources are placed to the right next to the columns from the first data source. If the columns from one data source have more rows than the other ones, null values will be placed in extra rows.

For example, assuming you want to use two data sources to create a report. The data table generated by the first source will look like this:

Data From the First Data Source

The data from the second source will look like this:

Data From the Second Data Source

When you combine two data sources, you will get the following data table:

Data From Combined Sources

As you can see, the two data sources have been placed side by side. Since the second source has more rows of data than the first, additional rows of null data were added.

	Note
	You cannot use parameterized data sources to create a multiple data source.

You can also use multiple data sources in reports by adding sub-reports. For more information about sub-reports, please see Section 4.1.9 - Sub-Reports.

There are many circumstances where you will want to move a group of templates or a complete installation of ERES from one location to another. For example an application can move from a development to a production environment. In each environment, the location and connection information for data sources may be different. In this scenario, updating report templates one by one as detailed in the previous section isn't a feasible way to change the connection information for a large number of templates. Instead, ERES allows you to quickly update a group of templates based on information in the data registry.

Although report and chart templates maintain an internal copy of the data source information (allowing them to be deployed independently), they also maintain information (location & source) about the data registry from which they were created. Hence, when you modify a report or chart's query (as detailed in Section 3.1.3.4 - Editing Queries), you have the option to save the changes back to the data registry. In order to use this feature, you will have to keep your data registry up to date. This means that query changes should be saved back to the registry and changes in data view structure should be propagated to data view queries.

To use this feature, first make any modifications to the data registry that you want to propagate to the templates. These modifications can include database connection information, file locations for text, XML data files, and even changes to data views or queries that you want to pass to the templates.

Note that if you're moving reports or charts between installations, the data registry file will need to be moved to the same relative location in the new installation. In addition, the associated query files for that registry (.qry/.dvw/.ddt) will need to be moved to /queries/ directory of the new installation (Query file names begin with the name of the registry).

From the data source manager, select Reports or Charts from the Update menu depending on whether you want to update charts or reports. This will bring up a dialog allowing you to select which files you want to update.

Select Reports for Data Source Updating

To select files to update, first browse to the directory that contains the reports/charts. After you select a directory, any report/chart templates will appear in the left side of the dialog You can select any templates you want like to update and click the Add button. You can navigate to as many different directories as you want to select templates. Selecting the Connection Info Only option will only update the connection information (database URL, driver, username, password, and locations for XML files, text files and Java classes). Queries and data view information will not be updated in the templates.

Once you finish selecting the templates you want to update, click OK and the updating process will begin. A dialog will display the current progress and any errors.

Updating Data Sources Progress/Log Dialog

A log file named UpdateDataSources will also be written in the root directory of the installation with the contents of the progress screen. Reports that fail the updating for various reasons can be updated manually using the option in Report Designer or Chart Designer.

	Note
	Only reports or charts for the current data registry will be modified. If you select templates that do not retrieve their data from a source in the current registry, they will be ignored.

If you want to connect to a data source driver that doesn’t ship with EspressReport ES, CData JDBC drivers are an interesting option.

CData JDBC drivers can be also used to increase capabilities of non-database data sources like Excel or JSON.

	Tip
	Although CData JDBC drivers are a 3rd party commercial product, you can install a free trial version to test the product before purchasing.

	Note
	CData JDBC drivers are a 3rd party product. If you want to use the drivers, you have to purchase them at https://www.cdata.com

3.1.13.1. Supported CData Drivers

Currently, we support the following CData JDBC drivers:

• Salesforce

• BigQuery

• Excel

• JSON

• MongoDB

• Kintone

Other CData JDBC drivers might work too but we can not guarantee full functionality for unsupported drivers. If you require a connection to a driver that is not listed here, please contact us at <support@quadbase.com>

3.1.13.1.1. Excel

Download: https://www.cdata.com/drivers/excel/jdbc

Official CData Documentation (for the JDBC driver only): https://cdn.cdata.com/help/RXF/jdbc

After downloading the driver, see the following chapters: Section 3.1.13.2 - CData JDBC driver installation, Section 3.1.13.3 - Deploying the CData JDBC Driver in EspressReport ES and Section 3.1.13.4 - Using the CData JDBC drivers in DataSource Manager

The CData Excel driver allows you to run SQL queries on top of an Excel file just like it was a database. This allows you to use the QueryBuilder, build Data Views in a graphical user interface as well as write SQL queries manually (in Query Builder).

	Tip
	You can also write queries with parameters and multi-value parameters using Excel files as the data source.

Since Excel does not have data types set for columns as a normal database has, determining the data type for the columns can be a bit tricky at times.By default, we added the following parameters to the CData Excel driver connection URL:

TypeDetectionScheme=RowScan;
RowScanDepth=10;
					

This makes the CData Excel driver scan the first ten rows when loading the selected Excel file and detect the data source for each column automatically based on the data in the first ten rows.

However, there are multiple options of how to determine the data type for each column.

For more options, read the CData JDBC Excel driver documentation about the TypeDetectionScheme parameter: https://cdn.cdata.com/help/RXH/jdbc/RSBExcel_p_TypeDetectionScheme.htm

You can change the TypeDetectionScheme value in the Setup Database… dialog in Data Source Manager in the URL field (the field where you entered the Excel file path).

3.1.13.1.2. JSON

Download: https://www.cdata.com/drivers/json/jdbc

Official CData Documentation (for the JDBC driver only): https://cdn.cdata.com/help/DJF/jdbc

After downloading the driver, see the following chapters: Section 3.1.13.2 - CData JDBC driver installation, Section 3.1.13.3 - Deploying the CData JDBC Driver in EspressReport ES and Section 3.1.13.4 - Using the CData JDBC drivers in DataSource Manager

The CData json driver allows you to run SQL queries on top of a json file just like it was a database. This allows you to use the QueryBuilder, build Data Views in a graphical user interface as well as write SQL queries manually (in Query Builder).

	Tip
	You can also write queries with parameters and multi-value parameters using JSON files as the data source.

3.1.13.1.3. Salesforce

To connect to Salesforce:

Download: https://www.cdata.com/drivers/salesforce/jdbc

Official CData Documentation (for the JDBC driver only): https://cdn.cdata.com/help/RFF/jdbc

After downloading the driver, see the following chapters: Section 3.1.13.2 - CData JDBC driver installation, Section 3.1.13.3 - Deploying the CData JDBC Driver in EspressReport ES and Section 3.1.13.4 - Using the CData JDBC drivers in DataSource Manager

3.1.13.1.4. MongoDB

To connect to MongoDB:

Download: https://www.cdata.com/drivers/mongodb/jdbc

A bug fix was added to newer MongoDB driver from https://cdatabuilds.s3.amazonaws.com/support/DGRJV_8598.exe

Official CData Documentation (for the JDBC driver only): https://cdn.cdata.com/help/DGF/jdbc

After downloading the driver, see the following chapters: Section 3.1.13.2 - CData JDBC driver installation, Section 3.1.13.3 - Deploying the CData JDBC Driver in EspressReport ES and Section 3.1.13.4 - Using the CData JDBC drivers in DataSource Manager

3.1.13.1.5. BigQuery

To connect to BigQuery:

Download: https://www.cdata.com/drivers/bigquery/jdbc

Official CData Documentation (for the JDBC driver only): https://cdn.cdata.com/help/DBF/jdbc

After downloading the driver, see the following chapters: Section 3.1.13.2 - CData JDBC driver installation, Section 3.1.13.3 - Deploying the CData JDBC Driver in EspressReport ES and Section 3.1.13.4 - Using the CData JDBC drivers in DataSource Manager

3.1.13.1.6. Kintone

To connect to Kintone:

Download: https://www.cdata.com/drivers/kintone/jdbc

Official CData Documentation (for the JDBC driver only): https://cdn.cdata.com/help/DBF/jdbc

After downloading the driver, see the following chapters: Section 3.1.13.2 - CData JDBC driver installation, Section 3.1.13.3 - Deploying the CData JDBC Driver in EspressReport ES and Section 3.1.13.4 - Using the CData JDBC drivers in DataSource Manager

3.1.13.2. CData JDBC driver installation

The installation process of all CData JDBC drivers is basically the same for all the supported data sources. We’ll guide you through the process of installing the CData Excel JDBC driver for example.

First, install the CData JDBC Driver of your choice. You will find the links to each supported CData JDBC driver in the chapters below.

After downloading the installer, proceed with the setup according to the dialogs.

In the following dialog, you can choose to either install a paid version of the product or a free 30-day trial version. In this example, we’ll use the trial version.

When the setup is complete, JDBC Driver will be created in "CData JDBC Driver for Microsoft Excel 2022\lib".

	Tip
	Executing "cdata.jdbc.excel.jar" will launch the connection test tool. You can use this tool to test or troubleshoot the CData driver.

	Tip
	The connection test tool also displays you the connection URL that can be used in EspressReport ES Data Source Manager.

3.1.13.3. Deploying the CData JDBC Driver in EspressReport ES

Locate the "CData Installation Directory"\CData JDBC Driver for Microsoft Excel 2023\lib (for example: C:\Program Files\CData\CData JDBC Driver for Microsoft Excel 2023\lib) on your hard drive.The directory should contain three files: cdata.jdbc.excel.jar, cdata.jdbc.excel.lic, cdata.jdbc.excel.remoting

Copy the three files to ERES/WEB-INF/

After you've copied the files, restart The EspressManager (if it is running).

3.1.13.4. Using the CData JDBC drivers in DataSource Manager

Launch the DataSource Manager and open a data registry in it (existing one or a new one).

Select the “Databases” option in the tree-list and press the “ADD” Button.

In the “Driver List:” drop-down menu, select “CData Excel” (or any other CData JDBC driver you might be installing).

In the “URL:” text field, replace the placeholders (like "EXCEL FILE LOCATION") with real values.

	Tip
	Alternatively, you can replace the text in the “URL:” text field with the connection string obtained by the CData Test Connection tool described in the previous chapter.

Click OK. You’re done. You can start using Excel files as if they were a database.