There are three types of Subject you can build. Freeform Subjects allow you to combine related tables which have Links between them, or you can achieve more complex Subjects by creating them from Business Logic Procedures or PROGRESS Smart Data Objects .
All DataPA Queries are based on Subjects. Subjects are a way of controlling the Query building process, preventing the use of unrelated tables, inefficient sorting on non-index columns, inefficient finds on non-index columns and the display of sensitive or system data.
By grouping together related tables and allowing you give columns appropriate labels, Subjects also simplify the Query building process for the end user by removing the need for any knowledge of the structure of the underlying application database.
Alternatively it is possible to use an Clone an Existing Subject.
Using the Subject Explorer you can enquire on, select (if you currently have the subjects tab selected) and search for particular subjects.
Subjects - Create New
To Create a new Subject you must be in the inmydata Configure Data Sources screen.
Ideally Select the System prior to creating a Link using the Tree view, this will preselect the System in the Wizard
Click on the 'New' Icon in the Toolbar.
The Subject Wizard - Introduction form will then be displayed
Click on File (On the menu bar), and then click on New Subject
This will also open the Subject Wizard - Introduction form.
Subjects - Modify Existing
To Modify a Subject you must be in the inmydata Configure Data Sources screen..
The Details will be displayed on the right hand side.
To amend the name and description of the Subject select the 'Name' Tab.
To view the type of the subject select the 'Type' Tab. (Note: This is not updateable.)
To amend the tables in the subject select the 'Tables' Tab
To modify any business logic Parameters select the 'Parameters' Tab
To change the subject's conditions select the 'Conditions' Tab
To amend the list of columns in the subject select the 'Select Columns' Tab
To modify any calculated columns in the subject select the 'Calculated Columns' Tab
To change any of the attributes for a column select the ''Column Attributes' Tab
After making any Changes, ensure you Save your Changes
Subject Wizard - Introduction
If you do not wish this screen to be displayed in the future, click on the check box labelled 'Skip this screen in the future'.
To continue and Create or Modify a Subject, click on the 'Next >' button and the Subject Wizard - Name and Description form will be displayed.
Click on the 'Cancel' button at any time if you wish to leave the Subject Wizard without saving your changes.
Subject Wizard - Name and Description
When you Create a Subject you must give it a name. This should be entered in the text box labelled 'Subject Name'.
If you wish, you can also enter a Description of the Subject in the box provided.
Click on the 'Next >' button to continue. If you are Creating a New Subject you will go to the Subject Wizard - Select System form, but if you are Modifying an Existing Subject you will go directly to the Subject Wizard - Select Type form.
Click on the 'Cancel' button at any time to leave the Subject Wizard without saving any changes.
Click on the '< Back' button at any time to step back and review your work.
Subject Wizard - Select System
The Subject Wizard - Select System form lets you define which System your Subject belongs to. You can not reassign an existing Subject to a different system.
Click on the Combo box button to see a list of available Systems, and then click on the name of the appropriate System to select it.
Once you have selected the System, click on the 'Next >' button to move on to the Subject Wizard - Select Type form.
Click on the '< Back' button to return to the Subject Wizard - Name and Description form.
Click on the 'Cancel' button at any time to leave the Subject Wizard and return to inmydata Configure Data Sources Screen without saving any changes.
Subject Wizard - Select Type
The Subject Wizard - Select Type screen allows you to determine which kind of subject you wish to create, and for business logic and smart data object (Progress Systems only) subjects, select the business logic procedure.
Each type of subject is described in more detail below:
A freeform subject allows you to create a subject based on a SQL query (SQL Systems) or Progress For Each statement (Progress Systems). This type of subject requires no business logic on the server and as such can be created entirely with the subject wizard.
Business Logic Subject
A business logic subject allows you to build a subject based on a business logic procedure available on the server. When the Create business logic subject option is selected, a list of available business logic procedures will be available in the combo-box. You must select a business logic procedure to continue.
For SQL Systems, this list of business logic procedures will contain all appropriate stored procedures available on the server.
For Progress Systems, the list of business logic procedures will contain all functions available in the AppServer super-procedure stack that return a single handle. For the subject creation to succeed, the server-side function must return a temp table with the handle. For details on the AppServer super procedure stack and functions, please refer to the Progress documentation.
SmartDataObject Subject (Progress System only)
A SmartDataObject subject allows you to build subjects using Progress ADM2 or Progress Dynamics SmartDataObjects. To create a SmartDataObject Subject, select the Create SmartDataObject subject option, then enter the name of the SmartDataObject in the combo box.
Alternatively, click on the drop down button on the combo box to list previously used SDOs from which you can select.
If you wish to use a Dynamics SmartDataObject ( System must be a Progress Dynamics System) , simply check the Dynamic SmartDataObject option and enter the name of the SDO.
Subject Wizard - Parameters
The Subject Wizard - Parameter Values window allows you to determine how inmydata populates input parameters for business logic procedures. inmudata gives you two options for populating business logic parameters, to always pass the same fixed value, or to prompt the user for a value each time a query is run.
To specify that inmydata should use a fixed value to populate a parameter when a query is run, select the parameter in the list view, and select the Use fixed values as parameter option. The rest of the Fixed value panel gives details of the fixed value that will be passed. The screen will change based on the parameter type
To specify that inmydata should prompt the user for a value for a parameter each time a query is run, select the parameter in the list view and select the Prompt user for parameter value option. You must also enter the settings for the required value.
The Required Value Name is the Name or Label that will be displayed when the user is prompted to enter a value at Query run time.
The Required Value Description is the description or help message that will be displayed when the user is prompted to enter a value at Query run time.
By putting a tick in the Mandatory check box, you can determine whether or not the user is forced to enter a value when running a Query based on this Subject. A Non-Mandatory condition gives the user the option to Skip entering a value when running a Query based on the Subject.
The Add button will open the Lookup Wizard to allow the creation of a new lookup.
If any Required Values are entered sample values will have to be entered after pressing Next in the Test Execution Parameters screen.
Subject Wizard - Test Execution Parameters
When creating a business logic subject, inmydata needs to execute the business logic on the server in order to determine the structure of the data it returns. The Parameters Wizard collects values for any input parameters that havee business logic requires in order to do this.
To complete the wizard, enter a value and press Next to move on to the next parameter.Once a value has been collected for each parameter, press Finish to complete the wizard.
Subject Wizard - Select Tables
This screen lets you include the Tables which make up your Freeform Subjects. The form contains a list of Available Tables and a list of the Tables you have selected.
To select your first Table, click in the Available list, on the table you wish to select. This will highlight your choice. Then click on the '>' button to move your choice over to the Selected list.
Text can be entered into the Filter box to limit the list of tables.
Your chosen table is now highlighted in the Selected list. The Available list has now changed. The Tables are now listed under Linked Tables, are those tables which have a Link to the Table highlighted in the Selected list.
It is possible to add a New Link if a table is required using the Add Link button that opens the Link Wizard .
Once you have more than one table in the Selected list, you should take care when choosing further tables. In the diagram above, Customer is selected and one of the available tables is Salesrep. In the diagram below, Order is selected and one of the available tables is again Salesrep. This means that there are links between Customer and Salesrep, and between Order and Salesrep. If you want to use the Link Between Customer and Salesrep, make sure that Customer is highlighted in the Selected List when you choose Salesrep from the Available List.
Once you have selected the tables you require, you can change the links that bind these records by selecting the child record of the link in the Selected list and pressing the Modify Link button. This will open the Link Wizard to allow you to change the link. Any changes you make will not effect the link for any other subjects.
If you incorrectly select a table, you can click on it in the Selected List and use the '<' button to move it back to the Available List.
By holding down the mouse button and dragging you can choose multiple tables in either list, to move over to the other list.
Once you have selected all the tables that you require in your Subject, click on the 'Next >' button to move on to the to the Subject Wizard - Define Criteria screen.
As with the other forms in the Subject Wizard you can click on Cancel or '< Back' at any time.
Subject Wizard - Define Criteria
Defining Criteria on a subject is a way of controlling the data that users will be able to extract using the queries they build on that Subject. If you do not wish to define any Criteria move onto the Subject Wizard - Select Columns form by simply clicking on the 'Next >' button.
If you do wish to define criteria, then click on the 'Add' button to bring up the Subject Condition form which will guide you through building the conditions of your criteria.
If you wish to Edit or Delete any of the conditions which make up your criteria, simply click on the condition in the list view to highlight it, and then click on the 'Delete' or 'Edit' button. If you click on the 'Edit' button, the Subject Condition form will be displayed to allow you to make your changes.
Subject Wizard - Subject Condition
The Subject Condition form lets you build up the components of your Subject Criteria. Your Subject Condition can either be a 'Fixed Value', a 'Required Value' a 'List of Values' or an 'Expression'. These can be set using the A Fixed Value is a value which is permanently in the condition. A 'Required Value' is one that the user is prompted to enter whenever they run a Query based on the Subject. A 'List of Values' is a list of fixed values of which one must be true for a record to be included in the results.
When you have multiple conditions, you can choose whether your condition logic states;
' where condition 1 AND condition 2' or ' where condition 1 OR condition 2' by using the condition combo box in the top left corner of the form.
Note you can the Preview Data button in this screen to preview a sample of data the field contains.
Using the relevant Combo boxes Select the appropriate Table and Field from the Tables which you have built into the subject.
Then select the appropriate Qualifier.
Click on the 'Fixed Value' radio set to enable the Fixed Value text box. You can then enter the Fixed Value that future Queries will compare the selected field against.
You can also compare against an expression by clicking the "Expression" tab and entering a valid Progress expression in the text box provided.
A Required Value is one which the user is required to enter at Query run time.
The 'Required Value Name' is the Name or Label that will be displayed when the user is prompted to enter a value at Query run time.
The 'Required Value Description' is the description or help message that will be displayed when the user is prompted to enter a value at Query run time.
By putting a tick in the 'Mandatory' check box, you can determine whether or not the user is forced to enter a value when running a Query based on this Subject. A Non-Mandatory condition gives the user the option to 'Skip' entering a value when running a Query based on the Subject.
LIST OF VALUES
A list of values is in effect a list of Fixed values.
Type each List Item into the Fixed Value text box and then click on 'Add' to add it to the List.
To delete an Item highlight it with the mouse and press delete.
An expression is a Progress calculation involving one or more columns.
An expression can be typed in directly into the Expression box, or be generated using the column and function options.
The 'Add New Function' or 'Edit' icon will load the Function Wizard screen to add/Edit a User Defined Function.
The Function is a set of Progress functions that can be used to build the expression.
The Check Syntax button will check that the Expression is valid before leaving the screen.
Once your Condition is defined, click on 'OK' to return to the Subject Wizard - Define Criteria form where you will see your condition listed in the list view.
Subject Wizard - Select Columns
You have already selected the tables or business logic that form the basis of your Subject and any queries that will be built on it. However you may not wish all the columns in the tables or business logic procedure to be visible in any subsequent Query. With the Subject Wizard - Select Columns form you can choose which of the table or business logic columns the users are allowed to build into their Queries.
Using the 'Table' Combo box select the table from which you wish to select the columns. The Available Columns will be listed below. Next highlight the column you wish to select by clicking on it. Then click on the '>' button and the column will be transferred across to the 'Selected' List.
You can select multiple Fields by holding the mouse button down and dragging over the fields in the Available List. Clicking on '>' will then move all the highlighted Columns over to the Selected List.
By clicking on the '>>' button you will Select All the Columns from All the Tables in your Subject.
This is basically the same as Selecting Columns. Select the table whose columns you wish to deselect, and selected columns will be displayed in the 'Selected' Columns List.
Click or Click and Drag on the Field or Columns you wish to deselect and then click on the '<' button. The deselected columns will be moved back to the bottom of the 'Available' Columns List.
By clicking on the '<<' button, you will deselect All the Columns, regardless of which Tables they were previously selected from.
NOTE you can preview a sample of the data contained in Columns via a right-click on the column list and selecting Preview Data from the pop-up menu.
NOTE you can filter the lists by entering text into the Filter text boxes
Once you have Selected all the Columns that you wish to be included in your Subject, click on 'Next >' to move on to the Subject Wizard - Calculated Columns form.
Subject Wizard - Calculated Columns
Defining calculated columns allows your subject to include data that is not directly available from the connected databases. Calculated columns can be derived from any valid Progress 4GL statement, including dynamic functions. If you do not wish to define any Calculated columns move onto the Subject Wizard - Column Attributes form by simply clicking on the 'Next >' button.
If you do wish to add calculated columns, then click on the 'Add' button to bring up the Calculated Column form which will guide you through building a calculated column.
If you wish to Edit or Delete any of the calculated columns, simply click on the calculated column in the list view to highlight it, and then click on the 'Delete' or 'Edit' button. If you click on the 'Edit' button, the Calculated column form will be displayed to allow you to make your changes.
The Calculated Column form lets you add calculated columns to your subjects using a Progress 4GL statement.
First, enter a valid name for your calculated column. The name must be unique and contain only valid characters.
Next select an appropriate data type for your column, and enter an appropriate Progress format string. For more details on Progress data types and formats see the Progress documentation.
Finally, enter a valid Progress expression for your calculated column.
If you want to include database columns available in your subject in your calculated column, simply select the required column using the Available Columns list, then double click on the item. When a query using the subject is executed, the value of this column will be substituted into your expression.
You can also select and insert into your expression the standard Progress Functions.
The list of Available Columns and Functions can each be filtered using the appropriate Filter Box and Data can be Previewed using the Data Preview Button.
Once you have finished entering your calculated column, press OK to continue.
Subject Wizard - Column Attributes
The Subject Wizard - Column Attributes form lets you set certain properties for the columns you have chosen to include in your subject.
To set a Column's properties, first choose the Table that it belongs to using the Available column Treeview. Click on the Column to select it. You can then specify the following properties;
Column Label - Very often the column names in a database don't mean very much to it's users. DataPA lets you specify in your Subjects, the Column names to be used when the users are building their queries. Simply type the name you wish to use in the 'Column Label' text box.
Allow Column to be used as a Condition - This will default to true if the column belongs one of the database indexes, and false if it doesn't. This Column lets you determine whether the users can put conditions on the column when building their Queries. You may set 'Postcode' as a condition column so that the users can build a query which looks for customers in a specific postal area.
Allow Column to be used for Sorting - This will default to true if the column belongs to a database index, or false if it doesn't. You may set the 'Salesrep' column to be used for sorting, thus enabling the users to build queries which Sort customer records by who the Sales Rep is.
NOTE: You can not use a column as a Condition or Sort field If the Subject is based on a Smart Data Object and the column is an SDO Calculated Column, or if the Subject is Freeform and the column has multiple extents.
NOTE: If your Subject is made up of multiple tables it may contain columns which come from different tables but which have the same Column Label. If this happens you will get an error message. To remedy this, you should change the Column Label of one of the instances of the column.
By 'Clicking' on the Options button you can gain access to additional options.
The pop up menu options allow you to set all your columns so that they may be used for sorting or as condition columns, or reset them to their defaults.
It also allows you to easily reset all the labels from the database labels, or to quickly set all the labels to the database column names or perform a global search and replace on all the column labels.
NOTE: Non Index columns can be used for Conditions and Sorting but this is not as efficient as using index columns and may cause queries to run slowly.
Once you have set the Column Attributes, click on 'Next >' to move on to the final screen which is Subject Wizard - Finished.
You can click on '< Back' at any time to review and amend the work you have done in the previous screen Subject Wizard - Select Columns.
Subject Wizard - Finished
The Subject Wizard - Finished screen lets you know that you have entered the information required for your Subject to be created. It also gives you the opportunity to go back revue the information you have entered before finally creating the Subject.
If you are happy with the details you have entered, click on the 'Finish' button to complete the Subject. If you want to review your work click on the '< Back' button to go back through the Subject Wizard - Column Attributes and all the previous forms.
If you want to, it is still possible to discard your changes by clicking on the 'Cancel' button.