Editing Cross-Table Queries |
Top Previous Next |
The Edit Cross-Table Query Definition dialog is shown when adding or editing cross-table queries from Queries Setup. Other functions where Queries are referenced, such as the Queries Tab view, may also have a button to directly Edit the Query without leaving that function and going through Queries Setup.
Here you can edit all of the components of the Query. The top portion has a few basic fields you can edit directly and buttons for editing the Filter Conditions for the Query and Testing the Query. Next is a grid for editing the "Axis/Grouping" definitions, and the main portion of the dialog for editing the "meat" of the Query -- the Data Expression and various formatting/action information.
Query Name
The name should be descriptive enough for selecting the Query out of a drop-down selection list. Queries will usually be shown in the order they appear in Queries Setup, not alphabetical, so the name doesn't affect the order. Each Query must have a unique name (which is not case-sensitive).
Base Table
The base table determines the primary data table of the Query -- that is, which records are potentially going to be included in the Query. For instance, if the base table is Reservations, then the Query will include all Reservation records by default, subject to the Filtering Conditions.
Any table in the database may be selected, but only a few are useful for most situations -- Reservations, Customers, Transactions, and Sites.
For more details about base table selection, refer to Editing List Queries in a previous section.
Save & Test Query Results
This function invokes the Save & Test dialog, which shows the Query in a grid. This can be used to test the query, without completely exiting the Edit function. Be aware that it does completely save any changes you've made to the Query, so it negates any possibility of canceling changes you've made.
Access Level
The access level simply determines which operator access level is required to view the Query. If the current operator does not have the selected access level, then the Query will not be shown in the selection list on the Queries Tab view, for instance.
Exclude from Lists
When this is checked, the Query will not be shown in any selection lists such as in the Queries Tab view. Note that this does not actually disable the Query -- e.g. if it was already selected as an add-on for a tab view then it will still be used in that function. However it won't be available as a general selection, which can be handy for keeping special-purpose Queries out of view. Of course this also means that you would need to uncheck this again (temporarily at least) if you ever did need to select the Query somewhere. Another alternative would be to set the Access Level for such Queries to Administrator, assuming that most operators are non-administrator.
Note that one place this does not affect is the Tab Views Setup (since this is assumed to be an administrator-only function anyway).
Filtering Conditions
The filtering conditions determine which records of the Base Table are included in the Query. Without any filtering conditions, ALL records are included. Refer to the Filter Conditions section above for more details.
Includes Text Filter
When this option is checked, the user will have the option to enter search text. You should include a filter condition that checks this text against the appropriate field, e.g. a customer name, site number, confirmation number, etc. While not necessarily useful for most cross-table queries, you can use it as a free-form filter, e.g. to show the results for only a certain operator, or only a certain reservation type (assuming you have the Filter Conditions set up accordingly).
Axis/Grouping Definitions
The Axis/Grouping definitions determine what kind of cross-correlation the query is going to show. These are shown in a grid, and the Add/Edit/Delete buttons to the left are used to modify them as needed. Note that the order of these in the grid does not matter. Currently there must be exactly two axis definitions, one for rows and one for columns, although the grid is designed to hold more than two (for future expansion, e.g. to allow sub-totaled cross-tables).
The most common combination of groupings uses one axis for dates (e.g. daily or monthly totals) and another for some other grouping of interest (e.g. transaction categories, reservation types, site types, etc.) This equates to the "Group by" and "Summ by" selections in transaction summary reports and most statistical reports available in the Reports menu. However it's also possible to make both groupings non-date, e.g. to cross-correlate reservation type with discount type, or how-heard with rig type (use your imagination!).
For more details, refer to Cross-Table Axis/Groupings.
Calculated Data Expression
This defines the "meat" of the Query. To edit the expression, click the Edit button or simply click on the text box below it. This invokes the Expression Creator dialog to edit the data expression.
After all of the cross-axis grouping is done to figure out what records are to be used to calculate each cell of the Query, the Data Expression is executed for each cell (each row and column combination). Obviously you need a way to get the list of records in the cell, and a way to do calculations from those records. The context functions ThisListCount and ThisListRec are used to access the records for each cell. Most cross-table Queries will involve summing up something about the records, so here's a simple example to add up the amount of Transactions for each cell (assuming the base table is Transactions):
LoopSum(1, ThisListCount(), "#i#", 'TranBalAmount( ThisListRec( #i# ))')
The expression above simply sums up the transaction amounts for each record in the list (ThisListRec) from 1 to the number of records in the list (ThisListCount).
There may be cases where you don't need to do anything with the records in the list, just show how many are in the list (included in the group), like in an Arrivals Statistics report. In that case the data expression can simply be ThisListCount(), which returns the number of records in the grouping.
While the expression's results don't need to be numeric, keep in mind that if you plan to show any kind of totals for the rows or columns then it must be numeric.
Data Color Scheme
If you want to use something other than the default Windows colors for the data in the grid, then you need to use a Color Scheme. Appropriate context will be available in the color scheme, such as the list of records being shown for the cell and the from/to dates selected for the Query. Thus you can create a color scheme that colors the data according to content or anything else about the record being shown. There are other context functions available specifically for cross-tables, which usually start with "ThisGroup". For instance, ThisGroupText will contain the heading text of the cell's group, and ThisGroupFromDate will contain the starting date for the cell.
The most common use for this in Cross-table Queries is to show negative values in red. This can be done easily using the ThisValue context function to get the value being shown in the cell. For example, create a Color Scheme with this expression for rule with red text:
ThisValue() < 0
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.
Format
This determines the format of all numeric values in the Query, assuming the Data Expression results in a number. For non-numeric data, the "General" option should be chosen. For numeric data, select an appropriate format, e.g. Currency, Integer, Percent, or Floating Point. If none of these quite fits your needs, then you can select Custom Format and enter an expression to format the data any way you need to.
The Format specification also determines the format of the row and column totals, if they are used in the Query.
Custom Format Expression
This field only appears when the Format selected above is "Custom Format". To edit the format, click the Edit button or simply click on the text box below it. This invokes the Expression Creator dialog to edit the format expression.
For the most part, the format expression should simply convert a numeric value to text. The numeric value to be formatted will be available with the context function ThisValue, which will already be shown in the expression the first time you edit it. There are several functions available for formatting numeric values. For a simple example, lets say that we want to take the number an show it as a currency value rounded to the nearest dollar. The expression entered would be:
Currency( Round( ThisValue(), 0))
Align Text
Select the desired alignment, or justification, of the data in the query grid (including any totals shown).
Pop-up Tip Expression
If you want something to appear in a pop-up hint whenever the mouse cursor is placed over a cell, then you can enter an expression here. To edit the tip expression, click the Edit button (or simply click on the text box below it). This invokes the Expression Creator dialog to edit the tip expression.
The result of the expression should be a text string to be displayed. Typical context will be available for the cell underneath the cursor, so the expression can use the value in the cell, group information, or fields from the list of records being shown.
Note that the pop-up tip can only be a single line, so be careful about its length.
Double-Click Action Expression
This can be used to make something happen when a cell is double-clicked on. To edit the expression, click the Edit button or simply click on the text box below it. This invokes the Expression Creator dialog to edit the expression.
This expression could be anything from showing a message box with information about the cell's contents to executing a Script performing all kinds of functions, even changing the values of fields. Just be careful about doing things that can't be easily undone, since a double-click might be accidental.
Notes
You can enter any notes for yourself here, or use this as a description of the Query -- they're only seen here and in the list in Queries Setup.
Additional Topics:
Advanced Customizations Overview & other topics