Using Custom Reporting Features in InfoAssist

You can use the following custom features when creating reports.

• Rank. Inserts a ranking column for dimension and measure fields in a report. For more information, see How to Rank Fields in a Report.
• Limit. Limits the number of unique variables in a column. For more information, see How to Limit the Values of a Column in a Report.
• Page Breaks. Starts a new page in the output when the primary sort field changes. For more information, see How to Add Page and Line Breaks to a Report.
• Line Breaks. Inserts a line in the report output when the primary sort field changes. For more information, see How to Add Page and Line Breaks to a Report.
• Subtotal. Inserts subtotals in the output for all numeric fields when the primary sort field changes. For more information, see How to Add Subtotals to a Report.
• Column Totals. Inserts a grand total row at the bottom of the report to sum numeric data in each column. For more information, see How to Add Column Totals to a Report.
• Row Totals. Inserts a grand total column to the right side of the report to sum numeric data in each row. For more information, see How to Add Row Totals to a Report.
• Sub Header. Adds a subheading just below the column titles in the report output when the primary sort field changes. For more information, see How to Add Subheadings and Subfootings to a Report.
• Sub Footer. Adds a subfooting at the end of the data on each page of the report output when the primary sort field changes. For more information, see How to Add Subheadings and Subfootings to a Report.
• Pop-up Titles. Adds pop-up titles to report output when the mouse pointer hovers over a column title. For more information, see How to Add Pop-up Titles to a Report.
• Data Bars. Adds data visualization bars to numeric data. For more information, see How to Add Data Visualization Bars to a Report.
• Aggregation. Displays numeric measure data or non-numeric dimension data using aggregation options other than the default of Sum for measures or Count for non-numeric measures. For more information, see How to Display Numeric Measure Data Using Aggregation Options in a Report or How to Display Aggregations on Dimension (Non-Numeric) Data.
• Repeat Sort Values. Displays all repeated sort values instead of blanks after the first instance of a new sort value appears in the report. The default behavior is to display blanks after the first instance of a new sort value. For more information, see How to Display Repeated Sort Values in a Report.
• Recompute. Recalculates the result of a Compute command. Recompute is similar to Subtotal in that it recalculates only at the specified sort break. For more information, see How to Recalculate the Result of a Compute Command.

Procedure: How to Rank Fields in a Report

You can add rank columns to the dimension and measure fields in a report by clicking the Rank button. You access the Rank button, on the Field tab, in the Sort group.

• Adding a rank column to a dimension field inserts a rank column immediately to the left of the field.
• Adding a rank column to a measure field creates a copy of the column as a dimension field and adds a rank column to the left of the new dimension field.

Note: The rank option can also be accessed by right-clicking a dimension or measure field and accessing the Rank option through the shortcut menu by clicking Sort and then Rank.

1. With a report open, in the Query pane, select a dimension or a measure field.

   The Field tab appears on the ribbon.

2. In the Sort group, click Rank.

   A rank column appears, as shown in the following image.

   

   Note: 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.
   • You cannot insert breaks or a filter on the RANK column.
   • No column can be moved in between the rank column and the column it is ranking.

Procedure: How to Limit the Values of a Column in a Report

You can limit the number of unique values that appear in a column through the Limit menu. First, you must select a column, then the Limit menu becomes available on the Field tab, in the Sort group.

Note: You can also access the Limit option by right-clicking a column, pointing to Sort, and then selecting Limit.

1. With a report open, in the Query pane, select a dimension or measure field.

   The Field tab appears on the ribbon.

2. In the Sort group, enter a value in the Limit field, or select a value from the list. The number of unique values that appear in the column is now limited to the value that you set.

Procedure: How to Add Page and Line Breaks to a Report

You can add page breaks and line breaks to report output for the primary sort field.

1. With a report open, in the Query pane, select a dimension (sort) field.

   The Field tab appears on the ribbon.

2. From the Break group, click Page Break or Line Break.

Procedure: How to Add Subtotals to a Report

1. With a report open, in the Query pane, select a dimension (sort) field.

   The Field tab appears on the ribbon.

2. From the Break group, click Subtotal.

   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.

   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.

   Clicking Subtotal turns Subtotal on for all fields as a RECOMPUTE and inserts a line of descriptive text (*Subtotal FIELD Value). Clicking the down arrow launches a menu of options. From this menu, you can choose between Simple and Recomputed. Selecting More Options opens a dialog box from which you can choose which fields to subtotal, as well as what type of aggregation to do for those fields. You can also change the Subtotal text.

Procedure: How to Add Column Totals to a Report

On the Home tab, in the Report group, click Column Totals.

Clicking Column Totals adds a grand total row at the bottom of the report that sums numeric data in each column. Clicking the down arrow launches a menu of options. From this menu, you can choose between Simple and Recomputed. Selecting More Options opens a dialog box from which you can choose which fields to total, as well as what type of aggregation to do for those fields. You can also change the Current Total text.

Procedure: How to Add Row Totals to a Report

On the Home tab, in the Report group, click Row Totals.

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

Procedure: How to Add Subheadings and Subfootings to a Report

You can add subheadings and subfootings to report output for the sort field.

1. With a report open, in the Query pane, select a dimension (sort) field.

   The Field tab appears on the ribbon.

2. From the Break group, click Sub Header or Sub Footer.

   The Sub Header & Sub Footer dialog box opens.

3. In the Sub Header & Sub Footer dialog box, type and style the text, and click OK.

   Subheadings appear just below the column titles in the report output every time the value of the primary sort field changes. Subfootings appear at the end of the data on each page of the report output every time the value of the primary sort field changes.

Procedure: How to Add Pop-Up Titles to a Report

On the Format tab, in the Features group, click Title Popup.

Clicking Title Popup displays a pop-up title when the mouse pointer hovers over any column title in the report, at run-time.

Procedure: How to Add Data Visualization Bars to a Report

You can add data visualization bars to the report output for a selected numeric data source field.

1. With a report open, in the Query pane, select a measure field.

   The Field tab appears on the ribbon.

2. In the Display group, click Data Bars.

   A data visualization column appears to the right of the selected numeric data source field to display values in each row. The column uses horizontal bars that extend from left to right and vary in length, depending on the corresponding data values.

Procedure: How to Display Numeric Measure Data Using Aggregation Options in a Report

You can display numeric measure data using a variety of aggregation type values other than the default of Sum.

1. With a report open, in the Query pane, select a measure field.

   The Field tab appears on the ribbon.

2. Access the Aggregation Functions menu by doing one of the following:
   • From the Display group, click Aggregation.

     or

   • Right-click the selected measure field, point to More, then Aggregation Functions, and choose one of the options. For example, First Value.

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

Procedure: How to Access the Within Functionality

You can use the Within functionality to apply specific aggregation tasks at different report levels.

1. With a report open, in the Query pane, select a numeric measure field.

   The Field tab appears on the ribbon.

2. On the Field tab, in the Display group, click Within.

   

   Note: Depending on how your data is set up, you can apply the Within phrase on a By or Across field.

   Optionally, you can access the Within functionality from the shortcut menus on the column level. You can also access these menus when you right-click a measure field in the Query pane.

   

3. On the Within menu, click the down arrow to select the Within phrase for the By or Across field.
4. Select a By field from the list of available fields.

   The report automatically generates based on your selection.

Procedure: How to Display Repeated Sort Values in a Report

Run this procedure when the output format, Excel, for example, does not sort properly.

On the Format tab, in the Features group, click Repeat Sort Value.

When you click Repeat Sort Value, all repeated sort values appear in the report output. This option overrides the default behavior, which displays blanks after the first instance of each new sort value that appears in the report.

Procedure: How to Recalculate the Result of a Compute Command

For more information on the compute command, see How to Using Define and Compute Fields.

1. With a report open, in the Query pane, select a By (sort) field.

   The Field tab appears on the ribbon.

2. You can recalculate the result of a Compute command in one of the following ways:
   • Ribbon: On the Field tab, in the Break group, open the Subtotal menu. On the menu, click Recomputed.
   • Shortcut Menu: Right-click a sort field, point to Break, then Subtotal, and then click Recomputed.