Using a calculated field to create a custom sorting column

This article is only applicable to clients who have subscribed to Vault Business Intelligence.

This article will show you the steps to create your own order for sorting. 
This may be necessary when you are trying to sort by a field that is treated by Vault BI as text, such as Potential Outcome Risk Score in the Events view as the example used in this article.

Potential Outcome Risk Score is seen as a text field because it contains non-numerical characters (i.e. "1 - Low"). For this reason, when sorting is applied to this field, it is sorted alphabetically rather than numerically.
When seemingly numeric values are sorted alphabetically, the numbers 1 - 25 are sorted as follows: 1, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 3, 4, 5, 6, 7, 8, 9.

Here is what you can do to workaround this, and allow the the data to be sorted by numerical value.

1. In the Report Builder, press the + button at the bottom of the fields list to add a calculated field
   
   
   

2. Name the field - I've used "Score Order" in this example. 
   Press Case and then When; this will open a new window. 
   
   
   
   

3. In the When section, select Potential Outcome Risk Score where indicated.
   
   
   
   
4. Next, select Equal as the Operator and press Add.
   
   
   

5. Enter your first risk score value and press Add. 
   Hint: before starting, add Potential Outcome Risk Score to the report columns so you can see the values
   
   
   

6. Now move onto the Then section - all you need to do here is type 1 and press Add.
   This assigns the number 1 to "1 - Low" which you will be able to use for ordering.
   
   
   
   

7. You will end up with this.
   Press Save. 
   
   
   

8. Press the When button again and repeat the above process for "2 - Low", "3 - Low", and so on, right through all of your different risk scores. 
   
   
   

9. Once you have added each of the When statements, click the End button.
   Here is a screenshot of an abbreviated version of the process - notice the END at the end.
   Save the calculated field. 
   
   
   
   

10. Under the Calculated Fields category, your newly created field will appear - you'll notice it is a metric field. 
    
    
    
    
11. Drag the field into the report.
    
    
    

12. You can now sort by the "Score Order" column.
    The order of the data will change accordingly.
    
    
    
    
    

13. You can then Hide the field to tidy the report up. The sorting order will remain in tact.
    
    
    
    

Done! Add the rest of your columns to the report and you're good to go.