Quick BI:Create a Workbook

Prerequisites

A dataset is created. For more information, see Create a dataset.

Entry

After you log on to the Quick BI console, you can use one of the following five methods to create a workbook:

The spreadsheet has five new portals:

• Method 1: Create a workbook from Quick Start on the Workbench tab out of a workspace.

  

• Method 2: Create a workbook in the left-side navigation pane of the Workbench tab in a workspace.

  

• Method 3: Create a workbook on the Workbooks page of the Workbench tab in a workspace.

  

  If you create a workbook by using one of the preceding three methods, no dataset is selected by default. You must select a dataset on the workbook editing page.

• Method 4: Create a workbook on the Datasets page.

  

• Method 5: Create a workbook on the editing page of a dataset.

  

  If you create a workbook based on an existing dataset by using Method 4 or Method 5, the dimensions and measures in the dataset are directly loaded to the Data pane of the Table Design panel on the right side. You can drag the required dimensions and measures to the Field tab of the Table Design panel.

Data Configuration

In this example, a workbook is created by using Method 1.

• Insert a dataset table.

  1. On the workbook editing page, choose Insert Dataset > Dataset table in the top navigation menu.

  2. In the Data pane of the Table Design panel, select a dataset from the Select a dataset. drop-down list.

     Fields in the selected dataset are automatically synchronized to the Dimension and measure sections.

  3. On the Field tab of the Table Design panel, add the required dimensions and measures to the workbook.

     You can use one of the following methods to add the required dimensions and measures based on your business requirements:

     • Method 1: Perform the following operations to add dimensions to the Rows section and measures to the Columns section:

       1. On the Field tab, add the required dimensions and measures.

          • In the Dimension section, double-click the province, product_type, and product_box dimensions. You can also drag the dimensions to the Rows section.

          • In the measure section, double-click the order_amt, shipping_cost, and profit_amt measures. You can also drag the measures to the Columns section.

       2. Click Update to update the workbook.

     • Method 2: Perform the following operations to add dimensions to the Rows section and measures and dimensions to the Columns section:

       1. On the Field tab, add the required dimensions and measures.

          • In the Dimensions list, double-click province. You can also drag this dimension to the Rows field.

          • In the Dimensions list, find Product Type and drag it to the Columns field.

          • In the measure section, double-click the order_amt, shipping_cost, and profit_amt measures. You can also drag the measures to the Columns section.

       2. Click Update to update the workbook.

     • Method 3: Perform the following operations to add measures to the Rows section and dimensions to the Columns section:

       1. On the Field tab, add the required dimensions and measures.

          • In the Dimensions list, find Product Type and drag it to the Columns field.

          • In the measure section, drag the order_amt, shipping_cost, and profit_amt measures to the Rows section.

       2. Click Update to update the workbook.

  4. Configure the format of the field.

     Configuration Item	Section Description
     Date display format	Automatic by default. You can set the YYYY-MM-DD, YYYYMMDD, YYYY/MM/DD, YYYY, MMDD, MM-DD, MM/DD, and DDMMYYYY formats. Note You can set the date display format only for date fields.
     Alignment	Automatic by default. Supports left alignment, center alignment, and right alignment.
     Sorting	By default, no sorting is performed. You can set Ascending, Descending, Within-Group Ascending, Within-Group Descending, Advanced Sort, Custom Sort, and Field Sort. Note Only measure fields support advanced sorting. Only dimension fields support custom sorting and field sorting.
     Field display content	Supports setting display names.
     Data display format	Automatic by default. You can set Integer, Retain 1 Decimal, Retain 2 Decimal, Percentage, Percentage 1 Decimal, Percentage 2 Decimal, or Custom. Note You can set the data display format only for measure fields. If the custom format type is numeric or percentage, you can set the negative number format to -1234 or (1234) and select Use thousands separator Separator.
     Aggregation method	You can set Sum, Average, Count, Deduplication Count, Maximum, Minimum, Population Standard Deviation, Sample Standard Deviation, Population Variance, Sample Variance, and Last Day. Note You can set the data display format only for measure fields.
     Advanced Calculation	You can set Cumulative Date, Percentage, Ranking, and Same comparison. Note Only measure fields support advanced calculation.

  5. To configure the filter feature, drag the required fields to the Filters section and click the icon on the right to filter the field values based on your business requirements. For more information, see Field filtering.

  6. Click Save.

     Note

     If you save the workbook for the first time, the Save Workbook dialog box appears. The following table describes the parameters in the dialog box.

     Name	Description
     Name	The name of the workbook.
     Save To	The directory in which the workbook is saved.

  7. Click OK.

     Note

     Excel workbook supports automatic sum identification. You can select some data in the workbook to view it.

     

