How To Filter Data at the client and within the database using Report Builder 2.0 and a Report Model
Data can be filtered in the database query, or at the client, or both. When you write SQL or MDX, you know what is filtered in the database, because you directly control the query. When your report uses a Report Model, you do NOT create the query, the report model does. You may not know where filtering occurs. Where filtering occurs does matter, as it can affect performance and the user experience.
Consider a Report on the AdventureWorks 2008 Model. This report shows the Category, Subcategory and Product name for each product, filtered by a single Category. You choose a category, Bikes for instance, and the report will display all of the products in the Bikes category. Since I don’t want to complicate the subject with parameters, I have hard-coded the filter. An example of this report is below.
This is the report.
Filtering at the database
The data for this dataset could be filtered at the database server in the query or at the client. If the data is filtered at the database server, a smaller subset of data is returned to the client. In this case, only the rows for products in the Bikes category will be returned to the client. However if the client wishes to re-run the report, choosing Clothing as the category, another round trip to the database will have to be made, requesting and returning only the products in the clothing category.
Filtering at the client
Another option is to have all of the products for all categories returned to the client. Then the client can show only those products which are in the Bikes category. A larger and potentially unused number of rows is returned to the client in this case. However, if the client then changes the category filter selection to Clothing, and re-runs the report, the report will refresh without having to go to the database a second time, since the data already exists at the client.
You can also do hybrid approaches where there is some filtering in the database, and additional filtering on the client.
In both cases, the report looks exactly the same to the end user. They simply see the products associated with the Bikes category.
Purpose of this paper
The purpose of this paper is to show you that where you place your filtering criteria in a report based on a SQL Server Report Model determines whether the filtering will occur within the database or on the client.
Where should filtering be done?
How this report will generally be used will help you determine where filtering should occur.
Scenario 1:
An individual user runs this report a single time for a given set of parameters, and then is through with the report. In this simple case, filtering the data in the query at the database is preferred.
Scenario 2:
An individual user runs this report for a given set of parameters. The user will often run the report again with different parameters to get more information. Depending on the size of the result set, this case lends itself to bringing more data to the client, and allowing filtering to occur there.
When the database access is slow, for whatever reason, it is more likely you will benefit from filtering at the client. This is true because the penalty cost of accessing the database a second time is very high.
|
|
Pros
|
Cons
|
|
Filter at the Database
|
Less client memory required
|
A new trip to the database for each filter change
|
|
|
Only required data crosses the network
|
More load on the database, due to multiple queries
|
|
|
|
|
|
Filter at the Client
|
Quicker turnaround when filter conditions change, because data already exists at the client.
|
May not have enough memory to hold entire data set
|
|
|
|
Available client memory varies from one desktop to another, depending on physical memory and other programs concurrently running.
|
|
|
|
Client waits for entire data set, even if the client wishes to see a subset of data
|
How to Filter at the database
When you create a dataset, and are inside the Query Designer, you may select the Filter item from the menu. Any filter conditions you place within the Filter dialog box will be restrictions added to the database query.
Creating filters in the database query.
Creating a database filter on the Category Column for Bikes.
Appendix 1 shows the query which results from this filter. It adds the following where clause to the query:
WHERE
[ProductSubcategory].[ProductCategory2] = 1
The value 1 is the Primary key value in the Category table for the Category = ‘Bikes’.
How to Filter at the client
When you create a dataset, and choose Dataset Properties, you can add filters by selecting the Filters option. You may add multiple filters with complex conditions. In any case, filters added here do NOT restrict the data coming from the database. However they do restrict what is shown in the report.
Creating a Filter on the client.
Appendix 2 shows the query generated by this report, which has no where clause which is always true, indicating that all properly joined products in the database will be returned to the client, even though only the Bikes category items will be shown, as in the example above.
Appendix 1
Query which filters data within the database
SET DATEFIRST 7
SELECT
[ProductSubcategory].[ProductCategory] [ProductCategory],
[ProductSubcategory].[ProductCategory1] [ProductCategory1],
[ProductSubcategory].[ProductSubcategory] [ProductSubcategory],
[ProductSubcategory].[ProductSubcategory2] [ProductSubcategory2],
[Product].[ProductID] [Product],
[Product].[Name] [Name]
FROM
[Production].[Product] [Product]
LEFT OUTER JOIN (
SELECT
[ProductCategory].[ProductCategory] [ProductCategory],
[ProductCategory].[ProductCategory1] [ProductCategory1],
[ProductSubcategory].[ProductSubcategoryID] [ProductSubcategory],
[ProductSubcategory].[Name] [ProductSubcategory2],
[ProductCategory].[ProductCategory2] [ProductCategory2],
[ProductSubcategory].[ProductSubcategoryID] [ProductSubcategoryID]
FROM
[Production].[ProductSubcategory] [ProductSubcategory]
INNER JOIN (
SELECT
[ProductCategory].[ProductCategoryID] [ProductCategory],
[ProductCategory].[Name] [ProductCategory1],
[ProductCategory].[ProductCategoryID] [ProductCategory2],
[ProductCategory].[ProductCategoryID] [ProductCategoryID]
FROM
[Production].[ProductCategory] [ProductCategory]
) [ProductCategory] ON [ProductSubcategory].[ProductCategoryID] = [ProductCategory].[ProductCategoryID]
) [ProductSubcategory] ON [Product].[ProductSubcategoryID] = [ProductSubcategory].[ProductSubcategoryID]
WHERE
[ProductSubcategory].[ProductCategory2] = 1
ORDER BY
[ProductCategory], [ProductSubcategory]
Appendix 2
Query which returns all data from the database, and filters at the client.
SET DATEFIRST 7
SELECT
[ProductSubcategory].[ProductCategory] [ProductCategory],
[ProductSubcategory].[ProductCategory1] [ProductCategory1],
[ProductSubcategory].[ProductSubcategory] [ProductSubcategory],
[ProductSubcategory].[ProductSubcategory2] [ProductSubcategory2],
[Product].[ProductID] [Product],
[Product].[Name] [Name]
FROM
[Production].[Product] [Product]
LEFT OUTER JOIN (
SELECT
[ProductCategory].[ProductCategory] [ProductCategory],
[ProductCategory].[ProductCategory1] [ProductCategory1],
[ProductSubcategory].[ProductSubcategoryID] [ProductSubcategory],
[ProductSubcategory].[Name] [ProductSubcategory2],
[ProductSubcategory].[ProductSubcategoryID] [ProductSubcategoryID]
FROM
[Production].[ProductSubcategory] [ProductSubcategory]
INNER JOIN (
SELECT
[ProductCategory].[ProductCategoryID] [ProductCategory],
[ProductCategory].[Name] [ProductCategory1],
[ProductCategory].[ProductCategoryID] [ProductCategoryID]
FROM
[Production].[ProductCategory] [ProductCategory]
) [ProductCategory] ON [ProductSubcategory].[ProductCategoryID] = [ProductCategory].[ProductCategoryID]
) [ProductSubcategory] ON [Product].[ProductSubcategoryID] = [ProductSubcategory].[ProductSubcategoryID]
WHERE
CAST(1 AS BIT) = 1
ORDER BY
[ProductCategory], [ProductSubcategory]