Configuring Financial Report Tables

After creating your financial statement or customized report, you must configure the components that comprise the tables displayed in it.

Tip: After selecting any changes you want to apply to the report, ensure you click Save in the tab in which you are working. This automatically closes the configuration modal and reloads your table. To confirm your changes are applied, a prompt is displayed indicating your table and filters configurations have been saved successfully.

Table Menu

Use the Table Menu FRB report configuration icon. to open the table configuration modal. Once the table configuration modal is displayed, you can select the specific tab in the modal you want to land on through this menu, and you can switch between tabs by clicking each tab name.

Edit access is required to configure reports and statements. If your org does not have the following icons, you might not have the required access:

  • FRB report configuration icon.

Table Configuration

This tab enables you to apply the following global settings to your report:

  • Pivot Table. Select this option organize the data displayed in your report columns. For example, if you pivot on the “Company” dimension, a column is displayed for each company in your report.
  • Table Name. Specify a name for the table displayed in your report.
  • Dataset. Select the dataset you want to use as the source of data for your report. Click to change your selected dataset.

Editing the Dataset Used by the Table

Note:

If you change your dataset after configuring the options in Lens Configuration, Dimension Columns, Soft Dates, Subtotal Columns, Measure Columns or formatting (), then your selected configuration is deleted.

Select the dataset containing the lenses you want to use as the source of data for your report or statement. Only lenses derived from this dataset are available for selection in the configuration options.

You can add or change the dataset used by the table by following these steps:

  1. From the Table Configuration tab, click next to Dataset to open the Edit Dataset modal.
  2. Click Edit.
  3. Use the search field to look for and select the dataset you want to assign to the report.
  4. Click Next to confirm your selection. This action returns to the Table Configuration tab for the table using the new dataset. Click Save to complete the process.

Editing the Financial Report Table Name

You can edit the name of your Financial Report Tables by following these steps:

  1. From the Table Configuration tab, enter a new name for your table by clicking in the appropriate field.
  2. Click Save to apply the name you entered to the table.

Lens Configuration

Lenses are the source of data for your statements and reports and are derived from Analytics Studio. In the Lens Configuration tab, click on the tool bar to add any of the following to your report:

  • A lens derived from Analytics Studio, click
  • A calculation row, click FX icon.
  • A blank row, click
Warning:

Financial Report Builder does not support lenses created from multiple datasets.

We recommend you do not use lenses with the following options applied in Analytics:

  • Sorting (ascending or descending)
  • Sorting within groups (ascending or descending)

Divide the affected data into smaller lenses to reduce the requirement to apply sorting in Analytics to avoid displaying incomplete data in your reports.

The configuration options displayed under Measure Columns, Formatting, and Soft Dates cannot be accessed until you have completed your lens configuration setup.

You can toggle the order of the lenses in your report by selecting the row you want to move and clicking or on the toolbar.

You can remove all the lenses and calculations in the Lens Configuration tab by clicking Remove All. To remove a single lens or calculation click next to the row you want to remove.

You can repeat the label for a selected grouping in your report by clicking on a non-formula row and selecting . For more information on this feature see Formatting Reports.

The following filter logic operators applied to lenses in Analytics Studio are supported by Financial Report Builder: AND, OR, NOT, and parentheses. You can apply these operators these lenses in Analytics Studio to control the data displayed in Financial Report Builder.

You can display positive values derived from your selected lenses as negative values, and vice-versa, by selecting on the toolbar on the Lens Configuration tab. This setting applies to individual lenses only. To reverse signs globally in your report, use the Reverse Sign setting in Formatting Measure Column Numbers.

Note:

When selecting a lens with an AggregateFlex query, your lens must contain measures, or measures and dimensions.

To configure a lens perform the following steps:

  1. Enter a Section name under which the data derived from your lens is displayed in your report's table by clicking in the appropriate field.
  2. Enter an Alias for your lens by clicking in the appropriate field. The alias you specify for your selected lens enables you to perform calculations using values derived from a unique row of data in your lens.
  3. Select your lens from Analytics Studio by clicking in the appropriate field. If you have applied a query limit for your selected lens in Analytics Studio, then this limit is applied to the data displayed in your report. You can click Format brush. to open, review, and edit your selected lens in Analytics Studio, or create a new lens for use if required.
  4. Click in the Lens Configuration tab toolbar if you want to display the rows derived from the selected lens. Otherwise, leave the field deselected, in which case only derived values are displayed. In either case, the data is available for use in a formula.
  5. [Optional] Click Add Calculation to create a formula row to apply a calculation to the data contained in your lens. For more information on supported formulas, see Adding Calculations to Reports.
Notes:
  • When editing a lens by clicking Format brush., if your selected lense is cached, a new tab might open displaying a security review to ensure the integrity and security of your system. To access the lens you want to review or edit, click Allow. If you click Deny, an error message will be displayed.
  • Admins can edit a token's lifespan if they have the Connected App installed in their org.

Configuring Dimension Columns

The Dimension Columns comprise the columns into which your report is ordered. You can pivot the data displayed in these columns.

To configure dimension columns perform the following steps:

  1. Click to add a dimension column to your report.
  2. Select your dimension from Analytics Studio by clicking in the appropriate field. You can toggle the order of the lenses in your report using or .
  3. [Optional] Enter a custom label to display in the table by clicking in the appropriate field.
  4. [Optional] Select Pivot Table in the Table Configuration tab. This organizes the data displayed in your report columns. For example, if you pivot on the "Company" dimension, your report displays a column for each company. See Pivoting Report Data for more information.
Tip: You can select dates as part of your dimensions, in addition to the full date you also have available partial dates. If the full date did not contain information for the selected partial date then zeroes are displayed. For example, you can select Financial Period End Date to display the full date "2020-12-31" in a dimension column. Alternatively, you can select Financial Period End Date (Year) to display only the year "2020" in the column.

Configuring Measure Columns

The Measure Columns tab contains the columns into which your report is ordered. These columns are contained in two tables. You can apply a range of formatting options to control the appearance of the columns in both these tables.

  • Measure Columns. This is a non-editable list of all the measure columns contained in your report.
  • Measure Calculation Columns. This enables you to pivot and apply calculations to the data displayed in your report.

To configure measure columns perform the following steps:

  1. Click to add a measure column to your report.
  2. Enter a name for your measure column by clicking in the appropriate field.
  3. Enter an alias for your column by clicking in the appropriate field. This is required to perform calculations using values derived from a unique row of data in your lens.
  4. [Optional] Enter a calculation to apply to your measure column data by clicking in the appropriate field in the Measure Calculation column. This opens the Formula Builder window. You can enter your own calculation using a measure derived from an Analytics Studio lens, or using the alias of another measure column. For example, measureColumn(sum_ValueDual_1) or measureColumn(A). Alternatively, click to search for a predefined calculation. See Adding Calculations to Reports for more information on adding calculations to your measure columns.
    Note: The range of predefined calculations available when configuring measure columns with Pivot Table selected might be reduced, due to an enhancement to improve the performance of Financial Report Builder. Following this enhancement, the number of rows read when your selected report is open for configuration is restricted to 25.
  5. [Optional] Select the appropriate option for the calculation precedence using . The options displayed determine which calculation is performed if a row calculation and a column calculation occupy the same cell.

    Available options are:
    1. Apply Column Formulas to Rows
    2. Apply Row Formulas to Columns
    3. Don't Apply Column Formulas to Rows.
  6. [Optional] Use the Show checkbox to toggle between hiding and showing a specific measure column. By default, the checkbox is selected.
  7. [Optional] Specify which calculation takes precedence if a row calculation and a column calculation inhabit the same cell. Available options are Row, Column, and None.
  8. [Optional] Click to suppress row calculations for selected measure columns.
Note:

The following conditions apply to data displayed in subtotal columns (if enabled) according to the calculation precedence you select in the Measure Columns tab:

If Apply Column Formulas to Rows, or Don't Apply Column Formulas to Rows are selected then intersect subtotals are not calculated, and any text suffixes or prefixes applied to the measure are not displayed in the subtotal columns.

Formatting Measure Column Numbers

If required, you can apply a range of formatting options to control the presentation of numbers in your report.

To do this, perform the following steps:

In the Measure Columns or Measure Calculation Columns table, click an entry in the Number Format Column. The following formatting options are available:

Number Formatting

Option

Description
Number Scaling
  • No Scaling
  • Thousands (102K)
  • Millions (1.2M)
  • Thousands (102)
  • Millions (1.2)
Decimal Precision
  • 0 decimals
  • 1 decimal
  • 2 decimals
  • 3 decimals
  • 4 decimals
  • 5 decimals
