Cells as Chart Data Sources

Version

Application Scenario

If the data to be displayed in the chart does not come directly from the dataset and needs to be calculated before displaying, you can use a cell data source to define the chart data, as shown in the following figure.

Note:

When using Cell Data as Data Source for the chart, use the functions under Cell Attribute > Sort After Expansion with caution, as there may be inconsistencies between the chart data and the actual data.

Function Description

1. Category Name, Series, and Value can all be filled with the cell data or directly entered with text content.

Note:

When using Cell Data, you must click the F(x) button to enter the Formula Definition page for editing. Direct inputting in the input box will be recognized as Text.

2. Notes for using Cell Data as Data Source of the chart:

• If the data comes from continuous cells like A1 - A3: Use the formula = [A1:A3] to define the data, where the cells are separated by colons and enclosed in [].

• If the data comes from multiple discontinuous cells like A1, A3, A5: Use the formula = [A1,A3,A5] to define the data, where the cells are separated by English commas and enclosed in [].

• Category, Series, and Value need to obtain the parent-child relationship of cells, for example, Category is the parent cell of Series, and Series is the parent cell of Value.

• When using the formula to calculate cell data for the second time, such as A1+10, A1/10, the result will lose its cell attributes and cannot obtain the parent-child relationship, resulting in abnormal display of the chart. 

Note:

If the original cells are displayed in a list and the number as well as the order of results correspond exactly to the cell data, the displayed results will be correct.

• Bind Cell Data to the point map heat map, drilling point map, and flow map, select Lat/Long as Geographical Information. If the latitude and longitude are the same but the region names are different, only the data of the last latitude and longitude will be obtained, and the data will be calculated according to the Summary > Last.

Data Preparation

1. Create a general report, and create a database query ds1 with the SQL statement SELECT * FROM Sales_Volume.

Report Design

1. Drag the Region, Product, and Sales Volume fields from the ds1 dataset into cells A2, B2, and C2 respectively, and set the data display method of the Sales Volume in cell C2 to Summary > Sum, as shown in the following figure.

2. Merge required cells below the chart and insert a bar chart, as shown in the following figure.

Data Binding

Select the chart, choose Cell Element > Data, select Cell Data, and fill in the corresponding cells in each setting.

Note:

Be sure to click F(x) to enter the Formula Definition page before inputting the cell.

In this example, Category Name is Region in A2, Series is Product in B2, and Value is the corresponding Sales Sum in C2, as shown in the following figure.

Style Setting

1. Select the chart, choose Cell Element > Style, select Title, and modify the title to Product Sales by Region, as shown in the following figure. 

2. Select Label, deselect Use Label to hide labels in the chart, as shown in the following figure.

Effect Display

PC

Save the template and click Pagination Preview. The following figure shows the effect.

Mobile Terminal

The report can be previewed on both the DataAnalyst app and the HTML5 terminal. The following figure shows the effect.

You can click to download the template Cells as Chart Data Sources.cpt