Filtering and sorting
Filtering and sorting are important concepts when running a report and, in many cases, are offered to the users as options in the request page.
How can I implement filters?
For every data item in your dataset there will be a separate tab in the request page allowing the users to apply filters on any of the fields of the tables. You can use the following properties to manage this:
- ReqFilterFields: The fields you put in here are the default fields that will be shown for this data item in the request page.
- DataItemTableView: If you select a key in this property then the data item disappears from the request page, unless you have selected RegFilterFields. The user will not be able to select a sorting order for this data item.
- ReqFilterHeading and ReqFilterHeadingML: Both can be used to change the name
FastTabfor the data items in the request page. By default, the table name is used.
Now, sorting and filtering is almost always something a developer is going to apply. The question then is, where do we apply sorting and filtering? There are several options, we can filter in the report dataset designer by making use of data item properties or triggers, or we can apply filters in the report's RDLC layout.
The answer to this question is that you should filter as soon as possible, which means in the report dataset designer. This is because we don't want rows and columns to appear in the reports dataset if they are not needed. The dataset is sent to the client, over the network. The bigger the dataset, the more memory the client will require and, at a certain point in time, it can run out and cause an out of memory exception.
For example, if you want to create a top ten customer list, you can use a table filter in the table data region in the RDLC layout. Let's assume you have about 100,000 customers in the customer table. Then, at runtime, all of them will be fetched from the customer table, sent to the dataset, and then filtered in the RDLC layout. I think it's better to fetch only the rows that you will actually use from the database and send them to the dataset.
When is it better to filter in the RDLC layout? I would answer, when you don't know the filter value and/or filter field. You can use that for dynamic filtering because the filter value and the filter field can be expressions in RDLC. Another reason is when you want to give the user the ability to dynamically filter the dataset.
The same is valid in SQL Server side reporting services. If you filter your query, you will get better performance than when you filter your dataset.
The only reason to filter in the layout should be to link a Tablix to a particular set of records in the dataset. This is done a lot in document reports.
Tip
In some cases, this concept means that you might have to redevelop your report's dataset to allow for dynamic filtering. An example is the Customer top 10 report (111).
Top-ten filtering is done in the dataset and not in the layout, although doing it in the layout is very easy, much easier than how it is done in the report dataset designer. The way that this report is developed, as well as how to apply dynamic filtering using an integer data item or temporary table, will be explained in a later chapter.
In this section, I will focus on how you can apply sorting and filtering in the Tablix. Examples of how you can apply filters in C/AL code in the dataset are available in Chapter 7, Performance Optimization Techniques.
Imagine you have the following dataset:
In this dataset there are two data items: Item and Integer. The Integer data item, which I have named Constants, contains the company name and logo. It is filtered via its DataItemTableView property, as follows:
By applying this filter to the Integer table, I make sure that only one row is added to the dataset. Now I'm going to create the following layout:
Tip
Instead of using an integer data item, in this example you can use the Company Information directly as a data item, because it's a setup table and it only contains one row. If you need to add any other information from other setup tables to the dataset later, you will need to redesign it, and use an integer data item. So, as a best practice, I recommend using an integer data item at the end or at the beginning of your dataset.
Next, in the layout, select a Table from the toolbox and drop it into the report body. Then, from the Report Data from the Parameters, drag and drop the No, Description, and Inventory in to the first row, or header row, of the Tablix. Then, in the second row, or detail row, click on the icon at the right top of the textbox so you can see a dropdown menu. There you can select the corresponding field from the dataset. Do this for all the columns, as in the following screenshot:
Save the layout in Visual Studio, import it into the dataset designer, save the report object, and run it. Then, the result will contain all the rows from the dataset and, because the Tablix should only show the columns with item values, the last row doesn't make sense:
This row at the end of the Tablix is the row from the integer data item. To filter it out of the Tablix, you can apply a filter in the Tablix properties.
To open the Tablix properties, you first need to select the Tablix. You can select it with the mouse or select it in the dropdown box in the property list. When the table is selected you right-click the handle at the left top and select Tablix Properties in the dropdown menu that appears. You will then see the Tablix properties in a popup window, and can set the Filters, as shown in the next screenshot:
Another way to access the filters is by clicking the ellipsis button next to Filters, from the properties page, as in the following screenshot:
To see the Tablix handles, you need to select it, then the gray bars become visible. These bars are referred to as handles. There's a handle at the top, one on the left and a small box in the corner at the left top. If you right-click the handle in the corner at the left top, it displays a drop-down menu. In this menu you can open Tablix Properties..., as in the following screenshot.
If you right-click on the left top handle of the Tablix, you can open its properties and add a filter. The filter in the preceding screenshot filters the Tablix with a condition that says that the Item Number must not be empty, and so you filter out the rows that don't belong to the Item Data Item.
Tip
Filtering on values
If you type the character 0 in the Value cell, by default, this evaluates to the string 0. To compare a numeric expression with the number 0, use the expression syntax which begins with an equal sign: =0.
When previewing a report, you may see a runtime error from data type mismatches that may be similar to:
"The processing of FilterExpression for the [data set name] cannot be performed. Cannot compare data of types System.Int32 and System.String. Please check the data type returned by the FilterExpression."
How can I implement sorting?
Sorting can be applied in the same window where you apply filters in the Tablix. Below Filters you can see Sorting and, in there, you can sort on any field from the dataset, or even an expression.
To sort the table, perform the following steps:
- Select the Sorting tab in the Tablix Properties popup.
- Select the field you want to sort on in the drop-down box in the column named Expression.
- Select a Direction, ascending or descending, via the Order button (A to Z or Z to A).
- Click on the Ok button to apply the sorting.
The following screenshot shows the sorting tab in the Tablix Properties popup window:
You can even define multiple columns on which to sort, if it is applicable. By using an expression to sort (or filter) you can make it dynamic. For example, you can use a parameter in the request page to let the user decide.
The Sorting and Filters that you apply in a Tablix are valid for all its members. This can be overridden if you are including one or more groupings in the Tablix.
Tip
If you are applying sorting in the layout of your report, there's no point in giving the user the ability to sort in the request page via the sort button on the data item tab, because whatever the user selects will be overridden at runtime by the sorting you apply in the layout. In that case, it's better to remove the sorting button from the request page by selecting a key in the DataItemTableView. Otherwise you will be confusing the user.
With interactive sorting we enable the user to sort on any column in a Tablix by adding sorting buttons to certain textboxes.
Open the textbox properties and activate Interactive Sorting for the selected textbox, as shown in the following screenshot:
Usually, you activate the interactive sorting option for textboxes located in the Tablix header. You can choose any dataset field to sort on, it can even be an expression. The rows you will sort in this example are the Tablix detail rows. Once you have created a group in a Tablix, you can also select the group as the sort target.
If you apply interactive sorting on all the column headers in the Tablix, the result will look as follows:
As you can see, up/down arrows are added in every column. When you run the report, use the Shift and/or Ctrl buttons to combine multiple sorting.
To apply dynamic filtering in the report, use the request page. The following screenshot shows how you can apply a filter on the item description field:
In the preceding example, the filter string is:
@*w*
The @ sign means it's case insensitive and the * sign means any character. So the applied filter says, any description that contains a w.