Dictionary maintenance

The dictionary functionality in Dynamic AI gives developers and end-users the benefit of “cascading” centrally defined elements, rules and definitions across any number of underlying Reports. E.g. if 10 Reports are build upon the same order information table, the dictionary definition of the order information table allows for central maintenance of e.g. Column and field captions, column and field help instructions etc. across the 10 Reports in multiple languages.

Dictionary definitions are resolved in real-time during any use of Reports.

Dictionary functionality can be divided into the following:

·         Captions and help-text maintenance

·         Enforced filters / list box definitions

·         Security definitions based on user alias settings

·         Security definitions based on user role settings

·         Primary Key / Unique sorting definitions

·         Data Partition definitions

·         Free dynamic view parameter definitions

Captions and help-text maintenance

The dictionary is used in order to allow central maintenance of field/column captions (in 3 different languages) as well as enabling central maintenance of field/column help texts.

Enforced filters / list box definitions

When building Dynamic AI reports, list-edit Reports and forms a wide range of parameter and filter formats are supported.

When a field on a form or a field as a filter is to be presented to end-users as a group of options (like in a COMBO or LIST box control) the dictionary allows for a central definition of the underlying source for the COMBO/LIST box.

The primary objective of the dictionary definition of parameter COMBO/LIST boxes is to simplify Report definition as well as to provide the benefit of central maintenance across any number of underlying Reports.

The secondary objective is to optimize COMBO/LIST box creation at run-time by:

·         Allowing the COMBO/List box to build upon an alternate data source

·         Allowing the COMBO/List box to be kept in dictionary memory (static type)

Security definitions based on user alias settings

Dynamic AI is designed for re-use of existing data and Report definitions much as possible while optimizing output to match the needs of individual users as much as possible.

One way to personalize output is to segment returned report information into user-specific segments e.g. depending on the department, organization ID, Customer ID etc. of the Dynamic AI user.

In order to link the user account to Reports, the user account is exposing the following variables at run-time:

@UserID

@Username

@Alias1

@Alias2

@Alias3

@Alias4

@Alias5

@Alias6

@Alias7

@Alias8

@Alias9

@Alias10

These @xxx types of variables can either be directly used in a Report definition as a condition to which records are to be presented whenever that specific user is executing the Report or centrally as a forced filter for a specific data-source.

Examples:

If the user is only allowed to see Order Information from the Order table whenever the “department code” on the order records in the table are ‘120’, the user account can be used to store the “department code” ‘120’ in @Alias1 for the specific user.

On Order Information Reports, the condition for “department code” set to @Alias1 will result in the result set being restricted to records where the “department code=@Alias1, i.e. “department code=’120’ when executed with that specific user account.

Any combination of @Alias1-10 can be used. @Alias variables can be used not only on Reports, also the connection definition and style definitions will resolve @Alias variables at run-time.

The dictionary allows for a central definition of @Alias parameters in the FORCEFILTER column, providing a central control – independent from underlying Report definitions – that @Alias restrictions to underlying data are being kept.

Security definitions based on user role settings

Where the use of @Alias can restrict the subset of records returned from a data-source and presented in Dynamic AI Reports, the use of User roles can be used to restrict the columns/fields from the underlying data-source exposed to the end-user at run-time.

In order for “User roles” to take effect, the user account must specify role types A through J, combined with the dictionary definition for the specific table / view which will restrict the exposed columns.

Primary Key / Unique sorting definitions

In order to ensure unique order presentation of returned records from certain databases it can be necessary to sort on unique keys on the underlying data-source. The dictionary allow the definition and maintenance of “Forced Sort” keys which are then used as the last sort key across all underlying Reports presenting record level information at the lowest level to end-users.

Data Partition definitions

Another feature of the dictionary is to extend any underlying data-table or view with an additional selection of “partition” through a COMBO/List box defined and kept in the dictionary.

The easiest way of doing this is when working with the Dynamic View or Dynamic JOIN. Read more about this in the section “New Dynamic SQL Views.

In order to create the extra dictionary element a record should be added to the dictionary for the specific

CONNECTIONID + TABLEQUERYNAME

Having the keyword “@DYNPARTITION” as the SOURCEFIELDNAME.

In order for the Partition definition to be valid, a COMBORID with corresponding COMBOVALUE and COMBODISP fieldnames must be specified.

Optionally the Partition COMBORID can be loaded and kept in application memory by specifying 1 in the MEMLOADKEYLIST (see below).

Example of a Partition definition in the dictionary:

Contents of the Report with the RightID number 10:

The PartitionInfo1 column contains the information which will be displayed in the selection COMBO/LIST box presented to the end user as a parameter on list / report Reports.

The ShowVersion column contains the partition definition including the following:

!¤Partion¤!ORDERINFO_20020131!¤DESC¤!Month-End (01/31/2002)

where !¤Partion¤! is the keyword specifying the Table/View name that Dynamic AI should use as the underlying data source when executing the Report at run-time. In the above sample the user would get data from the ORDERINFO_20020131 table/view using the ORACLEDATA database connection when selecting 31/01/2002 from the selection COMBO/LIST box.

If the user selects 01/02/2002 dynamic would be using the !¤Partion¤!ORDERINFO_20020201! And therefore use the Table/View named ORDERINFO_20020201 to retrieve information.

