How to create Fiscal time-scaled values (TSVs) in custom SubObjects

Document ID : KB000021526
Last Modified Date : 14/02/2018
Show Technical Document Details

Summary:

The Clarity v13 Release notes indicate there is a new feature whereby the Clarity Administrator can create a Time-Varying attribute using 'Fiscal' Time periods. I looked at the Administration Guide and the Studio Guide and there is no sufficient information on how this new feature works. How do we create the new attribute, how can we configure it and where does the data get stored in the Database?

Clarity v13.0 Release Notes:

Section = Studio > TSV Improvements

Fiscal time-scaled values (TSVs) are available with this release. Instead of operating on the standard yearly calendar, fiscal TSVs are based on the fiscal calendar.
You can configure TSV displays based on a specific date for the Start Date attribute property.

Solution:

This feature was introduced in v13.0 and is available in all 13.x and 14.x releases.

Fiscal Time Periods are created and activated in a Financial Entity.

The Fiscal TSV attribute can only be created on a Sub Object for which the Master Object is associated with a Financial OBS and will have the period type and period date ranges for the TSV on the subobject.

When the TSV Fiscal attribute is created, a new time slice table is created for storing the sliced data.

The list of available Fiscal Periods shown in the Fiscal Date Picker is determined by the 'Fiscal Period Type' configured on the Entity Properties.

NOTE: Using Fiscal Time Periods is tied to using Financial Department OBS within an Entity.
There a some known issues with selecting a financial department OBS unit on the Idea Object in Clarity 13.1. When you select the Department OBS unit and save the value, the unit should be displayed with the name of the entity > /Entity Name/Unit Name. If this path does not appear in the saved value, the use of custom fiscal time varying attributes cannot be fully implemented because the application is not saving the Entity reference for finding the Fiscal Time Periods. It has been tested and verified that the financial department OBS field works in Clarity 13.3 and beyond and therefore, the use of custom fiscal time-varying attributes can be configured and fully implemented.

Configuration:

  1. Log in as an Administrator

  2. Administration, Studio : Objects, Click 'New' button

    Object Name = 'MasterObj'
    Object ID = 'master'

  3. Administration, OBS, Open a Financial OBS (a Department OBS linked to an Entity)

    In the 'Associated Objects' section, click 'Add' button and select the 'MasterObj'

    Figure 1

  4. Administration,  Studio : Objects, Click 'New' button
    Object Name = 'ChildObject'
    Object ID = 'child'
    Master or Subobject : Select Subobject , Select 'MasterObj' for the Master Object

  5. For the 'ChildObject', Attributes, Click 'New' button

  6. Create a TSV Fiscal Attribute for Units
    Attribute Name = myFiscalTSV_Units
    Attribute ID = tsv_fis_00001
    DataType = Time-varying
    Time-varying Type = Fiscal
    Time-varying Data Units = Number (Possible values = Number, Money, Percentage)

  7. Create a TSV Fiscal Attribute for Money
    Attribute Name = myFiscalTSV_Money
    Attribute ID = tsv_fis_02
    DataType = Time-varying
    Time-varying Type = Fiscal
    Time-varying Data Units = Money (Possible values = Number, Money, Percentage)
  8. Configure the Entity Fiscal Period Type and create active Fiscal Periods. The 'Fiscal Period Type' can be set to any type and later changed if needed.

    Figure 4

    Figure 5

  9. You will now see the 'Fiscal Period Type', 'Fiscal Start Date' and 'Fiscal End Date' attributes on the Master Object.
    Be sure to configure these three attributes on the master object views.

    Figure 6

    Figure 7

  10. On the Sub-Object List Column View, configure the TSV attributes on a virtual Time Scaled Value' column

    On Child Sub-Object, Views,  List Column [Fields], click 'New' button
    Select 'Time Scaled Value', click 'Next' button - configure the layout, click 'Save' button
    After the initial configuration is saved, the 'Allow Editing' option will now appear on the page - be sure to enable editing so the end-user can enter data into the grid

    Figure 8

    Create a Master Record and Child Records in the application

    Master Records

    Figure 9

    Click on 'myMasterRecord001' to open the properties page
    On the Properties tab menu, click on the link for the child record list view

    Child Records:

    Figure 10

Retrieving TSV Data:

Once data has been entered, reporting can be accomplished by creating user-defined portlets or reports. The following information will assist you in locating the TSV Fiscal Attributes. Fiscal TSV attributes are not available for configuration to create user-defined Time Slice Definitions. The data is stored in an internal 'insta-slice' slice request table. The only slice that is available is that which is stored in this slice table for the Fiscal Period Type used in the configuration. The slice data is stored as a daily amount. When creating a query to retrieve the full amount for the Fiscal Period, the number of days should be used to multiple the daily slice amount.

    1. Locate the name of the slice table for the TSV Fiscal Attribute created (i.e. Attribute ID = tsv_fis_00001) created on your subobject (ie. Subobject ID = child)

      SELECT ID, REQUEST_NAME, TABLE_NAME
      FROM PRJ_BLB_SLICEREQUESTS
      WHERE REQUEST_NAME LIKE 'child::tsv_fis_00001%'

      • Replace with the name of your subobject::attribute

      This should return 2 rows.

      Locate the table name for the internal 'insta-slice' request name (i.e. child::tsv_fis_00001::segment)

      The name of the table should start with 'ODF_SSL_' (row 1 in this example)

      Figure 15

    2. Create a query to see the data using this table (example)

      SELECT
      PRJ_OBJECT_ID subobject_pkid,
      START_DATE,
      FINISH_DATE,
      ROUND((TRUNC(FINISH_DATE) - TRUNC(START_DATE)) * SLICE,2) calculated_slice
      FROM ODF_SSL_500702

      • Replace the insta-slice table name with the one created in your system

      WHERE PRJ_OBJECT_ID = 5002000

      • Replace 500200 with your subobject internal record id

Figure 16

    This matches what is seen in the application for this ChildObject record.



    Figure 17