Telerik Reporting: Filtering Your Reports Using Report Parameters

Users can control how data is used in a report by taking advantage of the Report Parameters feature in Telerik Reporting. You can add parameters to your reports at design time through an editor, at runtime through a user interface in the Report Viewer, programmatically, or via a SubReport parameter collection. In this post I’ll show you how to set up a report that allows users to enter parameter values through the UI of the Report Viewer. This functionality provides users with the means to filter their reports quickly so that only the most relevant data is displayed.

Setup the Report

To get started, create a new class library project in Visual Studio, name it ReportLibrary, and add to it a Telerik Report. When you are greeted with the Telerik Report Wizard, click Next until you are on the Choose Data Source page. Select Create new Data Source and click Next.

On the following page, select Database as your data source type and click Next. Next choose a connection to an instance of the Northwind database from the drop down list. If you do not have such a connection already set up, click the New Connection button to set up a new connection to Northwind. Click Next to continue to the next page of the wizard. Now you should be greeted with the Choose Your Database Objects page of the wizard. Instead of using the Query Builder, paste in the following query:

SELECT [t1].[CompanyName], [t0].[OrderDate], [t0].[RequiredDate], [t0].[ShipCountry], (

 SELECT SUM([t3].[value])

 FROM (

 SELECT (CONVERT(Decimal(29,4),[t2].[Quantity])) * [t2].[UnitPrice] * (CONVERT(Decimal(29,4),1 - [t2].[Discount])) AS [value], [t2].[OrderID]

 FROM [Order Details] AS [t2]

        ) AS [t3]

 WHERE [t3].[OrderID] = [t0].[OrderID]

    ) AS [OrderTotal]

FROM [Orders] AS [t0]

LEFT OUTER JOIN [Customers] AS [t1] ON [t1].[CustomerID] = [t0].[CustomerID]

I’m no T-SQL guru, so I definitely did not come up with this complicated T-SQL query all on my own. However, I am pretty good at LINQ, so I used LINQPad to write a query against the Northwind database. A nice feature of LINQPad is that it converts your query into T-SQL for you, making my job much easier. Thank goodness for developer tools. But I digress…

Enter a DataSet name of NorthwindOrdersDataSet and click Next.

Select a Standard report type on the next page and click Next. On the Design Data Layout page select all available fields and add them to the Details section. Click Next.

Leave the default Report Layout options on the next page and click Next, then select the Civic style sheet on the Choose Report Style page and click Next. Click Finish and you should be greeted with a nicely formatted report.

If you view the report in the Preview tab, it should look like this:

Add Report Parameters

To add report parameters to your report, simply click on the ReportParameters property in the report properties window.

You’ll be greeted with the ReportParameter Collection Editor. Click Add to add a new parameter. In the case of this report, we want to have the ability to filter by a start and end date for our orders, so the first parameter you should add will have a name of StartDate with a Type of DateTime. In the UI section, enter a Text value of Start Date and ensure you set the Visible property to True. This will ensure that the report parameter is visible in the Report Viewer when the report is deployed. To finish up with the report parameters, add a second with identical properties, but with a name of EndDate and a Text value of End Date.

Create a Filter

Finally, the only thing left to do is to add a filter so that the Orders data displayed within the report falls within the values of the StartDate and EndDate report parameters. Click on the Filters property in the report properties window, which will bring up the Edit Filters dialog. Click New and add the following two new filters:

With the filters in place, you can now return to the Preview tab to display your report. You’ll notice that there are now two data entry fields at the top of the report viewer, Start Date and End Date. Enter a start date of 7/1/1996 and an end date of 7/15/1996, the click Refresh in the top right-hand corner of the report viewer. You should now see the following report, which is filtered according to the report parameters you specified.

Wrap Up

Specifying report parameters in the report viewer is just one of the ways to pass values to your reports. As you’ve seen in this demo, report parameters are a very powerful feature as they allow you a greater level of control over the data that is displayed in your report.

If you are interested in the source code used in this demo, you may download it below.

[Source: C#]