Overview

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 Create/Edit Input Controls image link: Create/Edit Input Controls 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.

When queries, macros, or forms belonging to a particular User ID are executed and query variable(s) are referenced, QLR Manager will attempt to locate the query variable in the following priority sequence:
1   The query variable that is prefixed by the owner such as [joe01.account month] will always be used first.
2   A query variable exists that was created and saved by the owner of the object being executed.
3   QLR Manager will then look for the referenced control under the Administrator's master ID (the ID that was established when QLR Manager was installed).

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.

Naming input controls
The name assigned to an input control when it is saved is important. It must be named exactly the same as the query variable that is used in the Query, Wizard, or Form. Input control names should not contain the leading ** that identify query variables (older method), nor the brackets [ ] or { } used with Microsoft SQL Server, that can be used to declare query variables in QLR Manager version 5. For example, a query could be written with the text:
select * from invoices where invoice_year = [year]

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 input control name does not have to contain the @length option that can be used in a query variable. The above query could be written as:
select * from invoices where invoice_year = [year@4]

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:

Variable Value
Choose a year:
       
 
Prompt text

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

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".

Evaluated formulas can also be used to calculate the default value. As an example, if it is desired to have the current year as the default, it can be calculated by entering the following PHP code enclosed in the eval() function:

eval(substr(date('Y-m-d',strtotime('today')),0,4))

Calendar dates:

Setting the default date for 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 Calendar, the date separator can be selected from the Calendar date separator list.

Control types

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

 

Calendar:

A calendar is provided for use with input controls that require date and time 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 of a calendar to select both date and time. Click the button to launch the calendar.

Variable Value
Begin date:
 

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]'.

Time fields:

There are two time field formats provided by QLR Manager. The first is "Time - hh:mm 22:59", which is for hours and minutes. The second is "Time - hh:mm:ss 22:59:59", which is for hours, minutes and seconds. When either of these control types are selected from the available control type list, QLR Manager populates the Regular Expression field with a check string to validate that the provided data adheres to the selected time format. It also populates the Error Message field with default message text. The validation takes place when the query variable page is submitted. There is also a condensed version of the calendar hh:mm:ss controls that can be used to select the desired time:

Variable Value
Start time:
 

Note: Depending on the database engine being used, it may be necessary to manipulate how data is handled when using these control types. For example, the MySQL Time field types do not support the storing of time increments smaller than a second. So if it is desired to store tenths, or even microseconds, the time data must be stored in a decimal field, such as decimal(7,2). In order to convert the data displayed in the Input Control to the decimal representation (1 hour, 2 minutes, and 7.43 seconds 1:02:07.43 is 3727.43 seconds), the Input Control's Evaluated formula option is used to convert the time format into the MySQL database decimal format:

$value = "time_to_sec('$value') + microsecond('$value')/1000000"; $isSQL=TRUE

The formula converts the Input Control time into the necessary MySQL query text. Notice how $isSQL=TRUE is added to the end of the formula. This tells QLR Manager to treat the text literally as SQL. If not added, backslashes will be added to the single quotes.

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.

Variable Value
Select first names:
 
 
 

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.

Variable Value
Choose a year:
   
 

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.

Variable Value
Select a control type:
 

Searchable single select list:

A searchable select list displays similarly to the single select list above, but allows the User to click on the list box and type characters to search the list options. The search begins after a momentary delay when the User finishes typing. List options that contain the search string will be displayed. This control is most useful when a select list is comprised of 100's options.

Note: This control can only have a select list height of 1. All other height settings are ignored.

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.

Variable Value
Select a control type:
 [Select all] [Deselect all] [Reverse] 
[Ctrl+click to select multiple entries]
[Shift+click selects a range of entries]
 
 

Move selections:

This input control allows the User to select values by moving the desired values from the left list into the right list. This control can be useful if the order of the selected values is important, such as selecting from a list of column names to be added to a query.

Variable Value
Cities:
Select items by moving to right
 
 
[Ctrl+click to select multiple entries]
[+ moves selection down and - up]
 

Select list/text input combination:

This input control combines the features of a single select list with a text box. Values can be chosen from the select list, or new values can be entered using the text input field. When a new value is entered into the text input field, it is used. If a value is chosen from the select list after a new value is entered, the value from the select list is used.

Variable Value
Choose a city:
 

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:

Variable Value
User comments:
 

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.

Variable Value
Your feedback:
Field editor
 

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:

Variable Value
Customer ID:
 

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:

Variable Value
Enter invoice amount:
 

Text input - no quotes:

The purpose of this input control is to prevent "SQL injection" attacks that are accomplished by "short circuiting" the intended use of quotes for character value input. Suppose the following query exists with an input control called password:

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

