Cross-Table Axis/Groupings

Cross-Table Axis/Groupings

Top Previous Next

The Edit Cross-Table Query Axis/Grouping Definition dialog is only shown when you Add or Edit an axis/grouping from the Edit Cross-Table Query dialog. Here you can define the grouping criteria for a row or column of the Query.

The term "Axis" comes from mathematical X/Y graphing, were one axis is horizontal and one is vertical (in this case, rows and columns of values in a grid). From here on, we'll just refer to them as "Groupings", since in the context of this dialog you're really defining how the records are grouped to form rows or columns.

A Cross-table Query must have a Row grouping and a Column grouping, creating a cross-table grid of values where each cell in the grid results from records that meet both the row and column condition for that cell. For instance, lets say you have a Reservation-based cross table, with the column grouping being months and the row grouping being Reservation Type. So for each month you'll have a column, and for each Reservation type you'll have a row, and each cross-table cell contains the records meeting the month and type conditions for that column and row.

Note that whenever we refer to "base table records" or "all records" here, this is of course subject to the Filter Conditions of the Query itself.

So with that in mind, remember that this dialog only defines one of the two groupings at a time, either the rows or the columns. You'll be adding each of the groupings from the Edit Cross-Table Query dialog.

Axis name

This is just a name for your reference, which will be displayed on the Edit Cross-Table Query dialog. Keep it simple but relevant, like "Dates" or "Types".

Axis Type

This is either "Rows" or "Columns", whichever this grouping defines. Later versions may allow other options for sub-groupings.

Grouping Type

There are several types of default groupings available, which mostly just help you define the conditions of the query more easily than raw expressions alone, but also may affect what context information is available for the group's expressions.

Each type of grouping requires different information to define it, as described below.

None (single result) -- If you only want a single row or column, without any grouping.

Dates -- Group by date, e.g. daily, monthly, etc.

Records of a Table -- Create a group for each record in a selected table, e.g. each Site.

Items of a Pick List -- Create a group for each item in a selected Pick List, e.g. each Site Type.

Items of a Fixed List -- Create a group for each item in a selected Fixed List, e.g. each Transaction Type.

Expression, Filtered or All -- Create groups with headings according to an expression you define (see below for details).

Custom Groupings only -- Don't create any default groups, but use the ones you defined.

When a grouping type is selected, you'll see two things happen -- Other fields may appear for entering the details, and a default Group Conditional Expression is supplied to help you get started (which in some cases is already exactly what you need).

Date Grouping

If the "Dates" grouping type is selected, you can select a Date Grouping of Daily, Monthly, Quarterly or Yearly. The date grouping of course defines what groups are created (limited to the From and To date range in effect for the Query), and the date range that will be included in each group. When any date grouping is used, the context functions ThisGroupFromDate and ThisGroupToDate can be used to get the date range included in the group, in any of the group expressions.

You can optionally provide a Group Headings Conditional Expression. This expression will be executed for each date range being considered as a group, so that you can filter which groups in the overall date range will be included in the report (for instance you could use this to only include certain days of the week). The context functions ThisGroupFromDate and ThisGroupToDate will be available for this conditional expression.

Note that no matter what date grouping type is used, the From and To dates selected for the query will limit the actual groups created (and will most likely limit the records included in the query, since they're most likely being filtered by date in the Query's Filter Conditions.) Thus a Monthly column grouping will show a month for each column, but the data contained in the column may still only include one day.

Table & Heading Field

When "Records of a table" grouping is selected, you need to select the Table to be used and the Heading Field to be used for the group headings (row or column headings). For instance you might select the "Sites" table and the "Site Name" for the heading field so it shows the site names. A group will be created for each record of the selected table, regardless of any filtering -- every record of the table considered a group even if none of the Base Table records are related to it (e.g. even if a Site is inactive or has no reservations, it would be included). The default sorting of the records is the actual record order in the table.

The context function ThisGroupRec will be available to get the group's record (e.g. the Site for this row) in the group expressions, and ThisGroupText can be used to get the fields text (the group heading).

Pick List or Fixed List

When the "Items of a Pick List" or "Items of a Fixed List" grouping type is selected, you need to select the list to be used. A group will be created for each pick list or fixed list item (subject to the optional conditional expression as described below). For fixed lists, the headings will be the common item selection text. For Pick Lists, the Selection name field of the pick list items will be used. The default sorting of the groups is the order of items in the pick list or fixed list. The context function ThisGroupText can be used to get the list item's text (the group heading).

Group Headings Conditional Expression (for Date, Record, Pick List and Fixed List groupings)

This optional expression allows you to filter which groups are actually included in the report. For instance in a Records of a Table grouping, a group would normally be included for every record (e.g. every Site). By specifying a conditional expression here, you can limit the report to only show certain records (e.g. filter out by site type, park, etc.).

