[ class tree: reportico ] [ index: reportico ] [ all elements ]
Packages:
reportico


Tutorials/Manuals:
Package-level: Files:
reportico.php

Prev Next

The Criteria Menu

Criteria Menu Overview

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.

Criteria Types and Criteria Display Types

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 5 types of criteria that may be presented to the user:-

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

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

  3. 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 panel. Alternatively it can be manually specified in the relevant project config.php file by changing the SW_PREP_DATEFORMAT value.

  4. 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 panel. Alternatively it can be manually specified in the relevant project config.php file by changing the SW_PREP_DATEFORMAT value.

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

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). These different display methods are :-

  1. 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. Or it is useful where you wish to configure a default for the criteria entry that may not be modifiable by the user.

  2. Text Field

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

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

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

  5. Checkbox

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

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

  7. Date Field (D/M/Y)

    All date criteria are entered via a date picker. This option is no longer relevant. For Date and Date Range criteria types only. Presents date selection as 3 list boxes from which user can select day, month and year.

  8. Date Field (M/D/Y)

    All date criteria are entered via a date picker. This option is no longer relevant. For Date and Date Range criteria types only. Presents date selection as 3 list boxes from which user can select month, day and year.

  9. Date Field (Y/M/D)

    All date criteria are entered via a date picker. This option is no longer relevant. For Date and Date Range criteria types only. Presents date selection as 3 list boxes from which user can select year, month and day.

Criteria Linking

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.

The Criteria Menu

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.

Title

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

Main Query Column

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 Display

Indicates how the criteria item is presented to the user in Normal mode 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 values of No Entry,Text Field,Drop Down List,Multiple Selection Listbox,Checkbox,Radio Button and Date Fields are described above.

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 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 Normal mode.

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

  1. TODAY - defaults to today's date

  2. YESTERDAY - defaults to yesterday's date

  3. TOMORROW - defaults to tomorrow's date

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

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

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

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

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

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

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

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

  12. STARTOFWEEK - defaults to the first day of this week

  13. ENDOFWEEK - defaults to the end day of this week

  14. STARTOFLASTWEEK - defaults to the first day of last week

  15. 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.
Prev Up Next
The Assignments Menu Report Design Mode The Output Menu
Documentation generated on Mon, 09 Jun 2014 18:10:07 +0100 by phpDocumentor 1.4.3