Getting Started

To create a report, start by clicking the New Report button on the Home page of Informer. The New Report window will come up.

Give your report a descriptive name and choose your mapping data source, most likely Datatel Unidata.

Choosing a Mapping

In the “Select a mapping” box, you will select the primary file of your report, ie PERSON, STUDENTS, or HRPER. Your primary mapping must contain fields or links to files with fields you want to filter on or report (more on links later). The choice of your primary file is normally fairly easy, and in some cases, more than one choice will work fine. If you want information from both the STUDENTS file and the APPLICATIONS file, for example, you can probably start with either file and link to the other. An important consideration is that filtering on computed columns in files other than the primary file may be difficult. Linking to other files for filtering can also sometimes be problematic (see filtering section for more). Therefore, it is probably best to pick a file on which you can do most or all of the filtering. In the end, you can think of your query as boiling down to a list of IDs of your primary file, from which the columns you choose will then be gathered. Sometimes it will be more desirable to use IDs of one file over another (See the Filtering On a Computed Column in a Non-Primary File section in Using Select Returning Criteria for a good example of this).

Filtering

To define filter criteria for your report, go to your report’s Overview page (this will come up after you create a report; if you are on the Informer home page, click on the report; if you are looking at the report results, click the Report Template tab) and click the blue, bold text to the right of “Select Filter” (it will initially say “none”). The Edit Criteria screen will come up for your report. Remember to click “Save” or “Save and Close” when you are done or you may lose your work.

There are six types of criteria you can add for your report. The criteria you add will be joined together in one of three ways, depending on what you select:

• All – All conditions added must be true. Conditions are joined by AND’s.
• At least one – One of the conditions must be true. Conditions are joined by OR’s.
• None – All conditions must be false. Conditions are joined by NOR’s.

“Compound Condition” and “Select / Returning” criteria types have their own selection menus, simulating the use of parenthesis. So, for example, you can select anyone who has moved to student, and who is either female or lives in Ohio (this example is shown in the Compound Conditions section). To use any condition, click it on the left and drag it to where you want it to go. It will not matter where the condition goes unless you’re using a “Compound Condition” or “Select / Returning” condition.

Limits to Filtering

The few limits to filtering are as follows:

• Computed columns in a non-primary file cannot be filtered on. If the needed file can link to the primary file, you can use a select / returning block to work around this limitation.
• If access to a file requires more than one link, foreign keys for any links beyond the first link cannot be a computed column.

Simple Conditions

Simple conditions are the most commonly used conditions. A new simple condition looks like this:

A simple condition allows you to compare a property (a field in the database) to one of three types of values: a literal, a prompt, or another property. Each simple condition has three selections you must make, and some have an additional fourth:

Property

The property selection is where you choose which field in the database you want to filter on. You can click on the Property input box and begin typing a field name. A list of available fields in your primary file will appear and be filtered as you type. You can use * for a wild card. If you want to see all fields available in the primary file or want to filter on a field that is not in the primary file, click on the “…” to the right of the input box:

The right half of the window shows the fields available in the file. The Dictionary Name column shows the name of the field in Datatel. The Name column is usually the same, other than having spaces instead of periods and being in title case, although some columns may be given a more user-friendly alias. The attribute column is rarely of use, but tells the sequence number of each field in the file. Note that columns with an attribute of 0 are usually computed columns. Again, you can filter the fields shown by using the input box at the top of the window. The First, Previous, Next, and Last buttons allow you to scroll through the pages of fields available. To select a field, double click on it. You may filter any field, including computed columns, in your primary file. If you don’t see a field you need, it can be added.

If your primary file has links to other files, you can access these links in the left half of the window by clicking on the + symbol to the left of the file name:

Upon doing this, you will see the names of any links from your primary file. The name of a link is generally the name of the file it links to. If there is more than one link from a particular file to another particular file, a distinction will be made in parentheses. For example, “ADDRESS (Pref Mail)” and “ADDRESS (Pref Res)” are two links from PERSON to ADDRESS. Click on the name of the link to see all the fields available to filter on in the linked file. Note that you cannot filter on the computed columns of linked files using the Simple Condition. See the Select / Returning Condition section for a solution on this. Using links, you can go from one file to another and so on as far as you need to, but note that there are possible complications. For instance, if you have a chain of two or more links, files beyond the first two may not be linked via computed columns.

Value

The value selection is where you choose what you are comparing the property to. There are three types of values you can compare with:

• Literal – This is simply a hard-coded value that will not change. For instance, if you want to find all PERSON records with a SOURCE of “STU,” you would (after choosing SOURCE as the property) choose the literal option and type in STU in the input box next to it. Do not use quotation marks. If your property is a date and you want to compare it to the current date, you may type TODAY which will be replaced by the date at the time of running. You can also add or subtract from TODAY, for instance, TODAY+7 or TODAY-31. If the field may be more than one value, for example, a SOURCE of “STU” or “STA”, you can type in multiple values separated by commas (no quotes). For instance,
  STU, STA
  You may also filter on a user field. See [appdev:Informer User Fields] for more on this.
