Like what you see? Have a play with our trial version.

Overview

Numeric and Text based data displayed in a report can be translated from values held in the database to more meaningful values by adding a reference code to a report field. This mapping is called Reference Codes. The Ref Code Manager page allows administrators to edit existing Reference Codes created through the Model.

Reference Code Use

This type of mapping should be used if:

  1. You wish to translate a value held in the data source into business terminology. For example if male and female are stored as M and F you may want to translate these into ‘Male’ and ‘Female’

  2. If you wish to apply customized sorting to a field. For example: data returned in a query is usually displayed in Alpha / Numeric sort order. If your result set included: Admin Staff, Managers and Executive Management your report may look like this:

    Admin Staff500
    Executive Management10
    Management50

    Hierarchically your data may actually need to be displayed in the following way:

    Executive Management10
    Management50
    Admin Staff500

    This can be achieved by creating reference codes for the values and assigning a sort order to those codes.


Reference Code Manager

Navigate to Administration > Admin Console > Reference Codes to access a list of available Ref Codes.

From here, simply click on the reference code you wish to update to open it in the manager. The sections below cover the various tab on that page.


Details

The first tab shows the Code name, and some configuration features that users can set.

  1. Custom Sort: this allows you to apply a sort order other than alphabetical. For example, you could sort months chronologically by setting January = 1, February = 2, etc.

  2. Custom Colours: this allows you to assign colours to the various values of the field. These colours can be used in charts and filters to ensure that values are easily identified and consistent across reports.

  3. Custom Images: this allows you to assign images to the various values of the field. These images can be used with In List filters in order to aid the user in selecting values. The image will be displayed next to the value in the list.

  4. Trim Values: this allows leading and trailing whitespace to be removed from the values of the field.

  5. Delete: this allows you to delete the reference code. This will reset the formatting on fields that are using this reference code.


Values

Here is where you define the values for the Code and any other options enabled in Details (above).

  1. Code: this is the value found in the database that will be replaced with the description value.

  2. Description: this is the value that will be displayed in the report/filter rather than the code.

  3. Sort #: this is the order, the value will appear when sorted.

  4. Colour: this is the colour applied to the value, displayed in charts and filters (when selected).

  5. Image: this is the image displayed next to the value in filters (when enabled).

Here you can also add additional codes that you may not have included when originally defining the reference code. Simply enter the values required and click the plus (+) button (as depicted below) or import them via a CSV file.


Translation

If you have a multi-language deployment of Yellowfin, you can choose a language you want the reference code translated into. (If you can’t see this tab, then your system does not have a multi-language set up.)

The translated values can also be edited.


Refresh

Allows you to set up how to update the code values. Provides you with three options:

  • Manually: allows you to manually enter each value, or import from a CSV file.

  • Scheduled Sync: allows you to periodically sync values from a specific data source. You can schedule how often to perform this sync. Note that with this option, you’re only limited to one field and may require manual updating.


    Follow these steps to set up a schedule to sync values from a data source.

    1. Choose the Schedule Sync method for updating values.

    2. Enable the Replace existing values toggle to replace the values with new ones. If this toggle is disabled then new values will be added, without removing or updating the existing ones. 



    3. Specify the data source, view and field from where to fetch the values.



    4. Set up a schedule for how frequently the values should be updated automatically. For example, you can schedule the system update the field weekly. 



    5. You can also specify an admin user who should be notified when values are updated. 



    6. Click Save to perform the update.

    7. Note that with this option, you’re only limited to one field; in this case the code and description will be the same. You will then need to manually update the description in the Values tab. 


      You could also run this update task through the Schedule Manager.


  • Scheduled Query: allows you to periodically update values using an SQL query. This is the easiest way to populate the field, but requires SQL knowledge. 

    Here’s what to do if you’ve chosen to add values to the reference code field using an SQL query.

    1. Choose the Schedule Query method for updating values.

    2. Enable the Replace existing values toggle to replace the values with new one. If this toggle is disabled then new values will be added, without removing or updating the existing ones. 



    3. Select data source from where to import values.

    4. Provide the SQL query. 

      Note that the columns should be specified in the exact order in your query, e.g. code, language (if you have a multi-language system), description, and sort order. (The names of the column need not be exact.)

    5. You can use Query Help for assistance in writing your query.



    6. Click the Test Query button at the bottom to validate your query. If the query is valid, a success message will appear with an example of its result. (The button will also change to Save.) 



    7. Set up a schedule for how frequently the values should be updated automatically. For example, you can schedule the system to update values weekly. 



    8. You can also specify an admin user who should be notified when values are updated. 



    9. Click Save to perform the update. (In case of the query method, this can only be done if your query is valid.)


      You could also run this update task through the Schedule Manager.


Usage

This tab shows the current View usage of the code. This is ideal if you want to delete a code, but want to check where it is being used.





Create Reference Code

How to create a new reference code:

  1. Click the Add button under the list of Reference Codes on the admin console page.



  2. Establish the configuration settings of the code. See the Details tab explanation on which features to enable.

  3. Click on the Values tab to add values to your reference code field. You can manually supply the values or click on Populate from CSV to import values from a file.



  4. You can also set up a schedule to update reference code values periodically, by clicking on the Refresh tab. To learn more about this, refer to our updating section here.

  5. Click Save.



  • No labels