1. Designing with Reports for Oracle Enterprise Performance Management Cloud
  2. Working with Grids
  3. Working with Grouping
  4. Grouping Example with Multiple Groupings

Grouping Example with Multiple Groupings

Initial Grid Design

The Accounts, Entities, and Segments dimensions are on the row axis (with multiple member selections for each dimension), the Scenarios dimension is in the column axis. The grid property "Suppress Repeats" is enabled. Row 2 is the single data segment that will be used in the grouping.


initial grid design

In the grid preview, note how each Segment is repeated for each Entity region, and that each set of Entities and Segments are repeated for the two Accounts. The requirement is to see a subtotal of all Segments for each Entity and a total of all Entities and Segments for each Account.


initial grid design output

Inserting Text, Separator and Formula Rows with Text Functions

Before inserting a grouping, a formula row needs to be created to utilize the SUM function to sum the Segment row’s data values for each Entity. In addition, a text row will be inserted with the MemberAlias text function to display the Entity alias for each grouping. The formula row display text will also be customized to display the word "Total" along with the Entity alias for each grouping. Lastly, a separator row will be inserted after the formula row to display a blank row after each subtotal.

In the below grid design:

  • Row 1: separator row, which will not be used in the grouping.

  • Row 2: text row with the MemberAlias text function for the Entity group heading.

  • Row 3: data segment with selections for each dimension.

  • Row 4: formula row to sum the data segment in row 3, and to display the Entity subtotal for each grouping.

  • Row 5: separator row for the blank space under the Entity subtotal formula row.


insert text separator and formula

Adding the Grouping for Entities

The grouping is added by selecting rows 2-5, right-clicking on the row headings and selecting Group on and then click Entities.


adding grouping entities

The grouping indicator displays for the Entities grouping.


entities grouping

A preview of the results displays the subtotals for each Entity region with the Entity alias, the grouping heading with the Entity alias, and the blank row under each total.


entities grouping output

Adding Additional Rows and Defining the Grouping for Accounts

For the Account totals, an additional sum formula row will be added, in addition to a text row to display the Account alias and a separator row after the new formula row.

In the below grid design:

  • Row 1: separator row, which will not be used in the grouping.

  • Row 2: new text row with the MemberAlias text function for the Account group heading. Note that shading has been applied to the row.

  • Rows 3-6: existing rows previously defined for the Entities grouping. Included is the data segment which is now on row 4.

  • Row 7: new formula row to sum the data segment in row 4, to display the Account total for each grouping.

  • Row 8: new separator row for the blank space under the Account total formula row.


adding additional row defining grouping

The grouping for Accounts is added after selecting rows 2-8.


grouping accounts

The grouping indicator displays for the Accounts grouping.


accounts grouping

Note that the preview displays, in addition to the Entity grouping content, the totals for the two Accounts with the Account alias, as well as the grouping heading with the Account alias.


preview accounts grouping

Adding Minor Finishing Touches

The last step is to make some minor cosmetic changes. The row heading columns for the Account and Entity dimensions will be hidden in Column Properties since the dimensions’ member labels are already displayed in group headers and the total rows. Also, for the data segment row, banding is enabled.


adding minor finishing touches