Sorting and filtering a report or scorecard

Contents[Hide]

1. Overview

This article shows various ways to sort and filter rows in a report or scorecard.

Walkthroughs are presented using the AdventureWorks sample database.

2. Sort by a measure

When you add a data visualization that incorporates a measure to the report or scorecard body area, that measure is automatically added to the scorecard group's underlying metric set. This allows you to configure sorting for the measure via the Data Analysis Panel.

Design a scorecard as follows:

  1. Create a new scorecard from the main menu.
  2. Go to the Explore panel and locate the [Sales].[SalesTerritory] table under your data connector.
  3. Drag the Name column to the grouping area (drop a column or hierarchy here to repeat content for each value); a corresponding pair of data label and header will appear.
  4. Drag the SalesYTD column to the scorecard body area; it will add the measure to the group metric set and appear as another pair of data label and header.

Click the magnifying glass icon to the left of the grouping area. This opens the Data Analysis Panel for the group.

Design the initial scorecard
Design the initial scorecard

In the Data Analysis Panel, edit the SalesYTD measure to open the Configure Metric Set Element dialog.

Click Define custom measure sorting. In the Configure Measure Sorting dialog, set the Sort Direction to Descending.

Configure sorting for the SalesYTD measure
Configure sorting for the SalesYTD measure

Switch to View mode to see the scorecard. Verify the territory names are sorted from highest to lowest by the SalesYTD measure values.

Tip
Drop a column directly to the Measures field of the Data Analysis Panel if you want to sort without using the measure in the body.

3. Filter by a default value

Similar to the metric sets in a dashboard, the values of every element in the metric set of a scorecard or a report can be filtered.

Continue from the scorecard created above. Click the magnifying glass icon to the left of the grouping area. 

Edit the Name hierarchy from the Data Analysis Panel. In the Configure Metric Set Element dialog, click the Default Parameter Value dropdown and select Canada as the filter value.

Set the default value for the hierarchy
Set the default value for the hierarchy

Switch to View mode to see the scorecard rows. Observe that only the Canada row is listed.

Tip

4. Add a filter

From the toolbar, click Filter, and then click Member to add a member filter to the scorecard. 

Add a member filter
Add a member filter

Similar to a dashboard, a view parameter is created together with the filter. Dundas BI will connect the filter to the most fitting element, in this case the Name hierarchy. In the Parameters panel, scroll down to verify the filter is connected to the Name hierarchy.

Verify the filter is connected to the hierarchy
Verify the filter is connected to the hierarchy

Switch to View mode. From the toolbar, click Change Filters to open the Parameters bar and use the filter. 

Click Change Filters and use the filter
Click Change Filters and use the filter

Note
Users can open the Parameters bar using the context (right-click) menu when the toolbar is not available.

4.1. Filter only the control

As you saw above, the parameter connected to the group metric set and will filter all the controls that are based on it. If you want to filter only a specific control, you can detach it from the group metric set.

Select the SalesYTD data label and click Data Analysis Panel from the toolbar.

Click Select data separately to create a separate metric set for this control.

Create a new metric set for the control
Create a new metric set for the control

Note
Large reports or scorecards with a lot of separate metric set may experience reduced performance. Consider it carefully before create the new metric set, as you will be unable to undo this action once the metric set is saved.

Open the Parameters tab.

Select the Name hierarchy under dataLabel 2, which is now visible, and unselect it under Group 1.

Connect the filter only to the control
Connect the filter only to the control

Important
The parameter will disconnect from the filter if it will have nothing selected. This means you have to first select the new hierarchy and only then unselect the old one.

Switch to View mode. Observe that only the SalesYTD column is being filtered.

Filter only the column
Filter only the column

5. Add a Header control

The following example shows you how to use Header controls in your report or scorecard to allow users to sort and filter on multiple columns in View mode. This functionality is similar to using column menus for sorting and filtering a table visualization

Note
The Header control can filter only hierarchies present in the group metric set.

The Header control is a data visualization specific to reports and scorecards, which you can add at any time from the Data Visualization menu on the toolbar. Once added, select the control and open the Properties panel to set the Analysis Element to the measure you want to sort/filter on. The text of the header control also automatically update to reflect the name of the chosen element.

Add a Header control from the toolbar
Add a Header control from the toolbar

For this example, you can use the Header controls that were automatically added and configured with the two data labels. 

View the Analysis Element property
View the Analysis Element property

Switch to View mode to see the scorecard. Hover over the SalesYTD column header and click the A-Z icon. 

View the scorecard and click the A-Z icon
View the scorecard and click the A-Z icon

In the Sort/Filter menu, click Descending. The scorecard rows are now sorted in descending order based on the SalesYTD column.

Sort by SalesYTD in descending order
Sort by SalesYTD in descending order

Next, open the Sort/Filter menu for the Name column. Click the Filter Value dropdown and select a few hierarchy members.

Filter on the Name column
Filter on the Name column

The scorecard displays rows corresponding to the selected hierarchy members.

Scorecard is now filtered
Scorecard is now filtered

5.1. One Click Sorting

The Header control also supports one click sorting, which allows users to click the header to immediately sort the rows in ascending, descending, or unspecified order. A triangle icon above the header text tells you which direction it is currently sorted. If you do not see the icon, it means the sort direction is unspecified.

To use this option, go to Properties for the header control and set the Enable One Click Sort property to True.

Enable One Click Sort property
Enable One Click Sort property

One Click Sort icon indicates sort direction is Descending
One Click Sort icon indicates sort direction is Descending

6. See also

Dundas Data Visualization, Inc.
500-250 Ferrand Drive
Toronto, ON, Canada
M3C 3G8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours: 7am-6pm, ET, Mon-Fri