Parameterized Report using a Stored Procedure

Download Sample Project: Parameterized Report

In a recent forum post on BIDN.com a person asked how to filter the result on the report based on a stored procedure parameter. Since a short blog post without screen shots wouldn’t fully explain how to accomplish this, I decided to write a quick post to assist the person. I did a quick search on the web, and I found a few results but nothing that I thought was a good beginner posting. So here is my version.

The Stored Procedure

First you start with a couple of stored procedures. One stored procedure (Script 1-1) will provide the data set for the report. The second stored procedure (Script 1-2) will the act as the source for the parameter. The second stored procedure is not required, but at think it adds to the usability of the report.

USE AdventureWorks2008 GO IF(OBJECT_ID('dbo.GetStoreSales')) IS NOT NULL DROP PROC dbo.GetStoreSales GO CREATE PROC dbo.GetStoreSales @StoreID int AS SELECT s.Name StoreName, soh.OrderDate, soh.TotalDue, soh.PurchaseOrderNumber FROM Sales.SalesOrderHeader soh INNER JOIN Sales.Customer c ON soh.CustomerID = c.CustomerID INNER JOIN Sales.Store s ON c.StoreID = s.BusinessEntityID WHERE c.StoreID = @StoreID

Script (1-1)

USE AdventureWorks2008 GO IF(OBJECT_ID('dbo.GetStores')) IS NOT NULL DROP PROC dbo.GetStores GO CREATE PROC dbo.GetStores AS SELECT BusinessEntityID, Name FROM Sales.Store

Script (2-1)

The Report

Now that the stored procedures are created let’s create the report. Using Business Intelligence Development Studio (BIDS) create new report project. Add a share data source to the project. In this example the data source will connect to the AdventureWorks2008 database. Next add a new report to the project. The solution explorer should look similar to Figure 1-1.

Figure 1-1

Now you must create two data sets. First, on the Report Data tab add a new data source using the Share Data Source that was created earlier. Then right-click on the new data source and select Add Dataset. The Dataset Properties window will appear. Name the data set Stores, select the radio button labeled stored procedure and choose the GetStores stored procedure from the drop down list. Your Dataset Properties screen should resemble Figure 1-2.

Figure 1-2

Repeat the above steps that were used to create the Stores data set to create the second data set. The only difference is that you will choose GetStoreSales from the stored procedure drop down list and name the dataset GetStoreSales. Since the stored procedure contains a parameter a parameter is automatically added to your report. Expand the Parameters folder and you will notice a StoreID parameter in the listing (Figure 1-3).

Figure 1-3

Double click the parameter and the Report Parameter Properties window will appear (Figure 1-4)

Figure 1-4

Change the value in the textbox labeled Prompt to Choose Store. Click Available Values in the left navigation pane. Select the radio button labeled Get values from a query. Then select Stores from Dataset drop down list, select BusinessEntityID from the Value field drop down list and select Name from the Label fields drop down list. Click OK. The screen should resemble Figure 1-5.

Figure 1-5

Now click the Toolbox tab and drag a table onto the Report design surface. Click back on the Report Data tab drag the columns from the StoreSales dataset onto the table that you want to include in your report. Once you have added all the columns Preview your report. You will notice a drop down list at the top of the report labeled Choose Store. Select the store that you want to filter the report by and click the button labeled View Report. The table will contain only data for stores that match the selected value in the drop down list (Figure 1-6).

Figure 1-6

I know that this may be obvious or common place for most of us. However, for those of you just getting started with reporting service I hope this helps you out. As always, if you have any questions or comments about this posting please feel free to email me at pleblanc@pragmaticworks.com.

Talk to you soon.

Patrick LeBlanc, MCTS

Founder http://www.tsqlscripts.com/ and http://www.sqllunch.com/.

Visit http://www.bidn.com/, Bring Business Intelligence to your company.