Sorting and filtering a report or scorecard
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:
- Create a new scorecard from the main menu.
- Go to the Explore window and locate the [Sales].[SalesTerritory] table under your data connector.
- 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.
- 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.
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.
Switch to View mode to see the scorecard. Verify the territory names are sorted from highest to lowest by the SalesYTD measure values.
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.
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.
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 window, scroll down to verify the filter is connected to the Name hierarchy.
Switch to View mode. From the toolbar, click Change Filters to open the Parameters bar and use the filter.
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.
Open the Parameters tab.
Select the Name hierarchy under dataLabel 2, which is now visible, and unselect it under Group 1.
Switch to View mode. Observe that only the SalesYTD column is being filtered.
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.
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 window 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.
For this example, you can use the Header controls that were automatically added and configured with the two data labels.
Switch to View mode to see the scorecard. Hover over the SalesYTD column header 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.
Next, open the Sort/Filter menu for the Name column. Click the Filter Value dropdown and select a few hierarchy members.
The scorecard displays rows corresponding to the selected hierarchy members.
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.