The Data Definiton Wizard is started when you choose to 'Add Definition' or 'Edit Definition' from the inmydata Data Publisher Client Application.

Click on the check box labelled 'Skip this screen in the future' if you do not wish to see this screen again.

Click on the 'Next >' button to continue with creating or modifying your Data Definition.

You can click on the 'Cancel' button (at any time) to leave the Data Definiton Wizard without saving your changes.

To preserve the integrity of your data definition and publishing tasks, the inmydata Publisher bind the data definitions to individual tasks and prevent them from being accessed as a separate entities. You can either create a new Data Definition to base your publishin task on, or you can take a copy of an existing shareed Data Definition.

Create a new query is the default setting.  If you select this option and click on 'Next>' you will proceed through the data Definition Wizard creating a new data definition which will then be bound to your task and used as its data source.

Copy an existing query If you click on this option you can then click on the browse button.  A standard Windows 'Open' dialogue box will appear and you can select the data definition/query (.qpa) you would like to take a copy of.  Click on 'Next>' to then proceed through the Query Wizard and Modify the copy.  The copy is then bound to the task leaving the original query intact for others to use as per normal.

If at any stage you wish to step '<Back' or 'Cancel' what you are doing, click on the appropriate button.

Each Query runs against specific Subjects. It is not possible to change an existing Query so it will run against different Subjects.

To select a Subject, Click on the Subject Combo box button to reveal a list of Subjects belonging to the System you chose in the previous screen.  In the drop down list, click on the name of the Subject you wish to use.

You can also type part of the name of the Subject you are looking for into the Subject Combo box itself. This will filter the Subject in the Subject Combo box down to just those which match the string you have typed.

Alternatively, if your system contains a lot of subjects, you can use the Subject Explorer to select a subject by pressing the subject explorer button.

If you have selected a System that is based on SQL then you can press  'Next >'  to create a Freeform SQL Query.

Once you have chosen a Subject, click on 'Next >' to move on to the next step, the Data Definition Wizard - Define Filter Conditions form.

Click on '< Back' to return to the Data Definition Wizard - Select System form, or click on 'Cancel' to leave the Query Wizard without saving your changes.

You have already selected the Subject that you wish to base your Query on and that you wish the Query to be a standard one, however the Subject may contain data Fields that you do not wish to be included in your Query.  The Query Wizard - Select Field form lets you choose which Fields will be included in your Query, and in what order they will be included.

SELECTING FIELDS

The screen displays a List of the fields that are 'Available' for selection, and a list of Fields that you have already selected.  To select a Field, first highlight it by Clicking on it in the Available list, and then click on the '>' button to move it over to the Selected list. You can also do multiple selections by holding the mouse button down and dragging the mouse over the required fields.

You can also use the box immediately above the Field lists to enter part of the Field name that you are wanting to Add/Remove. As you type in this box you will notice that the list of Fields will be filtered based on the what you have typed. This is a useful way of searching for the field you want if there is a large number of Fields in the Subject you are using for this Query.

DESELECTING FIELDS

You can deselect any of the Selected Fields, or change their listing or order in the Selected list.  While you can deselect multiple Fields you can only change the order of one field at a time.

First highlight the Field/s in the Selected list in the same way that you can highlight fields in the Available list.  Then click on '<',  If you click on '<<' all Field/s will be de-selected and moved back into the Available list.

DATA PREVIEW

You can also select any fields displayed in either the Available Fields or Selected Fields list and select the Data Preview Icon. This gives options for adding or removing fields from each of the list but it also gives you the option to preview some of the data in the Data Preview screen.

Once you have finished making your selections click on the 'Next >' button to move on to the Data Definition Wizard - Sort Criteria form.

You have already selected the Subject that you wish to base your Query on and that you wish the Query to be a Summary one, however the Subject may contain data Fields that you do not wish to be included in your Query.  The Query Wizard - Select Field form lets you choose which Fields will be included in your Query, and which which of those fields should be used for grouping the data and which of those fields should be used to calculate summaries.

SELECTING FIELDS

The screen displays a List of the fields that are 'Available' for selection, and a list of Fields that you have already selected.  The Fields that have been already selected are divided by into two lists. The Group by list and the Summarize list.

To select a Field to group by, first highlight it by Clicking on it in the Available list, and then click on the top '>>' button to move it over to the Group by list.

To select a Field to summarize by, first highlight it by Clicking on it in the Available list, and then click on the bottom '>>' button to move it over to the Summarize list.

You can select the type of summary to be used for the highlighted Summary Field by using the Summary Type drop down list.

Depending on the data type of the highlight Summary Field different items will be available in the Summary Type drop down. For example numeric fields will have Sum, Maximum, Minimum, Average, Count, Distinct Count, First and Last but non numeric fields will only have Count, Distinct Count, First and Last.

You can also use the box immediately above the list of Available Fields to enter part of the Field name that you are wanting to Add. As you type in this box you will notice that the list of Available Fields will be filtered based on the what you have typed. This is a useful way of searching for the field you want if there is a large number of Fields in the Subject you are using for this Query.

REMOVING FIELDS

To remove a Group Field, first highlight it by Clicking on it in the Group by list, and then click on the top '<<' button.

To remove a Summary Field, first highlight it by Clicking on it in the Summarize list, and then click on the bottom '<<' button.

SUMMARY GROUP OPTIONS

You can set Summary Group Options for the highlighted field in the Group by list by clicking on the Group Options button. More details on Summary Group Options can be found in the Summary Group Options topic here.

