Input Controls are designed to replace the simple text input controls that are created when query variables are found in query text or wizard filter sets. The available input controls are the HTML form elements such as select lists, radio buttons, check boxes, etc. Instead of a simple text input being displayed on the query variable input panel, a predefined input control can be used. Selection lists can also be created so that the available values for selection are based on the selected value of another input control, such as displaying only the cities in a selected state. These are parent/child input controls.

For those Users with authority, input controls can be created by selecting "User ID Admin and Tools" from the  Connect  panel when logging on to QLR Manager. Input controls can also be created from the  Query ,  Wizard , and  Design Form  panels by clicking the Edit Input Controls link, which will launch a new window with the available selections required to create an input control, or retrieve and preview an existing control. An input control created by a User ID will be stored under the ownership of that ID like other saved objects in QLR Manager.

If none of the above are found, a text input will be created for user input.

This approach allows the Administrator to create a common set of input controls that can be referenced as query variables by all their users.

When building an input control, the  Preview  button can be clicked to display what the control will look like and list its available values. This is very useful when a query is used to create the value list.

Input Controls can be used in conjunction with Report Objects for building custom web pages to collect user input.

If an input control with the name "year" exists, it will automatically be displayed on the query variable input panel that is presented when the query or wizard is run.

The @4 would limit user input to 4 characters, but would still map to an input control named "year", if it exists.

Here is an example of an input control designed to allow the User to choose a year. It has been defined as a horizontal radio button set:

When designing the control, the entered prompt text will be displayed to the left of the input control. HTML tagging can be used if desired. If this field is left blank, the input control name will be omitted. In the above example, <B>Choose a year:</B> was entered as the prompt text.

Default values (optional) can be set by entering them in this field. In the example above, 2007 was entered as the default value. If more than one default value is to be set, such as for check box sets or multiple select lists, the values are separated with commas. Do not enclose the values in quotes. If different values are used for display versus what is actually used (more on this under List of values), the default values should match the values actually used.

When the value of [all] is entered for multiple select lists or checkboxes, all of the values in the list will default to "selected".

Calendar dates:

Setting the default date for popup calendars is very flexible. The PHP function strtotime() is used for determining the value. When left blank, the current date is the default. It is also possible to enter the following: "2002-05-25", "January 1, 2004", "-1 day", "-2 years", "+1 month", or even "-1 week -2 days". Please see the PHP documentation for more information about the strtotime() function.

When creating an input control that uses the Popup Calendar, the date separator can be selected from the Calendar date separator list.

QLR Manager supports the creation of several different control types by choosing from the following selection list:

Popup calendar:

A popup calendar is provided for use with input controls that require date selections. When creating the input control for date input, the date format and delimiter are selected by the Author of the control. The following is a functioning demo. Click the button to launch the calendar.

A date format can be chosen by selecting the appropriate "Calendar popup" value from the Control type list. Regardless of the displayed format, QLR Manager will convert the date entry to the SQL standard 'yyyy-mm-dd' format when the query is executed. The 'yyyy-mm-dd' format can be overridden in a particular QLR installation by adding an entry into the qlr_info table with the control date keyword. This entry utilizes the PHP date() formatting function parameters. For example, for an Oracle installation a dd-Mon-yy format may be desired, such as '10-May-08'. This is accomplised by inserting the following entry into the qlr_info table:

insert in qlr_info values ('control date','d-M-y')

The conversion to the standard SQL format can be prevented by checking the Use calendar date as is option when creating the calendar input control. Since dates are treated as strings, the most common practice is to surround the query variable in quotes when constructing the query, such as where trans_date = '[start date]'.

Checkboxes:

Check boxes are preferred when selecting multiple values is desired. Check box entries can be arranged to appear vertically, horizontally or in a grid (square) pattern. When multiple entries are chosen, the values are returned as a single string, with the chosen entries separated by commas.

When a large number of check boxes are used, a control type of "Check boxes - grid" can be selected and an input width from 1 to 12 can be entered to specify the number of columns of check boxes (with labels). This can be useful to control the grid matrix to accommodate short or long labels.

If more than 5 check boxes are used as an Input Control, the following controls are presented above the check boxes to aid the User with selection from larger check box groups:

 [Select all] [Deselect all] [Reverse] 

By default, this control set appears if there are more than 5 entries. This number can be altered by entering a parameter into the selection limit field.

Radio buttons:

Radio buttons are used when only a single value can be selected. Radio buttons can be arranged to appear vertically, horizontally or in a grid (square) pattern. Below is an example of a grid arrangement.

