Using reference codes to convert database values or apply custom sorting

You can represent the values stored in a database into a more meaningful form by using reference codes. For example, if male and female are stored as M and F, you can create reference codes as Male and Female respectively.

By default, data returned in a query is displayed in alphanumeric sort order, but you can apply customized sorting to a field. For example, if your result set includes incident statuses, the list will be ordered as follows:

• Assigned

• Cancelled

• Closed

• In Progress

• New

• Pending

• Resolved

For the incident management lifecycle, you can display the data in the following order:

• New

• Assigned

• In Progress

• Pending

• Resolved

• Closed

• Cancelled

To achieve this, create reference codes for the values and assign a sort order to those codes. 

The following video (4:50) discusses the process for creating a reference code:

 https://youtu.be/Tw5DcIi_UHc

To create reference codes

Perform the following steps to create to new reference code that lists and sorts all existing incident statuses:

1. As the Reporting administrator, on the Smart Reporting console, select Browse > Views.
2. Double-click the view for which you want to create reference codes.
3. If the view does not appear automatically:
   1. From the View Summary dialog box, click Edit View.
   2. Select the Edit radio button.
   3. Click Edit.
4. From the top menu, select Prepare.
5. In the left navigation pane, from the list of available fields, under Incident Details, right-click Status, and then select Edit Format.
   
6. In the Field Setting pane, in the Format field, select Reference Code .
7. Click the Create New Type link under Reference Types. 
8. In the Create Reference Code window:
   
   1. Click the Values tab.
   2. Click Pre-populate from Database.
      A list of pre-populated incident statuses is displayed.
      
   3. Under Add new values, add a new code and description and click the plus sign.
      
      In this window, you can also edit an existing reference code or delete an existing reference code. You must give the reference code you are defining a name so that the set of translations can be reused across multiple views. 
   4. Click the status name and enter a sort order for each status.
      For this, ensure you have enabled Custom Sort on the Details tab.
      
   5. Click Save. 
9. Click Save.
   

To edit a reference code

1. As the Reporting administrator, on the Smart Reporting console, select Browse > Views.
2. Double-click the view for which you want to create reference codes.
3. If the view does not automatically appear:
   1. From the View Summary dialog box, select Edit View.
   2. Select the Edit radio button.
   3. Click Edit.
4. From the top menu, select Prepare.
5. In the left navigation pane, from the list of available fields, under Incident Details, right click Status, and then select Edit Format.
6. In the Field Setting pane, click Edit under Reference Type.
7. Make the required changes such as changing the order of the status under Values tab and click Save.