DATA PREVIEW

You can also right click on any of the fields displayed in either the Available Fields, Group by or Summarize lists and a pop up menu will appear. This gives options for adding or removing fields from each of the list but it also gives you the option to preview some of the data in the Data Preview screen.

Once you have finished making your selections click on the 'Next >' button to move on to the Data Definition Wizard - Sort Criteria form.

In the Data Definition Wizard - Select Columns screen you chose the Columns for which data will be retrieved and displayed by your Data Definition.  The Data Definition Wizard - Sort Criteria screen lets you choose the fields by which you wish to sort the data your Data Definition retrieves.  The fields you choose to sort the data by, do not have to be field you have selected to display.

As with the Select Columns screen, Filtering of the lists and viewing of data are available.

As with the Query Wizard - Select Fields screen, you select Fields in the Available list, and then you can change the order of, or deselect the Fields in the Selected list.

The order of your Selected fields is very important.  The records will first be sorted into groups by the first field in the list.  Each group of records will then be sorted by the next field selected, and so on.

You can change the sorting order on each of the Fields you have selected.  Click on the Field in the Selected List, and then click on the 'Ascending' check box.  If the check box is checked then the data will assort in ascending order (eg 1 - 10). if the check box is unchecked the data will sort in descending or (eg 10 - 1).

Once you have defined your Sort Criteria click 'Next >' to move to the next screen, Query Wizard - Add Multiple Subjects to Query.

The Data Definition Wizard - Add Multiple Subjects to Data Defintion screen lets build more complicated Data Definitions by combining multiple Subjects within a Data Definition. 

The base Subject of the Data Definition you have been building is displayed in the top left corner of the treeview.  If you do not wish to create a Multiple Subject Data Definition simply click on 'Next >' to go to the Data Definition Wizard - Publish Settings screen.

To Add a child Subject to an existing Subject, click on the existing subject to select it and then click on the 'Add' button.  The Query Wizard will go through the necessary screens so that you can Select a Subject, Define Filter Conditions, Select Fields and define Sort Criteria for the additional Subject's Query as you did for the initial part of the Query.

The only difference in the process is in the defining of the Filter Conditions where the Query Condition form requires the parent and the child Subjects to be related to each other.  For details see Data Definition - Multiple Subject Conditions.  Once you have added the child subjects they will be displayed in the treeview.

You can not Delete or Edit the Base subject's query from the Multiple Subjects Form

To Delete or Edit the additional Subjects, or Add children to them, click on the Subject in the treeview and then click on the appropriate button.

The Split Query Results option allows you to return each subject as a separate recordset, rather than the default which is to allow inmydata to combine the results into a single recordset. 

The Inner Join option allows you to control the join between the highlighted subject and it's parent. If you select the Inner Join check box then the parent record and any of it's parents will not be include in the results if there are no records returned by the child subject. If you NOT select the Inner Join check box then the parent record and any of it's parents will  be included in the results even if there are no records returned by the child subject.

Once you have finished building your Multiple Subject Query, click on the 'Next >' button to go to the Data Definition Wizard - Publish Settings screen.

When building a Multiple Subject Query at least one of the Query Conditions should relate a column in the Child Subject to a column in the Parent Subject, Report or Dashboard.  As well as Conditions which relate the two Subjects you can also build Conditions which compare a Column in the Child Subject with a fixed Expression or Value.

TWO SUBJECT CONDITION

Using the first combo box select a Column in the Child Subject.

Then select a Qualifier.

As long as there are compatible Columns available in the Parent Table, select the appropriate Column from the last combo box.

Then Click on 'OK'.

If there are no Compatible Columns in the Parent Subject to match the Column chosen from the Child Subject, the Parent Subject Column combo box will be disabled and you will have to create an Expression Condition.  Even if Parent Subject Columns are available you can still opt to use an Expression Condition.

EXPRESSION CONDITION

Select the desired Column in the Child Subject Column combo box.

Then select the appropriate Qualifier.

If you are not forced to create an Expression Condition, you can still do so by clicking on the Expression check box.

Type your value or expression in the panel provided,or generate using the column and function options, that will be evaluated when the query is run.

You can enter any valid Progress expression. Then click on 'OK'.

If you need to create an Expression Condition which is a function of, or contains one of the columns in the Parent subject, select the column from the available columns list.

In the previous step the Data Definition - Select Subject you chose not to select a Subject from the drop down list and therefore decided to base the Query on an SQL select statement.

The SQL select statement that will be used to retrieve data for this query can be typed into the editor on the right of the form.

You can expand a database table in the treeview to see what fields are available from it.

You can insert a table or field into your SQL select statement simply by double clicking on it in the treeview.

If you think the database schema has changed then you can force a refresh of the treeview by right clicking on the database name at the top of the treeview and select Refresh DB Schema.

Once you have defined your SQL select statement click 'Next >' to move to the next screen the Query Wizard - Publish Settings screen. Your syntax will be checked at this point by performing a test execution of the SQL. If there are any errors you will be provided with a description of the error.

To Import data from a file, enter the filename you want to use, or select it using the Open File Dialog button.

The following file Types are supported.

After selecting the file, click the checkbox if the first row of you file has column headers. Depending on the file type you can then set the Delimiter, use the 'Other' option to set a specific non-standard delimiter.

Once you have finished making your selections click on the 'Next >' button to go to the Data Definition Wizard - Publish Settings screen.