When a large number of radio buttons are used, a control type of "Radio buttons - grid" can be selected and an input width from 1 to 12 can be entered to specify the number of columns of radio buttons (with labels). This can be useful to control the grid matrix to accommodate short or long labels.

Single select list:

A Single select list provides a drop down list of values, allowing only one entry to be chosen. This is the most common form of a select list. The selection of the control type when creating the input control is an example of a single select list.

Multiple select list:

A multiple select list provides a drop down list of values, allowing more than one entry to be chosen. Multiple entries can be chosen by holding down the Control (Ctrl) key and clicking on each item. The example below allows for multiple entries to be chosen, and the number of rows to be displayed in the select list has been set to 4. Notice how the text "[Ctrl+click to select multiple entries] [Shift+click selects a range of entries]" is added to the bottom of the multiple select control. Lists with 6 or more selections will automatically receive the  [Select all] [Deselect all] [Reverse] controls.

Text areas:

Text areas can be used for the entering of large amounts of text, perhaps used in an INSERT query. The size of the area is controlled by the "Text input width" for the width, and the "Text area height or select list rows" determines the height. The following is an example of "Text input width" = 40 and "Text area height or select list rows" = 4:

Text area pop-up editor:

Another text area control is the pop-up editor. When the icon to the right of the text area is clicked, a new window is opened allowing the User to edit the text in a large text area. This permits the control itself to be defined as a small area. The following is an example of Text input width value of 20 and Text area or select list height value of 2.

Text input - all characters:

The Text Input control is very similar to what is automatically created for a query variable. The advantage of creating an input control is that Prompt text and default values can be defined. The length of the input can be limited by setting the "Text input width" value. This is an example of a text input with a maximum length of 6:

Text input - numeric only:

This control is the same as the "Text input - all characters" except that it limits the User to entering numeric input. This can be tested in the example below:

Text input - no quotes:

select * from user where userid = '[userid]'

select * from user where userid = '' or '1'='1'

The beginning and ending quotes surrounding the password input variable have been effectively turned into part of an SQL statement, as opposed to surrounding a legitimate password value with quotes. The resulting query would display all rows in the user table, as opposed to only those where the User has entered their password. When quotes are blocked from being entering into an input control, this type of attack cannot be used.

Text input - Password:

This control allows for passwords to be entered, without the characters being displayed. As with the "Text input - no quotes" control (see above), single quotes are not allowed to be entered into this control type to prevent SQL injection attacks.

The order in which input controls appear in the query variable data entry page is based upon the size and type of input control. However, the Display order allows the User to override this automatic calculation for a specific input control. If the User wishes to move an input control to the top of the query variable page, this can be accomplished by entering 1 as the display order. A value between 1 and 999 can be used to sort controls to the top of the page. To move a control to the end of the query variable page, a Display order of greater than 1000, such as 1001 and 1002, can be entered. All controls that are not given a Display order value will remain in their original position as calculated based on their size and type. Since the input controls that appear on the query variable page depend upon which controls are used within a specific query or macro, the desired order for one page may not be what is desired for another page. In this case, a new input control can be created and saved with the same characteristics, but using a different Display order.

<div style="text-align:center;width:100%">Prompt text</div>
<div style="text-align:right;width:100%">Prompt text</div>

This data element controls the height for select lists and text areas. Generally, a single value select list is set to 1. Multiple select lists and text areas will generally have a value of greater than 1.

The text input width defines the width of both text input boxes and text areas. In addition, it sets the maximum number of characters that can be entered into a text input box.

In those instances where a large number of check boxes or radio buttons are used, a control type of "Check boxes - grid" or "Radio buttons - grid" can be selected with an input width to specify the number of columns of controls (with labels) to be presented. A value of 1 to 12 can be entered to override the default grid matrix to accommodate short or long labels.

Regular expressions used with QLR Manager input controls only apply to text inputs and text areas. The JavaScript regular expression syntax used in QLR Manager is very similar to Perl-style regular expressions. The syntax consists of /pattern/flags[,replacement]. All that is required is a /pattern/ to perform a match against text input. For example, if the User input must begin with a #, a simple pattern match like /^#/ can be used to validate that the # exists in the first position. When specifying a pattern match without the optional replacement value, a regular expression message must be provided to alert the User of the failed match so the input can be corrected. By using a [,replacement] value, the User input can be corrected automatically.

