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).
- Drag the Name column to the scorecard body; it will appear as a data label for displaying territory names.
- 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.
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 bySalesYTD 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 panel, 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.
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.
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.
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.
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.
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 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.
6. See also
- Top/Bottom N filtering for reports and scorecards
- Report basics and concepts
- Sorting a table visualization