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.

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 Edit → Delete Column. Once you have selected a column field, you can specify how you would like to sort the column, either ascending or descending 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, and last. 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.

To build your own column, right click on a blank column in the QBE window. 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 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

EspressChart can auto-correct items entered as query conditions, by appropriately appending the field name, and encasing string arguments in quotes. For examples, if you enter = ARC, EspressChart will change the query condition to Categories.CategoryName='ARC'. If you're using complex functions (i.e. database functions that take multiple string arguments), EspressChart 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.

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 the different operations into SQL. You can edit the generated SQL, however, if you change the SQL and then return to the Design View, any changes will be lost. If you save a query after changing the SQL, then the query will re-open to the SQL View tab if you select to edit it.

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

You can export queries in one of two ways. You can output the SQL statement as text, or you can output the query result as a 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 option that you would like, and a dialog box will appear prompting you to specify the file name and location.

This 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 chart in the chart 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 the 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 would prefer to get all the data from the column without constraints (sometimes this can improve the performance of the parameter prompts), you can set the -singleTableForDistinctParamValue argument when starting EspressManager. For more information about EspressManager configuration options, see Section 2.3 - Starting EspressManager

The map to database column feature is very handy for the end user to enter a valid value for a parameter from a list box. But what if the data the user wants is not the exact value from the database column. Rather it is something computed or derived from the value in a database column. For example, you want to find all the orders for 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 box and the values will be automatically filled in.

Map Parameter to Database Function

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

If your database is not listed in the .xml file, the function list will be populated by functions listed in the JDBC driver. However, the function parameters are not provided. For example, the HSQL database is not 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 map to database function, the following prompt would be shown.

No Parameter Types for HSQL Function

The DateDiff function takes 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

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 when running the chart. 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 to 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. An example is as follows:

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 simply 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 one of the columns will supply values for the drop-down list while the other 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 would like to use the product name as parameter since a product ID could be just a cryptic code. Using this feature, you can choose product name for the values in the drop-down list while product ID as the actual value filter condition.

Select Statement with Two Columns

Rather then having a drop-down menu with all the distinct column values, you can build a custom list of parameter values for the end user to select from as well. To set up the 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 the custom values, or select the values from the distinct values of a column in the database. Once you have finished specifying the values for the list, click OK and the choices will be saved.

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 chart 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've chosen) or map the default value to a SQL query.

For multi-value parameters (see Section 4.2.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.

This option is only available when the parameter is not mapped to a database column or function, or 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 may add or subtract units of time from the current date/time, allowing you to have a dynamic date range. For example, a chart may have the following default values:

StartDate: CurrentDate - 1 WEEK
EndDate: CurrentDate
                                

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

You can also use functions to define the parameter value:

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

Use this to add an option to the parameter prompt dialog that allows users to select all parameter values even for single-value paramters. See the Section 4.2.2.2.3 - All Parameters for more details.

This allows you to set the format in which the date parameter should be entered. This option is only available if you have not mapped the parameter to a column, or entered custom selection choices (i.e. the end-user will be typing in the date value). When you check this option you can enter the date format in a combination of characters that represent time elements. You can build the format easily using the date format builder by clicking on 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.

The following table illustrates the character combinations that you can use:

You can piece together almost any combination of these characters to produce a date expression in the format that you would like. The count of groups of characters determines the form that the element will take. For text elements 4 or more characters in a group will cause the full form of the element to be used. If less than 4 characters are used the short form will be used if one exists. For example EEEE would return Monday and EE would return Mon. For month M which can display as either text or a number, 4 or more in a group will display the full version, 3 will display the abbreviation, and 2 or less will display the number form.

For numeric elements, the count of characters is the minimum number of digits that the element will take. Shorter numbers will implement leading zeros. For example if the day of the date is 2, dd would return 02 and d would return 2.

Any character that is not a-z or A-Z like “;”, “:”, “@”, etc. can be inserted anywhere within the string expression, and will display as they are entered. You can also insert words and expressions by enclosing them within single quotes (type two single quotes to insert an apostrophe as text).

This allows you to specify the prompt that is given to the user in the parameter dialog.

It is possible to select all parameter values at one time even for parameters that don't allow mutli-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 doesn't allow multi-value selection).

Single-value parameter with the Select All functions enabled

The Select all feature, can be enabled on the Initialize Parameters dialog (see Section 4.2.2.2.2 - Initializing Query Parameters for more details) by selecting the Allow Select All Option check-box.

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

After you've selected 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.

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:

If a chart 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 EspressChart 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: