Filter Conditions
|
|
Filter Conditions
|
You can specify an initial filter for a new mission.
When you try to create a submission, its filter condition is automatically built by:
copying the filter condition from the parent mission;
adding the condition that retrieves only the data matching the current row (or tree branch) of the parent mission into the special clause of the submission filter.
So, if the parent mission retrieved client list of codes 100 - 300 and you create a submission for the separate branch client+shipping date, the filter condition for the submission will be:
But you can change any unit of the filter condition for a new submission.
The same time you create a mission using a saved mission plan you can get in need of changing the filter to retrieve the data belonging to a certain date interval, for example. You can change the filter condition of an active mission and any of the mission properties.
Main condition and subquery filters
Areas with filters can be nested, if a data model contains subquery missions. Subquery missions can have their own subquery missions. That is why there can be lots of nesting levels. A filter condition for such missions will have nested areas. The uppermost area corresponds to the main mission's filter condition, while the nested areas – to filter conditions of the subquery missions.
If a subquery mission defined in a data model has a reference to a saved plan, the subquery mission's filter condition will be taken from the saved plan when creating a new mission.
Basic conditions for individual fields
A filter looks
like a scrolling region in which filter conditions for every field of every
table
or for a
data model's subquery mission can be enabled/disabled and formulated. If a
field filter condition is disabled, you can see only its name and the combination string.
When you select the required field's check box, the controls used
to define a filter appear. The
appearance of the controls depends on the field type.
Numeric, symbol (no more than 15 characters), date and
date-time fields require specifying an interval of values.
You can click "Or" to define a set of intervals using the OR operation.
A date-time field filter condition looks like this:
Consider the
three Report Dates check boxes: 1, 2 and 3. These
options are effective when you save a mission as a plan
that you want to use in
reports. A user can once set report dates in the report creation dialog box
instead of searching for them and specifying these dates on all pages of report
filter conditions.
You can define a substring or an integral
value to search for in symbol fields of more than 15 characters long.
You can only select or clear the check box for logical fields.
The program will create filter conditions by combining the conditions for individual fields using the AND logical operation.
Combining equal fields of different filter condition areas
More complex data models usually cause a situation when one and the same property is simultaneously present in the fields of the main mission and of subquery missions. For example, you retrieve data from tables of goods according to an interval of codes in the main mission and create a subquery from a saved plan whose mission's data model also contains codes of goods. To optimize the retrieval you should specify the same code interval as in the main mission. It is particularly urgent when the data source of the main mission and that of subquery are incompatible! In this case, when specifying the interval of codes for the main mission you will have to search for the codes in subquery filter conditions and specify them again, that is not quite convenient.
It is also observed when forming reports that can have shoals of missions bookmarked, and you need to find all and fill the fields with codes of goods.
To prevent such actions it is recommended that you apply combination of properties. There is the "Combine as ..." field located on the right of an individual filter condition. The field is available both when the filter is enabled and when it is not. Make up a generic alias for the fields containing codes of goods, e.g. GOODS and specify it for all the fields with the codes in the main mission and submissions and in all other missions whose plans you save for further use.
Then it takes only to change values of such a filter condition element in one place to make Dataodyssey Express automatically change the values in all the fields, having this combination alias, in mission window or cube update filter areas and in all bookmarked missions when creating a report.
There is an instrument for creating a more complex filter in the beginning of the scrolling region, if the filter you need can't be created only by joining simple filters for certain fields using the AND operation.
Simple conditions in complex expressions
The first two buttons on the left of "Complex condition" allow to add rows to the end of the filter or insert them somewhere else. When adding a row, the program displays the list of tables, subquery missions of the data model and their fields. Select the field you need, and a simple condition row, referencing the field, will be added. For a simple condition you can specify a data interval in the Value 1 and Value 2 (or Value 1 only) fields according to the data type (for numeric, character (<=15 characters), date-time, data) or a certain value (character (>15), text).
Practically the whole unit "Complex condition" consists of separate condition strings, joined together by operators and brackets, that can be specified in the fields on the left or right of the condition itself.
After any change in the "Complex condition" unit the resultant expression will be displayed in the lower area of the "Expression" unit. If the expression contains an error, it will be colored red.
None of the methods described above allows to create relations (of comparison) between different fields of tables and subquery missions. But the "Complex condition" unit allows to do it. For example, you need to retrieve only the sales of price different from that defined in the price list on the basis of the sample data model, described in the manual. You will need the following condition:
b.PRICE<>d.PRICE
, where b is the alias of the INVOICE_ITEMS table and d of GOODS
Add a row referencing the b.PRICE field
Add the next row referencing the d.PRICE field
Select "no" in both rows as a comparison operator
Specify the <> comparison operator on the first row in the "Right operators" column
The "Complex condition" element allows to create subquery missions inside itself and use them with the Exists construction in a filter condition. Use the buttons group "Subqueries" with the New, Edit and Delete buttons. To add a subquery mission open the standard dialog of defining summary missions. If you create a subquery mission to retrieve data from a data source compatible with the main one, you will not even have to create a list of dimensions. It will be sufficient to describe a data model and filter condition. If the data of the subquery should be retrieved from a data source incompatible with that of the main mission, it will be necessary to create the list of dimensions and let it be as short as possible!
After creating the list of subqueries you can supplement the condition with the rows referencing the subqueries. Now, when you create a new row in the upper list of conditions, the tables and fields from subqueries will be listed in third lists. And you can correlate tables of current missions with tables of subqueries, but only if subquery is from compartible data source. If subquery selects data from uncompatible with current mission data source, then you can refer only to columns in result of this subquery.
Dataodyssey Express will analyze whether it was created within an Exists( construction. If not, the Exists( clause will automatically be added to "Left operators" field. You can add the Exists( clause to an already existing row using the appropriate button. The program enables you to specify comparisons for fields of the main mission and of subquery. But you should for yourself close the brackets of the subquery in the required place of the "Right operators" field.
Here is IN(Select ...) subquery sample:
Parent mission filter conditions
When a submission is created, the program offers you to retrieve data only for the current tree branch of a detailed mission or for the current row of a summary mission. It is technically done by putting the special element in filter expression area into operation. So, if a parent detailed mission has had the tree branch with code 7 for the 10th month of 2006 selected, the submission filter will contain the following element:
If certain units of such a filter do not suit you, it is possible to remove the rows and form another condition as described above.