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 window 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).
  4. Drag the Name column to the scorecard body; it will appear as a data label for displaying territory names.
  5. Drag the SalesYTD column to the scorecard body area; it will appear as a data label for displaying YTD sales.

Click the magnifying glass icon to the left of the grouping area. This opens the Data Analysis Panel for the scorecard 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 bySalesYTD 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.

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

Note
The view parameter overrides the default value created in the previous section. If you still want Canada to be your default value, select it in the Parameters bar while in Edit or View mode. You can open the Parameters bar at any time by clicking Change Filters from the toolbar.

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.

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.

Continue from the scorecard created above. Select the SalesYTD header label and delete it.

Go to the toolbar, click Data Visualization, and then select Header. Position the header control in the scorecard header area.

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

Select the header control and go to its Properties. Set the Analysis Element to the SalesYTD measure. This is the measure it will sort/filter on. The text of the header control also automatically updates to reflect the name of the chosen element.

Set the Analysis Element property
Set the Analysis Element property

Since the hierarchy Name is the same one used for grouping, it automatically created a Header data visualization. Select it and set the Analysis Element to Name.

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 don't 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