You can modify the order and arrangement of values in a report by using different sort orders. Reports are sorted into rows
based on the values of fields in the Rows bucket, and into columns based on the values of fields in the Column Groups bucket.
Sorting is hierarchical, so values are first sorted by the first field in the Row or Column bucket, then by the second field,
and so on. For example, in the following image, the report is sorted by the Product Category field, then for product subcategories
under each product category, then by sale year values for each product subcategory. Product Category, Product Subcategory,
and Sale Year have all been placed into the Rows bucket. Gross Profit was placed in the Summaries bucket, so it is not used
to sort the report. Instead, gross profit values are sorted by the other three fields.
By default, field values are sorted into ascending order based on the numeric and alphabetical order defined in the code
page that you are using. To reverse the sort order, right-click a field and click Sort descending. To return to the original sort order, right-click a field and click Sort ascending. You can also click the sort arrows on the fields in a bucket, as
shown in the following image.
You can rearrange the fields within a bucket by dragging them, which allows you to prioritize certain information in the
report. The previous example showed a report sorted by Product Category, then Product Subcategory, then Sale Year. If we move
Sale Year to the top of the Rows bucket so that it is the primary sort field, the report becomes a breakdown of yearly sales,
as shown in the following image.
You can sort by measure fields as well when they use the Summaries, Details, or Details with counter display options. To
sort by aggregated measure values,
right-click a field in the measure bucket and click Sort ascending or Sort descending. A hidden instance of the measure field is added to the top of the Rows bucket, as shown in the following image.
The result is a report that is sorted into rows based on the selected measure value, as shown in the following image.
In this example, sorting by the Gross Profit field from the Summaries bucket
has allowed us to see at a glance that larger profits have been made in more recent years. You could change the sort order
to see lowest profits first by clicking the sort arrow for the hidden Gross Profit field in the Rows bucket, just as you could
for a visible field.
You can move this hidden field in relation to the other sort fields to change the sorting priority. In the following image,
the hidden Gross Profit field has been moved to after Sale Year, allowing us to see the most profitable products in each year.
You can sort by multiple measures to have more granular control over how the report is sorted. If there are some matching
values of the first sorting measure, the
second sorting measure will sort those matching values.
When using the Details or Details with counter display option instead of the Summaries option, you can sort fields in the
Details bucket in the same way. This can be helpful if you want to view a sorted list of all records for a field. Right-click
a field in the Details or Details with counter bucket and click Sort ascending or Sort descending to sort the report using the values from that field.
You can remove the sorting effect of a field in the Summaries, Details, or Details with counter bucket by pointing to the
invisible measure field in the Rows bucket and clicking the X, or by right-clicking the original field in the measure bucket
and clicking No sort. The hidden field is removed from the Rows bucket, but the original field remains in the measure bucket. When you remove
the original field from the measure bucket, the invisible field in the Rows bucket is automatically removed as well.
You can also sort by hidden dimension fields, if you do not want them to display in the chart. To hide a dimension field
in your report, right-click the field in the Rows or Column Groups bucket and click Hide.
The following image shows a report sorted by Sale Day Name and Sale Date. We want to see sales information based on the day
of the week. Notice, however, that Sale Day Name is sorted alphabetically, by default.
We can add the Sale Day of Week field, which assigns a number to each day, to the report as the primary sort field, and then
hide it, so the days are listed in weekday order, as shown in the following
image.
Using Hidden Fields in Reports
When creating a report in WebFOCUS Designer, you can hide a field so that it exists in the report but is not shown as a column.
The main advantage of doing this is that hidden fields are still used for sorting, which gives you more control over how the
values in a report are displayed and organized. To hide a field in your report, right-click a field and click Hide. The hidden field appears slightly faded in its bucket, as shown in the following image.
You can display the field again by right-clicking it and de-selecting the Hide option.
You can sort by hidden dimension fields if you do not want them to display in the chart, but still want to use the values
in those fields to sort the values in the fields that do display.
The following image shows a report sorted by Sale Day Name and Sale Date. We want to see sales information based on the day
of the week. Notice, however, that Sale Day Name is sorted alphabetically, by default.
We can add the Sale Day of Week field, which assigns a number to each day, to the report as the primary sort field, and then
hide it so that the days are listed in order, as shown in the following image.
A hidden field is also created automatically when you sort by a measure field. To sort by aggregated measure values, right-click
a measure field in the Summaries bucket and click Sort ascending or Sort descending. A hidden instance of the measure field is added to the top of the Rows bucket, as shown in the following image.
The result is a report that is sorted into rows based on the selected measure value, as shown in the following image.
In this example, sorting by Gross Profit has allowed us to see at a glance that larger profits have been made in more recent
years. You could change the sort order to see lowest profits first by clicking the sort arrow for the hidden Gross Profit
field in the Rows bucket, just as you could for a visible field.
You can move this hidden field in relation to the other sort fields to change the sorting priority. In the following image,
the hidden Gross Profit field has been moved to after Sale Year, allowing us to see the most profitable products in each year,
as shown in the following image.
You can sort by multiple measures to have more granular control over how the report is sorted. If there are some matching
values of the first sorting measure, then the second sorting measure will be used as a secondary means of determining sort
order.
When using the Details display option instead of the Summaries option, you can sort fields in the Details bucket in the same
way. This can be helpful if you want to view a sorted list of all records for a field. Right-click a field in the Details
bucket and click Sort ascending or Sort descending to sort the report using the values from that field.
You can remove the sorting effect of a field in the Summaries or Details bucket by pointing to the invisible measure field
in the Rows bucket and clicking the X, or by right-clicking the original field in the Summaries or Details bucket and clicking
No sort. The hidden field is removed from the Rows bucket, but the original field remains in the Summaries or Details bucket. When
you remove the original field from the Summaries or Details bucket, the invisible field in the Rows bucket is automatically
removed as well.
Using Sort Limits
You can use sort limits to control how many values to display in your content. While a filter allows you to limit your content
by specifying which values should display, a sort limit allows you to limit it by specifying how many values should display,
depending on the amount of information that you want to see.
To add a sort limit, right-click a field in a bucket, point to
Sort limit, and select a value. Only fields in certain buckets, generally the default dimension and measure buckets, can have sort limits
applied. When you add a sort limit to a measure field, that field is used to sort your content automatically.
When setting a sort limit, the values 5, 10, and 25
are provided for quick selection, but you can select
Custom
to specify a different number as the sort
limit. You can remove the sort limit from a field by right-clicking
the field, pointing to
Sort limit, and clicking
No limit.
When a sort limit is applied to a sort field, only the specified number of values is displayed for that field within each
sort group. If a sort limit of 5 is applied to the primary sort field, then only the first five sort values display in the
chart or report. If a sort limit of 5 is applied to a secondary sort field, then five values for that field display within
each higher level sort value. In the following image, both the Customer Business Sub Region and Customer City fields have
a sort limit of 5, so the report shows the first five Customer Business Sub Region values and the first five Customer City
values within each subregion.
The values that display are based on the sort order of the field that has the sort limit applied. If an alphanumeric dimension
field has a sort limit of 5 and is sorted ascending, then the first five alphabetic values for that field display. If the
same field is sorted descending, then the last five alphabetic values display.
As a result, one of the most effective ways to
use sort limits is to apply them to a sorting measure field, which
allows you to limit the report to the highest or lowest aggregated
values in each sort category. To do this, right-click a field in
the measure bucket, point to
Sort limit, and select a value. In a report, a hidden instance of the
selected measure field is automatically added to the top of the
Rows bucket, with the sort limit applied. You can click the arrow
icon on the hidden field in the Rows bucket to change the sort
order, and you can drag the hidden field into a different order in
the Rows bucket to change the level at which the sort limit is
applied.
In a chart, you can right-click the measure field for which you created a sort limit and change the sort order from the shortcut
menu.
In the following image, a sort limit of 5 has been applied to the Revenue field from the Summaries bucket of a report. The
resulting Revenue sort field has been changed to descending order and moved to apply the sort limit after the Customer Business
Sub Region field. As a result, the report shows the cities with the five highest Revenue values in each business subregion.
If you are sorting by a measure field with a sort limit, and multiple rows within a sort group have the same value for that
measure field, then they are all displayed. This may result in a number of rows higher than the sort limit. For example, the
following image shows a report in which the Quantity Sold field has a sort limit of 5. Since multiple Customer City values
in the Africa subregion have the same Quantity Sold value, seven values display, representing the cities with the five highest
distinct Quantity Sold values in that subregion.
