Using Custom Reporting Features
You can use the following custom features when creating
reports in InfoAssist.
-
Rank. Inserts
a ranking column for By 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 using aggregation options other than the default
of Sum. For more information, see How to Display Numeric Measure Data Using Aggregation Options in a Report.
-
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.
x
Procedure: How to Rank Fields in a Report
You
can add rank columns to the By 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 By 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 By field and
adds a rank column to the left of the new By field.
Note: The
rank option can also be accessed by right-clicking a By or Measure field
and accessing the Rank option through the right-click menu.
-
With a report open, in the Query Design pane, select a By or
a Measure field.
The Field tab appears on the ribbon.
-
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.
x
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.
-
With a report open, in the Query Design pane, select a By or Measure
field.
The Field tab appears on the ribbon.
-
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 value that you set.
x
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.
-
With a report open, in the Query Design pane, select a By (sort)
field.
The Field tab appears on the ribbon.
-
From the Break group, click Page Break or Line
Break.
x
Procedure: How to Add Subtotals to a Report
-
With a report open, in the Query Design pane, select a By
(sort) field.
The Field tab appears on the ribbon.
-
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, as shown in the following image.
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, as
shown in the following image.
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.
x
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, as shown in the following
image. 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.
x
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, as shown in the following
image.
x
Procedure: How to Add Subheadings and Subfootings to a Report
You
can add subheadings and subfootings to report output for the sort
field.
-
With a report open, in the Query Design pane, select a By (sort)
field.
The Field tab appears on the ribbon.
-
From the Break group, click Sub Header or Sub
Footer.
The Sub Header & Sub Footer dialog box opens.
-
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. The
following image shows how subheadings and subfootings appear in
the report output.
x
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, as shown in the following image.
x
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.
-
With a report open, in the Query Design pane, select the numeric
data source field.
The Field tab appears on the ribbon.
-
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. The
following image is an example of report output with data visualization
bars.
x
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.
-
With a report open, in the Query Design pane, select the numeric
measure field.
The Field tab appears on the ribbon.
-
Access the Aggregation Functions menu by doing one of the following:
- From the
Display group, click Aggregation.
- Right-click the selected measure field, point to More, then Aggregation
Functions, and choose one of the following options:
- Sum
- Average
- Count
- Count Distinct
- Percent of Count
- Distinct Values
- First Value
- Last Value
- Maximum
- Minimum
- Total
- Percent
- Row Percent
- Median
- Average Square
If you change the Measure field container from Sum to Print,
Count, or List, it overrides all assigned aggregation type values.
The
following image is an example of the MIN (minimum), MAX (maximum),
and MDN (Median) aggregation prefix operators added to measure fields
in the Query Design pane.
The
following image is an example of report output produced by assigning
the Minimum and Maximum aggregation options to measure fields in
a report.
x
Procedure: How to Access the Within Functionality
You
can use the Within functionality to apply specific aggregation tasks
at different report levels.
-
With a report
open, in the Query Design pane, select the numeric measure field.
The Field tab appears on the ribbon.
-
On the Field
tab, in the Display group, click Within,
as shown in the following image.
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 right-click menus on the column level, as shown in the following
image. You can also access these menus when you right-click on an
operator (for example, SUM) in the Query pane.
-
On the Within
menu, click the down arrow to select the Within phrase for the By
or Across field, as shown in the following image.
-
Select a
By or Across field from the list of available fields.
-
Run the
report to view output.
x
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.
The first image shows a report before Repeat
Sort Value is applied.
The
second image shows the same report with Repeat Sort Value applied.
x
Procedure: How to Recalculate the Result of a Compute Command
For more
information on the compute command, see Calculation Group.
-
With a report open, in the Query Design pane, select a By (sort)
field.
The Field tab appears on the ribbon.
-
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.