Demystifying the Decode() Function

When I started learning to use OpenClinica, I was mystified by all of the things there were to learn. One year later, I continue to be surprised by the number of new skills and tricks I am discovering – whether in the form of existing functionality, new features (EventAction Rules!) or community contributions.

In May, I was putting together a workshop for OC14, OpenClinica’s Global Conference, on best practices for building studies in OpenClinica, and a colleague suggested I spend some time explaining all of the functions in our platform that can be used for calculations.

Alright, I thought, that should be easy. Functions for calculations in OpenClinica, while useful, are pretty simple. After reviewing the information found in the CRF Excel template for calculations, this is what I found:

Decode() screenshot

Sum()? Got it.

Min() and max()? Easy.

Stdev()? Fine.

Decode()? … wait, what?

A vague memory of our trainer’s explanation of the decode() function at OpenClinica’s Central User Training flashed through my mind. My guess is that there are users out there who don’t even know this little gem exists. Some of you may have seen it and simply dismissed it.

So what exactly is the decode() function?

The decode() function allows you to create an IF-THEN-ELSE type of statement. An item is compared to a list of search values, one by one. If the item matches a search value, then a corresponding result value is returned.

Here is your basic format:

decode(ComparatorItem, SearchVal1, ResultVal1, SearchVal2, ResultVal2, … DefaultValue)

The ComparatorItem is compared to each SearchVal in the list, one at a time:

  • The ComparatorItem is first compared to SearchVal1.
    • If the ComparatorItem = SearchVal1, return ResultVal1.
    • Otherwise, if ComparatorItem = SearchVal2, return Result2
    • And so on…
  • If none of the values in the Search List match the ComparatorItem, then the DefaultValue is returned.
    • The DefaultValue is optional, and can be omitted entirely.

The decode() function is probably best explained using an example. I will start by illustrating the general format of the function, and then move on to a few examples that may prove useful in your OpenClinica studies.

Let’s say users are entering a cardinal direction initial (i.e. N, S, E, W) and you want to decode the initial to the actual cardinal direction name (i.e. North, South, East, West). Your decode function would resemble the following:

decode(DirectionInitial, N, North, S, South, E, East, W, West, Unknown)

decode() function

Upon clicking save, the entry in the DirectionInitial Item will first be compared to “N” and if there is a match, “North” is returned. Otherwise, the DirectionInitial Item is compared to “E” and if there is a match, “East” is returned, and so on. If DirectionInitial Item does not match any of the options (N, S, E, or W), then “Unknown” will be returned.

Let’s look at a few examples that may be more applicable to your OpenClinica studies…


If you have a Vital Signs or Physical Exam CRF, you may be capturing the Subject’s temperature. In the case report form, there can be two options for temperature units (Celsius or Fahrenheit), but the statistician may be interested only in a final temperature in degrees Celsius. To please both your data entry person and your statistician, you can create a field to capture the Temperature, and then create another Units field to record either Celsius or Fahrenheit. The final Temperature item will contain the decode function, which would resemble the following:

decode(TemperatureUnits, Celsius, Temperature, Fahrenheit, ((Temperature-32)*(5/9)) )

If TemperatureUnits is equal to “Celsius,” then return the value recorded in the Temperature Item. Otherwise, if TemperatureUnits is equal to “Fahrenheit,” then convert the value recorded in the Temperature Item to Celsius.

decode() function example


Additionally, the decode() function is a great tool when scoring responses. For example, you may have an eligibility questionnaire with response options Yes, No, and NA, corresponding to response values 1, 0, and 99. You would like to count the number of ‘Yes’ responses. However, a simple sum() function will not work because if ‘NA’ is selected, ‘99’ will be included in the calculation. This can be solved by combining the decode() function with the sum() function.

First, you will need to determine the decode function for each item. In this case your function would be as follows:


If Yes is selected (value of 1), then 1 will be returned. If No is selected (value of 0) then 0 will be returned. If NA is selected (value of 99) then 0 will be returned.

Note: We only want to calculate the number of ‘Yes’ responses.

This can then be combined into a sum() function. If you have three eligibility items in your CRF, then your decode function would be as follows:

sum(decode(Item1, 1,1,0,0,99,0), decode(Item2, 1,1,0,0,99,0), decode(Item3, 1,1,0,0,99,0)

decode() function examples

Interested in seeing any of these examples in action?
You can download this CRF Excel template which includes each of the three above examples.

Also, if you found another useful way to use this function, feel free to comment and share your tips with us!

OpenClinica 2.5 Release

We are getting very near to the release version 2.5 of OpenClinica, representing a major milestone in the development of the popular open source software for electronic data capture (EDC) and clinical data management. The release contains a number of exciting improvements, including:

–    Rules engine for executing cross-field or cross-form edit checks and triggering notifications to users
–    Scoring calculations (derived values) in case report forms (CRFs)
–    New user roles for monitors and data managers
–    Improved monitoring/query management tools and discrepancy note reporting
–    Electronic signature capabilities
–    Ability to copy and edit previously created datasets
–    Improved auditing and logging capabilities
–    Simpler CRF upload
–    Improved support for CDISC ODM v1.3
–    Support for Oracle DB Backend out of the box.
–    Minor UI and application fixes from OpenClinica 2.2
–    Improved performance and scalability

Over the past 6 weeks the core development team has been putting OpenClinica through a rigorous, validated, QA and testing procedure. There are roughly another 3 weeks of documented testing ahead of us. If you would like to try the latest beta version of the application, please download it from

Thanks to everyone in the community who has contributed to this release and provided feedback during the alpha and beta periods!

As we get closer and closer to the production release of OpenClinica 2.5, stayed tuned to this space for additional updates.