Summary

This article describes the steps necessary to add the two types of fields to a report and provides examples for the Formula Field type. 

Table of Contents

• Add Fields
  • Add Data Fields
  • Add Formula Fields
  • Calculate Age Sample 
    • Answer Option Stored Value
  • Edit Fields
• Expression Operators
  • Operators
    • Examples
  • Comparison
    • Examples
  • Boolean
    • Examples
  • Fct Group
  • Functions
    • Examples
  • Properties
    • Examples

With Fields you can specify the data points that should be included in the report. This page describes the steps necessary to add the two types of fields to a report and provides examples for the Formula Field type.

Add Fields

The number of fields allowed in an Ad-Hoc report is limited to 150. Reports having more than 150 fields will need to be modified before they can be executed.

Two types of fields can be added to the report:

• Data Fields - Questions in the subject’s casebook. Each question is represented by the question text associated with it. The full question text and question ID will be displayed in hover text.
• Formula Fields - Custom expressions created using path language syntax. These expressions can be entered manually or with the Expression Builder. Formula fields can also be used to display the stored value of an answer option versus the displayed value.

Add Data Fields

1. In the ‘All Fields’ table, check the check box beside all questions that should be included in the report.
2. In the All Fields table, click the ‘Select’ button.
3. To change the order in which the fields are presented within the report, hover the mouse over the field until the cursor appears.
4. Click and drag the field up or down to change its location in the list.
5. Repeat steps 1-4 for any additional Data Fields.

Add Formula Fields

1. In the ‘Fields for Report’ table click the ‘Create Formula Field’ button.
   • The Expression Builder will appear.
2. Name: Enter a name for the expression.
3. Data Type: Select the data type for the expression from the list of available options.
   • Example 1: For an expression calculating the days between the Signed Consent date and Screening Date, the ‘Date’ Data Type should be selected.
   • Example 2: For an expression that combines the subject’s Screening Number and Subject Initials into a single field, the ‘String’ Data Type should be selected.
4. Complete the expression by reviewing the Expression Operators section and choosing the appropriate expression operators.
5. When all fields and aggregators have been added, click the ‘Next’ button to continue to the Properties page.

Calculate Age Sample 

The following example shows the use of expression operators to create a formula field to calculate the subject age. This expression returns the number of years between date of birth and informed consent date for the subject. The correct file paths must be selected from the 'All Fields' menu on the left. These file paths are then separated by a comma and surrounded with parentheses. Both 'Number' and 'String' can be selected for the Data Type. The function DateToString( Date ) must be selected. 

Answer Option Stored Value

To display an answer option’s stored value versus the display value, create a Formula field, enter a name, select the data type of the stored value and have the path of the question as the Expression.

Edit Fields

To edit existing fields, click the Edit icon to the right of each field name.

If the field is a standard data field, the Modify Field Name window will display showing the current field name (column header) based on the settings chosen for the report, a field to modify this name, and the Question Expression, which refers to the location of the field within the study configuration.

For Formula Fields, the edit icon will trigger the display of the Expression Builder window so that the formula field can be modified as needed.

Expression Operators

Expression operators help to simplify the expression-writing process by allowing you to select from a list of available operators that are then inserted into the expression. Each expression operator can be used in different ways and there is no minimum set of expression operators required to create an expression.

Operators

The basic operators are used on numbers and strings.

• Add - Adds two numbers together or combines two strings
• Subtract - Subtracts one number from another
• Multiply - Multiplies two numbers together
• Divide - Divides one number by another

Examples

Comparison

The comparison operators return Boolean values and allow for numeric question types to be compared.

• equal – Returns true if the two values being compared are equal
• not equal – Returns true if the two values are not equal
• greater than – Returns true if the first value is greater than the second value
• less than – Returns true if the first value is less than the second value
• greater or equal – Returns true if the first value is greater than or equal to the second value
• less or equal – Returns true if the first value is less than or equal to the second value

Examples

Since Comparison operators are not used on their own when creating Formula Fields, the following examples use Comparison operators within Common Functions.

Boolean

Boolean operators support the use of AND, OR and NOT to create more complex comparisons. Boolean operators return Boolean values.

• And – Returns true if the expression on both sides of the ‘And’ operator is true
• Or – Returns true if the expression on at least one side of the ‘Or’ operator is true
• Not – Returns true if the expression would return false on its own

Examples

Sinc#E8F0FDe Boolean operators are not used on their own when creating Formula Fields, the following examples use Comparison operators within Common Functions.

Fct Group

Function Groups classify the available functions into different groups. By selecting a group, you can change the list of functions available in the Functions drop down list. The following functions groups are available:

• Common
• String
• Math
• Date
• Partial Date
• Date Calc.

Functions

With functions you can perform more complex actions on fields using date calculations, conversions and conditional statements.

Additional information for each function can be accessed in the Expression Operators window by clicking the icon. Clicking the icon will bring up a window containing a description of the function and the function’s return type.

Examples

Properties

Properties are used to specify that a certain medical code, metadata status or attribute should be used. The following list describes the properties available for use within expressions.

• Code – Displays the medical coding property (code, name) of the indicated code level (LLT, SOC, MP, etc.).
• Value – Shows the displayed value of a response. This feature is useful when using a formula field to rename a column.
• Name – Displays the entire question text.
• Type – Displays the question type
• Alias – Displays the question alias
• Query Status – Displays the query status of specified question
  • C – Clean
  • O – Open Query
• Alert Status – Displays the alert status of the specified question
  • C – Clean
  • Y – Close with Reason
  • X – Closed
  • O – Open with Reason
  • V – Open alert (violation) with no reason for override
• SDV Status – Displays the SDV status of the specified question
  • Y – Yes
  • N – No
• Sign State – Displays the eSignature status of the form on which the specified question resides
  • Y – Yes
  • N – No
• Freeze State – Displays the freeze status of the specified question
  • F – Frozen
  • U – Unfrozen
• Lock State – Displays the locked status of the specified question
  • L – Locked
  • O – Open
• Answer State – Displays the answer status of the specified question
  • C – Complete
  • B – Blank
  • I – In Progress
• Id – Displays the ID of the question
• Record Status – Displays the subject status for the specified subject

Examples

Need more help?

Please visit the Fountayn Contact Information page.