Another common question that generally comes up is how do we change the sort order in Dashboard prompts. Generally, sort order is determined by the sorting column which you have specified in the repository. If you do not have any alternative sorting column specified in the repository then the default alphabetic sort is done on non-numeral columns and numeric sort is done on numeric columns. If you are not sure what i mean, lets create a simple dashboard prompt on the column REGION_NAME which does not have any sort order specified in the Business Model Layer.
As you see above, the dashboard prompt by default sorts it alphabetically. Now, how do we make it to sort in the descending order. In order to do this, go to the dashboard prompt. Convert the Show Results from “All Values” to “SQL Results”. Then enter the following SQL.
SELECT GEOGRAPHY.REGION_NAME FROM SH2 ORDER BY GEOGRAPHY.REGION_NAME desc
This will automatically sort the results in the descending order.
Now, the next question would be how do we define our own sort order. In order to do this, you would need another column with one to one mapping with the REGION_NAME. Any change in granularity will result in duplication. In our example, lets try sorting the same by REGION_ID. Edit the dashboard prompt and enter the below SQL
SELECT GEOGRAPHY.REGION_NAME FROM SH2 ORDER BY GEOGRAPHY.REGION_ID
This will again automatically sort the results based on another column of the same granularity.
The above can be useful in quite a few situations when you dont have a sort order specified in the repository.
Posts
