Custom Fields

Time Series Fields

You can also calculate weighted average of the times series at the group (portfolio) level.

The Eagle solution uses the Market Value field based on the report date to calculate the correct values.

Using Rollup fields, including Rollup Calculation and Rollup Inference, allows for enhanced data to be stored in the Data Mart group models. While Times Series Fields are based on detail level information, Rollup fields of a times series allow group level information to be stored.

Grouping Rule

You can use Times Series fields with Grouping. Perform careful analysis to ensure that Time Series fields and Grouping are being used in a way that makes business sense.

Use Business Calendar and Date Rules

Since Times Series Fields aggregate data across time, each field must be configured to evaluate a date range. The date range can consist of the same two dates to fetch a single data point or two dates to fetch multiple data points.

When configuring Times Series Fields for use with a Business Calendar, you select either the Date Rule Period or the Business Calendar option:

• To select the Date Rule Period, click Click here to define your reporting period options... These options are used to determine the range of dates for the engine to fetch the required data for the aggregation.

• To select a Business Calendar, select the Use Business Calendar checkbox.

Business Calendar and Date Rule Example

For example, you configure a date rule to use a date range of the prior month begin to prior month end using business dates for the range delineation. Month begin should be a business day and Month end should be a business day. In the following table, the date evaluated by the Period option for Business Days is January 2nd and January 30th.

If you have set the Business Calendar option to On in the fields, the data used in the aggregation only includes those dates that are business dates. In this table, the shaded dates are the days included.

Set Up Time Series Fields

You create and edit Time Series field on the Time Series Fields dialog box. Complete the following procedure to set up Time Series Fields.

1. To access the Time Series Field dialog box, open the Custom folder, located on the Field Selector window.

2. Open the Time Series folder on the Field Selector window.
   You see the Time Series field dialog box.

3. Complete the following options on the Time Series Field dialog box and click OK.

Time Series Fields Restrictions Per-Share Data

Times Series

Note that some of your analytical data fields may not be appropriate candidates for Eagle's time series fields. These include the per-share fields for equities, such as price, earnings per share and dividend per share. This is because these fields are impacted by capital changes that companies experience, like splits and stock dividends that change the definition of a share. Eagle's prices are not adjusted for splits, and normally, security analytics are loaded as unadjusted. It is not meaningful to include both pre- and post-split data in a time series calculation. Fortunately, many important equity analytics such as beta, and financial ratios such as P/E do not have a share dimension and are unaffected by splits. The same is true of index levels, which are normally loaded as prices.

Data Mart

You may be able to obtain split-adjusted per-share data through a direct vendor load or exporter approach. If so, you can analyze that data using time series fields. However, if you want to build such fields in Data Mart, your data will reflect different share definitions every time a security has a capital change. To solve this issue, you must re-build all per-share fields, for all past dates, for every required date (perhaps only monthly depending upon reporting requirements) in order not to miss a split in any stock in the Mart. This may be facilitated by adding an extension to the Mart's Security Details table and dedicating it to your per-share fields, building only that extension since inception on a selective-fields basis.

Custom Fields Calculator

Use the calculator when defining calculations for creating custom fields.

Use the commands If, Then, Like, And, Or, and Null to compare two values. For example, an inference field could be: If Cost Basis is less than market value, then display Losing Money.

Supporting Nested IF Clauses

You can use ELSE and END IF statements to support nested IF clauses in Custom type fields. Using these statements in Custom fields allows you to create complex logic similar to that of standard programming practices. For example, you can create statements such as:

IF Investment Type =Fixed Income

THEN

IF Coupon <= 5

THEN Fixed Income Less Than 5

ELSE Fixed Income Over 5

END IF

ELSE

No Security

Data Mart

You may be able to obtain split-adjusted per-share data through a direct vendor load or exporter approach. If so, you can analyze that data using time series fields. However, if you want to build such fields in Data Mart, your data will reflect different share definitions every time a security has a capital change. To solve this issue, you must re-build all per-share fields, for all past dates, for every required date (perhaps only monthly depending upon reporting requirements) in order not to miss a split in any stock in the Mart. This may be facilitated by adding an extension to the Mart's Security Details table and dedicating it to your per-share fields, building only that extension since inception on a selective-fields basis.

