Data Subset window

The Data Subset window is accessed from the Building Blocks window.

Please refer to Designing the Process Model for information on designing data subsets.

A note on popup windows

The Data Subset window uses a lot of popup menus and popup windows to allow you to see the big picture and hide the details of each step. These windows don't have an "OK" button; changes take effect immediately. Just click outside the window to hide it, or just start working on the next task and the popup will be hidden.

Adding and removing tables & steps

Remember that a data subset describes a temporary in-memory relational database (or data set) that consists of a subset of records from the whole database. The in-memory data set can have multiple tables. In the example shown here, there are two tables: FLY.task and FLY.job. Each table has a separate data source and sequence of steps to restrict or manipulate the table.

Press Add Table to add a new table. Click the red X by the table name to remove the table.

Click the black + symbol to add a new step in any position. Click the red X by the step to remove it.

Parameters

It's worth studying and experimenting with parameters because that is one of the main strengths of data subsets. In many business scenarios, you need to display records that meet criteria entered by a user - for example, locating a customer by name or zip code, or some other property. There are two steps:

• Define a parameter for each user entry. Parameters are defined on the "Name/Parameters" tab. Parameters must have a data type, just like properties of records.

• Use a parameter in one (or more) of the filter steps or a calculated column step.

Once you do these two things, when you load a data subset (by going to the Preview tab), Axisbase will ask for the values for the defined parameters.

In a situation where a data subset is invoked from a hyperlink from another building block, parameters can be supplied from the calling block; in this case, Axisbase won't ask for the value.

Optional filters

Optional filters come into play when you enter text into the entry "Prompt to control whether this filter is applied". That entry is available for fast filters and complex filters.

Here is an example of using an optional filter. Suppose you already defined parameters for company name and zip code for use in a customer search. When you load the data subset, Axisbase forces you to enter both a company name and a zip code, and it uses both entries to filter records. Many times, you want the user to be able to decide whether to search by name or zip code or both. In these cases, you enter a prompt in the filter popup window, such as "Filter by zip code?" or "Filter by company name?".

When prompts exist, Axisbase allows the user to choose whether to use that filter. You could build a data subset that has a lot of different filters, each one with an optional filter prompt, to give the user a lot of flexibility in how to locate records.

Optional filters can be used with or without parameters. Here is one of each kind:

In the upper example, the user will get the choice of whether to filter by task status equals 'A', or omit the filter. In the lower example, the user will get the choice of whether to filter by task status, and additionally the user will be asked to choose which status code to filter by.

Fast filters

A fast filter can only work on one property, and you can only choose among the comparisons: Equals, Starts with, and Is in range. These limitations ensure that indexed searching is used, which is faster than scanning all records. If you need a different kind of comparison, use a complex filter instead. But for good performance, you should generally try to use at least one fast filter (unless there are a small number of records total).

For the "starts with" comparison, the search is case-insensitive. It only works with string data.

For the range comparison, you can choose a lower limit, an upper limit, or both. Either limit can be a value you enter at design time or a parameter that the user enters at load time.

Complex filters

The Complex filter popup window doesn't give you much to go on, so here are some examples to get you started. Also see Expression syntax for the complete reference to expressions.

This one matches all customers whose city contains "burg":

match(#MOON.customer.city, "burg")

This one matches all customers whose name or notes contains the value of the parameter "findword": (|| means "or")

match(#MOON.customer.name, @findword) || match(#MOON.customer.notes, @findword)

This one matches employees whose current salary is lower than 10% more than their starting salary:

#PLUTO.emp.salary < (#PLUTO.emp.startingsalary * 1.1)

Multi-valued parameter filters

Multi-valued parameter filters allow the user to enter more than one value for a parameter, and Axisbase will pass records through the filter that match any of the values entered.

This kind of filter has good performance because it uses an index. It's a good alternative to a complex filter when possible.

You must define the parameter as multi-valued in order to use it with this kind of filter.

Summaries

If you know SQL, summaries are like a 'group by' clause.

When you include a summary step in a table, the data subset constructs a new table from a summary of the data in the original table. If you check "retain original table as detail", then the final dataset will contain both tables in a master-detail relationship; otherwise the original detail table is dropped.

To create a summary you enter three things on the popup window:

• a column name to group by (or multiple names separated by commas). Enter the columns for "create one row for each...". Typical grouping columns are dates and status codes. Another way to understand it is the categories in which you want summary information. For example, if you want total sales volume by month, then you would create one row for each date. (or range of dates)

• a choice of grouping: either on each unique value or on each range of values

• the summary outputs - that is, the minimum, maximum, sum, or average of some other column. For example, for total sales volume by month, the sale amount would be the column to summarize.

You must press Add for each summary column.

The created summary table will have columns for each group-by column as well as a column for each calculated column.

When editing a data set that has the detail table attached, Axisbase will recalculate the summary data after each change.

Calculated columns

A calculated column step adds a column to the in-memory data table and fills it with the results of a calculation. The calculation can be based on other values in the record, parameter values, and/or constants.

The format of the calculated column is NAME=EXPRESSION.

The name you enter will be the column name. Note that columns that come from database records will be named with the Axis ID and type, such as 'MOON.customer.name', while calculated columns will have simple names that you define, like 'newsalary'.

The expression can be anything defined by Expression syntax.

Linking tables

Use the Link table step to connect two tables in the data set in a master/detail relationship, such as sales linked to sale line items, or customers linked to sales. Linked tables are useful for lists and reports when you want nested (grouped) data.

The link table step should be added to the master table. In the popup window:

• The master table's "column forming relation" is usually the key (such as MOON.customer.key) but can also be another property that uniquely identifies the master record such as a customer ID number (depending on how you have designed your data model).

• The detail table's "column forming relation" is the reference in the detail table to the master table. For example, to link customers and sales, the sale (detail table) should have a property that references the customer, and that property will often be named "customer".

If the above is confusing, remember that in both cases, you are choosing the column that unquely identifies records in the master table.

You should generally define the master table first (possibly with filters), then define the detail table (possibly with filters), then add the link step to the master table.

By creating a link, you are implicitly creating a filter on the detail table; only those detail records that match one of the master records will be included in the data set.

Joining tables

When joining tables, Axisbase combines two tables into one. For example if you have a customer table and a sales table, you can join them so that each sale record has copies of the customer columns added on to it.

In you know SQL, then you will recognize that joining tables is similar to a SQL inner or outer join. This is also called "denormalizing" and is standard practice in databases for producing reports or other non-editable search results. Since Axisbase supports linking tables, and Axisbase lists are designed to work with linked tables, the role of joining tables is reduced.

You define a join step the same way as a link step - please see the notes above on linking tables.

Union of tables

Combine two tables in the data set into one table. The columns of the two original tables must be identical, and the rows of both tables are included in the final. Think of this as placing tables one under the other.

A common use of unions is to perform two or more unrelated fast filters and get the records that match either filter. This is different from adding two or more filters on the same table. If you have multiple filters on the same table, only records that match all filters will be included.

To load a union where both tables are based on the same record type:

• Define the first table to load all records of a type. For this example, we'll use the type MOON.customer.

• Add a second table and define it to also load all records of the type MOON.customer. However, since two tables can't have the same name, you will have to rename the second table after you define the source. You could rename it to MOON.customer2.

• Add a union step to the first table and in the popup window, enter the name of the second table (MOON.customer2)