Overview

The  Wizard  panel enables a user to create queries to generate reports without any knowledge of the Structured Query Language (SQL). An SQL query is ultimately produced, but this is transparent to the User. When the Wizard is saved, the SQL query is also saved using the same name. If the Wizard is updated and resaved, the query is also resaved.

Note: If a Query that was previously saved as a Wizard is edited and saved from the Query panel, it will be out of synch with the Wizard. The Wizard produces queries, but the  Query  panel does not produce or update Wizards.
The  Wizard  panel is constructed to guide the User through a few basic steps that will create a report:
 •   Select the table or tables that contain the desired data.
 •   Specify how multiple tables are "joined" together if more than one table is selected.
 •   Select the data columns from the tables that are to be produced in the report.
 •   Specify the selection criteria to filter the data to generate the desired rows.

Once a table has been selected, the Wizard can be executed by clicking the  Run Wizard  button or the  Report  tab in the header. If no columns are selected, all columns from the selected tables will be displayed.

Clicking the  Show Query  button at any time will display the SQL that the Wizard is generating. It is possible for the User to learn SQL by selecting various wizard options and then viewing the SQL that is generated.
Database & Table Selections

The Database & Table Selections section allows the User to choose the tables containing the desired data to produce a report and, if more than one table is selected, "join" the tables together.
Database/schema selection:

The first step in creating a wizard is to choose the tables that contain the desired data. In order to find these tables, it may be necessary to look in different databases or schemas. The database or schema can be changed by selecting a different entry from the available select lists. Once a new entry is chosen, QLR Manager will automatically update the list of available tables.
Table selection:

Tables are chosen by selecting a table name from the table list. After a table is selected, it will appear below the table selection control, prefaced with a letter reference. The Report Column Specifications section of the Wizard will also be displayed.
Using the database/schema name when referencing a table:

After a table has been selected, a checkbox will appear that allows the User to determine whether the table name will be prefixed with the current database/schema association. If checked, the table will be prefaced with the database/schema name such as nick.billings, where nick is the database name and billings is the table name. When the wizard (or the query it produces) is prefaced with this information, it will only examine the data in the specific database/schema.

When the checkbox is unchecked for a table, a dynamic reference is the result. If the billings table is unchecked in the above example and the User is connected to the nick database, the billings table in the nick database will be examined. If the database or schema being used is changed, for example to kadir, then the wizard/query will execute against Kadir's version of the billings table.

A mixture of checked and unchecked tables can be defined. When the checkbox is checked, the prefixing database/schema will be visible. When the checkbox is unchecked, the prefix will disappear.
Joining tables together:

If more than one table is selected, the User must specify how the data in one table matches up (is joined) to the data in the other table(s). This is accomplished by selecting the columns in the tables that map to one another. For example, one table may contain purchase orders and another table may contain the line items for each purchase order. If both tables contain a data column with the purchase order number, these two columns, one from each table, are the basis for joining them together.

QLR Manager displays the column selections for each combination of tables to allow the User to specify the join columns. The column names must be selected from each of these lists to specify the join. If more than one column set is necessary to join the tables together, such as purchase orders that also have version numbers, the  Add Pair  button can be used to add an additional pair of column select lists.

When presenting table columns to be joined, colors are applied to indexed fields in the column select list. When multiple columns are part of the same index, they will be given the same color. However, there is no relationship between the colors used in table "a" to the colors used in table "b". This color coding highlights up to 10 levels of indexed field combinations in the table. Whenever possible, it is recommended that tables be joined with indexed fields which will result in faster executing queries.

The following is an example of the colors used and their association to the level of index:
 
There are three ways in which tables can be joined together:
 1   Data rows in the left table match rows in the right table.
 2  All rows in the left table and matches from the right table.
 3  All rows in the right table and matches from the left table.

The second and third join types allow the User to see data where there may not be complete matches between the data in the tables. An example may be viewing all customers by joining the customer table to the purchase order table, regardless of whether they all have purchase orders. These are called left or right outer joins. The sequence of choosing tables for multiple table reports is important when outer joins are used. Once a left or right outer join is specified, all of the following table joins are treated as a left or right outer joins, depending on the type of first outer join that was found.
Report Column Specifications

The Report Column Specifications section of the Wizard allows the User to select the columns that will appear in the report output. Sort order, grouping and selection criteria can also specified in this section.
Selecting columns:

The columns to be used in a report are selected from the Columns select list. If more than one table has been specified for use in the Wizard, the Tables selection control is used to set the columns available for use in the Columns select list. To add a column to the report, select the desired column, select "Add column" from the Action for Col # control, and click the  Apply  button. The Column name will be added to the bottom of this section and will become the first column to be displayed in the report.
Column actions:

There are several actions that can be performed to manipulate column data in the Wizard. All the following actions require the  Apply  button to be clicked after the desired selections:
Action for
Col #		 What it does
Add column Adds the column selected from the Columns list to the end of the set of Column names.
Insert before Inserts the column selected from the Columns list before the column number specified by the Col # selection.
Replace Replaces a column in the set of Column names. Select the name of the column to be replaced from the Columns select list, choose the "Replace" option along with the Col # of the column to be replaced.
Delete (Col #) Deletes a column from the set of Column names as specified by the Col # selection.
To Move a column or element, left click on either the column number or name that you desire to move. Hold down the left mouse button and slide the entry up or down in the list. An entry can also be removed from the active list by dragging the entry back into the column selection drop down list.

An  Undo  button is provided which will undo all the column actions in the reverse order they were applied.
Column number:

The Col # indicates the position of the column within the report.
Column name:

The Column Name is the name of the column, prefaced by the table reference letter, eg. a.purchase_order. A special column is also added to the bottom of the set of Columns selections for each table called "Custom column". A custom column can contain formulas, such as "a.quantity * a.price" (without the quotes). Only one instance of a column name is permitted in a wizard. If an existing column name is desired in the report output more than once, the column can be added using the "Custom column" selection and entering the column name into the input field.
Hiding a column:

The Hide checkbox will suppress a column from appearing in the report. This is useful when a column is required as part of the data selection criteria, but not intended to be displayed in the report output. It is also possible to hide a column using the  Layout  panel.
Sorting report data:

The Sorting order provides a means of sorting the report data. Up to five levels of sorting, in either ascending or descending order, can be specified. Another quick way of sorting the report data is to check the Sort based on display order checkbox. This will sort the report output based on the first five columns of data in ascending order.
Grouping report data:

The Grouping function provides a means of compressing the report output. The data in a column can be grouped together with the "Group" selection or a grouping action can be applied to a column, such as a "Sum" or "count" of values. After a grouping selection is applied to a column, all remaining columns without a Grouping function specified will be omitted from the report. To include other desired columns, a Grouping function must be selected for those columns as well.
Selecting report data:

The selection of the desired rows of data from the database tables is accomplished by choosing a comparison operator and the data values to filter the data based on the comparison operator. The Wizard provides two sets of filters. The data being selected must meet all of the filter criteria in the first set or all of the criteria in the second set.
There are numerous comparison operators that can be used. Most are self evident, but some require further explanation to fully utilize their power:
Operator What it does
Equals
Not equals		 Equals and not equals can either have a single entry, or multiple entries, separated by commas in the filter set. When multiple entries are provided, the value in the database table column need only match one of the filter set values to be included in the report output. To find NULL entries, the word NULL (all uppercase) can be part of the filter set. To find the text string NULL in a case sensitive database environment, enter !NULL! as the filter text. In a case insensitive environment, simply enter a lower case null. Two single quotes ('') can be used to find empty values. An entry of NULL,'' will provide the same results as choosing the Is blank filtering option.
Less than
Less or =
Greater than
Greater or =		 These equate to the logical operator of <, <=, > or >=. Only a single filter set value can be supplied when these operators are used.
Between
Not between		 These operators allow for a range of data to be selected. A minimum and maximum value must be provided for the filter set, separated by the keyword "and". For example, "40 and 60" could be entered as the filter set to select all rows of data between these values. Conversely, "Not between" would select all values outside the minimum and maximum values.
Like
Not like		 Like and Not like provide a method to partially match the rows in the database table. They utilize the wildcard symbols of "%" and "_". The % will select data rows with partial matches, regardless of the number of preceding or succeeding characters that do not match. For example, "Like fred%" would select data rows with fredi, freddy, or frederica. The "_" character is a single character wildcard. "Like fr_d" would select fred or frod, but not freddy or fried. Conversely, "Not like" can be used to exclude data rows that partially match the value entered for the filter set.
Null
Not null		 Null selects rows from the database table where no data exists in the column being examined. Specifying Null will select all rows where no data is present. Not null will select all rows where data is present, regardless of its value.
Is blank
Is not blank		 Is blank will select all items where the value is either NULL or empty (''). Is not blank will select just the opposite of Is blank.
SQL For users familiar with SQL, this operator provides a method to write an SQL phrase to be inserted into the query that the Wizard produces. For example, selection criteria of "a.quantity * b.price > 500" could be entered into filter set.

Note: Character or date values entered into the filter set fields do not need to be surrounded in quotes. QLR Manager determines when data field types need quotes. For example, to select data where the first name is equal to fred or mary, it is entered as: fred,mary. There is no need to surround the values in quotes, such as 'fred','mary'.
Variables and input controls:

Variables and input controls provide a means of prompting for filter set input when a wizard is executed. The simplest method of prompting for variable input is to enter something like [Year], or if the server database is Microsoft SQL Server {Year}, into the filter set field. QLR Manager will detect the presence of the bracketed text and create a text input to enter the year when the Wizard is executed by clicking either the  Run Wizard  button or the  Report  tab in the header. For more information about using these variables, please see Query variables.

With the Enterprise Edition, input controls can be created that function like query variables, but instead of being limited to a text input, they can be created to use any of the HTML form elements such as select lists, radio buttons, check boxes, etc. Above the set of column names is an Edit Input Controls link that will launch a new window with the available selections required to create an input control, or retrieve and preview an existing control. The input control can be added to a filter set for a specific column in the same way a query variable is added, eg. [name of control], or {name of control} for Microsoft SQL Server. If input controls have already been defined for the logged on user, or other users have shared their input controls, they can be quickly accessed for use in the wizard by pressing the ctrl key. In supported browsers, a control selection list like the following will become visible:

access input controls

Clicking  Add Control  with the cursor in a filter set field will insert the selected control. For more information about creating and using these controls, please see Input controls.

If the User wishes to use an input control that was created under a different User ID, the input control name can be prefixed with the owning User ID followed by a period. For example, [joe01.account month] will find the input control named "account month", that belongs to User ID "joe01".

Note: Multiple query variables or input controls can be used in the same Wizard. QLR Manager will look for all instances of [bracketed] or **variable names when the Wizard is executed and present all the specified input controls to collect the User input required to execute the Query. Query variables used with Microsoft SQL Server must always be enclosed in curly brackets, eg. {variable name}.
Displaying all table columns:

Checking the Display all columns in the selected tables checkbox will generate a report with all of the columns in the selected tables, regardless of the Column names that have been individually selected and appear in the set of selected columns. This is a quick way of examining all the data available in the selected tables before proceeding to select specific columns to be included in the report output.
Ignore duplicate data rows:

This option provides a method to build a report that contains all unique entries. When checked, the data combinations of all the columns used in the report will have unique entries. For example, if the Column names of first_name and last_name have been added to the Wizard and there are two persons with the name "John Smith", only one row will appear with "John Smith" in the report output. If there is a "Kadir Smith" and a "Kadir Horton" in the selected database tables, both rows will appear in the report. Even though the first names are the same, the combination of the first and last name make each of them unique.
Sort based on display order:

When checked, the report output will be sorted in ascending order based on the data in the first five columns in the set of Columns names. This is a quick way to view the report data in a sorted order without having to individually specify the Sorting order in the set of column names.
Limit the number of report rows:

The value entered will specify the number of output rows that will be generated for the report output.
Using an existing layout

Run with Layout

The "Run with Layout" option allows the User to apply a layout to the data being selected in a wizard, formatting the report output prior to it being displayed. The list of available layouts will be based on layouts saved under the current connection ID, or others that have been shared by other users.

In addition to layouts created by users, there are two options that are listed under a special Owner named *QLR Manager*.
 •   The first is called "Based On Query", which is the default selection under most circumstances. When selected, no layout is applied and the report format is based upon the attributes of the data selected in the wizard.
 •  The other option is called "Current layout". This uses the existing layout attributes as found in the  Layout  panel. If data in the  Layout  panel does not yet exist, this choice will not be displayed.

Note: If a stored wizard is retrieved and a layout exists with the same Owner and Name, it will become the default layout used to format the report. This provides an easy means to employ a wizard/layout naming convention that will automatically find the layout associated with a wizard.

If the column counts between the query and layout do not agree, the report will be displayed with the default formatting and the report title will inform the User that the column counts do not match.