The Criteria Menu

The Criteria Menu is where you set up the criteria that a user may choose for a report. For example, in a stock control database you may have a report that shows items that are currently in stock. For such a report, you may wish to be able to run the report for specified suppliers and/or specified products and/or specified product types and/or products that came into stock between a particular date range. These selections can only be made on the report by setting each one as a criteria item within this section.

Each criteria item can operate in Expanded or Normal mode. When the report criteria screen is first displayed each criteria item is presented with a name and an input area that shows the users current selection. Each item here is in Normal mode and each one may be presented as an input field, a set of check boxes or with no ability for the user to enter criteria here. If an item is configured to expand then the Expand button (») appears alongside the criteria item. Pressing this provides entry of selection criteria for that item in the Expand pane on the right hand side. Where criteria items can be selected from a multi-selection list generated from database tables it is sensible to have this list presented in expand mode. In normal mode you may just summarise the user's selection or allow entry in a text box or use check boxes, list boxes and radio buttons where the selection list is small.

The Criteria Menu is where you set up the criteria that a user may choose for a report. For example, in a stock control database you may have a report that shows items that are currently in stock. For such a report, you may wish to be able to run the report for specified suppliers and/or specified products and/or specified product types and/or products that came into stock between a particular date range. These selections can only be made on the report by setting each one as a criteria item within this section.

There are several types of criteria that may be presented to the user:-

Text Field

User types in the criteria values into a text box at run-time and these values are then used within the main report query.

Database Lookup

User entry is selected from or validated against a list generated from the database. When setting up Database Lookup criteria items, it is necessary to provide the SQL that will provide the validation and selection list from the database. When running a report a Database Lookup criteria item can be selected by entering values into a text field or by selecting from list boxes, check boxes or radio buttons.

Date

Allows a date value to be prompted for in the report. Dates are specified through a pop up date picker. The format that dates appear in (e.g. dd/mm/yyyy) are specifed in a report project's configuration page under the Date Display Format.

Date Range

Allows a date range to be prompted for in the report via a date picker. The format that dates appear in (e.g. dd/mm/yyyy) are specifed in a report project's configuration page under the Date Display Format.

Custom List

A pre-determined list of values which can be presented during report criteria entry as text field, list box, check boxes or radio buttons. These predefined values are entered in the List Values attribute described below. They can be used within the SQL.

Display Types

There are 7 ways a criteria item may be presented during criteria entry. A criteria item may also be presented differently when in Expand mode (the mode you enter when you press the Expand (») button for an item). Note that date types are always presented using a date picker so this field has no relevance to date types.

These different display methods are :-

No Entry

User is presented with current criteria selection but unable to modify. This is useful when you want the user to only select criteria from a list in expand mode only and only use the unexpanded criteria mode to display what the user selected in expand mode ( pressing » ). Or it is useful where you wish to configure a default for the criteria entry that may not be modifiable by the user.

Text Field

Presents a text field where the user can type in the relevant criteria separated by commas.

Drop Down List

For Custom List or Database Lookup criteria types only. Presents the possible list of criteria values in a drop down single selection list box.

Multiple Selection Listbox

For Custom List or Database Lookup criteria types only. Presents the possible list of criteria values in a multiple selection list box. Useful when you wish to allow selection of multiple criteria values with use of the CTRL and SHIFT keys.

Checkbox

For Custom List or Database Lookup criteria types only. Presents the possible list of criteria values as a set of check boxes.

Radio Button

For Custom List or Database Lookup criteria types only. Presents the possible list of criteria values as a set of radio buttons. Useful when you wish to force entry of only value selection from the criteria list.

Lookup criteria Items can be linked together so that the list of values that the user selects from relates to another criteria selection. For example, if a set values for a country criteria item is selected, then a link can be set up that ensures when selecting from a city criteria, then only those cities belonging to the selected countries will be presented.

This is performed by embedding an extra where clause that pulls in the relevant users choices from another criteria into the lookup SQL. This is done in the same way as bringing criteria into the main report query. See Linking in Criteria Values for more details.

Selecting the Criteria tab presents you with the existing criteria items, if any, and allows addition of more.

To add a new criteria item, press the Add button. You will see a criteria button appear named CriteriaName. Click on this button and the Details sub tab menu appears. Change the name to something more meaningful as well as configuring the rest of the criteria item by filling in the attributes described below.

To edit or view the attributes of an existing criteria item, click on the relevant criteria item button and then the Details sub tab menu that appears.

The Criteria Item Details Pane

This is where you set up the basic attributes of a criteria item that identify the type of item and how the item is presented and selected from. Also how the criteria is incorporated into the main query and any links to that criteria. Default values for the criteria can also be set here.

Name

The name of the criteria item. This is name used to reference this item when linking to it or incorporating it within the main report query. This is not the same as the criteria title. It is best not to use spaces in this value.

Title

This is the label displayed alongside the criteria item during report criteria entry.

Main Query Column

Note it is recommended this is not set (the alternative criteria linking method should be used - seeLinking in Criteria Values)

… however …