If a user was to enter ' or '1'='1, the query would be rendered as

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.

Variable Value
Enter your User ID:
 

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.

Variable Value
Enter your password:
 
Display order

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.

Control alignment
This selection provides the ability to specify the alignment of an input control. The default alignment is "Left", but there may be some instances where it may be desirable to align the Input Control to the "Center" or "Right". These selections only affect the Input Control, not its associated prompt text. Although there is no separate control to specify the alignment of the Prompt text, this is possible by entering HTML tagging into the Prompt text field. For example:

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

Text area or select list height

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.

Text input width

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 expression validation

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.

A regular expression like the following can be used to insert the # in the first position, if it doesn't already exist:

/^([^#])(.*)$/, "#$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.

JavaScript functions can also be entered as a [,replacement] value. The following regular expression will match all characters A-Z and use the JavaScript toLowerCase() method to change all the upper case characters to lower case:

/[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.

Regular expression message
The regular expression message is a JavaScript alert that will be triggered under two conditions when leaving the field containing a regular expression validation:
1   A simple regular expression match is used and the User input fails the validation. The regular expression message is required in this instance. The User will not be able to leave the input field without correcting the validation failure and must be informed what condition is causing the failure.
2   A [,replacement] value is used in the regular expression. In this case, the regular expression message is optional, but may be useful to alert the User to what has been replaced.
Character or selection limit

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.

Surround selection with quotes

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'.

Adding empty selections

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.

Variable Value
Choose a year:
   
   
 
Always refresh list values

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.

Evaluated formula
The Evaluated formula field allows PHP code to be entered that will manipulate the value selected by the input control. This provides greater flexibility when using input controls. An example might be the use of a field to enter a date value, but if no date is entered, then the query does not even contain selection criteria for a date:

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

The input control used in the query might look like this:

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.

Parent/Child select lists
Sometimes it may be desirable to have the choices available in one select list (child) based on the selected value of another select list (parent). An example could be when selecting a customer from the customer select list, only the purchase orders for that customer are displayed in the purchase order list. This is accomplished by selecting the parent control name from the Parent control list while creating the child control. For this example, the customer input control name would be selected from the Parent control list while creating the purchase order select list control. The general rules are:
 •  The parent control must be a single select list.
 •  The child control can be either a single select list or a multiple select list.
 •  A parent control cannot have its own parent control defined.
 •  Multiple child controls can reference the same parent control.
 •  The parent control does not have to be referenced in the query as a query variable. It will automatically be added due to the presence of the child control.

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:

Jan::1::1,Feb::2::1,Mar::3::1,Apr::4::2,May::5::2,Jun::6::2,
Jul::7::3,Aug::8::3,Sep::9::3,Oct::10::4,Nov::11::4,Dec::12::4

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.

Variable Value
Choose the quarter:
Choose the month(s):

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:

select purch_ord_num,purch_ord_num,cust_num from purchase_orders order by 3,1

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.

Option groups

If a parent control is not specified and 3 data values are present for each select list entry, the third value in the list will be used to create a select list "option group". Option groups appear in both single and multiple select lists as group headings, with the selectable values indented under each heading. QLR Manager automatically creates option groups when a third data element is present, and no parent control is specified. In these cases, it is best to organize the listed values by first sorting them based on the third data element.

The following entries entered into the List of values for a Select list - multiple select:
January::1::1st qtr,
February::2::1st qtr,
March::3::1st qtr,
April::4::2nd qtr,
May::5::2nd qtr,
June::6::2nd qtr,
July::7::3rd qtr,
August::8::3rd qtr,
September::9::3rd qtr,
October::10::4th qtr,
November::11::4th qtr,
December::12::4th qtr

Results in the following select list with a Text area or select list height value of 8:

Variable Value
Select month(s):
 [Select all] [Deselect all] [Reverse] 
[Ctrl+click to select multiple entries]
[Shift+click selects a range of entries]
 
Searching for multiple entries within the same field

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.

When the input control generates it's output, it will be (toppings LIKE '%sausage%' OR toppings LIKE '%pepperoni%'). Notice how the field reference is part of the generated value. Since this is the case, there is no need to specify the table column of toppings in the query text. Assuming that the Input control is named "getToppings", the query might look like this:

select * from pizza_orders where [getToppings]

When the query variable is replaced, the query text will be:

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

If the User wishes to find the "Meat Lovers" pizzas that contained both sausage and pepperoni, then the Contains all comparison option can be used. The resulting query would be

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

If there is a chance that no toppings would be selected, this would cause a problem because the resulting query text would be:

select * from pizza_orders where

The Input Control's Evaluated formula field can be utilized to solve this problem by entering a formula of:

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

and omitting the "where" keyword from the query text:

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.

List source

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.

List of values

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:

Variable Value
Choose a month:
       

Note: If the list of values for an input control contain commas, the commas should be entered as _cm_. For example:

Vaughan_cm_ Stevie Ray, Hendrix_cm_ Jimmy

will be displayed as:

Vaughan, Stevie Ray
Hendrix, Jimmy

Notice that multiple values are still separated by a comma.

Query for list of values

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.

Drill Down input controls
It is possible to reference input controls within the query text of an input control. Doing so allows for the creation of "drill down" controls. For example, suppose there is a control to select a given part number. The query text would be:

select partnum from inventory order by 1

Suppose this produces a list of thousands of part numbers. You may not find it to be a very usable control listing so many part numbers. Assuming that your inventory data has a "category" related to each part number, you could modify the query to:

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

You would then create an input control called "category" that had query text of

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.

Drill down controls can be multiple levels. The following is an example of how to prompt the User to find a specific zip (postal) code for a given city. The approach is to have the User first pick a state, then a city within a state, and then choose the desired zip code for that city. Following are some steps to accomplish this:

The query that the User executes contains the reference for these query variables:

select zip,street_name from zipcode where zip=[zip code] order by 1,2

When the query is executed, QLR Manager will find the [zip code] input control and see that [zip code] contains query text that references a list of states and cities:

select distinct zip from zipcode where state=[zip state] and city=[zip city] order by 1

The [zip city] query text contains a reference to the state. QLR Manager will then examine the [zip state] control.

select distinct city from zipcode where state=[zip state] order by 1

The [zip state] query text is as follows:

select distinct state from zipcode order by 1

It will find that the [zip state] control's query text does not contain any embedded references to an input control. The process of looking for more levels of controls ends here and QLR Manager will display the [zip state] control.

Note: Since [zip state] does not need to contain a reference to another input control, then the values used in this control can either be provided by using a query as the source, or a static list of values.

Display of the input controls will take place in reverse order from how the input controls were found. [zip state] will be shown first, [zip city] will be shown second, limited to cities found in the state chosen in [zip state]. The [zip code] input control will then be displayed with values limited to the selected [zip city] and [zip state].

The above example was limited to selecting a single value for each level. It is also possible to allow the User to select multiple values. This is controlled by how the underlying queries are written. In this example, multiple cities could be selected using a control type of multiple select lists or checkboxes.

Hint: Check the "Surround selection with quotes" option when defining an input control with the potential of selecting multiple alphanumeric values.

select zip,street_name from zipcode where state=[zip state] and city in ([zip city]) and zip in ([zip code]) order by 1,2

If you want to create a Widget and "preset" a value for a specific input control (such as in this example, setting the State to CA for California), the [zip state] input control must be included in the initial executing query, such as:

select zip,street_name from zipcode where zip=[zip code] and state='[zip state]' order by 1,2

This will allow QLR Manager to display the prompt for setting the value for the [zip state] query variable.

Slider input controls

Slider input controls use the jQRangeSlider plugin (jQuery) to produce sliders that provide for the selection of numeric values, percentages and dates. Sliders can be configured to select a single value or range of values. Using the interface provided in the panel to Create/Edit Input Controls accessed with the image link: Create/Edit Input Controls on the  Query ,  Wizard , and  Design Form  panels, it is possible to create, customize and save sliders for use as Query variables.

The following is an example of a Basic Numeric slider to select a range of values. The label floats and gradient range bar can be moved using the mouse, or by clicking the desired handle and using the keyboard ARROWS, TAB and BACK keys to make selections:

 

The selections and inputs used to create sliders are presented when "Slider control" is selected from the Control type list. As the slider is being constructed using the many form inputs described below, the changes are automatically applied so the resulting effect can be immediately seen. Following is a screen capture of the form controls used to create a slider:

Create slider

Slider type:

This list is the starting point for selecting the type of slider. The Basic and Custom date & time sliders are fully configurable for minimum and maximum values. The Current date & time sliders are limited to the time frame selected and are dynamic based on the date they are launched. For example, choosing the "Current year" slider will always be the current year based on the clock in the PC or device accessing the html page.

Slider length:

This value represents the horizontal width of the slider in pixels. For aesthetic purposes and consistent rendering across different browsers, choosing an appropriate slider length is particularly important when using Minor tick steps to allow the ticks to be uniformly spaced. The slider example above is 530 pixels wide. The arrows on each end are 15 pixels each, allowing 500 pixels for the body of the slider. Since the scale ranges 0 to 100, there are 49 pixels between each major tick mark plus 1 pixel for the tick mark. There are 10 minor tick marks (the tenth obscured by the major tick mark) with 4 pixels between each, plus 1 pixel for each minor tick. This provides equal spacing of the minor ticks for the length of the slider. The same uniform spacing can be achieved for the above example with slider lengths of 230, 330, 430, etc. If the minor tick marks are omitted, any length can be used and the difference in spacing between the major tick marks will likely be imperceptible.

Slider margins:

These are the margins surrounding the slider, with the top margin being the most important to keep the "floats" within the same container in the html page. The default values change based on the Slider height selected. Checking the Hide floats checkbox will also change the defaults by removing most of the top margin. The default values can be overridden by entering the desired margins before saving the slider.

Value selected:

This selection determines whether the slider will select an individual value with a single "float", or a range of values with two "floats". When constructing the slider, the floats can be moved and the selected values will be displayed adjacent to the Captured values label.

Slider height:

Three different slider heights are provided to suit User preference... 16px, 22px and 30px. This is the actual height of the slider body, without the floats.

Slider theme:

Two themes are provided. The default is black tick marks and labels over a "Light" grey gradient background. The "Dark" theme provides white tick marks and labels over a dark grey gradient background.

Minimum value/Maximum value:

The start and end points for the slider are entered as the minimum and maximum values. For Basic sliders (Numeric and Percent), the values can be entered as whole numbers or decimals, both positive and negative. The Custom date & time sliders are pre-populated with the acceptable input formats for date and time values, using the current date and time as an example. These values can be over-typed to create a slider with fixed start and end points.

Note: Sliders have practical limitations for the number of values that can be accommodated on the slider scale. For example, attempting to creating a slider that spans several years with Slider steps set to a time value of seconds, minutes or hours, will likely exceed the limits of a slider's capabilities. In a scenario like this, it would be more practical to use two separate sliders, one to select the day and another to select the time.

Current date & time sliders have their input values disabled and cannot be changed. These are dynamic sliders that use the clock in the PC or device accessing the html page to set the values for the selected range, i.e. year, month, week, day, or hour.

Initial min value/Initial max value:

These are the initial values for the slider range when first presented. When the Value selected is "Single", only the Initial max value is used for the single selection. If no values are present, the default Initial max value will be the same as the slider Maximum value, and for a range slider, the default Initial min value will be the same as the slider Minimum value.

Needle color:

When "Needle" is selected for the slider Range marker, this color is applied to the needles for a range slider, or the individual needle for a single select slider.

Canvas color:

The canvas refers to the area surrounding the slider and contained within the Slider margins. The canvas color can be customized to suit the desired appearance for use as a query variable. With the associated "None" checkbox checked, no canvas color is applied and the color surrounding the slider will be that of it's parent container.

Range color:

When "Gradient bar" is selected for the slider Range marker, this color is applied to the gradient bar between the floats for a range slider, or anchored at the left and draggable to the selected value for a single select slider.

Range marker:

There are three available options to indicate the position of the selected value(s) on the slider scale... "Gradient bar" and "Needle(s)" are visible over the slider scale, while "None" will omit any markers.

Range opacity:

When "Gradient bar" is selected for the slider Range marker, these transparency values can be applied. The values range from "5%" opacity, which is barely visible, to "Opaque", which obscures the slider scale beneath.

Maj tick labels:

These selections are only applicable to Slider types of Current date & time and Custom date & time. They provide some flexibility over the labeling and Maj tick steps applied to the slider scale. For example, a slider constructed to span a period of multiple years may look very busy with a major tick and label for each month. It may be more desirable to select "Years" and show a major tick and label for the beginning of each year.

For "Numeric" and "Percent" slider types, the only option that can be applied is "Numeric".

Slider steps:

The slider steps designate the precision of the slider in terms of the values that can be selected. For example, a Basic "Numeric" slider type may have a minimum value of 0 to a maximum value of 100. The default slider steps would be 1, but if it is desired to provide the ability to select values to the thousandths, slider steps could be entered as .001. This example would provide a slider with 100,000 total steps ranging from 0.000 to 100.000. Since the User's mouse movements are limited to the number of pixels across the length of the slider, selecting values to this degree of precision can only be accomplished by using the keyboard, i.e. clicking the desired handle and using the keyboard ARROWS, TAB and BACK keys to make selections. Up to 1,000,000 steps can be applied to a numeric slider.

Slider steps for date & time sliders are entered differently. In general, they are entered as:

precision: increments

More specifically, entries like "months: 1", "days: 1", "minutes: 15", etc. can be entered (without the quotes). There are several examples that can be viewed by retrieving any of the Current date & time sliders from the Slider types. A maximum of 500,000 steps can be applied to a date & time slider.

Label steps:

This value specifies the frequency which labels are applied to the Maj tick steps on the slider scale. For example, a slider that spans several years with a Maj tick label value of "Months" may get a bit crowded on the slider scale. Entering a value of 3 would apply a label to every third major tick and display Jan, Apr, Jul, Oct, Jan, etc.

Skip labels:

Sometimes it may be desirable to skip individual labels, particularly when the slider begins or ends with a partial increment for a major tick. For example, a slider that begins in the latter part of a month and spans many months could result in a short distance between the first and second major tick. In this scenario, eliminating the first label may be appropriate.

Note: Multiple values can be entered separated by a comma.

Capture format:

This field only applies to date & time sliders and provides some flexibility over the formatting of these values. For example, the default SQL format to capture a date would be 'yyyy-mm-dd'. If for some reason this format must be different for use as a Query variable, a value like mm/dd/yyyy could be entered into this field and the captured date format would be modified accordingly.

Since the intended use of these sliders is input to SQL as query variables, numeric values are unquoted and date & time values are enclosed in single quotes. If the Value selected is "Range", the selected maximum value is "maximized". In other words, a range slider that spans 10 years with Slider steps set to "months: 1", would select the first of each month as the beginning of the range and the end of the selected month for end of the range. The resulting SQL input might look something like:

BETWEEN '2010-01-01' AND '2015-10-31'

If this slider was saved as "date range slider", it could be used in a query with the standard QLR syntax to reference an Input Control as a Query variable with something like:

start_date [date range slider]

This would be resolved in the query as:

start_date BETWEEN '2010-01-01' AND '2015-10-31'

Float format:

These selections provide the capability to format the values presented in the float(s). This selection will have no effect on the format of the value submitted as the Query variable, which can be formatted using the Capture format above. Float format is for display purposes in the float(s) only.

Maj tick steps:

These are the tick marks on the slider scale that cover the full height of the slider. For Basic sliders with numeric values, the application of the major tick steps is merely the number of increments between the minimum and maximum values to apply the tick mark. The major tick steps for Date & time are defined in conjunction with the Maj tick labels. For example, a slider that spans several years with a major tick label value of "Months" would create a tick mark for each month. Entering a value of 3 would create a major tick mark every third month and along with a Label steps value of 1, would display |Jan, |Apr, |Jul, |Oct, |Jan, etc.

Min tick steps:

These are the short tick marks that run along the bottom of the slider scale and used to display additional increments between the major tick steps, without any labels. A value of 0, will omit any minor tick marks. For aesthetic purposes and consistent rendering across different browsers, choosing an appropriate number of minor tick marks relative to the slider length may be neccessary to allow the minor ticks to be uniformly spaced. Please see Slider length for more information.

Label prefix/Float prefix:

The prefix fields provide the ability to display any characters before the labels across the slider scale or the float(s). For example, prefixing a numeric value with a currency symbol.

Label suffix/Float suffix:

The suffix fields provide the ability to display any characters after the labels across the slider scale or the float(s). For example, adding a % to numeric values, which is applied automatically when a Slider type of Basic sliders "Percent" is selected.

Slider font face:

This list provides a large selection of fonts that can be applied to the slider float(s) and labels.

Slider font size:

The "Default" font size varies depending on the Slider height selected. The default size can be enlarged or reduced in increments up to 3 pixels.

Hide floats:

Checking this checkbox will hide the float(s) until the Range marker is moused over. When the mouse leaves the range marker, the float(s) will fade out. When checking this option, the Slider margin for "Top" will be reduced to 2px. This top margin can be changed to a different value before the slider is saved.

Captured values:

This area displays the values that would be captured and submitted as the Query variable. Since the intended use of these sliders is input to SQL as query variables, numeric values are unquoted and date & time values are enclosed in single quotes. If the Value selected is "Range", the selected maximum value is "maximized". In other words, a range slider that spans 10 years with Slider steps set to "months: 1", would select the first of each month as the beginning of the range and the end of the selected month for end of the range. The resulting SQL input might look something like:

BETWEEN 20 AND 60
or
BETWEEN '2010-01-01' AND '2015-10-31'

Reset Slider Values:

As a slider is being constructed, the values associated with the specific Slider type being defined are temporarily saved. This is useful when constructing a Custom date & time slider and there is a need to view the selections for a Current date & time slider for reference. The  Reset Slider Values  button will restore all the original defaults for all slider types.