Some times we may need to implement reports which has the ability to pick multiple parameters. If no parameter is selected it should run for all the values. Let's see how this can be achieved using Telerik Reporting. As an example let's consider a system which has banks and branches. Banks have many branches (One to many relationship). In this example banks are parameters. It's multi value.
Fig 1
If you take the Fig 1 User has run the report by selecting two banks. Then report will be generate for 2 banks.
Fig 2
If you take the Fig 2 User has run the report without selecting any banks.Then report will be generated for all the banks.
Let's see how we can achieve this kind of functionality using telerik reporting.
After creating a new telerik report we have to add data source. In this example we will add Sql Data Source. To achieve this multy value parameter scenario we can't use stored procedures. We have to define SQL Statement in the report it self.
Here is the Sql statement to achieve this functionality.
SELECT B.BankName,B.ChequeNumberFormat,B.Active, BB.BranchName,BB.BranchCountry,BB.BranchSortCode
FROM dbo.Bank B INNER JOIN dbo.BankBranch BB ON B.BankID = BB.BankID
AND ((B.BankID IN (@bankIds)) or COALESCE(@bankIds,null) is NULL)
We have to define the parameter as B.BankID IN @bankIds. When parameter is not selected by user to display records for all the banks, we have to add COALESCE(@bankIds,null) is NULL.
This part is not much documented in Telerik reporting documentation.
Fig 3
And it's really important to define parameters MultiValue property as True during the setting Data source operation. Otherwise you will get an error.
This can be achieved using Entity frame work data Sources as well. I hope to publish a post how to implement this scenario using Entity framework in the future. Please comment if you have anything to say regarding this.
No comments:
Post a Comment