Defining Custom Sorting Order For An Attribute

Related Tags: data modeling reporting data analyst

Through changes to the logical data model, you can define custom sort orders on an attribute.

Alphabetical or numeric ordering isn’t always the most appropriate way to sort data, 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. However, by default, GoodData does not handle this ordering gracefully:

Report With Wrong Sorting
Report with Wrong Sorting

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 you wish 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.

    NOTE: 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.


    Selecting datatype for new label
    Setting the datatype
  8. Verify that Sorting Order has been set to be an Integer (INT) value. Your dataset should look like the following:
    Modified dataset
    Modified dataset
  9. Click on the Status attribute detail:
    Sort label
    Attribute details
  10. Select the sorting Label to Status Ordering:
    Status Ordering Setup
  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:

Report With Correct Sorting
Report with Correct Sorting