Not Displaying Hidden Cell Data in Charts

Version

Application Scenario

If the data in the dataset needs to be calculated before it can be used in the chart, you can select Cell Data for Data Source to define the chart data.

 In some cases, cells are set up with condition attributes to hide certain data, and you also want the chart not to show hidden rows of data.

 However, if the cell containing the hidden data is referenced directly, the chart results will not match the cell data source, as the Hide Data does not take effect on the chart, and all data will still be displayed in the chart, as shown in the following figure.

Implementation Method

When quoting cell data, you can set filter condition by hierarchical coordinates to solve the problem.

Note: 

Filter Condition should be the opposite of the Condition Attribute1 set to hide the cells.

Note: 

Cross-sheet and cross-table references are not supported.

Data Preparation

Create a database query named ds1, and enter the SQL statement SELECT * FROM Sales_Volume, as shown in the following figure.

Table Design

Design the table according to the effect shown in the following figure and drag the fields from the dataset into corresponding cells. Select the cell C2, and add Condition Attribute 1 to hide the sales data less than 400.

Chart Design

Merge required cells, and insert a column chart. Set =A2 for Category Name, =B2[!0]{C2>400} for Series, and =C2[!0]{C2>400} for Value to make sure that the chart will only show data for sales greater than 400, as shown in the following figure.

Note:

B2[!0] represents all the data extended from cell B2. For details, see the document Common Formulas of Hierarchical Coordinates.

Effect Display

1. PC

2. Mobile Terminal

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

For details, you can download the template Not Displaying Hidden Cell Data in Charts.cpt