Special features

Special features to enhance custom reports

Feature Description
Rank Inserts a ranking column for By and Measure fields in a Report.
Limit Limits the number of unique variables in a column.
Page Breaks Starts a new page in the output when the primary sort field changes.
Line Breaks Inserts a line in the report output when the primary sort field changes.
Subtotal Inserts subtotals in the output for all numeric fields when the primary sort field changes.
Column Totals Inserts a grand total row at the bottom of the report to sum numeric data in each column.
Row Totals Inserts a grand total column to the right side of the report to sum numeric data in each row.
Sub Head Adds a subheader just below the column titles in the report output when the primary sort field changes.
Sub Foot Adds a subfooter at the end of the data on each page of the report output when the primary sort field changes.
Data Bars Adds Data Visualization Bars to numeric data.
Aggregation Displays numeric measure data using aggregation options other than the default of Sum.
Repeat Sort Values Displays all repeated sort values instead of blanks after the first instance of a new sort value displays in the report. The default behavior is to display blanks after the first instance of a new sort value. See Displaying Repeated Sort Values in a Report.
Recompute Recompute - Recalculates the result of a Compute command. Recompute is similar to Subtotal in that it recalculates only at the specified sort break.

Ranked fields

You can add rank columns to BY and Measure fields by selecting a BY or a Measure field in the Query Design pane and in the Field tab, Sort Group, selecting Rank. Alternatively, right click on the field and select Rank on the context menu.

  1. Adding a rank column to a BY field inserts a rank column immediately to the left of the field.
  2. Adding a rank column to a Measure field creates a copy of the column as a BY field and adds a rank column to the left of the new BY field.

The rank column can now be edited and formatted like any other column, with the following exceptions:

  • The only formatting that can be applied is Traffic Light Conditions.
  • It cannot be hidden.
  • Breaks or filters cannot be inserted.
  • No column can be moved in between the rank column and the column it is ranking.

Limit column variables

  1. You can limit the number of unique variables that appear in a column by selecting a BY or Measure field in the Query Design pane and in the Field tab, Sort group, using the Limit dropdown menu. You can also right-click on a column and select Limit on the context menu.
  2. Enter a number or select a number from the Limit drop-down menu. The field is now limited to the entered number of unique values.

Page and line breaks

Add page and line breaks to report output for the primary sort field.

  1. Select a BY (sort) field in the Query Design pane.
  2. In the Field tab, Break group, select Page Break or Line Break, and run the report.
    1. If you select Page Break, a new page is created every time the value of the primary sort field changes. Each page includes a new set of column titles, as shown in the following image.
    2. If you select Line Break, a new divider line is inserted in the report output every time the value of the primary sort field changes.

Apply Line Breaks

Apply Page Breaks

Subtotals

  1. Select a BY (sort) field in the Query Design pane.
  2. In the Field tab, Break group, select Subtotal, and run the report.

Selecting Subtotal inserts a line, descriptive text (*TOTAL FIELD Value), and subtotals for all numeric fields every time the value of the primary sort field changes.

Sample Output, Subtotal Option

Column totals

  1. From the Home tab, Report group, select Column Totals.
  2. Run the report.

Selecting Column Totals adds a grand TOTAL row at the bottom of the report that sums numeric data in each column.

Row totals

  1. From the Home tab, Report group, select Row Totals.
  2. Run the report.

Selecting Row Totals adds a grand TOTAL column to the right side of the report that sums numeric data in each row.

Subheaders and subfooters

  1. Add subheaders and subfooters to report output for the primary sort field by selecting the field.
  2. In the Field tab, Break group, select Sub Head or Sub Foot.
  3. In the Sub header or Sub footer dialog, type the desired text and apply style options using the custom style controls.

    • Subheaders are displayed just below the column titles in the report output every time the value of the primary sort field changes.

    • Subfooters are displayed at the end of the data on each page of the report output every time the value of the primary sort field changes.

Subheader output

Data Visualization bars

Add data visualization bars to the report output for a selected numeric data source field by selecting the desired numeric data source field in the Query Design pane, or from the Field tab, Specific group, selecting Data Bars.

When you run the report, a data visualization column is added to the right of the selected numeric data source field to display values in each row.

Output with Data Bars

The column uses horizontal bars that extend from left to right in varying lengths, depending on the corresponding data values.

Measure data with aggregation options

  1. Display numeric measure data using a variety of aggregation type values other than the default of Sum with several options:
    1. Select the desired numeric measure data source in the Query Design pane
    2. Or from the Field tab, Specific group, select Aggregation.
    3. Or right click the selected measure field and select the Aggregation Function.

If you change the Measure field container from Sum to Print, Count, or List, it overrides all assigned aggregation type values.

  1. In the Aggregation Functions drop-down menu, choose an option:

Aggregation options

  • Sum
  • Average
  • Count
  • Count Distinct
  • Percent of Count
  • First Value
  • Last Value
  • Maximum
  • Minimum
  • Total
  • Percent
  • Row Percent
  • Average Square

The image is an example of report output produced by assigning the Minimum, Maximum, and Average aggregation options to measure fields in a report.

Aggregation Options

Repeated sort values

In the Control Panel, Format tab, Features group, select Repeat Sort Value.

When you select Repeat Sort Value, all repeated sort values are displayed in the report output..

This option overrides the default behavior, which displays blanks after the first instance of each new sort value that displays in the report.

Applied Sort Values

Recalculate computed results

Select a BY (sort) field in the Query Design pane, or from the Field tab, Break group, select Recompute. Alternatively, you can right click the selected sort field and select Recomputed Totals.

The following image shows a calculated field name Diff, which is the difference between Dollars and Buddollars. This value is then recomputed for each sort break on region. The recomputed value is the difference between the totals for Dollars and Buddollars.

Note: Recompute is not available for ACROSS fields.

Recomputed Result