Adding Data Set Attributes

To add data set attributes:

1. From Quick Links, select Data Sets.
2. Select a data set, and then click Edit .
3. Select the Attributes tab, which has the following columns:

   • Key Identifier

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

     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.

     Note:

     Supplemental Data Manager does not support the Calculated attribute as a Key Identifier attribute in data sets. If you select the Calculated attribute as a Key Identifier, you may experience unexpected behavior and errors in data sets.

   • Assign Workflow

     Only attributes with Key Identifier On can be assigned workflow. Assign workflow On means that you want to select members from this dimension to assign workflow to each preparer. To assign workflow, the attribute must be a dimension, because you assign a workflow from a predefined list.

   • Name

   • Dimension Name

   • 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

4. 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.

        Note:

        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.

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

   • Name

   • Description

   • Specify the Attribute Type:

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

       Note:

       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.

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

   • Data Type

     Select one:

     • Date

     • Date and Time

     • Integer

     • List

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

     • Number

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

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

       • Select Display as Percentage to display a percent sign.

       • Select 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, 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.

     Note:

     You can change the default value during data entry.

6. Click OK to save the attribute.

   Note:

   You must save the attribute before including it in a calculation.

7. Do One:

   • 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 is an example of how to add a calculation attribute:

     1. Create a data set with these attributes:

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

        TextInput: Input and Data Type: Text.

     2. Save the data.

        Note:

        You must save the attribute now to use it in TextCalc.

     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

        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

           Note:

           <Value> changes to {TextInput}.

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

           Note:

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

        Example INSTRING({TextInput}, 'tion')

8. To add a calculation expression:
   1. Select a data type and calculation type as described in the following table:

      Table 15-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. Note: You must have saved the List attribute values.	Return an attribute value based on the assignment to a List member.
      For all data types	Conditional	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.
      List	Assign List To Value	Based on the value of an attribute, return the related member from the list.
      Numeric, Integer	Formula	Calculate an attribute using common mathematical expressions. Example: (A+B)/C
      Numeric, Integer	Round	Round attribute to the specified number of digits. The default is 2.
      Text	Concatenate	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	Scripted Note: This feature is available only in 11.1.2.4.100 (patch 20307212). For information about new features and updating to release 11.1.2.4.100, see My Oracle Support https://support.oracle.com	A free-form scripted calculation. Scripted is available for attributes of type Integer, Multi-line Text, Number, or Text. See step 9.

   2. Click OK.
9. Scripted functions with examples:

   • 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.

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

     Example: EDate(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. For example, this function creates a value of type Date from the function, so DATE(2018, 5, 31) would convert to May 31st 2018.This can then be used in the Date Difference function, to pull the difference in days between a date, such as an end date, and this specific date.

     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

     DAY(<DATE>)

   • 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>)

     Example:

     IF_THEN_ELSE( {Risk Rating} = 'Low', 'Good',
     IF_THEN_ELSE( {Risk Rating} = 'Medium', 'Better',
     IF_THEN_ELSE({Risk Rating} = 'High', 'Best','Bad')))
     

   • Lowercase: Returns the value in lower case.

     LOWERCASE(<Value>)

     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>, <To Currency*>, <Rate Type*>, <Rate Period*>)

     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>, <Number of Periods>, <To Currency*>, <Rate Type*>, <Rate Period*>)

     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>)

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

     PRIOR(<Value>, <Number of Periods Prior*>, <To Currency*>

     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>, <To Currency*>

     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.

     UPPERCASE(<Value>)

     Example: UPPERCASE( {Name} )

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

     YEAR (<DATE>)

10. Click Save, Save and Close, or Close.

    Note:

    Changes to data set attributes apply only to subsequent data collection periods.