1. Problem description

The figure below shows a table from database in which series under each category are specifically sorted (series summary, i.e. total flow of each series in descending order) for better data analysis and data under some categories are default.

Create a dataset data source column chart with the data in the above table for being a better reporting tool and business intelligence tool as shown below to see the difference in data sorting between the legend and database in which the sorting of series under each category is the same (default series not applicable to sorting), while the legend order in the chart follows the order of series (port) field read from database. If the name of series under the first category is non-default, the legend order in the chart will be displayed normally (completely displayed by directly reading from the first to the last). If the series data under the first category is default, the legend order will first display series available under such category followed by default series, thus disrupting the original order:

Now how to keep the legend order in the chart and the order in the database the same in the case of default series?

2. Solutions

By using cells as chart data source, reorder data in cells, i.e. to form a crosstab with category and series respectively as horizontal and vertical headers, and the default left blank automatically, and reorder series.

3. Example

3.1 Cell data

As shown below, drag data columns to cells to form a crosstab, and calculate the total flow of each category:

Note: it is necessary to manually set C15, i.e. the left father cell of the cell of function sum, to A15.

For the method of creating a crosstab, please refer to Crosstab – Data Horizontal Extension.

3.2 Reorder cells

Select the cell of chart series, i.e. A15, and select descending sort by typing the formula of =C15 in Cell Property Table – Extension Attributes > After Extension, as shown below:

Reorder the value expanded by the A15 cell in a way of sorting summarized data in the C15 cell from large to small.

Effect

Click Page Break Preview to see the series name in the crosstab is reordered as shown below:

3.3 Chart data source

After the creation of cell data, bind the data with the chart, i.e., re-add chart data source by selecting the chart and selecting Cell Data Source as Data Source in Chart Attribute Table – Data
, as shown below:

Note: if you do not want the cell data to be displayed, then hide them.

3.4 Effect viewing

Click Page-breaking Preview to see the effect as below:

For completed templates, refer to:%FR_HOME%\WebReport\WEB-INF\reportlets\doc\advanced\chart\sort.cpt