Date and Date Part Commands

You can perform math functions on dates in reports. For example, you can calculate the number of days between the trade date and settlement date on a trades report. The detail calculation and inference fields allows you to compare two date fields using the subtraction ( - ) operator. The concentration engine calculates the difference in days.

The Date Part option lets you add days to a field and extract a month value from a field. You can also use Date Parts to display portions of the full date. For example, if a report is grouped by the month portion of the trade date value, you can create a detail calculation field that retrieves the month portion of the trade date field. You can then use the detail calculation field in the grouping rule. When you select Date Part, a window appears that allows you to decide which part to use. Options include Date, Month, and Year.

String Command

You can limit a calculation to part of a string of characters by using the string function in the detail calculation or inference fields. Eagle supports the following string manipulations:

• Right Trim. Takes a parameter for how many characters to the right. For example, if (right trim,5(Entity Name)= “HEDGE”) then Account Type = “Hedge Fund” else “Private Account.”

• Left Trim. Takes a parameter for how many characters to the left. For example, if (left trim,11(Entity Name)= “Institution”) then Account Type = “Institutional Account” else “Private Account.”

• Upper. Evaluates a string of data in all upper case regardless of the actual case of the string. For example, if (upper(security description) = “Cisco”) then (Share Par Value * 2) else Share Par Value would return the result of (Share Par Value * 2) for the string "Cisco" or for "cisco".

• Lower. Evaluates a string of data in all lower case regardless of the actual case of the string. For example, if (lower(security description) = “motorola”) then (Share Par Value * 2) else Share Par Value would return the result of (Share Par Value * 2) for the string "Motorola" or for "MOTOROLA".

