The foundation of a custom report, created in IQ, is a criteria-based List. A List Record exists for every List and contains the following six panels: Review Query, Edit Query, Screen Builder, Report Builder, Status Log, and SQL Reference.
The List Record page opens in the Review Query panel for both new and existing lists. The Edit Query panel is where you define the criteria for your query. The Screen Builder and Report Builder panels are used to create custom reports. The Status Log panel displays a history of actions for the list record. The SQL Reference panel contains the actual SQL that is generated to retrieve the data.
The Review Query panel of the List record contains information about the list. On the left-hand side, you will find the Usage designated for the list, along with other information including the Name, Owner, and Description. The right-hand side of the panel displays the query that has been created.
Identifies how the list created from the query will be used. The Usage may be changed at any time. For example, a query initially may be for a List as you create it and verify the results; and later be expanded into a custom report. Selecting the usage alters the options available as you work with the List.
When you use a List to create a new report, the Usage field must be set to one of the following:
Detail Report SQL &endash; is selected when your query will be used in conjunction with a report format to present data.
CrossTab Report SQL &endash; is used when your query will be used in a cross tabulation of two or more variables in a report that analyzes data.
The Edit Query panel of the List record is where the criteria of your query are defined. The left frame initially contains information about how to edit your query. The right frame is where your criteria statements appear.
The query statement shows the requirements that must be met in order for a record to be included in the resulting list. The heading in the top of the right pane will reflect the module and Purpose selected when the List Record was created. The statement that appears below it consists of one or more lines that include information about which fields and values will be evaluated.
When the value for a field in a query line is left blank, it is represented by the word Nothing, and allows for that line to be used as an empty criteria field in the Report Criteria Screen.
When you click on the Screen Builder tab, if the Usage for the List Record has not been set as Custom Quick Search or as a Report Criteria Screen, the Screen Builder panel will display the following message and options:
To create a Report, select an option that will create a Report Criteria Screen and SQL Builder.
The Screen Builder panel allows you to define the user experience when a query is set to be used as a Custom Quick Search or Report Criteria screen. Specifically, each line of the query can be set to prompt the user for values, or to use the values provided in the query without prompting.
Click the Edit button next to a query line in the Find People&ldots; section, and then select how it will be presented in the Report Criteria Screen.
Determines whether the query line will be included in the criteria screen. The following options are available for the Usage field:
Do Not Prompt for this value &endash; is selected when you do not want the query line to be included as a field in the criteria screen for the report.
Prompt for this value &endash; is selected when you want the query line to be included as a field in the criteria screen for user input. If selected, the page will refresh to display additional settings for the criteria field.
Use another field’s value here &endash; with this usage selected, the query line will not appear as a field and value in the criteria screen for user input. Instead, the value that a user enters in one of the prompted criteria fields will be reused as the value for this query line. To indicate which field’s value to reuse, click in the Reuse Field field and select one.
When you have finished editing the selected Screen Builder Line, click one of the following buttons, which are located at the bottom of the section:
Save &endash; to save changes to the selected line and then choose another line for editing
Next &endash; to save changes to the selected line and automatically switch to editing the next Screen Builder Line
Previous &endash; to save changes to the selected line and automatically switch to editing the previous Screen Builder Line
Enter a prompt or label for the field to appear in the criteria screen.
This determines the depth for the field when displayed in the criteria screen. Select Single Valued if the field should have only one line available for the user to enter one value. For a multi-valued field, select 2 Rows, 3 Rows, 4 Rows, 5 Rows, or 6 Rows to have the field include a specific number of rows, with a scroll bar for entering additional values.
Enter text to explain what should be entered or selected for the criteria field; the text will appear when a user places their mouse pointer in the box provided for the field’s value.
Allow &endash; The field is initially empty in the criteria screen and can remain empty; the field will be included (using a null value) in the Report SQL.
Use default and allow if empty &endash; the value you entered in the query line is used as the default; if the user removes the value from the field in the criteria screen, the field will be included (using a null value) in the Report SQL.
Use default and remove if empty &endash; the value you entered in the query line is used as the default in the criteria screen and can be changed by the user; if there is no value in the field when the report runs, the field will be omitted from the Report SQL.
Use default and require entry &endash; the value you entered in the query line is used as the default in the criteria screen; the user can replace the value, but cannot leave the field blank.
Remove query line &endash; the field is initially blank in the criteria screen and the user is not required to enter a value; however, if the field is left blank, the query line will be removed from the Report SQL.
Require field &endash; the field is initially blank in the criteria screen, and the user must enter a value for the field; the report will not run if the field is left blank.
The Report Builder panel allows you to determine what columns of data are to be gathered together, and how that data should be sorted in the report. This applies to queries in which the Usage field on the Review Query panel is set to Detail Report SQL or CrossTab Report SQL.
Click the Edit button next to a column line in the Detail Report&ldots; section on the right side of the panel, and then select how that data should be sorted when passed to the report.
You can set up which columns are displayed and how the data will be sorted depending on the purpose of your query, the criteria you select, and specific report you are building.
Select a field that represents a column from a table, or combination of columns. For example, People ID retrieves the data in the People_ID column for each People record in the query results; whereas Sort Name retrieves data from the ulast, ufirst, and middle columns for each People record in the query results and passes them to the report as one generic column of data (i.e. LAST, FIRST MIDDLE). Use the filter in the Possible Values column to narrow down the list of choices by typing the first character, or characters, of the field you want to select.
Choose how you want the column of data to be passed to the report.
As Data &endash; select this if you want the column of data to remain as retrieved (unsorted).
Sort (A-Z) &endash; select this if you want the column of data to be sorted in ascending order by the SQL.
Sort (Z-A) &endash; select this if you want the column of data to be sorted in descending order by the SQL.
The Status Log panel displays the created date and time, and the last modified date and time, along with a history of all the actions taken on the query. Previous executions of the query are stored and can be restored as the current execution.
The SQL Reference panel contains the query and the actual SQL that will be run to create the List. This information is provided as a convenience in debugging any problems, and shows how the list data will be retrieved for use in the Report.