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

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 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 example, 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 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.

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.

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.

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 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 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 -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 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 /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

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

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.

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 chose) 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 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 chart can 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 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:

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.

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 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 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 you want. The count of groups of characters determines the form 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 it exists. For example, EEEE would return Monday and EE would return Mon. For month M which can be displayed 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 be displayed as 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 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 4.2.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:

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.

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: