System administrators can now define dynamic queries that can then be made available to users to design their own extracts and grouping of data. There is a new dataset builder and corresponding enquiries, reports and extracts that can use it.

The queries can also be used in the Report Builder.

Dynamic Queries allows an administrator to define the dynamic queries (datasets) so that they contain the records and fields that the users require.

Dynamic Queries Browse Screen

Fields

Field

Description

Query

The code used to identify the query.

Description

A description of the query.

Result Table

The temp table name of the resulting dataset.

Table(s)

One or more tables that are used in the query.

RSP(s)

The corresponding RSPs for each of the tables in the Table(s) list. This allows specific RSPs to be used for specific tables.

Query

This is either:

Parameters Section

If the query requires one or more parameters (for example, a financial date, a currency code or a contract), create a page section update form that will be used to prompt the user for this, and enter the page section code here.

Parameter Fields

A list of the fields on the update form specified in Parameters Section. These will be prompted for in the Parameters section of the selection form.

Dynamic Filters

The number of dynamic filters that will be built on the Filters section of the selection form for the enquiry or report.

When you have defined the table and query select Save (). You can then select the hyperlink for your Dynamic Query to select the fields that will be available.

Dynamic Query Fields

From v11.08 additional features have been added to the dynamic queries definition screens to allow the query to optimised at run time.

On the field you can specify when a subquery can be used to optimise the query.

Fields

Field

Description

Source

The source table.field for this field in the query. The table.field combination must exist in the corresponding RSP defined for this table on the query. Leave this blank to specify a calculation (see below).

Field

The field name in the resulting temp table. It must be unique for this query.

Although you can use any name for the field, it is sensible to use standard names (for example: job_num), as the name dictates which lookup is used.

Description

The description (label) for the field.

Data Type

The data type of the field.

Format

The default format for the field.

Filter

Whether the field should be presented to the user in the Date Flters section or the Filters section. Leave this blank if you do not want the field to appear as a filter.

Column

Whether the field should be presented to the user as group field (dimension) or a value field (measure). Blank will mean the field can be selected for a report/extract but cannot be used in the grouping report.

Calculation

A calculation to populate the field in the temp table. This is run if the Source is blank Calculations must use the original source fields from the query tables, not the calculated fields.

Do not use variables that attempt to span across fields as the user may not select all the fields you use. Variables can be used within the calculation of a single value.

Subquery

The Subquery field tells the engine which subquery to use. The subquery will need to be defined on the subquery tab for the query to work. The Source2 field should define the field name in the subquery table that corresponds to the field defined in the dynamic query. Often this may be the same but sometimes it will be different. You will be warned if the Subquery does not exist yet but it will allow you to save the record so that you do not necessarily need to create the subqueries before building the fields. However, if you do not complete the Subquery details then the dynamic query will fail if you use the field with the missing subquery.

Source2

The Source2 field should define the field name in the subquery table that corresponds to the field defined in the dynamic query. Often this may be the same but sometimes it will be different. You will be warned if the Subquery does not exist yet but it will allow you to save the record so that you do not necessarily need to create the subqueries before building the fields. However, if you do not complete the Subquery details then the dynamic query will fail if you use the field with the missing subquery.

Subqueries

On the subquery tab you can define how the subquery can be evaluated before the main query. This will allow pre-filtering of the subquery records so that they can be efficiently built in to the main query to enhance performance.

The subquery is largely similar to the definition of a dynamic query as it will be run as a subquery to the main query.

The subquery code needs to match the subquery code used on the fields of the dynamic query.

Field

Description

Query

The table(s), RSP(s) and query are the same as used on the main query and define how to access records in the subquery.

Fields

This is a list of fields (one per line) that will be assigned to variables in the pre-filter records that match the subquery criteria. The Source2 field from the field is passed over and added to the query defined on the subquery. The resulting records are then put in to the pre-filter record and Fields are assigned to fields in the pre-filter record. The fields in the pre-filter are i1,i2 (for integers) and c1-c5 (for characters). You decide the field you want to use to store the values from the subquery. In the example kco is put in i1 and jty_type in c1. The fields you specify are from the subquery tables.

Where1

This is a list of fields that will be added to the main query to filter the records using the variables (i1,i2,c1-c5) that you have assigned when the subquery was used. The fields specified are from the main query tables.

If there is an index on the main query that will allow the records in the main query table to be filtered based on the pre-filter then specify it. This will allow the pre-filter to be placed in front of the main table(s) query when run and reduce the number of records processed.

Weight1