This is an easy way of linking the criteria selection to the main report query without having to specify the link in the main report query SQL using the “[]” notation. By filling in this value, when the report runs, the main SQL will be modified to link in this criteria item by adding a line to the where clause something like “AND table.column IN ({criteriavalues})” where table.column is the value entered in this field and {criteriavalues} are the users selected values that relate to the Return Column described below.

If this value is not filled in then the main query must be modified to reference the criteria item using “[]” notation.

Criteria Type

Indicates the behaviour of the criteria item. The criteria types of Text Field, Database Lookup, Date, Date Range and Custom List are described above.

Criteria Help

Enter some help text which tells the user what the criteria is used for. When you set some help text here, then when entering criteria the user will see a popup help icon next to the criteria title which when hoverd over will show this text

Criteria Display

Indicates how the criteria item is presented to the user when selecting criteria on the left hand side of the report criteria entry screen. This values identifies whether the criteria should be selected via list box, check box etc. The valid values of No Entry,Text Field,Drop Down List,Multiple Selection Listbox,Checkbox,Radio Button and Date Fields are described above.

Expand Display

Indicates how the criteria item is presented to the user in Expand mode when selecting criteria on the right hand side of the report criteria entry screen. This values identifies whether the criteria should be selected via list box, check box etc. The valid value of No Entry,Text Field,Drop Down List,Multiple Selection Listbox,Checkbox,Radio Button and Date Fields are described above.

Criteria Required

If set to yes then a report will not run if this criteria is not set and will throw up a popup message indicating so.

Hide Criteria

If set to yes then the criteria item will not appear on the report criteria entry page. This is useful if you dont want the user to see the item but provide a value for this when embedding in a web page (using the initial_execution_parameters parameter ) or calling from a url link ( by providing it in a MANUAL_criteria=? url parameter)

Return Column

Relates to Database Lookup criteria types only and is only available once a criteria SQL query has been made in the SQL sub tab. It is the name of the column from the criteria SQL whose values are used by the main report query in order to generate report output that matches the user's selection for this criteria.

For example, in the case of a country criteria that selects both country_code and country_name from the country table, you probably want to use the country_code values to link in with main report query (and use country_name as the column which the user selects from). In this case the country_code column should be selected here.

See the start of this page for details of how criteria works within Reportico.

Display Column

Relates to Database Lookup criteria types only and is only available once a criteria SQL query has been made in the SQL sub tab. It is the name of a column from either the criteria SQL or created as an assignment column (within the criteria ) whose values are used to populate selection list boxes, check boxes, radio buttons that the user may select from.

Overview Column

Relates to Database Lookup criteria types only and is only available once a criteria SQL query has been made in the SQL sub tab. It is the name of a column from either the criteria SQL or created as an assignment column whose values are used to summarise the user's selection in the right hand side.

For example, in the case of a country criteria that selects both country_code and country_name from the country table, you may wish the full country name to be selected from in Expand mode, but country codes to be displayed or entered in the summary box in Normal mode. If this is the case then, country_code should be chosen.

Match Column

Relates to Database Lookup criteria types only and is only available once a criteria SQL query has been made in the SQL sub tab.

It is the name of a column from either the criteria SQL or created as an assignment column whose values are compared with the user's search string in expand mode when the Search option is used.

Defaults

Provides default selection values for this criteria item that are already selected when the report criteria screen first displays. For database lookup criteria, these must be values that relate to the Return Column described above.

For Text Field,Database Lookup and Custom List types then this can be populated with comma separated values.

For Date types a single date value can be entered or more useful codes as follows:-

TODAY - defaults to today's date

YESTERDAY - defaults to yesterday's date

TOMORROW - defaults to tomorrow's date

FIRSTOFMONTH - defaults to the date of the first day of this month

LASTOFMONTH - defaults to the date of the last day of this month

FIRSTOFLASTMONTH - defaults to the date of the first day of last month

LASTOFLASTMONTH - defaults to the date of the last day of last month

FIRSTOFYEAR - defaults to the date of the first day of this year

LASTOFYEAR - defaults to the date of the last day of this year

FIRSTOFLASTYEAR - defaults to the date of the first day of last year

LASTOFLASTYEAR - defaults to the date of the last day of last year

STARTOFWEEK - defaults to the first day of this week

ENDOFWEEK - defaults to the end day of this week

STARTOFLASTWEEK - defaults to the first day of last week

ENDOFLASTWEEK - defaults to the end day of last week

For Date Range types, the lower and upper range values can be specified with a separating hyphen (“-”). So to default a Date Range criteria item to last month, you should enter FIRSTOFLASTMONTH-LASTOFLASTMONTH.

List Values

For Custom List criteria items only. Provides the values that the user can select from and the values that will be used in the report query when generating the report data to match the query. The values should be entered as comma delimited values each consisting of a query value and a display value separated by commas.

So for example, if you entered a value of Gold=G,Yellow=Y,Blue=B,Lime Green=L,Red=R then user will be able to select from a list comprising “Gold”, “Yellow”, “Blue”, “Lime Green” and “Red” but it is the keys of G,Y,B,L and R that are used in the main query.

If you wanted to default this criteria item to Gold and Blue, you would enter G,B against the Defaults parameter.

  • the_criteria_menu.txt
  • Last modified: 2018/05/04 21:40
  • (external edit)