Data Settings
1) After inserting data columns and set expansion attributes, the data after expansion can be displayed in the form of group, list and summary based on the data settings.
2) Prerequisite: Insert Data Columns, Cell Expansion.
| You will learn |
|---|
|
I. Group
1. Create a new dataset [ds1]
1) Create a DB query dataset.
2) Drag the table [Sales_Volume] into the DB query.
2. Preview
3. Drag data columns into cells
1) Drag the data columns of [ds1] into B2~F2.
2) Set the attributes of B2~F2:
Expand direction: Vertically
Left parent cell: Default
3) Set B2~F2 as center alignment and add borders.
4. Default data setting: Common Group
The “G” preceding the data column means the data setting is [Group].
5. Preview
After expansion, the values of the same content in the data column will be automatically merged to show the effect of “Group”.
6. B2 data setting –Proximal Continuum Group
1) Method 1: Click the drop-down box on the right pane and select [Proximal Continuum Group].
2) Method 2: Double click the data column and modify the settings in the [Data Column] pane.
7. Preview
After expansion, the values of the same content adjacent to each other in the data column will be merged to show the effect of “Proximal Continuum Group”.
8. Data setting-Advanced Group
1) Click the drop-down box on the right pane and select [Advanced Group].
2) Click [Custom]. When the setup pane pops up, select [Condition Group] or [Formula Group].
3) See Statistical Report for specific setup methods and usage scenarios. (An example presentation)
II. List
1. Create a new dataset [ds1]
1) Create a new DB query dataset.
2) Drag the table [Sales_Volume] into the DB query.
2. Preview
3. Drag data columns into cells
1) Drag the data columns of [ds1] into B2~F2
2) Set the attributes of B2~F2:
Expand direction: Vertically
Left parent cell: Default
3) Set B2~F2 as center alignment and add borders.
4. Data setting of B2: Common Group
1) Modify the data setting of B2 as [List].
2) Keep the default data setting of C2~F2 as [Common Group].
3) The “S” preceding the data column means the data setting is [List].
5. Preview
After expansion, all the data are not grouped and are displayed in the form of “list”, with the same preview effect as that of [ds1].
6. Data setting of C2: List
1) Restore the data setting of B2 to [Common Group].
2) Modify the data setting of C2 as [List].
3) Keep the default data setting of D2~F2 as [Common Group].
7. Preview
All the data in the data column [Region] are displayed as a group, while that in the data column on the right side are displayed as a list.
8. Data setting of D2: List
1) Restore the data setting of C2 to [Common Group].
2) Modify the data setting of D2 as [List].
3) Keep the default data setting of B2 and E2~F2 as [Common Group].
9. Preview
Data of [Salesperson] in the data column [Region] is displayed as a group, while that in the data column on the right side is ungrouped and is displayed as a list.
III. Summary
1. Create a new dataset [ds1]
1) Create a new DB query dataset.
2) Drag the table [Sales_Volume] into the DB query.
2. Preview
3. Drag data columns into cells
1) Drag the data columns of [ds1] into B2~F2.
2) Set the attributes of B2~F2:
Expand direction: Vertically
Left parent cell: Default
3) Set B2~F2 as center alignment and add borders.
4. Data setting of F2: Summary
1) Modify the data setting of F2 as [Summary].
2) Summary: SUM, AVERAGE, MAX, MIN, COUNT, NONE.
3) Select [SUM]. The “Sum” preceding the data column means the data setting is [Sum-Total].
4) Keep the default data setting of A2~E2 as [Common Group].
5. Modify the left parent cell of F2
1) Left parent cell: Custom
2) Select D2.
6. Preview
After expansion, the SUM is displayed in [Sales_Volume] column by [Product_type] column.
7. Modify the left parent cell of F2 as C2
8. Preview
After expansion, the SUM is displayed in [Sales_Volume] column by [Product_type] column.
Data Filters
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 Formulas, Insert Data Columns, Cell Expansion.
| You will learn |
|---|
|
I. 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:
Expand Direction: Vertically
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.
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.
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.
5. Construct a common condition
1) Select B2 and set Filter.
2) Common Condition:
Available Columns: [PAID]
Operator: greater than
Value Type: Double-Precision
Value: 0.0
3) Click [+Add]. Click [OK] to complete Filter settings.
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.
7. Combine two common conditions
1) Select B2 and set Filter.
2) Common Condition:
Available Columns: [SHIPPORT]
Operator: in
Value Type: String
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.
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.
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.
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.
11. Remove, move up or down conditions
II. 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.
Advanced Data Column Attributes
1) After inserting data columns into cells, you can set advanced attributes, and the advanced attributes should be set in parent cell at the highest level.
2) Advanced attributes include: Sort, Result Filter, Custom Display, Expansibility and Supplement Blank Data.
3) Prerequisite: Insert Formulas, Insert Data Columns, Cell Expansion and Sort Data after Expansion.
| You will learn |
|---|
|
I. Sort
1. Create a new dataset [ds1]
1) Create a new DB query dataset.
2) Input the SQL statement: SELECT * FROM ProductLIMIT 10
2. Drag data columns into cells
1) Drag the data column [ProductID] into B2, [Product_name] into C2 and [Unit_price] into D2.
2) B2~D2: Set as center alignment and add borders.
3) Maintain the default expansion attributes of B2~D2.
Expand Direction: Vertical Expansion
Left Parent Cell: Default.
3. Advanced settings
1) Method 1: Click [Cell Element] > [Advanced] on the right side of the setup panel.
2) Method 2: Double click the cell and select [Advanced] in the data column panel
4. Sort: Null
Preview the effect.
5. Sort: Ascending
1) Select B2 (the highest-level parent cell) and click the [Ascending] button.
2) Click the [Formula] button. In the formula editor, double click [Unit_price]. Click OK.
6. Preview the effect.
The data is arranged in an ascending order of unit price.
7. Sort: Descending order
1) Click the [Descending] button.
2) Maintain the text fields below unchanged.
8. Preview the effect
The data is arranged in a descending order of unit price.
9. Comparison of Advanced Sort and Expanded Sort
1) Similarity: Both of them must be set in the highest-level parent cell.
2) Differences:
| Sort by | Order of execution | Performance | |
|---|---|---|---|
| Advanced Sort | Any data column in a dataset | Before cell expansion | Better |
| Expanded Sort | A data column having been inserted in a cell | After cell expansion | / |
II. Result Filter
1 Create a new dataset [ds1]
1) Create a new DB query dataset.
2) Input the SQL statement: SELECT * FROM ProductLIMIT 10
2. Drag data columns into cells
1) Drag the data column [ProductID] into B2, [Product_name] into C2 and [Unit_price] into D2.
2) B2~D2: Set as center alignment and add borders.
3) Maintain the default expansion attributes of B2~D2.
Expand Direction: Vertically
Left Parent Cell: Default.
3. Result Filter-Undefined
Preview the effect.
4. Result Filter-Top N
1) Select B2 (the highest-level parent cell). Click the drop-down box and select [Top N].
2) Input the value of N: 3.
5. Preview the effect
Only the first 3 rows of data are displayed.
6. Result Filter-Odd
7. Preview the effect
Only data in row 1, 3, 5, 7 and 9 are displayed.
8. Result Filter-Specify
1) Click the drop-down box and select [Specify].
2) Input the following in the text field below: 1,2,5-8.
9. Preview the effect
Data in row 1, 2, 5, 6, 7 and 8 are displayed.
10. Set [Sort] and [Result Filter] simultaneously.
1) Select B2 (the highest-level parent cell).
2) Sort
Ascending.
Sort by: [Unit_price].
3) Result Filter
Top N.
N: 3.
11. Preview the effect
1) Only 3 rows of data with the lowest unit prices are displayed.
2) Sort takes effect before Result Filter.
III. Supplement Blank Data
1. Create a new dataset [ds1].
1) Create a new DB query dataset.
2) Input the SQL statement: SELECT * FROM ProductLIMIT 10
2. Drag data columns into cells.
1) Drag the data column [ProductID] into B2, [Product_name] into C2 and [Unit_price] into D2.
2) B2~D2: Set as center alignment and add borders.
3) Maintain the default expansion attributes of B2~D2
Expand Direction: Vertically
Left Parent Cell: Default.
3. Supplement Blank Data
1) Select B2 (the highest-level parent cell). Check [Supplement Blank Data].
2) Line Numbers: 12.
4. Preview the effect.
The system adds two rows of blank data, so that the total Line Numbers becomes 12.