• Prompt – When you compare a property to a prompt value, the user will be asked for a value at runtime. This provided value will be used to filter on the field. The text you provide in the input box to the right of Prompt will be the text used to prompt the user for the value. So if you are asking for a term, an appropriate prompt would be Enter term: Using multiple prompts in the same report with the same prompt text will cause the user to only be prompted for the input once. This one input will then be used multiple times. For instance, if you have several prompts with the text Enter date:, the user will be asked Enter date: one time. The date they enter will be used in each condition that has the prompt text Enter date: The same applies to prompts in TCL blocks, discussed more in the TCL Block section. You can choose whether to make each prompt required or not. If a prompt is not required and the user leaves in blank, note that the condition will be left out of the filtering, rather than searching for records with the property blank.
• Property – This option allows you to compare the first selected property with a second property. Selection of the second property works exactly the same as the selection of the first.

Condition

The condition selection allows you to choose how your two values are compared. For example, equal to, greater than, not equal to, starts with, contains, is empty, is not empty. The condition options which appear in the pop-up menu depend on what type of field the property is (text, date, number). The options are fairly self-explanatory.

Limiter

The limiter option is rarely used and is only available for multi-valued fields. It will be grayed out for single valued fields. When available, there are four options:

1. Any – The default option. A record will be selected as long as one sub-value of the multi-valued field matches. For example, if a PERSON record has a SOURCE of 
   STU
   EMA
   and you are searching for a SOURCE of “STU” using “Any”, this particular record will be selected.
2. Every – Every sub-value of the multi-valued field must match. In the example above, every sub-value of SOURCE must be “STU”, and the example record would fail this.
3. No – No sub-value of the multi-valued field can match. In the example above, no sub-value of SOURCE may be “STU”, and the example record would fail.
4. When – This option is only useful in groups of associated values. When used, the conditions must match for a corresponding group of sub-values. The example below searches for applications in which the status became CA on 1/19/08. These conditions for APPL.STATUS and APPL.STATUS.DATE must match corresponding sub-values. If “Any” was used rather than “When”, this query would find any applications were any status change occurred on 1/19/08 and one status change was CA. Using “When” links the association together.

Select / Returning Blocks

Select / returning blocks allow you to perform a select on another file, as if it were a primary file. The select will return a list of ID’s to the primary file which will then be used in the remainder of the query. The immediate files you can select in a select / returning block must have a link to your primary file, as the foreign key in the selected file will be used to select the IDs of the primary file. However, it is possible to nest select / returning blocks inside each other, so a direct link is not necessary.

To use a select / returning block, drag it from the menu on the left and place it where you’d like it to go on the right. There are two pop-up menus to select from.

In the first menu, you choose which file you want to select from. Only files with a direct link to the primary file will be listed. In the second menu, you select which link you want to use. This will dictate which field will be returned from the selected file and then used to select on the primary file. When going from one file to another, there is usually only one link, so there is only one choice in the second menu. However, PERSON to ADDRESS has several links. In cases like this, you will need to choose which link you want to use.
Once you have chosen the file to select from and its link to the primary file, you can drag any type of condition into the select / returning block, including simple conditions which can filter on computed columns in the selected file. You can also nest another select / returning block inside the first select / returning block. This will allow you to select on a file a return a list of foreign keys linking back to your first select / return file, which will return the list of foreign keys to link back to the primary file. In the example above, you could nest a select / returning block inside the first one to select on any file that has a link to PERSON. You can nest an indefinite number of select / returning blocks within each other.

If you are familiar with Uniquery, it may be helpful to describe select / returning criteria in the following way: In essence, they allow you to perform a SELECT <file> <criteria> SAVING <foreign key> and use the resulting saved-list in the remainder of your Informer query, possibly very similar to a SAVE.LIST / GET.LIST type of procedure, although more functional in that the saved-list can be excluded from the rest of the selection. Select / returning blocks are not often needed, but can be essential in certain situations. For more information on when and how to use this feature, see Using Select Returning Criteria.

TCL/ECL Block

TCL/ECL blocks are typically only accessible by IT. However, they are rarely needed.

GET.LIST

A GET.LIST block simply returns the IDs from a specified saved list. GET.LIST blocks can be nested within compound conditions or select / returning blocks. The saved list specified for a GET.LIST block is hard coded. If you would like the user of a report to be able to enter the name of the saved list used, this can be done with a TCL/ECL block. Ask IT for assistance if you do not have access.

Key List

A key list block is similar to a GET.LIST block, except you specify the IDs (separated by comas) rather than a saved list.