/^([^#])(.*)$/, "#$1$2"

Note: When using the replacement value, the regular expression message is optional.

There are flags that can be used with the pattern match. The most common are i and g. The i will instruct the pattern match to ignore case and the g will perform a global match and search the whole string rather than stopping on the first occurrence. These flags and others can be used in combination.

/[A-Z]/g, function ($1) { return $1.toLowerCase() }

Regular expressions can be very complex. This information is not intended to be a tutorial about the use of regular expressions, but merely a brief overview describing what is possible. Regular expressions can be a useful feature when used in conjunction with QLR Manager Input Controls to validate user input, or replace specific values in the input. An abundance of information can be found on the Internet about Regular Expressions by searching for "javascript regular expressions". A good library of regular expressions can be found at: http://tools.netshiftmedia.com/regexlibrary/feed.php.

The character or selection limit field accepts 3 optional values, separated by commas. The following is an explanation of each of these values and the function they perform.

The character limit applies to text inputs and text areas and can be entered as a single value, i.e. 100, 10, etc. When used with a text area, the character limit will be displayed above as Character limit: 100. The selection limit can be set for multi-select lists and checkbox groups. For these types of controls, Selection limit: 10 is displayed above the control. As characters are typed into the text area or selections clicked for a multi-select list or checkbox group, the value is decremented to display the remaining available characters or selections.

The minimum character count or selection value can also be set by entering a second parameter. An entry of 10,2 indicates that a maximum of 10 selections are permitted and a minimum of 2 selections are required.

A third parameter can be entered which determines when the selection controls are presented, which will override the default of 5:

 [Select all] [Deselect all] [Reverse] 

An entry of 10,2,1 indicates that these controls should be displayed when there is more than 1 entry.

This selection determines whether the returned value will be surrounded by single quotes, such that Spain will be returned as 'Spain'. It is necessary to use this feature for controls that can return multiple values that each need to be surrounded in quotes. An example of this is for use in a query that uses an IN clause:

select * from customer where last_name in ([lastName])

If the lastName control is a checkbox set that allows for multiple last names to be selected, each of these last names must be surrounded in quotes for the query to execute as expected, such that Smith,Jones is returned as 'Smith','Jones'.

There may be times when it is desired that a control, such as a radio button set or a select list, provides the capability to not select any items. The nature of these types of controls prevents that from happening. By adding an empty selection value to the list, the User can select the empty value. Here is an example of a radio button set, in a grid configuration, with an empty value added. Notice that the empty value is labeled as "None" for radio buttons. For a select list, it will be a blank entry at the top of the list.

When this is checked, the list of values available to the User is updated every time the query variable input page is displayed. This is most useful when lists are comprised of actively changing data, such as customer lists or invoices and the list source is the query text. When not checked, the list is updated the first time the User accesses that particular input control during their browser session.

if($value!='') $value = "start_date = '".$value."'"; else $value = '';

SELECT custnum, first_name, order_date
FROM orders
WHERE [getStartDate]

If a date value of 2007-11-19 is provided, the input control returns start_date='2007-11-19'. If no date is provided, the input control returns a blank. Notice that the value that is manipulated in PHP is $value. Also remember that if Surround selection with quotes is checked, the value will have single quotes around it.

In addition, the data in the child input control must be mapped to the insert value of the data in the parent input control. This is accomplished by specifying three data elements for each desired child entry: the display value, insert value, and the parent's insert value. When a List of values is used, the entries might look like the following if the months of the year are mapped to each of the four quarters:

For the month of April, "Apr" is the displayed value and if selected, the value of 4 (month number) would be inserted as the query variable value. The parent's insert value of 2 (quarter number) is used to associate months 4 through 6 with the parent list selection. Only the entries for Apr, May and Jun are displayed when the "2nd Quarter" is selected from the "Choose the quarter" list.

If a query is used as the source, the query must select 3 fields. The sequence being the display value, insert value, and the related parent's insert value:

In this example, the purchase order number is both the displayed and insert value. The customer number maps to the customer number found in the customer input control, which has been specified as the Parent control. If an input control with a parent control is used in a query, the parent control does not need to be specified in the query. QLR Manager will automatically add it to the query variable input panel immediately above its associated child control.

There may be instances where the User may need to search for multiple entries within the same field. An example of this, is the MySQL "SET" field type is used to store information. A SET field contains multiple values separated by commas. An example of this would be a field in a table, related to pizza orders, called "toppings". It might be that this field contains the possible toppings for a pizza order. Valid data elements for this field might be any combination of "onions, sausage, pepperoni, olives or mushrooms".

Suppose the User wishes to find all pizzas that have meat on them. You would need to find entries that contained either sausage or pepperoni. An input control can be defined to do this by specifying a Multiple entries, same field value of Contains and defining the Multiple entry field name as toppings.

select * from pizza_orders where [getToppings]

select * from pizza_orders where (toppings LIKE '%sausage%' OR toppings LIKE '%pepperoni%')

select * from pizza_orders where (toppings LIKE '%sausage%' AND toppings LIKE '%pepperoni%')

select * from pizza_orders where

if ($value!='') $value = 'where '.$value

select * from pizza_orders [getToppings]

This will then conditionally add the where clause to the query only when at least one topping is selected. It is most likely that the use of the Multiple entry option is used when the input control type is a checkbox set or a multiple selection list. In order for this option to work, values for both Multiple entries, same field and Multiple entry field name must be specified.

There are two ways to populate check box, radio button and select list input controls. The values can be provided from a static List of values or by executing a query to build a dynamic list of values. The "List source" indicates which source to utilize. Entries for both a List of values and a query can be present at the same time. It is the List source selection that determines which one will be used.

There are two ways of creating a list of values that will comprise the User's selection options. One is to enter the values into the "List of values" text area. The other is by authoring a query to select the values.

Values are entered into the List of values text area, separated by a comma. To create a selection list of valid years, the data can be entered as:

2004,2005,2006,2007

or as:

2004,
2005,
2006,
2007

It is also possible to enter a text/value pair for each entry, such that the text is displayed in the input control and the value is the actual value submitted for the selection. The displayed text and the value are separated with a double colon :: to make the delineation. For example, to build a list that shows the names of the month and uses a month number as the selected value, enter a List of values like the following:

January::1,
February::2,
March::3,
April::4

This can be used to produce a horizontal radio button set:

There are two ways of creating a list of values that will comprise the User's selection options. One is to enter the values into the List of values area and the other is by creating a query to select the values. To create a list of values using a query, a query must be authored that will select a list of values intended to be offered as selections. It is advisable to test the query by clicking the provided  Preview  button and to fully qualify table name(s) by preceding it with a database/schema reference where applicable.

Note: The following examples use standard brackets [ ] to identify query variables. If the server database is Microsoft SQL server, curly brackets { } must be used instead.

A query may look like:

select custNum from mydb.customer where region = 'southwest' order by 1

This produces a list of customer numbers that could be used to create a single select list control.

As is the case with the "List of values", text/value pairs can be defined. The first column selected is used as the displayed text and the second column selected is treated as the value to be submitted for the selection.

select concat(last_name,', ',first_name),custNum from mydb.customer
order by 1

This example creates a list that displays the last and first names concatenated together (MySQL concatenation syntax shown here), with the substituted value being the actual customer number.

It is also possible to use the special QLR Manager query variable called [qlr_userid] to build lists specific to the logged on User ID:

select invoiceNum from mydb.invoice where custNum = '[qlr_userid]' order by 1

The database/schema name can also be referenced by using [qlr_userdb]. This will substitute the value of the current database or schema connection:

select invoiceNum from [qlr_userdb].invoice where custNum = 198563 order by 1

It is important consider the implications of having select authority on the table(s) that are being referenced when building a dynamic list of values. When an input control is owned by the QLR master ID, the master ID connects to the database to execute the query. Therefore, the master ID has to have select authority on the applicable table(s).

If the input control is owned by an individual user, obviously the User needs select authority on the applicable table(s) in order to build the list of values. What may not be obvious is that when sharing a query for execution and it has an input control owned by the author of the query, the person executing the shared query also needs select access to the table(s) that are part of the input control. If an input control is to be widely used, it is probably easiest to create it under the QLR master ID and then grant the appropriate select authority to the master ID.

select partnum from inventory order by 1

select partnum from inventory where category='[category]' order by 1

select distinct category from inventory order by 1

When QLR Manager executes the query to display the query variable input panel, it will first prompt the User to select a category. Then a second query variable page will appear and ask the user to select a partnum that falls within that category. This is similar to the parent / child controls that are described above. However, there are several differences. The first is that parent / child controls that appear on the same input page work well with small number of children, perhaps a hundred or so. When there are thousands of entries, it is probably best to use the approach described here. Secondly, the parent / child controls that appear on the same page are limited to a single parent / child relationship. Using this method allows for an unlimited number of drill down tiers, so you can have child / parent / grandparent, etc. relationships to narrow down the data being presented to the User. Lastly, using this approach does not limit you to a "select list" input control type.