Adding Collection Attributes

Attributes are user-defined fields defined centrally by administrators and used in many places. You can specify different value types for attributes: Date, Date and Time, List, Number, Text, and True or False.

For collections, you can add date calculation attributes such as a Start Date and End Date for a data collection period.

To add collection attributes:

  1. On the Home page, click Application, and then click Supplemental Data.
  2. Click the Collections tab on the left.
  3. Select a collection, and then click Edit.
  4. Select the Attributes tab, which has the following columns:
    • Key Identifier

      If no data exists for this collection, you can modify the Key Identifier and attributes.

      The Key Identifier and Assign Workflow check box option for Entity is selected by default. As you add attributes, you can select one or more as the Key Identifier.


      Supplemental Data Manager does not support the Calculated attribute as a Key Identifier attribute.

      If you select the Calculated attribute as a Key Identifier, you may encounter unexpected behavior and errors in collections.

    • Assign Workflow

      Displays the workflow defined in the collection interval. It is not editable.

    • Name

    • Dimension Name

    • Attribute Type (displayed if there are existing attributes)

    • Data Type—Date, Date and Time, Integer, List, Number, Text, True or False, Yes or No

    • Total—Enables you to specify the totaling method for the attribute:

      • Sum: Additive total

      • Average: The average of the rows with data. Rows without data are not counted in the denominator

      • Count: The count of the rows with data

      • None: No total is calculated

  5. Click Actions, then New, and then select:
    • Add Attribute: Go to step 5.

    • Add Attribute from Dimension:

      1. Select a Dimension.

      2. Select attributes from the Available Attributes list and Move them to the Selected Attribute list.

        The system includes the key attribute of the dimension as a Selected Attribute. You cannot clear the key attribute.

      3. Click OK and then Save or Save and Close.

  6. If you selected Add Attribute, on Create New Attribute, enter the Properties tab information:
    • Name

    • Description

    • Specify the Attribute Type:

      Changing the Attribute Type overrides previously specified Validations or Calculations. You cannot change this setting after the attribute is created.

      • Input: Input is the default, and the Validations tab is enabled.

        For periods that have been opened and form instances created, the modified validation rule does not apply. Changes made apply only to new form instances.

      • Calculated: If type is Calculated, then the Calculated tab is enabled.

    • Data Type

      Select one:

      • Date

      • Date and Time

      • Integer

      • List

        Click Add and enter values for the attribute. List is local to the collection and can't be shared among other collections.

      • Number

        If you select Number, select formatting options to override the defaults set in the Preferences section of System Settings.


        You can only enter numeric values for this attribute.

        • For Decimal Places, enter the number of decimal places to display.

        • Select Display as Percentage to display a percent sign.

        • Select Use Thousands Separator to display a thousands separator (for example, 1,000.00). The system displays the thousands separator symbol for the user locale.

        • In Currency, select the currency, for example, (INR)

        • In Negative Number Format, select how to display negative numbers; for example, (123).

        • To scale a number, in Scale, select from 1000 to 1000000000000

      • Text (255 characters maximum)

      • True or False

      • Yes or No

    • Required

      If the attribute is required, you must enter a value in this field during data entry.

    • Use Value

      If Use Value is populated, the system applies the value entered by the designer for any record created by the user.

      You can change the default value during data entry.

  7. Click OK to save the attribute.
    You must save the attribute before including it in a calculation.
  8. To continue adding an attribute:
    • If you selected Input for the Attribute Type, then select the Validations tab.

      To add a conditional expression:

      You can create a special validation rule for the value entered by the user.

      1. Click Add.

      2. Select an Operand and enter Value1 and Value2.

      3. If you need a second condition, select from the following lists:

        • Conjunction; for example, And, Or

        • Operand; for example, Equals, Between, Does not Equal, Greater than, Is blank, Is not blank, Less than, Not between

        • Value1 and Value2

      4. Click OK.

    • If you selected Calculated for the Attribute Type, then select the Calculations tab.

      The following section is an example of how to add a calculation attribute.

      1. Create a collection with these attributes:

        Int1 Input and Data Type: Integer. Click OK, and from Edit Collection, select Int1 as a Key Identifier.

        TextInput: Input and Data Type: Text.

      2. To use the attribute in TextCalc, save the data.

      3. Create an attribute TextCalc: Calculated and Data Type: Text.

      4. On the Calculations tab, enter the following fields:

        1. Calculation Type: Select Scripted.

        2. Add Function: Select TextLocation, and then click Add.

        3. INSTRING(<Value>, <Value To Search>) is added to Calculation Definition.

        4. Click <Value> and either enter a value (enclosed in single quotation marks and case sensitive), or, in Add Attribute, select an attribute, TextInput, and then click Add.

          <Value> changes to {TextInput}.

        5. Replace <Value to Search> with 'tion'

          Ensure that you replace <xxx> with single quotation marks: 'xxx'.

        Example INSTRING({TextInput}, 'tion')

  9. To add a calculation expression, select a data type and calculation type as described in the following table, and then click OK.

    Table 29-1 Data Type and Calculation Type

    Data Type selected on Properties Tab Calculation Type Description
    For all data types

    Assign Value to List, and then select the Attribute value.

    You must have saved the List attribute values.

    Return an attribute value based on the assignment to a List member.

    For all data types


    For a given attribute, return attribute value A if specified conditions have been met. If the conditions have not been met, return attribute value B.


    Assign List To Value

    Based on the value of an attribute, return the related member from the list.

    Numeric, Integer


    Calculate an attribute using common mathematical expressions.

    Example: (A+B)/C

    Numeric, Integer


    Round attribute to the specified number of digits. The default is 2.



    Paste together text attributes. This includes literal strings and automatically converting non-text attributes to strings.

    Example: First_Name+““+Last_Name+“:”+Birth_Date

    Integer, Number, Text


    See the following section:"Scripted Functions".

    A free-form scripted calculation. Scripted is available for attributes of type Integer, Multi-line Text, Number, or Text.
  10. Click Save, Save and Close, or Close.


    Changes to attributes apply only to subsequent data collection periods.

    Scripted Functions

    • Absolute Value: Returns the absolute value of a specified number. If a number is less than zero, the positive value of the number is returned. If the specified number is equal to or greater than zero, the specified number is returned.


    • Add Month: Returns a date offset a specified number of months from the starting date. The date will always fall in the specified month offset. If the starting date has a day value beyond what is in the offset month, the last day of the offset month will be used. For example, EDate (31-Jan-2017, 1) returns (28-Feb-2017). For Months, enter the number of months before or after the start date. A positive value for months yields a future date. A negative value yields a past date.

      ADD_MONTH(<Start Date>, <Months>, <Length>)

      Example: ADD_MONTH(DATE(2017, 2, 15) 3)

    • Average Prior: Averages a numeric amount over the prior X periods.

      AVERAGE_PRIOR(<Value>, <Number of Periods>, <To Currency*>

      Example: AVERAGE_PRIOR( {Balance (Reporting)}, '2', 'EUR'

    • Date: Returns a date value based on specified integer values for the year, month and day.

      DATE(<Year>, <Month>, <Day>)

    • Date Difference: Returns the difference in days, hours minutes, or seconds between two dates. For DATE 1 and DATE 2, the values TODAY and NOW can be used, which denote the current date (with no time component) and date-time, respectively.

      DATE_DIFF(<Date1>, <Date2>, <Type>)

      Example: DATE_DIFF('TODAY', {Preparer End Date}, 'DAYS') or DATE_DIFF({Preparer End Date}, 'NOW', 'HOURS')

    • Day: Returns the day value of a date as an integer number


    • Extract Text: Returns the substring within the value, from the positions specified.

      SUBSTRING(<Value>, <Location>, <Length>)

      Example: SUBSTRING( {Name} , 5, 10)

    • If Then Else: Allows the user to insert a conditional calculation into the scripted calculation. IF_THEN_ELSE calculations can also be nested to support “ELSE IF” type calculations.

      IF_THEN_ELSE(<Condition>, <Value1>, <Value2>)


      IF_THEN_ELSE( {Risk Rating} = 'Low', 'Good',
      IF_THEN_ELSE( {Risk Rating} = 'Medium', 'Better',
      IF_THEN_ELSE({Risk Rating} = 'High', 'Best','Bad')))
    • Length: Takes a text value as a parameter and returns an integer which is the number of characters in the text. If the value is empty/null, the calculation will return 0.

      Length ({<attribute>})

      Example: LENGTH('Value') would return 5, and LENGTH({Name}) would return the number of characters in the name of the object.

      Use the calculation with SUBSTRING to extract the last four characters of a text value.

      SUBSTRING( {MyString}, LENGTH ({MyString}) - 4

    • Lowercase: Returns the value in lower case.


      Example: LOWERCASE( {Description} )

    • Maximum: Returns the maximum value from a list of attributes. There can be any number of parameters.

      MAX(<Value1>, <Value2>,<ValueN>)

      Example: MAX( TRANSLATE( {Source System Balance (Entered)}, 'USD', 'Accounting'), TRANSLATE( {Source System Balance (Functional)}, 'USD', 'Accounting'), TRANSLATE( {Source System Balance (Reporting)}, 'USD', 'Accounting') )

    • Maximum Prior: Returns the maximum value over the prior X periods.

      MAX_PRIOR (<Value>, <Number of Periods>)

      Example: MAX_PRIOR( {Balance (Functional)}, '6', 'CAD', 'REC')

    • Minimum: Returns the minimum value from a list of attributes. There can be any number of parameters.

      MIN(<Value1>, <Value2>,<ValueN>)

      Example: MIN( TRANSLATE( { Balance (Entered)}, 'CAD', 'REC'), TRANSLATE( {Balance (Functional)}, 'CAD', 'REC'), TRANSLATE( {Balance (Reporting)}, 'CAD', 'REC') )

    • Minimum Prior: Returns the minimum value over the prior X periods.

      MIN_PRIOR (<Value>, (<Value>, <Number of Periods>)

      Example: MIN_PRIOR( {Source System Balance (Functional)}, '6', 'EUR', 'Simplified')

    • Month: Returns the month value of a date as an integer number (1-12)

      MONTH (<DATE>)

    • Power: Raises one number to the exponential power of another.

      POWER(x,y) where x=BASE NUMBER,and y=EXPONENT and x and y can be attributes or calculations, as long as they are numeric.

      Example: POWER(3,4)=81


      Fractional values will reduce the number to its root. For example, POWER(27, 1/3) = 3 the cube root.

      Negative values will perform an inverse of the exponential calculation. For example, POWER(2, -2) = 1 / (2^2) = 1 / 4 = .25.

    • Prior: Returns the value of the specified prior period.

      PRIOR(<Value>, <Number of Periods Prior>)

      Example: PRIOR( {Source System Balance (Entered)}, '1', 'EUR'')

    • Round: Returns the value rounded to the decimal places specified.

      ROUND(<Value>, <Decimal Places>)

      Example: ROUND( ({Scripted Translate} /7), 4)

    • Sum Prior: Returns the sum of a value over the prior X periods.

      SUM_PRIOR(<Value>, <Number of Periods>)

      Example: SUM_PRIOR( {Balance (Reporting)}, '3', 'EUR')

    • Text Location: Returns the location of the substring within the attribute value, starting at 1 as the first position.

      INSTRING(<Value>, <Value To Search>)

      Example: INSTRING( UPPERCASE( {Name} ), 'TAX' )

    • Translate: Translates a currency attribute to a numeric attribute using a specified rate type.

      TRANSLATE(<Value>, <To Currency>, <Rate Type>)

      Example: TRANSLATE( {Balance (Entered)}, 'EUR', 'Acct')

    • Uppercase: Returns the value in upper case.


      Example: UPPERCASE( {Name} )

    • Year: Returns the year value of a date as an integer number.

      YEAR (<DATE>)