Field Aggregate Expressions (FAE’s) permit calculations to be performed on a per-row basis on Related Issue Displays. The FAE’s are added to fields within a Related Issue Display as layout cell attributes with a type of FIELD AGGREGATE EXPRESSION. There are also custom user exits for retrieval of data, accumulation of results, and the display of results. The display of the accumulated results is generated at the end of each group or collection of rows in a Related Issue Display, as an additional row with a user-defined title. The title is added as a layout cell attribute of type FIELD AGGREGATE EXPRESSION TITLE.

Creating and Using a FIELD AGGREGATE EXPRESSION

The following are the steps for creating and using a FAE:

Example

  1. Create your Related Issue Display layout and save this in the usual way
  2. Edit the Related Issue Display layout and select a column to which you want to apply an aggregate function. Click on the link button to add a new layout cell attribute
  3. Choose the FIELD AGGREGATE EXPRESSION – generate an aggregate value for this row entry. Select the appropriate Value for your function from the list – Count, Sum, Minimum, Maximum or Average. The Use User Custom Code value is only valid if you have user custom code in place that will populate the results of the Field Aggregate Expression. See later on this page for details
  4. The Field value should be set to a number – this will be tied to the FIELD AGGREGATE EXPRESSION TITLE on the Related Issue Display, as well as to the order in which multiple FIELD AGGREGATE EXPRESSION rows should be displayed. It is recommended you start with 1 for the first entry, and increment from there. The number you set must match the number you set in step 7
  5. Save your changes to the Related Issue Display layout
  6. Navigate to a parent issue that contains the Related Issue Display and verify the correctness of the results of the aggregate expression you created. It will not have any title or grouping at this moment, but you should see the results of the aggregation
  7. To create a title on the Related Issue Display, navigate to the add or edit layout that embeds the Related Issue Display. On the cell that contains the Related Issue Display, you create an attribute of type FIELD AGGREGATE EXPRESSION TITLE where the Value is the Title of the row to be displayed, and the Field value should be a number (start with 1). If you have more than one FIELD AGGREGATE EXPRESSION defined on the Related Issue Display, this number is the order in which they will appear. The Field value should map to the Field value you selected in step 4
  8. You can also optionally aggregate values while grouping together values within a field, and providing sub-totals or other attributes for each group. On the add or edit layout that contains the Related Issue Display, add a layout cell attribute to the Related Issue Display within the layout, of the type RID SORT ORDER. Select the field in the related issue display that you wish to group by, and select Group by equal to Yes. Now you will generate subtotals as well as a grand total. Clicking on a field title at the top of the related issue display will cause a refresh of the display, change the sort order and the sub-totals
  9. Again navigate to your parent issue that contains the Related Issue Display and verify that you now see the title and the optional grouping.
  10. In this example, we want to add totals to a Related Issue Display as shown in this screen shot:


    Totalling a field on a Related Issue Display

    The key steps are as follows:

    1. On the Related Issue Display, add the following Layout Cell Attributes to the field that you want to total (HOURS_INCURRED in the example). Add a FIELD AGGREGATE EXPRESSION with a Value of Sum and a Field value of 1. Save the changes
    2. On the edit layout that contains the Related Issue Display, add two additional Layout Cell Attributes. These are in addition to other attributes that most probably exist to provide the RID RELATIONSHIP NAME and other factors for the layout.

      • RID SORT ORDER – This provides the initial sort order for the display
      • FIELD AGGREGATE EXPRESSION TITLE – This provides the title for the expression. Note that this should be the same value as set in the FIELD AGGREGATE EXPRESSION. The screen for the Layout Cell Attributes will be something similar to the following. Note that only the RID SORT ORDER and the FAE_TITLE pertain to the calculations. The other entries have their own purpose and function.


        Layout Cell Attributes for the FAE

This gives the result shown in the screen shot above. Note the initial sort order. Also note that if you click on another field title within the Related Issue Display, there is a re-sort, and the totals for the field are automatically recalculated. This is shown below, where the display is now sorted on the Category field.


Resorting the display

Implementation Notes

  • Expressions will only work with Related Issue Displays that contain 1,000 rows or less, if you implement grouping with aggregate functions
  • The Count function works on all field types
  • The Sum, Average, Minimum and Maximum functions work only on numeric display type fields – Number, Decimal and Currency
  • The User Custom function requires custom code before it will achieve anything
  • You can only group by one column in the related issue display RID SORT ORDER layout cell attribute
  • You will get incorrect results if you group on a multi-valued field, or on a repeating record field, for calculations such as Average

Utilizing the User Custom Code Exits

This is an advanced topic and it is assumed the administrator is thoroughly familiar with programming within the User Custom Code environment of ExtraView. Please contact ExtraView Support for additional details on how to implement this feature.

Setting up field aggregate expressions is similar to the above, but there are a few additional steps.

The key step is that the aggregate expression performed in user custom code must be linked to a field on the Related Issue Display. This is achieved by creating a user defined field within the data dictionary, of type Expression. This may have any valid display type and will correspond with the returned value from the custom code. This provides your environment with a field name, a display title, security permissions and other attributes. You may have more than one custom aggregate function, with each being linked to a separate user defined Expression field.

To implement, you provide Java code for the 3 user custom exits for data selection, accumulated value computation and accumulated value display, and compile the code into the user custom class used in the installation.

The presence of FIELD AGGREGATE EXPRESSION layout cell attribute in the Related Issue Display layout directs the reporting logic to invoke three FAE-specific custom exits: data selection, value computation, and value display. In addition, the FAE-specific rows are added to the end of the report rows i.e. on the right for transposed Related Issue Displays and on the bottom for non-transposed Related Issue Displays.

Each FAE is associated with a specific field in the Related Issue Display, as part of the attributes on the Related Issue Display layout: this defines the row on a transposed Related Issue Display, or the column on a non-transposed Related Issue Display of the FAE result display at the end of the report. There may be more than one FAE associated with a field, resulting in additional columns on transposed Related Issue Displays or rows on non-transposed Related Issue Displays.

The three FAE user custom exits are as follows:

  1. ucFAESelect: called during generation of SQL for the report to permit insertion of SQL into the detail report query for later consumption by the FAE value aggregation. Signature:

       public String ucFAESelect (
                    SesameSession session,
                    Connection conn,
                    String faeName,
                    String ddName,
                    DetailReportElement re,
                    HashMap distinctTables,
                    String[] locParts,
                    EVHierarchyLevel ehl,
                    HashMap dataAlias,
                    String itemTableAlias,
                    String itemIdKey
   )  throws Exception
  2. ucFAEAggregate: called for each row in the Related Issue Display to permit aggregation of the values in the report – this could include summation, averaging, or special operations as required. Signature:

       public void ucFAEAggregate (
                    SesameSession session,
                    Connection conn,
                    String faeName,
                    String ddName,
                    HashMap row,
                    ResultSet resultSet
   ) throws Exception
  3. ucFAEDisplay: called once at the end of the report for each FAE in the Related Issue Display, to allow custom generation of display values for the FAE. Signature:

       public String ucFAEDisplay (
                    SesameSession session,
                    Connection conn,
                    String faeName,
                    String ddName
   ) throws Exception