Defining Custom Sorting Order for an Attribute

You can define custom sort orders on an attribute through changes to the logical data model.

Sorting data by alphabet or numeric value is not always the most effective way, particularly when data has an inherent order that is understood by users. For example, suppose your CRM system or support ticket system contains the following workflow stages:

1. New
2. Open
3. Pending
4. Closed

These items have an inherent, obvious order, which is not alphabetical. By default, GoodData sorts them as shown in the following example:

In the following example data.csv file, the status values are bucketed according to age in days, creating an implicit ordering if you sort by the Age fact value:

Status,Age
New,140
Open,182
Pending,198
Closed,270

The intuitive way to manage this kind of sorting is to drag rows in the report to sort the data Excel-style. However, this method is not a recommended practice in an analytical system; data can change, new statuses can emerge, and other changes can cause you to revisit all of your manual sorting.

Instead, the appropriate place to solve this sorting issue is in the data layer. In this case, you can insert another column in the dataset called Status Ordering. The data.csv should look like the following:

Status,Status Ordering,Age
New,1,140
Open,2,182
Pending,3,198
Closed,4,270

In CloudConnect, this low-impact change can be performed by completing the following steps:

Steps:

1. Open the CloudConnect project containing the data model that you want to modify.

2. Open the *.ldm file in the project.

3. In the dataset, select Edit.

4. In the Attributes and Facts window, click the Show Datatypes checkbox.

5. To add the Status Ordering field, click the Plus (+) icon at the top of the window. Select Add Label For > Status.  Label this new fact Status Ordering.

6. For the Status Ordering label, click the Datatype column. Click the Browse… button.

7. In the Select Datatype window, click the Integer (INT) radio button.

   To set up correct sorting, you must specify the INT datatype for the label. Otherwise, sorting is based on alphabetical principles, so the value 12 is sorted before 2, for example.
   
   
     

8. Verify that Sorting Order has been set to be an Integer (INT) value. Your dataset should look like the following example:  

   
   
     

9. Click on the Status attribute detail:  

   
   
     

10. Select the sorting Label to Status Ordering:  

    
    
      

11. Save the file.

12. In the Server Explorer tab, verify that the working project is the appropriate GoodData project.

13. To publish the updated logical data model to the current working GoodData project, click Publish model to server.

14. This change should be a low-impact change.

At this point, the Status attribute sorts naturally as expected with GoodData sorting all rows according to the Status Ordering column in your source data: