The job of a report writer product is to allow users easily turn raw date into nicely organized and formatted documents for end users to assimilate information. In addition to the ability to display pixel perfect reports on a web browser and export to various formats such as Excel, PDF,RTF and so on, EspressReport offers extensive features that allow user interactivity and to view dynamic contents for actionable insights and decision support. To that end, EspressReport supports dilldown and parameterized reports. In this blog, we will discuss the extensive parameter features offered by EspressReport.
In general, users run reports with some filter options. For example, when you log into your bank account to review a monthly statement, you are running a report that uses your account name and the current month as query/filter parameters.
Since in real life, users will most likely be running reports against a SQL database, we can focus on parameters in SQL statements.
How to specify parameters
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.
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.
Multi-Value Parameters
EspressReport 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.
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.
From this dialog you can specify the following options:
Map to database column:
This is the simplest and most commonly used option. It 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.
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 2020. 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. Consider the following query:
SELECT ORDERS.ORDERID, ORDERS.ORDERDATE, PRODUCTS.PRODUCTNAME, CATEGORIES.CATEGORYNAME, ORDER_DETAILS.QUANTITYFROM ORDER_DETAILS, ORDERS, CATEGORIES, PRODUCTS WHERE (((YEAR(ORDERS.ORDERDATE) = :YAER))) AND ((ORDERS.ORDERID = ORDER_DETAILS.ORDERID)AND (PRODUCTS.CATEGORYID = CATEGORIES.CATEGORYID)AND (PRODUCTS.PRODUCTID = ORDER_DETAILS.PRODUCTID))
Notice that the date function YEAR is applied to the column ORDERS.ORDERDATE in the where clause with parameter :YEAR.
If you are building the query with query builder, you can invoke the formula builder to make the parameter as follows.
In the formula builder, enter a condition comparing a function result to a parameter as shown below:
In the initialize parameter dialog, check the Map to database function checkbox and the values will be automatically filled in.
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 (which is included in the installation) 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) >= :ShipDelayThis 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:
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 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 Cascading Parameters).
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
When the user runs the query, 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. An additional 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.
To utilize this feature in the above example, you need to modify to original query to make PRODUCTS.PRODUCTID the parameter instead of PRODUCTS.PRODUCTNAME 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.PRODUCTID =:product)))
You can map the parameter “category” to SQL result set the same way if you want to make CATEGORIES.CATEGORYNAME to be presented in the parameter drop down list for the user.
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.
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 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 result set 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.
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.
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 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 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.
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.
For more information about characters and their formatting, please refer to the documentation for the printDate() function in Section 1.8.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.
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, EspressReport provides a feature that allows the user to configure the order in which the parameters should be entered. With this feature enabled, the user enters parameters in the dialog in a pre-defined order. As such, each selection will be applied as a filter to the next parameter prompt(s). Using cascading parameters can limit the number of distinct values presented to the user and can prevent the user from selecting invalid parameter combinations.
To enable cascading parameters, check the option marked Prompt parameter in sequence in the parameter initialization dialog. Then click the Config button to set the order of the parameter prompts. A dialog will open showing all the parameters defined in the query.
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 Map to SQL ResultSet.
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.
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.
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).
Select All functions enabled
The Select all feature can be enabled in the Initialize Parameters dialog by selecting the Allow Select All Option checkbox.
This option is only available for parameters that meet the following requirements:
- 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
- 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:
- 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 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 with the where clause appended with something like (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 EspressReport 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:
- Try to modify the query so it can be parsed by our parser.
- Add your own Select all parameters condition to the query.
For example:
WHERE ((column = :Parameter) OR (:Parameter LIKE 'selectall'))
3. Contact Quadbase support.
Conclusion
Quadbase’s reporting products offer unique, advanced query parameter treatments that empower developers to build reports that give end users maximum flexibility in filtering the most relevant data in their data visualization and reporting experience.