A Design Considerations

As you design your report, here are some elements to consider.

Grid Data Limit

The maximum number of cells that can be returned by a grid is 1,000,000 cells. If this limit is reached when previewing the grid or report, you will receive this error: Error executing query. The number of cells exceeds the maximum of 1000000.

Chart Object's Data Set Limit

The maximum chart data set limit is 50 rows by 25 columns, or 1,250 values.

Using Expanded Versus Single Data Segments in Queries

A data segment is a row or column that retrieves data from a database. An expanded data segment is a row or column that can expand, so that the resulting grid expands to two or more rows or columns when viewed. Often, expanded data segments use functions such as Children Of or Descendants Of. A single data segment is a row or column that remains a single row or column when shown in the viewer.

While it is generally valid to use expanded and single data segments in the same grid, when designing a grid with large amounts of data, consider using expanded data segments rather than single data segments. Expanded data segments provide some performance advantages over single data segments; however, to produce detailed formatting on different data rows or columns, use single data segments.

Suppressing Missing Blocks

Note:

The ability to suppress missing blocks is available for EPM Cloud products only.

You can suppress missing blocks to improve performance when rows or columns contain sparse dimensions. When you suppress missing blocks, you can place large sparse dimensions in rows, while providing good response time if the density of the query is low. Only blocks with data are retrieved. For example, if you place an employee dimension consisting of thousands of members in rows, and place the entity in the page or POV, then only employees of the selected entity are retrieved.

Suppressing missing blocks aids in suppressing missing data when a large number of rows, such as 90% or more, is suppressed. However, choosing to suppress missing blocks when few or no rows contain missing blocks that would be suppressed can degrade performance. Also, certain suppressed blocks may ignore dynamic calculation members.

Renaming Dimensions and Members

If you rename the dimensions or members in a data source, you must manually update each report in Reports to reflect the changes.

Other Design Considerations

When designing reports:

  • Use expanded data segments for optimal performance:

    • Use functions on expanded data segments that are not placed on separate rows or columns.

    • Use multiple member selections on expanded data segments that are not placed in separate rows or columns.

    • Use single data segments only when required for formatting or calculations.

  • Write efficient formulas:

    • Use row or column formulas rather than cell formulas, when possible.

    • Use reference properties.

    • Use a cell reference instead of cross-axis references.

    • Remove unnecessary parenthesis from formulas.

  • Do not limit your report to the Grid object:

    • Add functions in text boxes that highlight specific areas.

    • Hide a grid of data in a report to highlight just the graphics.

Rounding and Footing in a Report

Overview

When displaying data values in a Report grid, where the data values are displayed as scaled (For example, 173,545,723 scaled in "thousands" is rounded and displayed as 173,546), may result in the scaled balance detail not footing to rounded subtotals and totals within the report. The grid design can be modified to correct any footing differences caused by the rounded values by using a row or column formula to determine the difference and include it in a "plug" amount.

This topic provides an example of the updates needed to a simple grid displaying scaled and rounded Current Assets rolling into a Total Current Assets member. The example uses the Narrative Reporting Sample Application.

Problems in Reports

Note the following grid design, where the individual Current Assets and Total Current Assets are selected in separate rows.

  • The first column has the original, or unscaled data values for Q2.

  • The second column is scaled by "thousands" in cell formatting for Q2.


explaing the problems faced in a report

Here is the grid preview, note the scaled values in the second column. Also, note that the second column values sum to 904,569, which results in a scaled variance of " 1 " from the scaled total displayed of 904,570.


previewing a report

Solution for Reports

The solution is to create a formula row in the grid to calculate the variance and apply the variance as a "plug" amount to one of the existing accounts in the rows (Prepaid Expenses in our example). A row formula for the plug amount will replace the Prepaid Expenses data row, which will be hidden from the display.

The Report grid formulas utilize the underlying unscaled data values, and not the displayed scaled values (for example, 173,545,723 unscaled in the first cell vs 173,546 scaled by thousands), the data values will need to have rounding applied via a formula column before applying the row formula for the plug amount. The original data column will be hidden from the display.

Note that in the modified design below, where the first column still contains the unscaled data values, the second column is a formula column that rounds the values in the data column by three digits using the following column formula: ROUND([A],-3). Also, note that a text function is used in the formula column to display the member name from the first data column.


fixing the problems in a report

A formula row was inserted right below the Prepaid Expenses data row (member name 114000) which takes the difference between the Total Current Assets member (110000) and the sum of the Current Assets accounts, except for the Prepaid Expenses data value in row 5: [7] - SUM([2:4]). A "Custom" formula was used, which applies the formula only to the selected cell, not an entire column or row.


adding a formula in the report

Also, a Custom Heading of " Prepaid Expenses " was specified for the formula row header:


formula row header

Next, Column A and row 5 are hidden from display:


row and column display

In the grid preview, note that the original variance of " 1 " is plugged into the original Prepaid Expenses value of 124,569, resulting in the value of 124,570, which now correctly foots into the Total Current Assets amount:


review the report after rounding