Creating Fiscal time-scaled values (TSVs) in custom SubObjects

Products

Clarity PPM On Premise

Issue/Introduction

From Clarity v13 Release there is a new feature whereby the Clarity Administrator can create a Time-Varying attribute using 'Fiscal' Time periods. This article covers how one can configure it and where does the data get stored in the Database.

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

Environment

Clarity 13 and upper releases

Resolution

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.

Configuration:

Log in as an Administrator
Administration, Studio : Objects, Click 'New' button

Object Name = 'MasterObj'
Object ID = 'master'
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'

Administration, Studio : Objects, Click 'New' button
Object Name = 'ChildObject'
Object ID = 'child'
Master or Subobject : Select Subobject , Select 'MasterObj' for the Master Object
NOTE: For the Cost Plan Detail object make sure the Project object is associated on the OBS.
For the 'ChildObject', Attributes, Click 'New' button
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)
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)
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.
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.
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

Create a Master Record and Child Records in the application

Master Records

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:

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

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

Additional Information

FAQS.

1. Unable to create Time Slice for Fiscal Type Time Varying attribute. When we tried to create new Time Slice, only Calendared type Time Varying attributes appearing in the dropdown to select as Item.

There is a insta slice (system slice) that is created in the system when a "Fiscal Time Varying attribute" is created, and the same does not allow the creation of time slice. One can still get the ID and relate to the underlying slice table by querying the prj_blb_slicerequests table.

For example:

In customproject object if we create fiscal time varying attribute the following slicerequests get created in the system.

2. Fiscal Time Varying attribute only allowing to enter monthly data, not quarterly or annually.

The system allows only Monthly data and not quarterly or yearly.

3. When we choose Time Scale option as 'Fiscal Period Type', the list page is generating an error as Time Period does not exists. We have created Fiscal Periods already in Entity.

Please refer:

TSV Scrolling or Editing problem on Financial Plans using Calendar Date Options