Columns

The columns section of a report is where you specify what data will be returned. Columns have less restrictions than filters, and are generally easier to use, but there are still some things to watch out for.
To add columns, click on the bold text next to “Columns” on the report overview screen (initially “none”). When you are done, remember to click Save and Close or you may lose you work.

Fields, Calculations, and Aggregates

There are three things you can add to a report: fields, calculations, and aggregates.

Adding Field

To add a field, click the Add Fields button. A window will come up very similar to the window on the filter screen for adding a property to a simple condition. The window works exactly the same way. You can add any field to your report that is in the primary file or in a file that the primary file can get to in any number of links. This includes any computed columns. In the Column Display Editor toward the bottom of the screen, you can control several options for a column, including the header name, alignment, style, and whether it is hidden or not. A few things to keep in mind on fields:

• The only known limitation on fields that can be added: computed columns in a file linked from the primary file via two or more multi-valued foreign keys will not work (this may be fixed in the future).
• The sub-values of multi-valued fields or fields linked to the primary file via a multi-valued foreign key will be listed on separate lines within one row, while the single valued fields are listed only one time.

You can change results like those above to results like those below by normalizing. See the normalizing section for more.

If your report has a field in a file linked via two or more multi-valued foreign keys, or it has a multi-valued field linked via one multi-valued foreign key, you may up with a multi-valued multi-valued (or a two-dimensional) field. These fields are separated by commas, as can be seen below:

If you don’t like this format and would prefer a layout like the example below, see [Dealing with Multivalued Multivalues (Arrays of Arrays)].

If you filter on a multi-valued field and also display that field, note that by default, the field will only be filtered for the ID selection and the field will be displayed unfiltered. For example, if you filter PERSON.MULTIPLE.SOURCES on “STU”, the report will find only PERSON records that have a sub-value in PERSON.MULTIPLE.SOURCES of “STU”, but if the field is displayed, it will show any and all sub-values. If you want a multi-valued field filtered in the display, you will need to use the “Apply selection criteria to normalized values” on the Normalize screen. See this section for more.

Calculations

Calculations allow you to create a column which is calculated by a script that you write. Calculations can perform functions such as concatenating other fields together, parsing a delimited field, or spacing out two-dimensional fields. This is an advanced function and rarely needed. If you think it may be helpful, ask IT for assistance.

Aggregates

Aggregates are not useful on a Unidata database.

Sorting

To set up a default sorting for a report, click the bold text next to Sorts on the Report Overview screen. The sort screen allows you to add any number of sorts, ascending or descending. The fields available to sort on are any columns in your report, including calculations. If you want to sort on a field but do not want it shown in the report, you can hide the column and you will still have the option to sort on it.

Grouping

To set up a grouping for a report, click the bold text next to Groups on the Report Overview screen. You may group, as with sorting, on any column in your report. Results of the report are grouped as specified and condensed into one row per group. The row can be expanding by clicking the + button. The example below is grouped by state.

Normalization

To set up normalization for a report, click the bold text next to Normalize on the Report Overview screen. Normalization can be performed on any fields which are multi-valued or are linked to the primary file via a multi-valued foreign key. Calculations, whether multi-valued or not, show up for selection but do not appear to work. As mentioned in the Columns section, multi-valued fields are normally printed within one row, each sub-value on a separate line with the single valued fields being displayed once. When a field is normalized, each of its sub-values will be split into a separate row. The single valued fields will be repeated in each of these rows (see Columns section for pictures of an example), If normalization is done on one field, but there is another multi-valued field which is not normalized, all sub-values of the non-normalized field will be printed on each row. If there is more than one field is normalized in a report, the first sub-value of each field will be printed on the first row, the second sub-value on the second, and so on. Nulls will be printed if one field runs out of sub-values before another. When you have multi-valued fields which are associated (including a set of fields in a file linked via a multi-valued field), Informer will display each associated set on the Normalization screen and let you choose all of them. You may also choose “Define Custom Association Set” and select whichever fields you want. It is usually most desirable to select all of the multi-valued fields of a report when normalizing.
As mentioned in the Columns section, filtering on a multi-valued value will only filter the records that are selected. Multi-valued fields will be displayed with all sub-values, unless they are normalized and you check the box “Apply selection criteria to normalized values.”

Sharing

To share a report, allowing others to run it themselves, click the bold text next to Sharing on the Report Overview screen. A report will initially be set to Private, allowing only you and those with full access to see it. You should never set a report to Public. This will allow all Informer users to run it. To share, select “Available only to groups and users listed below:.” In the text box, type the name of each group or user you want to share it with and hit enter or click it in the popup menu. You should avoid using usernames if possible, since this will increase need for maintenance when users are added or removed. If you don’t know which group to use, see the Informer Group Members report, which shows all users and which group or groups they belong to.