Sorting a category field by a measure in visualizations

You can sort a category field by a measure in visualizations that are inserted in IBM® Cognos® Active Report.

About this task

In visualizations, you can sort a data item by the overall totals or by the totals in the context of an extra category. While Cognos Active Report supports client-side filtering for visualizations, sorting always occurs on the Cognos server. As a result, you must construct the visualization so that the underlying crosstab reflects your filtering options and provides the correct sorting. As you construct the visualization, you can view the underlying crosstab by right-clicking the visualization data container and clicking Show Container Data.

This task describes how to maintain the sorting of a category by a measure when the visualization is filtered by a single selection control. For example, an active report contains a data drop-down list control with Quarter as the data item, and a clustered bar visualization that shows quantity that is sold by retailer type. In the visualization, Retailer type appears as the series, Quantity appears as the measure, and no data item is specified for the categories. You want to sort the visualization by quantity that is sold, and filter by quarter. To have the sorting applied for each quarter, place Quarter on the same edge as Retailer type in the underlying crosstab, which is accomplished by inserting the Quarter data item as an extra category for the series. In addition, Quarter must be nested outside of Retailer type in the underlying crosstab. Otherwise, Retailer type is sorted according to the quantity that is sold for all quarters.

This technique does not work when a visualization is filtered by multiple controls, or by a control that accepts multiple selections, such as a check box group control.

Procedure

In the visualization, select the category field that you want to sort. In the preceding example, select Retailer type.
In the Properties pane, set the Category Range property to Filtered Data.
Double-click the Sorting property.
In the Data Items box, drag the measure that you want to sort the category by to the Sort List box. In the preceding example, drag Quantity.
To specify the sort order, click the Sort Order icon.
Click OK. A warning message appears that informs you that sorting is applied when the report is run, and does not reflect client-side filtering.
Click OK.
Select the clustered bar data container.
In the Properties pane, double-click the Nesting Order property, and change the nesting order of the extra category as needed. In the preceding example, for the series, move Quarter above Retailer type.
Optional: To view the data in the underlying crosstab, click Show Container Data. The data in the crosstab is sorted by the sort order that you specified in step 5.
Click OK.