In any of above mentioned cases the !¤DESC¤! keyword prefixes the text to be displayed confirming the selected partition when returned as output to the end user. For instance, when the user selects 01/02/2002 the report returned would in the header display: Daily (02/01/2002).

Using the CAPTIONLNG1, CAPTIONLNG2, and CAPTIONLNG3 fields, a caption for the partition parameter can be specified.

It is possible to combine partition selection COMBO/List boxes with Dynamic AI view definitions, and parse the @DYNPARTITION@ value to the source of the Dynamic AI View definition. Optionally the partition can be extended with any number of parameters parsed to the Dynamic AI view definition in the format:

!¤P1¤!P1 VALUE!¤P2¤!P2 VALUE!¤P3¤!P3 VALUE etc.

E.g. a valid partition definition for a dynamic view could be:

!¤Partion¤!ORDERS20020228!¤P1¤!ORDERS20020131!¤DESC¤! Two versions (02/28) + (01/31)

where P1 could be used to specify the second table/View in a union SQL statement for comparison of values in-between 2 separated table/views.

The P1-Pn values are defined in the Dynamic View as:

@P1@

@P2@

etc.

To create above sample with a union the Dynamic View syntax could be:

Select * from @DYNPARTITION@ O inner join CUSTOMERS C ON O.CustID=C.CustID

UNION ALL

Select * from @P1@ O2 inner join CUSTOMERS C2 ON O2.CustID=C2.CustID

Dynamic executes at runtime the SQL statement:

Select * from ORDERS20020228 O inner join CUSTOMERS C ON O.CustID=C.CustID

UNION ALL

Select * from ORDERS20020131 O2 inner join CUSTOMERS C2 ON O2.CustID=C2.CustID

Free dynamic view parameter definitions

Dynamic AI View definitions allows the use of input parameters, that the user will be requested to input before executing the defined view against the background data-source.

When parameters are entered into Dynamic AI view definitions, they will be automatically detected and declared as parameters along with a default value, and finally stored into the dictionary for the view definition.

No specific knowledge or use of the dictionary is necessary during definition or use of dynamic views. The dictionary is used solely as a store for the free parameters maintained directly through the dynamic view definition interface.

Dynamic AI Dictionary definition

The Dynamic AI Dictionary object is a child object to the connection object and the parent object for all underlying Reports defined on the dictionary connection + table / view / dynamic view.

Dynamic AI will be using the dictionary, providing that definitions exists.

The dictionary is defined in the repository table: DNMCDICT and can be accessed through various dictionary system lists:

New entries

New entries will show the latest entries in the dictionary

The fields included on the list are:

Forced filter/Key fields

The list forced filter/Key fields is used for defining the fields that should have a forced filter applied, eventually via @alias functionality and for defining list boxes.

The fields included on the list are:

Roles

Roles are used to restrict certain users from view informations stored in specific fields in the data source.

The fields included on the list are:

Caption by fields

The list Caption by fields can be used for simple search for specific field names across the dictionary for easy maintaining of captions.

The fields included on the list are described above.

Global SQL Columns

Global SQL Columns can be used as “global +columns”. Meaning that in the same way it’s possible to ad extra columns via +Columns on a individual report (please see section “+Columns”). The main difference is that when using a SQL Columns stored as a global it can be used on all reports based on the specified data source.

As shown above Connection ID and table/view names are specified by typing the name. If you want to create a Global SQL column on top of a Dynamic SQL View or Dynamic JOIN you will have to address it by typing the object number, which can by found I design mode of the View/JOIN or at mouse over on the View/JOIN in the Databases menu.

As show below it’s now possible to use that field directly from the design of the report based on that Tabel/View:

Or Dynamic View/JOIN:

Refreshing the Dynamic AI Dictionary

The Dynamic AI dictionary is refreshed during server initialization (Starting and stopping Microsoft Internet Information Server or restarting the server PC), and whenever updating one of the system tables as described above. The dictionary is obviously also refreshed whenever “Upd Dict” is executed on the connection definition form or “Create dictionary” from explore databases is executed.

Be aware that the “Create Dictionary” option in Explore databases will only appear to users who have the “Maintain Dictionary” right assigned in their User account.

The “Upd Dict” option on the Connection definition form will automatically add new records specifying:

·         Connection ID

·         Table/View name

·         Field Name

·         Added

·         By

to the dictionary for the specific table / view / query or dynamic View definition for Field names not previously defined on the Table/View name for that specific Connection ID that are used in reports in the application.

The “Create Dictionary” option is a fast and effective way of establishing the basic dictionary definition for a Table/View name.

A Dynamic AI dictionary can be applied whenever the need arise. Whether or not the dictionary is defined before or after the creation of Reports, the dictionary will take effect.

Maintaining the Dynamic AI Dictionary

The easiest way of maintaining the dictionary is by reaching the dictionary via “Maintain Dictionary” found in the drop down menu next to the tables/views/Dynamic JOINS/Dynamic SQL Views in the Databases menu.

From there a report like the one below will be opened:

The first list shown is for language 1 (in this case English). The list is automatically filtered on the right Connection ID (in this case Demo) and Table/View name (in this case 1005).

The only field on the list not addressed in the above section regarding Dictionary definition is the “Help English” (or any other supported language).

In the top of the screen you will see a menu giving you access to control the dictionary regarding other languages, enforced filters, Roles, and Global SQL.