In a report grid we can sort rows, columns, and page-by elements. To sort on a single column, simply right-click on the header and select Sort | Ascending or Descending from the context menu that appears. If you want more complex sorting, you can use the Advanced Sorting window.
Prepare a new report with the following objects:
- The Country attribute on page-by
- The Product Category attribute on rows
- The City attribute on rows
- The Sum SalesAmount from FactInternetSales metric on columns
Run the report; by default, the cities are ordered alphabetically.
We want to sort the cities in reverse order based on the sales amount inside each category:
- Go to the Data | Advanced Sorting menu.
- In the Sorting window, the Rows tab is displayed by default, click on the Add button. A new line is added automatically with the leftmost attribute that appears in the grid, in this case Product Category and the sort order is set to Ascending (you may have to enlarge the columns a little to see the field contents). Leave this row as it is.
- Click again on the Add button to create another line. This time in the column Sort By, change the default City value to Sum SalesAmount from FactInternetSales, and in the column Order set the value to Descending.
- Click on OK to confirm.
- Note that the categories are now sorted alphabetically and inside each category the cities are in descending order of sales amount.
- Save the report as
20 Cities sorted by SalesAmount.
The sorting is done by the Analytical Engine on the Intelligence Server, not on the database. Switching to SQL View, you can see that the SELECT statement doesn't change (no ORDER BY clause is created).
Use the Sorting window to order columns or page-by elements also. In this last report, for example, France appears after Germany when looking at the drop-down list. To correct this:
- Open Data | Advanced Sorting.
- Click on the Pages tab.
- Click on the Add button to insert a new line, leave the defaults and hit OK.
Now France appears before Germany.
- Save and close this alphabetically (and politically) correct report.
If you try to sort the 19 Products Sales on Internet report (from the previous recipe) in descending order of sales amount, you'll notice that the nulls are displayed before any other value: sometimes this is not what we'd expect.
In MicroStrategy, null values come first by default; to change that while you're in Grid View:
- Go to Data | VLDB Properties and expand the Analytical Engine folder in the left tree.
- Click on the Display Null On Top option and uncheck the Use default inherited value box.
- Select the first radio button Display NULL values in bottom while sorting, as shown in the following screenshot:
- Click on Save and Close; the report will be re-executed and the NULL values sent to the bottom of the grid.