AX1832

Adding data columns and calculated columns to a web report

When creating a web report, you can add as many columns as needed to define the data that you want to display in the report. You can also define calculated columns, such as to show the difference between two columns.

The data columns and calculated columns for the grid are placed in the right-hand box at the top of the Report Canvas, known as the Column Definitions box. This box defines the columns to display in the report. Although it is possible to add columns and calculated columns to the Column Definitions box before specifying a row dimension or a fixed row structure, the grid in the Report Canvas will not populate until the rows are defined.

Column Definitions box at the top of the Report Canvas

Adding data columns

To display data in the report, you can drag and drop table columns from the Data Panel to the Column Definitions box in the Report Canvas. The Data Panel displays the tables and columns that are eligible to be included in the report, based on the selected data context (primary table) and the specified row dimension.

To add a data column to a web report:

  1. In the Data Panel of the Report Builder, expand the table tree until you locate the column that you want to add to your report. You can also use the search box at the top of the panel to find a particular column by name.

  2. Drag and drop the column to the Column Definitions box at the top of the Report Canvas.

    The preview grid in the canvas updates to show data from the specified column.

  3. If the column is not in the desired location within the grid, drag and drop it within the Column Definitions box to reorder the columns.

  4. Use the Column Settings in the Configuration Panel to configure display properties for the column, such as column width, alignment, header text, and formatting. For more information, see Configuring column properties for a web report.

    By default, when you drag and drop a column to the grid, that column is selected and its column properties display in the Configuration Panel. You can return to the column properties at any time by clicking the column name in the Column Definitions box.

Example web report after dragging and dropping data columns

As you drag and drop data columns to the grid, the preview grid in the Report Canvas updates to show data for that column, using the specified row dimension or fixed row structure.

NOTE: If Use fixed rows is enabled for the grid, currently the Report Builder does not dynamically update the tables listed in the Data Panel based on the specified fixed row structure. If you drag and drop a column from a table that is not valid in the context of the fixed row structure, a generic error will occur when the Report Builder attempts to populate the grid.

Adding calculated columns

Calculated columns can be used to display totals, differences, percentages, and other calculations within a column of the report. Calculations can be based on columns from related tables that are eligible to be included in the report.

To add a calculated column to a web report:

  1. In the Report Canvas of the Report Builder, click the plus sign in the top right corner of the Column Definitions box, and then click Add Calculated Column.

  2. At the top of the Add Calculated Column dialog, define the following properties:

    • Header: Enter the column header text for the calculated column. This is effectively the name of the calculated column. By default, the header text is "Calculation".

    • Numeric type: Select the desired numeric type for the calculated column. If this is left at Default, the default numeric type for calculated columns is currency.

    You can change these properties later using the Column Configuration properties in the Configuration Panel.

  3. To create the calculation, drag and drop columns from the table tree on the left to the calculation canvas. See Defining calculations for more information.

  4. When you are finished creating the calculated column, click OK.

    The calculated column is added to the Column Definition box, and the preview grid in the canvas updates to show the calculated data.

  5. If the calculated column is not in the desired location within the grid, drag and drop it within the Column Definition box to reorder the columns.

  6. Use the Column Configuration properties in the Configuration Panel to configure display properties for the column, such as column width and alignment. For more information, see Configuring column properties for a web report.

    By default, when you define a calculated column, that column is selected and its column properties display in the Configuration Panel. You can return to the column properties at any time by clicking the column name in the Column Definitions box.

As you add calculated columns to the grid, the grid in the Report Canvas updates to show data for that column, using the specified row dimension or fixed row structure.

Example web report after creating a calculated column

Defining calculations

Using the Add Calculated Column dialog, you can build a calculation based on columns from related tables that are eligible to be included in the report. The column does not have to be present in the grid in order to be used in a calculation. Numeric values can also be used in the calculation.

The left-hand side of the dialog lists a table tree of available columns, while the right-hand side of the dialog—the calculation "canvas"—is where you build the calculation. To start the calculation:

  • Drag and drop two columns out to the canvas. The two columns are separated by an operator selector.
  • Select the desired operator.

You can continue building the calculation by dragging and dropping additional columns and selecting the operator. You can also do the following:

  • Numeric values: To add a numeric value to the calculation, click the plus icon at the top right of the dialog. You can then move, reorder, or delete the numeric value just like columns.
  • Reorder items: To change the order of columns in the calculation, drag and drop them on the canvas.
  • Parentheses: To add parentheses to a part of the calculation, select Add Parentheses from the operator selector. The two columns affected by the operator will become enclosed in parentheses.
  • Delete items: To delete an item, hover your cursor over the column and then click the trash can icon.

Example calculation in the calculation editor

Calculations can use the following operators: addition (+), subtraction (-), multiplication (*), and division (/). Use parentheses to determine calculation order, such as: (GL2021.Q1-BGT2021.Q1)/BGT2021.Q1.

Calculations can use the following columns:

  • Numeric columns from the primary table, whether or not those columns are also in the grid.
  • Numeric columns from related tables, whether or not those columns are also in the grid.

  • Numeric columns from the grid, including other calculated columns. Grid columns display using the header text defined for the column.

    If you use a table column from the grid instead of from the table itself, then the calculation will use the column as it is configured to display in the grid. For example, if the grid column has a column filter or uses an alternate aggregation, the calculation will be based on that modified version of the column.

NOTES:  

  • If you drag and drop a column from the primary table or a related table, it displays on the canvas using the column name only—such as M1. You can hover your cursor over the column box to see a tooltip with the full table.column name—such as GL2021.M1. If you drag and drop the column from the Grid Columns node, then it will display using the defined header text for the column.

  • If you use a grid column in the calculation, then the grid column cannot be deleted from the grid because deleting it would cause the calculation to become invalid. An error message will display if you attempt to delete a referenced column from the grid. To resolve the issue, you can do one of the following: edit the calculation to remove the reference, delete the calculated column, or configure the grid column as hidden so that it can still be referenced in the calculation but not display in the report.

Editing calculated columns

You can edit an existing calculated column to change the calculation.

To edit a calculated column in a web report:

  1. In the Report Canvas of the Report Builder, click the calculated column in the Column Definitions box.

  2. On the General tab of the Column Configuration properties, click the Edit icon to the right of the Calculation box.

  3. In the Edit Calculated Column dialog, edit the calculation as needed, then click OK.

Additional column actions

Once data columns and calculated columns have been added to the grid, you can further adjust them as follows:

  • Reorder columns: You can reorder the columns in the grid by dragging and dropping them to any location in the Column Definitions box. Note that you cannot drag and drop a column from the Column Definitions box to the Row Definitions box and vice versa. If you accidentally dragged a column to the wrong box, you must remove the column and then drag and drop it again from the Data Panel.

  • Remove columns: You can remove columns from the grid by clicking the X icon to the right of the column name. Use caution before removing a calculated column—if you later decide you want to re-add the column, you will need to re-create the calculation from scratch.

  • Group columns: If you want a set of columns to display under a group header, you can define a column group and then add the columns to that group. For more information, see Defining column groups for a web report.

  • Configure columns: To configure display properties for a column, select the column name in the Column Definitions box, then use the Column Configuration properties in the Configuration Panel. For more information, see Configuring column properties for a web report.