DB Filters: Filtering DB Data
The data that is imported into the temporary non-editable XML file from the database (DB) can be filtered. (Note that the non-editable XML file is used for report generation, and the effect of a DB filter will therefore be seen only in the HTML, Text, RTF, PDF, and Word 2007+ preview; not in Authentic Preview, which displays the temporary editable XML file, and not in Authentic View.) The DB filter (DB Filter) can be created either within the DB itself (if this is supported in your DB application), or it can be created within the SPS (SPS file). In the SPS, one DB Filter can be created for each top-level data table in the XML Schema (i.e. for the data tables that are the children of the DB element). Each time a DB Filter is created or modified, the data from the DB is re-loaded into the temporary non-editable XML file that is generated for the DB. In this way, DB Filters help you to keep the XML file down to an optimal size and to thus make processing for report generation more efficient.
| Note: | Using a DB Filter modifies the data that is imported into the temporary non-editable XML File. If you save an SPS with a DB Filter and then generate an XML File from the SPS, the generated XML File will be filtered according to the criteria in the DB Filter. |
Creating a DB Filter
| 1. | In the Design Document or Schema Tree, select the data table element for which you wish to create a DB Filter (either by clicking the start or end tag of the element, or by selecting the element in the schema tree). |
| 2. | Select Database | Edit DB Filters or click the  icon in the toolbar. The following dialog is displayed: |
| 3. | Click the Append AND or Append OR button. This appends an empty criterion for the filter (shown below). |
| 4. | Enter the expression for the criterion. An expression consists of: (i) a field name (available from the associated combo-box); (ii) an operator (available from the associated combo-box); and (iii) a value (to be entered directly). For details of how to construct expressions see the Expressions in criteria section below. |
| 5. | If you wish to add another criterion, click the Append AND or Append OR button according to which logical operator (AND or OR) you wish to use to join the two criteria. Then add the new criterion. For details about the logical operators, see the section Re-ordering criteria in DB Filters. |
Expressions in criteria
Expressions in DB Filter criteria consist of a field name, an operator, and a value. The available field names are the columns of the selected top-level data table; the names of these fields are listed in a combo-box (see screenshot above). The operators you can use are listed below:
= | Equal to |
<> | Not equal to |
< | Less than |
<= | Less than or equal to |
> | Greater than |
>= | Greater than or equal to |
LIKE | Phonetically alike |
NOT LIKE | Phonetically not alike |
IS NULL | Is empty |
NOT NULL | Is not empty |
If IS NULL or NOT NULL is selected, the Value field is disabled. Values must be entered without quotes (or any other delimiter). Values must also have the same formatting as that of the corresponding DB field; otherwise the expression will evaluate to FALSE. For example, if a criterion for a field of the date datatype in an MS Access DB has an expression StartDate=25/05/2004, the expression will evaluate to FALSE because the date datatype in an MS Access DB has a format of YYYY-MM-DD.
Using parameters with DB Filters
You can also enter the name of a parameter as the value of an expression. This causes the parameter to be called and its value to be used as the value of that expression. The parameter you enter here can be a parameter that has already been declared for the stylesheet, or it can be a parameter that you declare subsequently. Note, however, that when a parameter that has not been declared is typed into the value field, the OK button is disabled.
Parameters are useful if you wish to use a single value in multiple expressions, or if you wish to pass a value to a parameter from the command line (see StyleVision Server for details).
To enter the name of a parameter as the value of an expression, type \$ into the value input field followed (without any intervening space) by the name of the parameter. If the parameter has already been declared (see Parameters), then the entry will be colored green. If the parameter has not been declared, the entry will be red, and you must declare it.
Declaring parameters from the Edit DB Filter dialog
To access the Edit Parameters dialog (in order to declare parameters), do the following:
| 1. | Click the Parameters... button in the Edit DB Filters dialog. This pops up the Edit Parameters dialog shown below. |
| 2. | Type in the name and value of the parameter in the appropriate fields. |
Alternatively, you can access the Edit Parameters dialog and declare or edit a DB Parameter by selecting Edit | Stylesheet Parameters.
Note: The Edit Parameters dialog contains all the parameters that have been defined for the stylesheet. While it is an error to use an undeclared parameter in the SPS, it is not an error to declare a parameter and not use it.
After a DB Filter is created for a data table element, that element in the Schema Tree is displayed with the filter symbol, as shown for the Addresses element in the screenshot below.
Re-ordering criteria in DB Filters
The logical structure of the DB Filter and the relationship between any two criteria or sets of criteria is indicated graphically. Each level of the logical structure is indicated by a square bracket. Two adjacent criteria or sets of criteria indicate the AND operator, whereas if two criteria are separated by the word OR then the OR operator is indicated. The criteria are also appropriately indented to provide a clear overview of the logical structure of the DB Filter.
The DB Filter shown in the screenshot above may be represented in text as:
State=CA AND (City=Los Angeles OR City=San Diego OR (City=San Francisco AND CustomerNr=25))
You can re-order the DB Filter by moving a criterion or set of criteria up or down relative to the other criteria in the DB Filter. To move a criterion or set of criteria, do the following:
1.Select the criterion by clicking on it, or select an entire level by clicking on the bracket that represents that level.
2.Click the Up or Down arrow button in the dialog.
The following points should be noted:
•If the adjacent criterion in the direction of movement is at the same level, the two criteria exchange places.
•A set of criteria (i.e. criteria within a bracket) changes position within the same level; it does not change levels.
•An individual criterion changes position within the same level. If the adjacent criterion is further outward/inward (i.e. not on the same level), then the selected criterion will move outward/inward, one level at a time.
To delete a criterion in a DB Filter, select the criterion and click Delete.
Modifying a DB Filter
To modify a DB Filter, select Database | Edit DB Filters. This pops up the Edit DB Filters dialog box. You can now edit the expressions in any of the listed criteria, add new criteria, re-order criteria, or delete criteria in the DB Filter. After you have completed the modifications, click OK. The data from the DB is automatically re-loaded into StyleVision so as to reflect the modifications to the DB Filter.
Clearing (deleting) a DB Filter
To clear (or delete) a DB Filter, select the element for which the DB Filter has to be cleared either in the Design Window or the Schema Tree. (There is one DB Filter for each (top-level) data table element.) Then click Database | Clear DB Filter. The filter will be cleared, and the filter symbol will no longer appear alongside the name of the element in the Schema Tree.