IBM Business Automation Workflow Filtering and Sorting Coach View for Server Side Paging Section Asset
Leonard Blunt , Greg Ekaprana, Okka Sudaryanto, Diorella Mari Tom
Code Repository
Paging Section Toolkit Git Repository
Introduction
In a separate posting we detailed the use of a Paging Section coach view that supports limiting the data retrieved to a table and manages the data paging on the server side. Once we had this capability business users still needed to be able to sort and filter data. As the table view was only a subset of the entire data this meant we needed a way to capture the filter/sort data and process that on the server side. The basic initial business request was to be able to filter the dataset using a filter that only included actual data in the full dataset, for example if the table had a column of cities the business only wanted cities in the filter that existed in the table not a filter of all possible cities. While this was the origin, the requirement evolved to incorporate a number of different ways the business wanted to filter the dataset. Each way final requirement is detailed in the section titled “The Business Requirements”.
Initially some attempts were made to do this column by column but we quickly realized to determine the filter set for filters that are not later used in a view was highly inefficient. This led to the design and approach of the current Filtering and Sorting Coach View (CV) that provides user interaction that can be used in conjunction with the Paging Section to filter and sort the data on the server side. This article will provide a step by step example of using the filtering and sorting CV in conjunction with a Paging Section CV. For a detailed introduction to the Paging section see “IBM Business Automation Workflow Server Side Paging Section Asset”.
IMPORTANT: The filter and sort coach view captures what the user wants filtered or sorted. The filter coach view does not execute the services flow to limit the data displayed. When a filter or sort selection is made a changed event is triggered passing the filter data and sort config business object lists as parameters on the event. The developer should then update the filter and sort variables and invoke the paging section coach view first() or init() functions.We will use the following sections to capture the details of the example:
3. Filter Changed Event and Paging Section
4. Server Side Query modifications with Filtering and Sorting
The Business Requirements
The original requirement was to have a capability for a user to filter the dataset in a table using a list of values, but as different data types and columns were examined the filter types evolved into those summarised in the series user stories below.
Setup and Configuration
At this point we will introduce the components of the implementation and break down what you need to know in order to setup and use the coach view (code examples usage, and database DDL scripts can found here). Figure 1 shows the key components of the view and the numbered items will be discussed in more detailing what a developer needs to know to configure and use the view.
- Configuration
2.Binding — FilterDataSet
The developer does not need to know a lot about the bound business object to use by the coach view. The high-level bound instance must be initialized. Create a local variable and initialize it then bind it to the view
tw.local.filterDataSet = {};3. Filter Config Service Call
When the coach view is initialized it invokes the Filter Config service call, the “View Name” specified in the configuration on the coach view is the key for the configuration in the database. Through the database configuration the developer is able to manipulate the columns that can be filtered and specify the filter type of each column. The filter type dictates the user interaction model (This is further elaborated as we proceed in this posting). Before elaborating on this further we will step back and examine how the filter is used this understanding should make it easier to understand the filter configuration to this end shown below is a contrived dataset to illustrate a simple application dataset and a mock-up of how this may be displayed.
TIP: Remember that ultimately the filter and paging section where built for working with large dataset when it is not reasonable to pull all data to the client side. The filter ultimately supports refining the server-side query by helping to identify appropriate data limiters. It is rarely necessary and not recommended to filter and sort based on every column.
Through configuration the columns to be filtered are set. Figure 4 shows a configuration of sample data using the example data set out in Figure 3.
Each of these filters is configured as a row in the SS_FILTER_CONFIG table, the filter config table determines the filters that are shown to a user based on the “View Name” from the coach view configuration.
The definition of filter config table is shown in Figure 5.
The DDL for the table setup for the filter and sort is available in filter-config folder of the “paging-section-tk” git repository. 
Supported Filter Types
The filter coach view supports two categories of filter types, the first provides ways for the user to provide direct inputs to set the filter data and the second category allows the user to select the filter data from lists of existing data from a dataset. The lists are returned from SQL queries that are configured and run when the filter is selected. The queries are preset in the FILTER_QRY column. The supported filter types that may be entered into Filter config TYPE column are defined thus.
The FILTER_QRY column used by the category 2 filter types must map to selected values to “name” for int, dec, and string and “name” and “value” for nvp and intValue where “name” maps to the data and “value” maps to what is displayed to the user.
For example, “SELECT X as name …” and “SELECT X as name, Y as value …”.Filter Configuration Example
Using the table defined from Figure 3: Simple Application Example Data” we created a filter configuration example as shown in Figure 6.
To illustrate different configuration types the four highlighted configurations from Figure 6 will be examined in some detail
Filter Config Example 1: Receipt ID is configured as type “batch”, this provides a text based input for the output FilterData the input data is mapped to the stringList and the datatype is set to string
REMEMBER: The filter does not apply the filtering. And consequently, with batch type inputs there is no validation if the values input exists. Values that do not exist will not impact the filtering but that means users need to be conscious of correctness of values entered.- Filter Config Example 2: Purchase Date is configured as “dateR” (date range), this provides a data entry for from and to date input. The output is mapped to the fromDate and toDate while the datatype is set to dateR
- Filter Config Example 3: City of Purchase is configured as “nvp” (name value pair), this is a category 2 type the coach view retrieves the configuration and executes the query that is stored in the FILTER_QRY column. and the options are mapped into a list and the user is provided a pick list. The pick list data is set by the data returned by the FILTER_QRY, the selected data items are mapped to stringList and the data type is string.
SELECT CITY_NM AS value, CD AS name FROM <schema_name>.EG_CITY_CODE
WHERE CD IN (SELECT CITY_CD FROM <schema_name>.EG_PRODUCT_PURCHASE GROUP BY CITY_CDThe filter framework supports a string substitution for the schema name that can be replaced as shown in the example by <schema_name> this value will be replaced at runtime by the schema ENVIRONMENT variable tw.env.DSN_NEWSCHEMA_NAME.
There are architectural considerations that is worthwhile commenting on at this point. There is an inherent cost to providing such filtering, so developers and users need to take this into consideration. As a result, we recommend that filtering is only done on columns that will help a user simplify the large dataset quickly this will help avoid over indexing or taxing the database with complex and expensive queries. Plan for and include additional indexing on columns that are filtered, but as most database administrators would tell you there is also a cost to this and be careful not to overuse these database features.
There is also an argument on whether it is really valuable to filter a dataset such as cities to only be those in the table, a different approach to the query shown above would be to get all the cities in the city list, and allow the server side query to return no values for cities not in the dataset. In the case of a full list the data result could be cached and not have to check the database each time for the current values in the dataset. This comes down to efficiency versus usability for the application design and depends largely on the dataset.
When planning filters for the data the impact on performance and frequency of queries to the database should be considered.
In the case of that, the dataset is likely to include all possible values the filter config could be simplified to the full pick list data and the result cached to reduce calls to the database. In the example above, the CACHE_QRY column in the SS_FITLER_CONFIG would be set to 1 and query could be simplified as shown here,SELECT CITY_NM AS value, CD AS name FROM <schema_name>.EG_CITY_CODE
- Filter Config Example 4: Country is configured as “string” (string list), this is a category 2 type the coach view retrieves the configuration and executes the query that is stored in the FILTER_QRY column. Unlike the “nvp “query of “Filter Config Example 3” this query maps the values as name, the query reader maps the name to value for displaying to the user.
SELECT COUNTRY AS NAME FROM <schema_name>.EG_PRODUCT_PURCHASE GROUP BY COUNTRY
4. Sort Config Service Call
The Sort configuration is a separate configuration from the filter, as sorting does not rely on data in the column it is a much simpler configuration. Figure 11 details the SS_SORT_CONFIG table definition and Table 3 provides column description of the table.
- Sort Config Example 1: The configuration of the sort is a row for each column to be sorted, grouped by a view name. When selecting multiple sorts, the order is determined by the order the user selects the sort items.
Filter Changed Event and Paging Section
Once configured for a view the coach view will support the creation of combinations of filters and sorting. While combinations are possible the intent for usage should remain constant it is about quickly simplifying the data view for a use to find and work with the data they need.
Multiple Filters can be applied at one time, they will be passed to the backend as a List of FilterData, the order of filters in the list is based on the order the filter is added. Likewise, data can be filtered and sorted at the same time.
When the filter or sort is modified or cleared the filter, “On Change” event is thrown. This is the default behavior but a manual apply can be configured in the configuration that would then require the user clicking the “Apply” button that is visible on view under this configuration. This configuration is more efficient when it comes to managing the number of backend calls. In the case of manual apply the “On Change” event is only triggered when the button is clicked.
The “On Change” event exposes the current filter and sort information’s the parameters passed to the event are FilterData<List> and SortConfig<List>
When using the filter together with the Paging Section variables of this type are mapped to the paging section configuration. Changing the values and calling init on the paging section will refresh the table content inside the paging section. After the onChange event occurs the filter and sort data should be updated and then call first() or init() function on the PagingSection coach view.
Example Paging Section and Filter Coach View
The paging section facilitates the movement of the filtering and sorting data to the data service that is configured on the Paging Section.
Setting up the Filter and PagingSection to work together is easy, we will now step though setting up the example shown in Figure 15. In this section the details to setup a paging section and filter coach view.
The anatomy of this example is three coach views as highlighted in Figure 16
- PurchaseProductTableCV
- PagingSectionCV
- TableFilterCV
The “PurcahseProductTableCV” has a binding to the list data that will be ultimately displayed in the Table for showing the data. The same list is also bound to the Paging Section. Configuration for the coach view includes a FilterDataSet and PagingSectionInit business objects, the first is used by the table filter coach view binding and the second is used to set the configuration on the paging section.
The PagingSectionInit is initialized in the CSHS variable defaults. Each of these values maps to the equivalent configuration item for the PagingSection coach view, see the earlier post for more details.
The passed in values are mapped to the paging section. The paging section binds to the business object list and the configuration is linked to the attributes of the PagingSectionInit business variable.
The filter coach view is setup inside a “deferred section” that is loaded initially when the “Show Filter” button is clicked.
Note: The deferred section is set to visibility NONE, so you need to click "Show invisible items" to see the filter configuration in the exampleThe loading of the deferred section will trigger the configuration loading for the filter coach view. Using such a section is not essential for the example, it is typical of the use we have seen and a common practice, so the page only loads essential features on load. The onClick Event of the “Show Filter” button calls the showHideFilters() function. If the deferred section is not yet loaded, the load is initiated when the loaded event occurs the showHideFilters() function is called again.
Before the deferred section visibility was set to None, the Filter (TableFilterCV) coach view is configured, binding the passed in variable filterDataSet and setting the filter configuration, this is shown in Figure 23.
Having setup the Filter and Paging section coach views, the final step is to link them so that when the onChange event occurs in the filter it triggers the paging in the paging section with the updated filter data.
When the filter changes the onChange event occurs and the filterSortChangedEvent() function is invoked (Figure 24), in this function the filter and sort data is updated, and then the first() function on the paging section is invoked (Figure 25).
For the purpose of the example the data is logged to the console, but this is not necessary.Server-Side Query modifications with Filtering and Sorting
Identifying the filtering and sorting to be completed is half of the action, this section focuses on how this data is used to modify the server-side query.
Would like to remind you at this time that the data design will be impacted by excessive filtering and sorting, and on large tables the wrong choices could have performance impacts. So, while a useful business tool please take care to consider and test the impacts of your filtering and sorting choices.In the upcoming section we shall illustrate the additional server-side code for adding the WHERE and ORDER by statements.
Our assumptions for the following example (which you could be altered based on your own needs):
1. While the data should only be coming from the filter data requests can be faked so we still recommend using parameterized queries and validate the DB column names.
2. Multiple filter/sort options compound meaning AND statements not OR.
3. Multiple filters/sort are applied in the order they are set (So there is no additional logic to filter based on columns selected).
4. Filters are exact matches only. We are not in this example using like or other options on the submitted data.
With these assumptions in place an execution pattern shown in Figure 26 that you can reuse to quickly get started with the filter using a three (3) steps.
Step 1: Set base SQL statements
- Set the business object query
- Set the total records query
The total could be manipulated based on filter also to represent the count based on filter but for our business needs if that was preferred to reflect a total number of unfiltered records. So, no filtering is applied to the count in this example.Step 2: Build WHERE and ORDER by clause
Here we will provide a sample block of code that requires minimal change that can be reused over and over. This code sets up the parameterized section of the WHERE and adds offset conditions needed when paging. As it is largely reusable you can look at the example in the toolkit or get the code snippet in from git repository. Here we will focus on detailing the flow of the code and the likely changes that will be needed.
A simple code flow algorithm is detailed in Figure 29 that captures what the WHERE and ORDER series of functions does.
The section of code that needs to be modified is the initTableProperties() function, this function needs to setup a list of ColumnParameter that is specific to the table that is being mapped. The column parameter maps the column name and the SQL Parameter type. By putting the column name here, it helps protect against injections on the SQL and helps setup parameterized value substitution. Change the initTableProperties() function to represent the columns specific to the table being queried.
Step 3: Setup the Execute SQL statements
The execute are standard SQLExecute statements, one retrieves the total records and the other retrieves the filtered and ordered data. As common with using this the data mapping is essential.
Step 4: Walkthrough of Query Code using Debugger
Now we shall look at an example and examine the outputs of this standardized code to clearly illustrate the inputs and outputs here.
- Setup Mock Input Data
In our case we setup the default values on the service flow inputs of the service to mock the data coming into the service. Be careful with this approach and remove all defaults before promoting code.
input = No default set
pageSize = 5;
currPage = 1;
filterDataList = List of three filter type "nvp" (name Value Pair), dateR (date range), and intValue (Integer Value Pair) see code snippet here.
sortConfigList = 1 sort based on PURCHASE_DT and sort ascending see code snippet here. 2. Walk the Debugger to the point Pre-SQL Execute
With the mock data setup walk the debugger up to the SQL Execute statement then we shall take a look at what the Filter and Sort script task has done.
Let’s now examine the SQL String and the SQL parameters. The original SQL string is shown in Figure 25 and there is no WHERE, ORDER BY or LIMIT portions in the statement this has been inserted dynamically based on the query inputs.
SELECT
EPP.ID AS ID,
RECEIPT_ID AS RECEIPTID,
PURCHASE_DT AS PURCHASEDT,
PURCHASE_AMT AS PURCHASEAMT,
COUNTRY AS COUNTRY,
CITY_CD AS CITYCD,
ECC.CITY_NM AS CITYNM,
PRODUCT_TP AS PRODUCTTP,
EPT.PRODUCT_NM AS PRODUCTNM
FROM PROTELDBNEW.EG_PRODUCT_PURCHASE EPP
LEFT JOIN PROTELDBNEW.EG_CITY_CODE ECC ON ECC.CD = EPP.CITY_CD
LEFT JOIN PROTELDBNEW.EG_PRODUCT_TYPE EPT ON EPP.PRODUCT_TP = EPT.ID
WHERE (CITY_CD IN (?, ?)) AND (PURCHASE_DT BETWEEN ? AND ?) AND (PRODUCT_TP= ?)
ORDER BY PURCHASE_DT ASC
LIMIT 5 OFFSET 0Examining the WHERE condition first, this is built based on the 3 filters in the mock-up data Filter data list. The first entry CITY_CD is a list of 2 and maps to a “IN” clause with two parameter markers (?) added for each entry. The second entry was a date range and the BETWEEN is mapped for the range with a parameter marker (?) inserted for the ‘from’ and the ‘to’ values. Finally, the PRODUCT_TYPE is integer value pair which maps to the integer list in the filter data list but when the list only has one value the equals is used and not the IN.
The ORDER By clause is straight forward for each entry in the sortConfigList a value is added in the ORDER by clause.
The LIMIT on the end ensures that the current page and page size are honored when executing the query.
As the WHERE condition is built, the parameter configuration from the initTableProperties() for the column are used to build up the sqlParameters to substitute. The parameters will be substituted at runtime.
You can now step to the end and see the results mapped into the result list.
Make sure you have data that satisfies all your example conditions or simplify the mock-up start with one filter.Advanced configuration
The following summarizes the two advanced configurations that the filter supports.
Filter Cache Configuration
The first is the ability to cache the result from the filter query. When enabled the Filter Cache will cache all the queriers read from the configuration database and will cache the results for the queries with the CACHE_QRY column set to 1. Enable the cache by
- Configure an object cache instance in the Business Automation Workflow administration console with the below properties.
Name = Filter Cache
JNDI name = services/cache/fdKeep other default values for development and tune sizing based on number of objects and queries in testing.
- In the toolkit set the environment variable to enable the cache
// Code value is literal string match must be true to enable.
ENABLE_FILTER_CACHE=trueIt should be noted that in the current implementation “Applied Filters” which will be discussed shortly has no built-in caching. Caching of the data returned applies to Category Type 2 Filter types.
Applied Filter Configuration
The concept of applied filter allows you to infer a relationship from one filter to another, for example, if I select filter of country then I want to select a filter by associating the country to the city through the applied filter then the cities in the filter would be limited to only cities of the currently filtered countries.
In the current version of the framework the query for this dependant filtering must be explicitly entered we do not currently have a configuration that will support the injection of the dependant filter.
This is a plan to enhance this to be configured as with standard filters, however as of current version the developer needs to insert a branch into the “Get Filter Data Applied Filter” service, adding the conditional query. See the code and service flow for example.
