GLOBAL FILTERS
= optional
Parameter (col A in PARAM) |
Mandatory/Optional |
Value in col C (example) |
Value in col D FORM(example) |
Value in col E (example) |
Occurrence in PARAM sheet |
Comment |
|
---|---|---|---|---|---|---|---|
FORMS |
REPORT |
||||||
<filter value(s)> |
<filter value(s)> if other filter values should be used when retrieving data to the form |
N/A |
x number of times |
Define an object ID or data view field as parameter and filter value(s) as parameter value. |
In the PARAM sheet, an object or any other data view field can be defined as parameter, that is common for all data rows in the template. The global filter column does not need to exist in every data source (SOURCE in col A) used in the template. Instead, Bizview will evaluate in which of the used data sources the defined filter column exists, and add the parameter as a where-clause in the SQL queries run towards that/those particular data source(s).
For example, an object can be ACCOUNT, UNIT, CUSTOMER, PROJECT, etc. If the ADDSHEET parameter is used, then the addsheet object should be defined as a Global filter in PARAM in column A, since it is the object values stated in column C, that will be used to create worksheets. If the Addsheet object is not added as a global filter, then all values related to the addsheet object will be used when creating the break sheets in the form or report.
Any global filter value (value entered in column C in PARAM) containing a - (hyphen) must be stated within { } in PARAM sheet. Otherwise, SQL will not treat this as a unique value and data might not be retrieved correctly to the report/form. Additionally, in a form the data will not be updated with this value, since it will not be treated as a unique value.
Access Global Filters in the Template Design right panel by selecting Parameters > Global Filters:
In the Global Filters grid, you can view any existing global filters defined for the template. In this interface, you can also add new, edit existing, or delete any global filter.
To add a new filter, select the plus icon:
The Add New Global Filter dialog displays, as shown below:
When adding a new global filter, you must define the following:
- Filter Column: Either enter a value directly in the text box, or enter a formula in the text box. You can also select an object from the object list, or select any other column from the data views used in the template by clicking the two different buttons to the right of the text box.
- Filter Value: Enter the values for the Filter Column that the data should be filtered on. You also have the option to select a CF_GETPANEVALUE formula to retrieve the filter values directly from the selection pane. If you want to define several filter values, then separate them with # in the text box, or use other SQL Syntax to define the filter values. For example, if you select UNIT as Filter Column and want to use Filter values 10,11, and 12, you should enter 10#11#12 in the Filter Value field.
- Section: If the template contains several sections, you can also define which section the global filter should apply to. If you leave the Section drop-down empty, the global filter will apply for all sections.
Upon adding a global filter, the following occurs:
- The Filter Column value will be written to the PARAM sheet in column A.
- The Filter Value(s) will be written to the PARAM sheet in column C in the Filter Column row.
- If selected, the Section will be added the Filter Column name in column A.
Retrieve/Update Values - Column C and D in PARAM
In a FORM, if only a value in column C in PARAM is defined for the <Global_Filter> parameter, then that value will be used both when retrieving data to the form and when updating values to the database.
If for some reason you want to retrieve values for a different set of filter values compared to the filter value you want to be updated, then you need to define those retrieve value(s) in column D in PARAM sheet. If several filter values should be retrieved, then these filter values need to be separated with # in column D in PARAM. For example, UNIT1#UNIT2#UNIT3
REPORT templates only support values in column C in PARAM. Column D in PARAM should not be used.