Work with dashboard report tables

Report data can be displayed in two types of tables:

  • Standard table - a table containing a series of columns (based on data dictionary fields you select) on which you can define sections, breaks, groupings, and calculation. See Standard table design.
  • Cross table - a table containing a number of dimensions and data objects. You can define sections, calculations and rankings for cross tables. See Cross table design.

Standard table design

A standard table is a series of columns (see Columns, where you can define sections, breaks, groupons and calulations. For details, see, Sections, Breaks, Groupings, and Calculations.

Columns

A column in a standard table is a field previously selected in the Field selection tab. It contains a heading (in which a label text will be entered) and a number of lines of data.

You can perform certain actions on columns using the pop-up menu that opens when you left-click on the column heading.

Add or remove a column

To add a column, do the following:

  1. Left-click on the column heading next to where you want to add the new column.
  2. The pop-up menu opens and there are two possible locations to add the new column: Add left or Add right, relative to the selected column.
  3. Select the field group (Attribute, Metric or Time Axis).
  4. The Metric group contains all numeric fields. By adding a numeric field, this will be used as a metric, by automatically adding a default (sum) aggregation. If no aggregation is needed, Aggregation none can be selected.
  5. Select a specific field to be added to your table.
  6. You can also add All the selected fields or All the remaining fields in a single click.

To remove a column, click the Delete column button in the pop-up menu.

Update Column properties

To update column properties:

  1. Left-click on the heading for the column for which you want to update properties.
  2. The pop-up menu opens.
  3. Select Column properties from the pop-up menu.
  4. The Column properties dialog box displays.
  5. Specify values for the following properties.
  6. Some of the properties described below (Aggregation, Inline calculation, Inline calculation scope, Visible) are accessible directly from the pop-up menu.
  7.  
  8. Property Description

    Header caption

    Description displayed in the column heading. This can be internationalized.

    Data format

    Lets you format the data by clicking the button next to the label. In the Format definition pop-up menu you may choose special formats for Number, Date or Interval data.

    Filed

    The field containing the values to be displayed.

    Inline calculation

    Lets you perform calculations that take into account the values on the preceding lines. Example: it is possible to calculate a cumulative total that adds line values to the previous values on a running basis.

    Inline calculation scope

    Determines when inline calculations are reset.

    Aggregation

    It is possible to aggregate the values in the column. If the data in the column is non-numeric, the only permitted aggregation actions are Minimum, Maximum and Count.

    Distinct values on aggregation

    Any values already included in the aggregation process can be ignored.

    Visible

    It is possible to hide a column, for example if its values are used for a dynamic link. .

    Delete duplicates

    When the same value is repeated several times in a particular column, this property merges all the cells and shows the value only once.

    Width

    You can customize width of the rows in a column. If you do not set this value, the table adapts to fit the content. This value will apply to all the columns in the table

    Height

    The height of each rows in a column can be customized. If you do not set this value, the table adapts to fit the content. This value will apply to all the columns in the table.

    Header

    You can apply style attributes to the header of a column by clicking the button next to the label. The Style definition pop-up menu contains the following options: Color, Background color, Fond, Size, Style, Weight, Alignment.

    Data

    You can apply style attributes to the data in a column by clicking the button next to the label. The Style definition pop-up menu contains the following options: Color, Background color, Fond, Size, Style, Weight, Alignment.

  9. Click OK.

Move a column

You can change the position of a column by drag and drop.

  1. Click on the column heading and hold down the mouse button.
  2. Move the mouse pointer over the heading of the destination column and then release the button.

When you move columns, the following rules apply:

  • The moved column (column 1) takes the place of the destination column (column 2).
  • When you move a column from left to right, all columns located between the source column and the destination column, including column 2, are shifted one place to the left, and column 1 is placed to the right of column 2.
  • Similarly, when you move a column from right to left, all columns located between the source column and the destination column, including column 2, are shifted one place to the right, and column 1 is placed to the left of column 2.

Conditional format

You can use the conditional format option to apply particular styles and formats to data, based on certain criteria. For standard reports, formats are accessible from the pop-up menu for each column. For cross reports, formats are accessible for each data field.

  1. Left-click on a column heading and select Conditional format from the pop-up menu.
  2. Click the Add button to add a field and specify values for the following fields:
  3. Field Description
    Field

    Select the field for which to apply the conditional format.

    Operand

    Select an operand from the drop down list.

    Value Specify a value for the field.
    Threshold pattern Specify a pattern to be displayed when the value is reached.

    Style

    Define the style to apply if the condition is true.

    Add

    You can add as many lines as you like by clicking the Add button.

    Delete

    You can delete them by clicking the button Delete.

  4. Click OK.
  5. (Optional) Repeat steps 2 and 3.

Sections

A section groups together a table's data by distinct field value. Each group is shown in a different table, with the field value as the section title. When you add a section, the section is also added to the table with a row after it. This row is used to specify any calculations that must be performed at the end of the section.

Add a section

  1. Click the No section button.
  2. Select Add section.
  3. Specify the field to which the section relates.

Add multiple sections

You can add multiple sections to a report. In this case, they will be organized hierarchically. Any actions that apply to the sections are accessible in the pop-up menu.

When adding multiple sections, set the position of the new break. Select Add before section or Add after section from the pop-up menu.

Each section is also added to the table with a row after it. This row is used to specify any calculations that must be performed at the end of the section.

Delete a section

To delete a section click on its label and click Delete.

Edit properties

Left-click the section title and select Properties from the pop-up menu.

Each section has the following properties:

Field Description
Field The field to which the section relates.
Sort The order in which the sections display.
Format The format applied to the section's title.
Style The style applied to the section's title.

Breaks

A break is defined for a column in the table and is used to enable intermediate calculations to be performed on data objects grouped by value. The data and calculation lines are displayed in the same table.

Actions are performed on breaks via the pop-up menu that opens when you click No break or click on an existing break.

To add a break, select a column, You can add a break from the menu, selecting the column concerned by the new break, which can be added before or after the existing one. For that use the following options: Add break, Add before break, Add after break.

To delete a section, click on its label and then click Delete.

In the break Properties pop-up, you can specify the order in which the break's data objects are displayed.

Calculations

Calculations can be added in three places:

  • the section level
  • the break level
  • the table level (globally)

In all cases, the calculations are shown at the end of the section, break, or table.

To add a Calculation, select Add calculation line (before or after) from the pop-up menu.

Calculations are configured in the same way in all three cases. Each calculation line contains blank cells by default. You can use each cell's menu to set it as either a Calcul or a Label.

To edit the label click on Label > Properties and change this Label to suit your requirements.

With calculation cells, you can define the calculation to be applied directly from the menu. You can edit the properties to change the Calcul performed, or Format the calculation result. You can also specify the line Height, which will be the same for all calculation lines. You can also choose whether or not to use Distinct values, Conditional format or Raw data; that is, the basic values before any aggregation operations.

Groupings

Use a grouping row to group columns together. The grouping row is located above the column headings.

To show the grouping row, click on No grouping to open the pop-up menu and then click Add grouping line.

An additional row is displayed. This row contains a single block spanning all the columns. If you require more than one block, click on the cell to open a menu and click Split as blocks. Selecting this option opens a sub-menu that lets you split the block in several different ways. The first digit represents the number of columns in the left block and the second represents the number of columns in the right block.

You can repeat this block splitting procedure until you have the desired number of blocks.

You can edit each block's Properties to define a Grouping label, the Style that will be applied to the block and the Height of the grouping line. The height of the grouping line is the same for all blocks.

If you are not satisfied with the blocks that you have created, you can merge two blocks by selecting the appropriate menu option Right merge or Left merge, according to whether you want to merge with the block to the right or the one to the left (if one exists). The name and style of the block from which you opened the menu will be adopted for the new merged block.

Remove a grouping by clicking Delete this line in the pop-up menu.

Cross table design

A cross table consists of a number of dimensions and data objects at the intersection between the axes. You can define sections, calculations, and rankings for cross tables.

Dimensions

A cross report consists of two axes, each having one or more dimensions, and data (at the intersection between the axes).

Click on a dimension to display a pop-up menu. From the pop-up menu, you can Add dimensions above, under, left, right, Add calculation column or line, Delete the dimension, show all values by selecting Visible, and edit the Properties.

The Properties pop-up menu for a dimension contains the following information:

Field Description

Caption

A label.

Field

The field containing the data to be displayed.

Sort

The sort criteria that specifies the field used as the basis for the sort and the sort order .

Mode

A list of categories to which you can apply the dimension properties. The possible modes are:

  • All

  • All non-empty
  • Selected - Only the rows containing a specified value for this dimension are displayed

  • Group - The values of this dimension are grouped based on the existing values from the other dimensions. In the below example, the Group mode is applied to the dimension created for the Department field.

Attention! This Group mode should not be mistaken with the SQL clause “group by”.

Selected value

A value you can specify so that only the rows containing this value will be displayed. This field is editable only if you choose Selected in the Mode list.

Dimension

The dimension of the row.

Format

The format of the data for this dimension.

Style

The style applied to the data for this dimension.

Visible

Specifies whether to display all the values in the base, including those not used by the data in the table.

For the data inside the cells, the menu lets you add data in the table: Add left or Add right, Delete data and make it Visible or not.

It also lets you specify a Ranking and Conditional formats.

The Properties pop-up menu for a data object contains the following information:

Field Description

Caption

A label.

Format

The format of the data.

Field

The field containing the data to be displayed.

Aggregation

The aggregation merges the data for display purposes.

Distinct values on aggregation

Any values already included in the aggregation process can be ignored.

Style

The style applied to the data.

Sections

A section is defined by a selected field. As in a standard table, sections can be used in cross tables to group together data in different tables, sorting them by value. However, sections in cross tables do not have directly linked calculation lines.

You can use the pop-up menu to delete or edit the properties of the current section, or add other sections before or after it.

Calculations

In cross tables, each calculation line relates to a dimension. These dimensions will be displayed after the table's data.

Calculations involving cross tables are different from standard calculations in the way that the first cell Label in the dimension represents the row name, and the cells located in the data part can be calculations. Concerning the data, there is a calculation cell on each calculation line for each data object defined.

If the Calculations option from the pop-up menu is not selected the cell will remain empty.

You can use the pop-up menu to add another calculation line: Add calculation line before or Add calculation line after the current line on the same dimension. You may also Delete this line or edit its Properties.

For more details, see Calculations.

Ranking

With cross tables, you can use a ranking to keep only one end of a table along the X- and Y-axes.

The ranking is defined in the data objects pop-up menu.

Rankings have the following properties:

Field Description

Nb on X axis

The number of rows or columns to keep on the X-axis.

Nb on Y axis

The number of rows or columns to keep on the Y-axis.

Descending

Indicates whether or not data is sorted by descending order of value.

Display other value

By checking this option you may display the cumulative total of the intermediate lines.

Other values label

Provides a description for the line above.

Related Links