Skip to content

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

May 2, 2009

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:

ssrsdemo01

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.

ssrsdemo021

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:

ssrsdemo03

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:

ssrsdemo04

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):

ssrsdemo051

We’re done. See our Report in action:

ssrsdemo06

Creative Commons License
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!)

kick it on DotNetKicks.com

About these ads
7 Comments leave one →
  1. Raja permalink
    September 21, 2010 10:52 AM

    How do I filter the data for a report at runtime without any parameters?

  2. May 11, 2010 3:03 AM

    May i knw how to fill the dropdown in SSRS using the Stored procrdure based on the system user login condions

  3. Vani permalink
    July 7, 2009 8:24 AM

    Hi,

    Articles on SSRS are very good.
    But,I have a small request. Can You provide information on how to link up sql server reporting services with Share point. The installation process and linking up.
    Also, how to create graph reports in SSRS.

    Thanks in advance.

Trackbacks

  1. 2010 in review « Marlon Ribunal's Note On SQL Server
  2. How To: Report Authoring in SSRS 2005 – Part V (Pimp Your Parameters) « Marlon Ribunal's Note On SQL Server
  3. How To: Report Authoring in SSRS 2005 - Part III (Designing Manually) « Dangling On The Shoulders Of Giants

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

  • Archives



  • RSS SQLServerPedia

  • RSS SQL Crunch – Hot SQL Links

  • My Bookmarks

  • Business Career DBA Tool Events free ebook Humor IT News Members Only Microsoft MySQL Operating System Oracle Personal Reporting Services SQL Server SQLServerPedia Syndication SSC SSIS TSQL Tutorial Uncategorized Video


  • Software Blogs - BlogCatalog Blog Directory
  • Follow

    Get every new post delivered to your Inbox.

    Join 28 other followers

    %d bloggers like this: