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 Table Configuration. This automatically closes the configuration page and reloads your table. To confirm your changes are applied, a prompt is displayed indicating your table configuration has been saved successfully.

Table Menu

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

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 Add Lens to add a lens derived from Analytics Studio or a formula row FX icon. to your report.

Warning:

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

The configuration options displayed under Measure Columns, Formatting, and Soft Dates cannot be accessed until you have completed this step. You can toggle the order of the lenses in your report using Upward chevron. or Downward chevron.. 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 Reverse Sign in the Lens Configuration tab. This setting applies to individual lenses only. To reverse signs globally in your report, use the Reverse Sign setting in Configuring Financial Report Tables.

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 by clicking inside the relevant field.
  2. Enter an Alias for your lens by clicking inside the relevant 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. 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 and review your selected lens in Analytics Studio.
  4. Select Visible if you want to display the rows derived from the selected lens. Otherwise, leave the field deselected. In either case, the data is available for use in a formula.
  5. [Optional] Select Highlight if you want to highlight the rows derived from the selected lens. Otherwise, leave the field deselected.
  6. [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.

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 Add Column to add a dimension column to your report.
  2. [Optional] Enter a custom label to display in the table by clicking inside the relevant field.
  3. Select your dimension from Analytics Studio by clicking and searching inside the relevant field. You can toggle the order of the lenses in your report using Upward chevron. or Downward chevron..
  4. [Optional] Select Pivot. 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 comprise the columns into which your report is ordered. You can pivot and apply calculations to the data displayed in these columns.

To configure measure columns perform the following steps:

  1. Click Add Column to add a measure column to your report.
  2. Enter a name for your measure column by clicking inside the relevant field.
  3. Enter an alias for your column by clicking inside the relevant 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 inside the relevant field. 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 Add icon. to select a predefined calculation using the drop-down list of options. 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 Apply Row Calculation checkbox if you want to selectively apply row calculations to your measure columns. If the checkbox is not selected, calculations are not performed at the intersections between a row calculation and a column calculation.
  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.
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:

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.
  2. Select whether to display any soft date rows with a value of zero in your report using the Hide 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.
    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 Subtotal Columns

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.
    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 the Show Above checkbox to override the subtotals default behavior and display the data in the first row for each category.
  4. [Optional] Select the Highlight checkbox to highlight the row where subtotals are displayed.

Editing the Dataset Used by the Table

Note:

If you change your dataset after configuring the options in Lens Configuration, Measure Columns, Soft Dates, Subtotal 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 Financial Report Table Configuration, 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 opens the Table Configuration page for the table using the new dataset, proceed to configure the table and 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 Financial Report Table Configuration, click Edit Table Name to open its respective modal.
  2. In the Edit Table Name modal, enter a name for your table in the available field.
  3. Click Save to apply the name you entered to the table.