This expression is executed for each potential grouping, e.g. each record, date group, or list item. As with any Conditional expression, it should have a boolean (True/False) result -- if the result is True, then the group (record or item) will be included. The context available depends on the grouping type -- for Records of a Table use ThisRecord(), for Items of a Pick List or Items of a Fixed List use ThisGroupText(), and for Date groupings use ThisGroupFromDate and ThisGroupToDate.

Group Headings Expression (for "Expression" grouping types)

When "Expression, Filtered" or "Expression, All" is selected for the grouping type, you also need to enter a Group Headings Expression. This expression will be executed for each record of the Query's base table (ThisRecord will have the record context) and must have a text result type (within this expression. Each unique text result created will be used as a group definition, the result text being used for the actual row or column headings. The context function ThisGroupText can be used to get the expression's text (the group heading). By default these headings are sorted in the order that the unique values are found (e.g. no sorting is done).

If "Expression, Filtered" is selected then only records that pass the Query's Filter Conditions will be used to determine the groups (i.e. the Group Headings expression is only executed in the context of those filtered records, and only those results will be used as groups). If "Expression, All" is used, then the filtering is ignored and all records of the base table are used.

A common use of this type of grouping is to group transactions by Operator, since the Operator field of transactions is just a text field. If the "Expressions, Filtered" grouping type is used, then you would expect the report to only include operators involved in at least one transaction in the date range of the report. If the "Expressions, All" grouping type is used, then the report will include all operators who were ever involved in a transaction, even if they didn't have any for the transactions in the Query's date range. Either way, the Group Headings Expression in this example would simply be Tran:Tran_Oper, which returns the operator name for the transaction. Of course this assumes that the base table for the Query is "Transactions".

Show totals for each group

If this box is checked, then the "totals" will be calculated for the column or row and shown at the bottom or side. If the Axis Type is "Columns", this determines whether totals are shown at the bottom (totalling each column). Likewise, if the Axis Type is "Rows" then this option will show totals on the right-hand side, totalling each row.

The calculations for the totals shown can be a Sum, Average, or Count. Sum is obviously just the sum total, Average will divide the total by the number of records involved, and Count will simply show the number of records.

Align headings text

This is simply the text justification used for the headings. Usually "Center" is best for columns and "Left" for rows.

Group headings color scheme

If you want to use something other than the default Windows heading colors, then you need to use a Color Scheme. Appropriate context will be available in the color scheme for the grouping (as described above), in addition to ThisListCount and ThisListRec for access to all records included in each group. Thus you can create a color scheme that colors the data according to group's heading, record or date information, or anything about the records being shown (e.g. the color could be based on how many records are included in the group or even the total transactions amount).

If you haven't created the color scheme you need yet, or if you find that adjustments to the color scheme are needed, then you can use the Edit button next to the color scheme selection list.

Group Conditional Expression

This is the most important aspect of the grouping -- The Group Conditional Expression determines which records (from the Query's Base Table) are to be included in each group. The expression is executed for every record to be included in the Query (subject to the Filter Conditions for the Query), with the context of each group (the row or column grouping information, from context functions as described above). The expression must return a boolean value -- True if the record should be included in the group and False if it should not.

Note that the expression is executed for every grouping expression (for each record) to see if it's included in the group, not just until it finds a True result -- thus it's possible that a record might be included in more than one group, or even all groups. This is normally not desired, but it's certainly possible with the appropriate expressions.

When the Grouping Type is selected, a basic default expression appropriate for that grouping is entered here automatically. In some cases where it can't determine what needs to be used, it will include text like "<fill in field name>". You just need to edit the expression and insert the appropriate field name. Of course these are just basic assumptions, and may not be what you need, but it's intended to provide a hint of what's needed in the expression.

Group Heading Sorting Hierarchy

If the order of the groups is not what you want by default, then you can use one or more expressions to determine the group order in the rows or columns. The context of the group is available for the sorting expressions as described above (e.g. the heading text, record or date information). To edit the sorting hierarchy, click the "Edit group heading sorting hierarchy" button above the list.

Note that this sorting is only done for the "default" groupings as defined by the Grouping Type. It does not affect any Custom group Definitions -- all custom groupings will appear after the sorted default groupings.

Context for Expressions

The context functions available for each grouping type are detailed below. These context functions are available to any expression executed for a particular cell in the cross-table:

The Group Conditional Expression

ThisRecord() / ThisResv() / etc. for the record being testing for inclusion in the group

ThisGroupRec() if it's by table,

ThisGroupText() if it's a records of a table, pick list, fixed list, or expression

ThisGroupFromDate() / ThisGroupToDate() specify the group's range for a date grouping

The Group Heading Sorting Hierarchy expressions

ThisGroupRec() if it's by table,

ThisGroupText() if it's records of a table, pick list, fixed list, or expression

ThisGroupToDate & ThisGroupFromDate if it's grouped by dates

The Group headings Color Scheme