Page tree
Skip to end of metadata
Go to start of metadata

I. Overview

1) After inserting data columns into cells, you can filter data using conditions, and only data that make the condition true will be displayed when being previewed. Through filters, you can realize two effects:

  • Just as its name implies, filter out unwanted data
  • Connect two datasets whose data source may be different databases

2) Prerequisite: Insert Data ColumnsInsert Formulas and Left Parent Cell and Upper Parent Cell.

You will Learn

  • Common Condition & Formula Condition

  • Combine several conditions

  • Edit conditions

  • Add or remove parentheses, remove conditions, move up/down conditions

  • Connect two datasets

II. Filter out unwanted data

 1. Create a new dataset [ds1]

1) Create a new DB query dataset.

2) Drag the table [ORDERS] into the DB query.

2. Drag datasets into cells

1) Drag all data columns of [ds1] into B2~K2. Maintain the default expansion attributes:

a) Expand Direction: Vertically

b) Left Parent Cell: Default.

2) Adjust the size of the cell, so that K2 is located within the dotted line. The dotted line indicates the size of the report page after the preview.

3) Set B2~K2 as center alignment and add borders. Preview the effect.

Note

  • B2 is the parent cell of C2, C2 is the parent cell of D2, and so on, so that B2 is the highest-level parent cell of the data in the second row.
  • Set the following steps in B2, so that the effect of Filter can apply to all cells.

2.3 Two ways to set Filter

1) Method 1: Select B2, select [Cell Element] > [Basic] on the right of the setup panel and click the [Edit] button next to Filter Condition.

2) Method 2: Double click B2 and then click the [Filter] tag.

2.4 Filter Type: Common

1) Click the 3 drop-down boxs to select: Available Columns for judgment, Operator used to construct a condition and value types for comparison.

2) Available Columns: All data columns of [ds1] to which the current data column belongs.

3) Operators: equal to, not equal to, greater than, greater than or equal to, less than, less than or equal to, begin with, not begin with, end with, not end with, contain, not contain, in, not in.

4) Value Types: String, Integer, Double Precision, Date, Boolean, Formula, Parameter, Cell, and Column.

2.5 Construct a common condition

1) Select B2 and set Filter.

2) Common Condition:

a) Available Columns: [PAID]

b) Operator: greater than

c) Value Type: Double-Precision

d) Value: 0.0

3) Click [+Add]. Click [OK] to complete Filter settings.

2.6 The effect of Filter

1) B2 is marked by a yellow triangle at the left bottom, indicating that Filter has been set.

2) Preview. Only data with a [PAID] value greater than 0 are displayed.

2.7 Combine two common conditions

1) Select B2 and set Filter.

2) Common Condition:

a) Available Columns: [SHIPPORT]

b) Operator: in

c) Value Type: String

d) Value: ('A','B')

3) Maintain the logical operator as [AND], click [+Add]. Click [OK].

4) In the preview, only data with a [PAID] value greater than 0 and a [SHIPPORT] value being A or B are displayed.

2.8 Filter Type: Formula Condition

1) Select B2 and set Filter.

2) Select [Formula] and click [Define].

3) Input the following in the Formula Definition panel: MONTH(SHIPDATE) = 4. Click [OK].

4) Maintain the logical operator as [AND] and click [+Add]. Click [OK].

5) In the preview, only data with a [PAID] value greater than 0, a [SHIPPORT] value being A or B and the month of shipment being April are displayed.

2.9 Modify conditions

1) Select B2 and set Filter.

2) Click the second Common Condition and modify the Operator to [not in].

3) Maintain the logical operator as [AND] and click [Modify]. Click [OK].

4) In the preview, only data with a [PAID] value greater than 0, a [SHIPPORT] value being neither A nor B and the month of shipment being April are displayed.

2.10 Add or remove parentheses

1) Hold down the Shift key to select a number of adjacent conditions, and click [Add Parentheses].

2) These conditions form a condition group. Click the triangle mark preceding the condition group to display the sub-conditions included.

3) Select the condition group and click [Remove Parentheses] to remove the condition group.

2.11 Remove, move up or down conditions

III. Connect two datasets

1. Create two datasets

1) Create a new dataset [ds1] and the data source is FRDemo

2) Create another new dataset [ds2] and the data source is FRDemoEN

2. Insert data columns

1) Drag data columns [ORDERID] and [CUSTOMERID] of [ds1] to B2 and C3.

2) Drag data columns [PRODUCTID] and [QUANTITY] of [ds2] to D2 and E3.

3. Preview the effect without setting a filter

Data from the 2 datasets do not display any relationship and they expand vertically individually.

4. Set a filter to connect the two datasets

Double-click D2 and choose [Filter] tab in the pop-up panel. Set a common condition:

  • Available Columns: [ORDERID]
  • Operator: Equal to
  • Value Type: Cell
  • Value: ('A','B')

Click [+Add] to finish the setting.

5. Preview the effect after setting a filter

Data in the two datasets are connected according to Order ID.

 

Page viewed 160 times