• Substring. Takes a parameter for start location and stop location. For example, if (substring(Security Name,1,3 = “ADR”) then Security Type = ‘ADR’ else Security Type.

1. Click String.
   You see the String drop-down menu.

   

2. Select the string type.
   To evaluate the last letters in a value, use the right string. For use with substrings, you have to populate a start position and the number of letters to extract for the word part. For example, if you are looking for the value “Consumer Cyclicals,” you may evaluate for the word part “Cyc” by entering 10 as the start position and 3 for the number of letters to extract.

Other Calculator Commands

Entity Build Field Attributes

All entity build related field attributes are grouped under the Entity Build field category. These include field attributes defined as Entity Build and Advanced with a process of Entity build Position Table Columns.

Field Attributes Defined as Character Numeric

For any field attribute that is defined as character numeric, the OLAP engine converts the numeric value to a string, which retains the decimal precision before matching the value to the code value. The OLAP engine applies the field attribute’s precision, which is typically 0 for character numeric defined field attributes. Typically, you see this in ratings field attributes when adding a reference code to the Numerical Rating field.

Published Field Attributes

Published field attributes are available to additional report types. You can edit a published custom field attribute and select additional Report types from Make available to other Report types. These changes are saved in the database. You can also make published custom field attributes available to additional report types.

Field Attribute Mapping

Field attributes are mapped to report types. Field attributes with RPT_SUB_TYPE_INST values of zero in the REPORT_TYPE_FIELDS table of the Rules database are available for all subtypes of the corresponding report type value. The existence of these zero records has no impact on how reports run.

You can restrict certain field attribute types from certain report type and report component types. For example, you can make trades fields unavailable to Position reports, either by using the report rule or field rule maintenance window, or from the profile level filtering setting. This change prevents reporting scenarios that are known to cause either report failures or inconsistent report results.

Map Field Types

You can use the Map Field Types feature to make specific field attribute types available for various components of each report type. The scenarios and combinations which work correctly are scripted. You can apply tighter restrictions with this feature, disallowing specific scenarios that are mapped by default. You can allow additional combinations by mapping field type and report type combinations that are not mapped by default.

To open Map Field Types, right-click on the contents of the Fields folder on the Component tab and select Map Field Types.

This window has four main folders:

• Fields. Used to select a specific custom field type, and specify which underlying field attribute types are included in it. For example, expand the Fields folder to display the list of custom field attributes. Select the Rollup Field type, and explode that folder to display the report types that can include rollup fields. Select the report type, such as positions single period, and use the Field Types check boxes in the right pane to select the field types that can be included in a rollup field created in a single period position report.

• Others. Used to map field types for report types that do not fit into the typical structure of other report types. This folder contains the query tool.

• Profiles. Maps field types to report types where the fields can be used in the profile-level filter feature. Because this filter feature, available in the Select Criteria tab in report profiles, is designed to be executed directly against the PACE report result set and is not as dynamic as the guideline filter feature, it is listed separately in the Map Field Types dialog box. The selections made to map field types to a report rule are not the same selections made to map field types to a report profile of the same report type.

• Rules. Used to map field types to field rules, filter rules, range rules, and report rules. Expand the folder to display the rule types, and expand the Rule Type folder to display the report types that use the selected rule type. Select the check boxes to modify the map field types by selecting a report type. To restrict a field type from the selected rule and report type, deselect the check box next to that field type..

Field Type Limitations

The Cash Adjustment and Trade Adjustment custom field types have limited field types available to them by default in the Map Field Types feature. Cash Adjustment fields have only cash and security fields available in the formula editor of that field type. Trade Adjustment fields have only trade and security fields. You can override these default mappings.

Map Field Types Workflow

When you change Map Field Types settings, the change applies to all users.

If a specific field attribute was previously mapped to a report type, and that combination is later restricted by using the Map Field Types function, that field no longer appears as eligible. For example, a trades field is made available to multi period positions reports and you then use the map field types function to restrict trades fields from multi period positions report field rules. When you create a multi period positions report field rule, the Trades folder in the field attribute selector no longer appears. Those field types are no longer eligible for that component type.

If you add a trades field to a multi period positions field rule, and the map field types function is used later to restrict that combination, the trades field remains as part of the field rule created previously. Changes to the map field types settings do not impact components created and saved previously.

If a specific combination was restricted via the map field types function and you make a field attribute available to a report type that is restricted, you get a warning that you are about to map a restricted condition. You can choose to not publish the field to the restricted report type, or ignore the warning and proceed with the selected mapping. If you make the field available to more than one report type, only the restricted report types are listed in the warning message.

Map Field Types Database Information

The data that is maintained by the map field types feature is stored in primary tables:

• Field Types. This table contains a record for each field attribute type in PACE, and drives the lists of field attribute types in the Map Field Types dialog box.

• Context Types. This table contains information about each specific PACE reporting component type that is supported by the map field types feature.

• The combinations that are mapped using the map field types feature are stored in two separate tables.

• CONTEXT_REPORT_TYPES_FILTER. This table contains the relationships among report types and subtypes, and context types. This drives the folder structure that is displayed on the left side of the Map Field Types dialog box.

• FIELD_TYPES_FILTER. This table contains the mappings among reports, contexts, and field types that are represented by the selected check boxes in the right half of the Map Field Types dialog box.

The full structure of these tables is shown in the tables that follow.

Custom Categories

You create custom categories for field attributes to determine how field attributes are displayed. You can organize the fields by indicator, database, table, or by category. You can also list the fields all in one folder in alphabetical order. Complete the following procedure to create custom categories.

1. From any Eagle window, click the Eagle Navigator button to access the Eagle Navigator.

2. Enter Codes in the Start Search text box.

3. Click the Codes(System Management Center) link to access the Codes window.
   You see the Codes window.

4. From the Codes module, select the Field Category Code and add new code values. The short description holds a number and the long description holds the actual description, as follows:
   Short description of 1 = Long description of “Accounting Data”
   Short description of 2 = Long description of “Performance Measurement Data”
   Short description of 3 = Long description of “Holdings Data”
   Short description of 4 = Long description of “Security Master Data”
   Short description of 5 = Long description of “Shared Data”

5. Open the Field Attributes module, under the Metadata category.

6. Assign a category to each field attribute. From the workspace, select just one grouping, or create a tree so that one grouping is designated as a main category and then one or more other categories are added as subgroups.

7. To customize the display, click Customize.

   

8. Select as many groupings as you want to create the grouping tree. Click OK to save your changes.
   The resulting view is displayed in all other field rules until it is changed. If a field attribute is not assigned to a category, it appears under the category Unknown.

   The field rule view is saved in the registry in the section HKEY_CURRENT_USER > Software > EagleSystems > Clients > EaglePACE > Settings > FieldsCtrlGroups. Category=Setting of 1, Indicator=Setting of 3, Database=Setting of 2 and Table=Setting of 4. This registry setting applies to the user.

   

Benchmark Comparison Fields

Eagle PACE allows you to compare portfolio and benchmark information in Performance Analysis Reports. These comparisons are done at the security or grouping levels of reports. When comparisons are done at the grouping level only, the data is stored in the Performance tables at the grouping level only and at the security or grouping levels for Performance reports. The comparisons are done as columns in the Performance report.

Eagle PACE also allows you to perform this type of comparison on other types of OLAP reports, including position and entity reports. This allows you to provide benchmark comparison reporting in reports, Advanced reports, Portal Queries, and Data Mart.

You can set benchmark definitions on the Holding Field Attribute and Entity Field Attribute Maintenance windows, using the Benchmark Definition option.

Use the Benchmark Definition setting to create a field attribute that retrieves data from the benchmark entity associated with the entity that is selected in the report that contains this field.

• This setting lists the long descriptions of the code values that exist for the internal code IBMARKDEF.

• This setting also lists the option Profile Portfolio, which is the default setting.

• When Profile Portfolio is selected, the field retrieves data from the entity selected in the report profile.

• When a value other than Profile Portfolio is selected, the field retrieves data from the corresponding benchmark that is associated with the entity selected in the report profile.

• If a benchmark is selected and the entity in the report does not have a corresponding benchmark, then the field returns a NULL value.

• Rollup and inference fields, as well as other custom field types, can be defined against fields with the Benchmark Definition setting populated.

• This setting is also available for advanced holding field attributes.

Allow Benchmark Override

You can override a benchmark when submitting position and entity range reports. This feature is only enabled for position and entity range reports. You can override any of the benchmark types listed in the IBMARKDEF code with any entity in the PACE system. If the primary benchmark setting is overridden, all fields with that benchmark definition selected are run with the entity selected as the override.

Composite Enumeration

If the report is submitted for a composite entity, then consideration needs to be given to which benchmark is retrieved in the report:

• If the Enumerate Composites setting is not enabled, then the benchmark associated with the selected composite entity is retrieved by the report.

• If the Enumerate Composites setting is enabled, then the benchmark associated with the underlying entities is retrieved by the report.

Currency Conversion

• If a field attribute has both a currency process and a benchmark definition defined, then the currency value of the associated benchmark is used for the currency conversion process.

• As in current currency conversion processing, if the associated benchmark does not have a Base Currency defined, then the value is not converted.

Reports Submitted for Clients

When a PACE report is submitted for a client, the benchmark information is retrieved for the entities owned by the selected client.

If the Treat Clients as Contacts setting is selected, the benchmark information is retrieved for the entity owned by the client related to the selected client and the entity owned by the selected client is ignored.

Work with Advanced Field Settings

This section describes how to use more advanced settings with custom fields.

Create As Of Field Attributes

You can create As Of time sensitive field attributes for security, ratings, price, analytic, entity and holding field attributes.

As of field attributes are advanced field attributes and require that a process be defined. In the following example, the process is End Date Holding or Entity.

Create Source-Specific Fields

Source-specific fields are intended for reconciliation purposes. You can create source-specific field attributes that assigns a specific source to a field attribute used in a report. You can create source-specific field attributes for security, analytics, ratings, price, and holding fields.

To create source-specific security, analytics, ratings or price fields create the field attribute and attach a source.

Source-Specific Holding Fields

You can select a specific source for any holding fields you want to display in a position or lot level position report. When you include source-specific holding fields in a report, the queries run to retrieve data for the report are altered. For reports using regular holding fields, the matrix fetch to retrieve position data uses the source rule you have assigned to the report. For Source-specific fields, the matrix fetch does not use the source rule, but instead uses the sources selected for the source specific fields to retrieve position data. All holding fields must be source specific. However, other field types do not have to be source specific. Therefore, a report can have the field attribute Market Value from Bloomberg and the field attribute Market Value from IDC, but the same report should not have the field attribute Market Value without an assigned source. PACE optimizes the matrix fetch process in the Concentration engine.

Source-specific holding fields can be used in derived fields, such as detailed calculation fields and in grouping rules, as well as in a field rule. Complete the following procedure to create source specific holding fields.

1. Select the Database (Holding).

2. Select the appropriate Holding table and the column on which the field attribute is to be based.

3. For fields used in single period reports, set the Process field to End Date Holding or Entity.

4. For fields used in multi period reports, assign the appropriate process, either Begin Date Holding or Entity or End Date Holding or Entity.

5. Set the Field Indicator field to user defined.

   

6. Create any custom field attributes, such as detail calculation fields, based on source-specific fields.

   

7. All holding field attributes must be source specific to create a report using source-specific holding field attributes. Even though sources have been selected for the field attributes, you must also specify a mandatory holding source for the report, which is a dummy source.

   

Source-Specific and Relation Fields

You can run single period position reports related to source-specific fields used with Relation fields. If a single security was held long on one source and short in a second source, the positions yield two separate holdings in the reports results.

Custom Sorting on Code Value Sets

You create custom sort orders on code value data sets. This allows you to set up custom sort orders more easily based on code values and dictionary classifications. Complete the following procedure to set up a custom sort order.

1. In a report rule, add a field that is configured with a translation code.

   

2. Change the sort from default to custom.

   

3. Select the values in the order you want. You can reorder them at any time using drag and drop.

4. Click Done. Custom sorting is now set. To make changes to the order, double-click on the Custom cell.

5. Use the Sort column in the grouping rule to configure custom sorting for a translation code enabled field placed in a grouping rule.

Create Entity Cross Reference Fields

PACE allows you to store identifiers for how entities and clients are identified in other systems. You can include those references on any entity based PACE report.

A custom field called Entity Cross Reference allows data from the ENTITY_XREFERENCE table to be included in a PACE report. In the Entity XReference field, you can set up multiple code value sets to display varied XReference fields. For example, if you have multiple accounting systems sending data to PACE, you can create a field attribute for each entity identifier that is specific to each accounting system for display in a report.

Complete the following procedure to create Entity Cross Reference fields.

1. Create a new custom field attribute from the Entity XReference category. Enter a name in Name field, and any comment in the Comment field. Select 0 from the Precision drop down list.

   

2. Click Field Options and select the field options such as publishing and report mapping.

3. Enter a value for the Column and the Entity XReference Type.

Create an Entity Hierarchy

You can create composites of composites. When you create composites of composites, composite entities consisting of either composites or of portfolio entities are created. You can also enumerate composites. This allows you to direct the Reporting engine to look through the composites selected for a report, drilling through one composite to the underlying report composites. PACE supports the storing of hierarchy of entities as it stores the relationships between entities, which can be nested to any degree.

Entity Hierarchy functionality is supported in cash activity, cash balance, ledger activity, lot level position, position, and trade reports.

You do not have to run the entity build process for the composites on which they choose to report, they simply must include the composites in the report. You must select the option enumerate composites for the entity hierarchy functionality to work in a report.

Set Up the Entity Hierarchy Function

Before using Entity Hierarchy functionality, you must complete the procedure explained in this section. An internal code is added through the installation and upgrade scripts. The code has a short description of ELEVEL and a long description of IENTITY LEVEL. The code instance for this internal code is 10051. First, you must create code values to reference the level number of each composite:

1. Create code values under the Eagle PACE source. The code values must reflect the appropriate level. The short description is the level number. The long description is a descriptive name.

2. After creating the codes and code values, you create entity hierarchy fields. Return to General Reporting, and select Compose.

3. Select the report type you want to create.

4. From the Fields toolbox, select Entity Hierarchy Field.
   You see the Entity Hierarchy Field dialog box.

5. Complete the fields on the Entity Hierarchy Field dialog box as described in the following table.

Entity Hierarchy Fields

You create Entity Hierarchy fields based on entity fields from the Entity Table or on fields from the Entity Extension tables. You cannot create Entity Hierarchy fields based on fields from the Entity Characteristics table of the Rules database.

The Entity Hierarchy Custom fields can work with fields from the scrub tables, specifically, fields created on columns in the inventory of fields, from tables where the table was added to the inventory of tables with an indicator of ”e” is available.

The entity fields that appear in the Entity Hierarchy Field dialog box are those entity fields that were made available to that report type. Therefore, you might see certain fields while creating an entity hierarchy field for a position single period report that you would not see when creating an entity hierarchy field for a trades multi period report. Using the example above, an entity hierarchy field could be created using the Entity field of Entity ID, and assigning the hierarchy level of one.

You can use Entity Hierarchy fields in a field rule, although they lose much of their meaning in this position. You must create rollup fields of the field attributes in the field rule to see data for the composites at the top level. In the option Choose Entities and Dates, select Enumerate Composites.

You can run the report for the lowest or highest level of the hierarchy. The data returned is based on which entities the report is run. If you run the report for a portfolio, and the entity hierarchy fields are included in the report, then that entity hierarchy relationship is displayed in the result set.

The above example shows the entity hierarchy fields used to group the report. The top level of the report is the Company Entity ID, next is the Branch Entity ID, followed by the last level of composite, the Group Entity ID. Finally, you can drill down to the Entity ID of the portfolio and to the security level. You can see values returned at the composite level because there are rollup sums on the field attributes. The Entity Build process was not run for these composites.

Hierarchy Assumptions

If a fund (portfolio) is held by more than one composite, the data for the fund is duplicated for each relationship it has with other entities. For example, in Figure 119 on page 131, if US Fund 1 belonged to both US Equities and US Balanced the data for US Fund 1 would be displayed twice in the report.

The level numbers are relative to the funds for which the report is being run. In the Entity Hierarchy Grouping example above, if a report is run for US Branch and composites are enumerated, then the report is run for US Fund 1, US Fund 2, US Fund 3 and US Fund 4.

If a relationship chain has funds that are not in the list of funds used to run the report, then those relationship chains would be filtered out and not displayed in the report. In the Entity Hierarchy Grouping example above, if the report is run for US Equities, which contains US Fund 1, then the report does not display US Balanced, although US Fund 1 does also belong to US Balanced.

If relationship information does not exist on a particular level, then the relationship information is labeled Unknown.

This functionality does not support fetching details from the Entity Details table, such as percent of ownership and is intended only for composites.

Entity hierarchy fields are used as the grouping rule.

The option Enumerate Composites is selected.

Hierarchy Scenarios

The following table contains an example of hierarchy Entity IDs.

Hierarchy Reports

For all of the following example reports, the Enumerate Composites option is on. The only items that change in these examples are the entities for which the report is run. All of the appropriate fields in the field rule were rolled up to show data at the composite level. The entity build process was not run for these composites. The reports are set up as shown below.

This example shows a report run for EPDC1. This report receives data for EPDC1, EPDB1, EPDG1, EPDP1, EPDP2, EPDG2, EPDP3, EPDP4, EPDB2, EPDG3, EPDP5, and EPDP6. The Company Level is Company Entity ID, the Branch Level is Branch Entity Id, and the Group Level is Group Entity ID.

This example shows a report run for EPDG1. This report receives data for EPDC1, EPDB1, EPDG1, EPDP1, and EPDP2.

This example shows a report run for EPDP1. This report receives data for EPDC1, EPDB1, EPDG1, and EPDP1.

Other examples result in the following:

• Run a report for EPDB2 – This report receives data for EPDC1, EPDB2, EPDG3, EPDP5, and EPDP6.

• Run a report for EPDG2 – This report receives data for EPDC1, EPDB1, EPDG2, EPDP3, and EPDP4.

• Run a report for EPDG3 – This report receives data for EPDC1, EPDB2, EPDG3, EPDP5, and EPDP6.

• Run a report for EPDP1, EPDP2, EPDP3, EPDP4, EPDP5, and EPDP6 – This report receives data for EPDC1, EPDB1, EPDG1, EPDP1, EPDP2, EPDG2, EPDP3, EPDP4, EPDB2, EPDG3, EPDP5, and EPDP6.

• Run a report for EPDC1, EPDB1, EPDB2, EPDG1, EPDG2, and EPDG3 – This report receives data for EPDC1, EPDB1, EPDG1, EPDP1, EPDP2, EPDG2, EPDP3, EPDP4, EPDB2, EPDG3, EPDP5, and EPDP6.