Currency Symbol
  • None
  • Apply to All
  • Apply to Totals and Subtotals

You can also format your numbers by choosing whether you want to include a percentage, and whether you want your numbers to be displayed with decimal numbers. To do this, in the Measure Calculation Columns section, click the Percentage drop-down. The following options are available:

  • None
  • 1 decimal place
  • 2 decimal places
  • 3 decimal places
  • 4 decimal places
  • 5 decimal places
Note:

If you select None, your numbers will be displayed without a percentage and without decimal numbers.

Configuring Soft Dates

You can select the soft dates you want to apply to your report or statement.

To configure soft dates perform the following steps:

  1. Select whether to activate soft dates in your report using the Soft Dates toggle.
    Note: If you activate soft dates in your report, then pivoting is automatically applied to the soft date columns displayed in your report.
  2. Select whether to show any soft date rows with a value of zero in your report using the Show Empty Rows toggle.
  3. Select a dimension such as Financial Period Start date to begin configuring your soft dates We recommend that you do not specify a date dimension elsewhere in your report configuration options or selected lenses, otherwise your data might not display as expected.
  4. Select your time period type. Available options are "Week" or "Month".
  5. Select the start of your selected time period type. If you select Month, you can select the month on which you want to start. If you select Week, you cannot specify a start for this time period type.
    Note: This is to avoid errors due to the handling of dates in Analytics. For example, a week intended to be grouped as week one of 2020 might be interpreted by Analytics as week 53 of 2019. A 53-week year can occur under circumstances such as a leap year. These normally begin on a Wednesday or Thursday. If you have a year that lasts 52 weeks and one day, this would normally be considered a 52 week year. The extra day would fall into the last week, or the first week of the following year. However, Analytics treats any date past the fifty-second week as belonging in week 53.
  6. [Optional] Select a current period date override. You can add a date to override your current period date selection. Your selected time and period type are then adjusted relative to this date. For example, if you selected February 17, 2021 after selecting "Monthly" and "February", this would then ensure that February 17 is defined as your current day. The months in your report would then be calculated relative to this selection, with March 17, 2021 calculated as the start of your second month.
  7. [Optional] Click Add Period Format to select one or more periods, or aggregations of periods, such as "Current Quarter". You can toggle the order of your selected period formats in your report using Upward chevron. or Downward chevron..
    Note: If you add two period or period groupings with identical labels, the latter overwrites the former if you apply pivoting to your report table. For example, if you add two instances of "Current Year", covering rolling periods, and rolling periods cumulative, the second would overwrite the first if you apply pivoting.
    Note: When selecting Current Year (Opening Balance) or Last Year (Opening Balance), it is recommended to also select the Financial Period End Date to achieve the best results.
    1. [Optional] Enter a custom label to change the period formats displayed in your report. For example, you might select "Last period (cumulative)" as a period format, but want to display this as "Opening" in your report.
      Note: If you have applied custom labels to your Period Formats, these labels are displayed in the report if you select the Pivot Table checkbox in the Soft Dates tab.

Configuring Subtotals

Subtotal columns are optional columns that, when enabled, allow you to add a subtotal for each dimension value split within that dimension. For example, if the dimension being subtotaled is "Trial balance 2" and it contains values of "Current assets", "Fixed assets", and "Other assets", then a subtotal is generated for each of these categories. Dimensions appearing in the right hand column are those set on either the lens, the dimension column, or in the soft date column. This dynamic list reflects the dimensions in your table when viewing your report. If no subtotal columns appear in this tab, try modifying or adding dimension columns or soft dates to your table. By default, subtotals are added to the table at the bottom of the category being subtotaled.

To configure subtotals perform the following steps:

  1. Select the Subtotal checkbox for the columns you want to add subtotals to.
  2. [Optional] Enter a custom label to display in the table by clicking in the appropriate field.
    Tip: You can enter "{value}" to make the label dynamic. For example, if you have dimensions 1 and 2 then enter "{value} Subtotal", the subtotals are displayed in the table as "Dimension 1 Subtotal" and "Dimension 2 Subtotal".
  3. [Optional] Select Show Above to override the subtotals default behavior and display the data in the first row for each category.

Formatting

You can control the appearance of the data displayed in your reports using the options displayed in the formatting toolbar. For more information, see Formatting Reports.