This allows you to put a weighting on this pre-filter so that the engine can decide which pre-filter is best to use. When the subquery is run the number of records returned is recorded and multiplied by the weighting. The subquery with the lowest count * weight is considered to be the best one to use in front of the query if there is an index that allows an efficient join to the main query tables. If weight is omitted then 1 is assumed. This and the weight2 allows the designer of the dynamic query to encourage the query to use one or other index in preference based on the expected efficiency of each index.

If the join to the main query tables requires a further subquery to be performed then that can be specified in a similar way to above in Subquery2, Where2, Index2 and Weight2. This is a rarely used option but allows for example filters on jc_job (in subquery1) and jc_costcode (in subquery2) which is then used to link to transactions say that have both job_num and jcc_cc.

In this example with filters on type and group and customer number.
In the log we see the following queries (they are run in the order of definition):
​
​for EACH ar_cust WHERE ar_cust.kco = 10 and CAN-DO( "B*", ar_cust.rcm_num ) no-lock
5 results
for EACH jc_ctype WHERE jc_ctype.kco = 10 and jc_ctype.jty_desc >= "A" AND jc_ctype.jty_desc <= "Z}" no-lock
12 results
for EACH jc_cgroup WHERE kco = 10 and jc_cgroup.jgr_desc = "Sector 01" no-lock
1 result
​
The weightings on each of the subqueries is 1 so the query with an index with the lowest count is used. Jgr_group does not have an index on jc_job so rcm_num is selected.
The resulting main query is :
​
​EACH xttResult1 WHERE xttResult1.cSubquery = 'RCM' no-lock,each jc_job WHERE jc_job.kco = 10 AND jc_job.job_complete = FALSE and xttResult1.i1 = jc_job.kco AND xttResult1.c1 = jc_job.rcm_num no-lock , FIRST xttResult3 WHERE xttResult3.cSubquery = 'JGR' AND xttResult3.i1 = jc_job.kco AND xttResult3.c1 = jc_job.jgr_group NO-LOCK, FIRST xttResult4 WHERE xttResult4.cSubquery = 'JTY' AND xttResult4.i1 = jc_job.kco AND xttResult4.c1 = jc_job.jty_type NO-LOCK [17/12/14@17:31:16.859+0000] P-026070 T-2425304960 1 WS -- (Procedure: 'runQuery.x4 sycou005.p' Line:3527) Rows: 7
​
This uses the RCM subquery using the results returned form the 1st subquery, to then join to the jc_job table using the index on rcm_num on jc_job using the i1 and c1 fields specified in the subquery. The other subquery joins are then placed after the main query to further filter the results.

The results returned by the optimised query are exactly the same as would be returned if the subqueries are not defined but by careful consideration by the designer the query used can be considerably more efficient.

Dynamic enquiries can be run from the Dynamic Querybrowse by clicking the button.

Dynamic Query enquiry

The selection for the enquiry is split into four sections (though some sections may not be used).

Dynamic Query enquiry Selection Screen

Parameters are single values that are required to run the query or fields, for example: Financial Period.

Date Filters are fields that are specified as date filters in the dynamic query configuration and are shown in full, with a label, From and To fields, and a date lookup. The CLEAR button to the right will clear all the fields in this section.

The fields in the Filters section are dynamic: you can select the field and the filter value. The number of dynamic filters shown is defined in the dynamic query configuration. The lookup button is active if the field selected in the dynamic filter has a lookup associated with it. The CLEAR button to the right will clear all the fields in this section. The filters can include filters on ANY of the fields in the dynamic query even if they are RO fields or are calculations.

The Fields section allows you to select which fields from the dynamic query will be shown in the enquiry page.

Click the Next button to run the enquiry and show the resulting records and fields.

Dynamic Query enquiry Results

​Dynamic Queries - Dynamic Reports

Dynamic reports can be run from the Dynamic Query browse by clicking thebutton. The selection screen is the same as for the Dynamic enquiry.

Dynamic Report Selection Screen

The report runs and (if requested) produces a PDF and spreadsheet export, and the pivot data for the resulting records and fields.

Dynamic Report Output

Dynamic Report Spreadsheet

Dynamic Report Pivot Table

Dynamic grouping is similar to the dynamic report, except that you are able to select fields from the Dimension fields and values from the Measure fields as defined in the configuration. The resulting output is aggregated by the dimensions, and shows the count of the records for that grouping and the sum of the value fields selected.

Dynamic grouping can be run from the Dynamic Query browse by clicking the

button.

Dynamic Grouping Selection