Tuesday, January 12, 2010

Parameters in Report Builder...

Report Builder 2.0: When developing reports, there are situations where on needs to create parameters. The purpose of parameters is allow the user to generate reports for a specific set of values. One of my colleague had to build out a report using report builder 2.0  and wanted to pass an ID as a parameter. In this situation the SQL was going to be executed as a query. At first i tried to create a parameter in report builder and defined all the necessary attributes for the parameter. The stumbling block was how to pass the parameter to the SQL Statement in the dataset being created, again we were not using a stored procedure. In the sql query as part of the where clause, i changed the clause to something like Where CustomerID = @CustomerID. When i used the placeholder @CustomerID in the where clause, this caused report builder to automatically create a parameter called CustomerID. Once this was complete at run time of the report there was a prompt requesting the user to input the CustomerID. So the query in the dataset looked like
SELECT CustomerName,CustomerCity,CustomerAddress
FROM Customer
WHERE CustomerID = @CustomerID