How To: Report Authoring in SSRS 2005 – Part IV (Adding Parameters)

After a long break on this series, I am finally continuing to Part IV. This SSRS How-to is just an introduction to the Reporting Services and never intended for the advanced users. This was clearly stated in Part I of this series. In Part II I showed how the Report Wizard can be used to build a decent report. Part III demonstrated how to manually build a report.

In this part, I’ll show how a simple parameter can be added to the report we have built so far. Let’s get started.

Here’s how our report currently look like:

What we are required is to filter the report by “Department”. So for that end, we will create a stored procedure that will meet the said requirement.

USE [SQLServerBlogDemo]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Report_Department]

@department NVARCHAR(50)

AS
BEGIN
SET NOCOUNT ON;

SELECT e.EmpFirstName, e.EmpLastName, e.EmpBirthDay, e.EmpSSN
FROM Employee AS e
JOIN Department AS d
ON e.DeptID = d.DeptID
WHERE d.DeptName = @department

END

After we have the Stored Procedure in place, we’re now ready to work the report itself using the Report Designer.

Let’s modify our dataset by using the Store Procedure that we have built instead of the plain TSQL statement.

When you modify the datasource make sure that you rebuild the design (rows, columns, etc) using the new dataset to avoid data-related errors. The designer should be able to detect the parameter from the store procedure and it should know that you are intending to use the said parameter. This is how it looks like in the designer’s preview:

But as you might have noticed from the figure above, the report consumer has to type in the Department name to come up with the results. Suppose your company have tens of department names; chances are you do not want to be typing and memorizing all these names. You would want to just choose from a dropdown box and pick the appropriate department name. So we will modify our design a bit.

First, we create a new dataset for the department names. We can just create a simple query against our database to extract the Department names:

And here we’re modifying our existing Parameter. Just notice the option as indicated by the arrow and how we utilized our new dataset (see red box):

We’re done. See our Report in action:

How To: Report Authoring in SSRS 2005 by Marlon Ribunal is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 United States License

Next >> Pimp Your Parameters! (Updated!)