Sorting grouped values
The output of the previous recipe brings the right information back on the screen. It filters the rows correctly and shows the highest selling product per product line for the selected shipment month.
For better representation and to highlight the best-selling product lines, we need to sort the product lines in descending order of quantity.
Getting ready
Open the report created in the previous recipe in Cognos Report Studio for further amendments.
How to do it...
In the report created in the previous recipe, we managed to show data filtered by the shipment month. To improve the reports look and feel, we will sort the output to highlight the best-selling products. To start this, perform the following steps:
- Open the report in Cognos Report Studio.
- Select the Quantity column.
- Click on the Sort button from the toolbar and choose Sort Descending.
- Run the report to check if sorting is working. You will notice that sorting is not working.
- Now go back to Report Studio, select Quantity, and click on the Sort button again. This time choose Edit Layout Sorting under the Other Sort Options header.
- Expand the tree for Product line. Drag Quantity from Detail Sort List to Sort List under Product line as shown in the following screenshot:
- Click on the OK button and test the report. This time the rows are sorted in descending order of Quantity as required.
How it works...
The sort option by default works at the detailed level. This means the non-grouped items are sorted by the specified criteria within their own groups.
Here we want to sort the product lines that are grouped (not the detailed items). In order to sort the groups, we need to define a more advanced sorting using the Edit Layout Sorting options shown in this recipe.
There's more...
You can also define sorting for the whole list report from the Edit Layout Sorting dialog box. You can use different items and ordering for different groups and details.
You can also choose to sort certain groups by the data items that are not shown in the report. You need to bring only those items from source (model) to the query, and you will be able to pick it in the sorting dialog.