• Insert Freestyle Cell

  Note

  Only worksheets purchased separately in the Pro and Pro editions support free-form cells.

  1. (Optional) You can set the format of the report before you insert a free-form cell based on your business requirements.

     Note

     If you need to merge cells, merge fields first and then drag fields, because cells cannot be merged after you drag fields.

  2. On the workbook editing page, choose Datasets > Freestyle Cells.

  3. In the Data panel, click Select Dataset and select the dataset from the drop-down list.

  4. Select the required dimension fields and measure fields, and double-click or drag the fields to the workbook editing area.

     You can set Aggregation Mode, Advanced Calculation, Associate, and Delete for a field.

     1. You can move the pointer over the left side of Measures and click the icon to enter the Aggregation Method settings as shown in the figure.

        You can set multiple aggregation methods for a measure field, such as sum, average, count, deduplication, maximum, and minimum.

     2. You can move the pointer over Metrics and click the icon to enter the Advanced Computing settings as shown in the figure.

        Advanced calculation supports date accumulation, percentage, ranking, and year-on-year and month-on-month comparison.

        

        Configuration Item	Description
        Date Cumulative	For more information about date rollup, see Date rollup.
        Percentage	Sets the percentage format of the data.
        Ranking	You can configure ascending or descending order. For more information, see Data Ranking.
        Same Comparison	For more information about how to configure the same-period comparison of data, see Same Comparison.

     3. You can configure association for fields.

        • Click Associate.

        • Move the pointer over the OK icon to switch between Aggregate and Detail.

          After you click OK, the association is successful. In this example, Order Date (year) is associated with Order Quantity, Order Amount, and Profit Amount.

        • You can also click Disassociate to cancel the association.

     4. You can delete fields that you no longer need.

        Note

        • Dimension fields can be associated or deleted.

        • You can set the aggregation method, associate, or delete a measure field.

  5. Optional. You can add a query component based on your business requirements. In this example, the Year and Region search conditions are added.

  6. Click Update. The system automatically updates the workbook.

  7. Click Save.

  8. Create a card view.

Card View

You can use the card view to create column-based reports and visualize and analyze metric cards in reports.

Methods for creating a workbook

• In freestyle cell mode, the function icon for the card view appears in the toolbar.

• Procedure

  1. Click Card View to go to the card view creation page.

  2. Create a card view.

     1. Select the base table for creating a card view to create a card view constraint.

        1. You can create a card view and then manually select a data range.

        2. You can circle a data range before creating a card view.

           Note

           Fields in different datasets cannot be associated with each other.

     2. Set card display. You can customize the number of groups to display cards in a row and the maximum number of groups to display cards.

     3. Set Calculation Mode to Aggregate or Detail.

     4. Select Create Card View Page or Current Sheet Extension as the location where you want to place the data pivot table.

        In this example, Create Card View is selected.

  3. Click OK.

  4. Set the card page view. If the data pivot table is placed on the Create Card View page, you can change the number of card groups to be displayed in a row, set filtering, grouping, and sorting, and support viewing details and searching on the card view page.

What to do next

For more information about workbook style configuration and analysis configuration, see Style configuration and Analysis configuration.