Reporting On Data From Stored Procedures (part 1)

Introduction

The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The dataset can use a SELECT query, which is the most common way of retrieving data and one that you’re probably already familiar with.  But it can also use a Stored Procedure (aka stored proc or SP).

The purpose of this article is to demonstrate how data can be retrieved from a SQL Server database through Stored Procedures defined in that same database, and then displayed in a SQL Server 2008 Reporting Services report.

I’ll be using the AdventureWorks2008 sample database available for download at CodePlex.

What Are Stored Procedures?

There are actually different types of stored procedure in the context of SQL Server.  The type that I am using in this article is called a “Transact-SQL Stored Procedure”.  According to the Books Online, this type of stored procedure is:

“A saved collection of Transact-SQL statements that can take and return user-supplied parameters.”

If you have experience using a regular programming language such as Visual Basic or C#, I’m sure this sounds familiar.  You can think of a stored procedure as a method that takes any number of parameters, depending on its definition, and that possibly returns a result, again depending on its definition.  Instead of being stored in a compiled .exe or .dll, it is stored in the database.

In this article I will be using some stored procedures that return a dataset as result.  How stored procedures are written is not the purpose of this article.  For that I’d like to refer you to this Books Online page: Implementing Stored Procedures.

Reasons For Using A Stored Procedure

There are several reasons why it is more interesting to use datasets based on stored procedures as opposed to SELECT statements.

Performance: stored procedures perform faster than SELECT statements.  The reason for this is because they are compiled when they’re created and their execution plan gets stored by SQL Server so that it can be reused for each procedure call.

Maintenance: when a database needs to undergo some changes to its schema, the changes can be handled in the stored procedures.  This makes it transparent for the reports that are using these stored procedures.  The reports will keep functioning as expected without any modifications to them.

Reuse: imagine a situation where several reports are reporting on the same set of data.  If you wouldn’t use stored procedures, you may have to repeat a possibly complex query in each report.  With stored procedures you just need to define the query in the stored procedure and then call it in each report’s dataset.

Security: in environments where DBAs are responsible for the SQL Server databases, the report developers will possibly not get sufficient rights to retrieve data from the tables directly.  One of the ways to prevent this is to give them access to a bunch of stored procedures and views instead.

Simple Procedure Call

Okay, enough theory, time to show you how it’s done!

Setting Up The Stored Procedure

Our first procedure queries the AdventureWorks database to return a list of employees with their corresponding department.  Here’s the code for the SP:

CREATE PROCEDURE GetEmployeeData
AS
BEGIN
    SELECT E.NationalIDNumber, E.JobTitle, E.BirthDate, E.MaritalStatus, E.Gender,
        E.HireDate, E.SalariedFlag, E.VacationHours, E.SickLeaveHours,
        D.GroupName as DepartmentGroupName, D.Name as DepartmentName,
        P.FirstName, P.MiddleName, P.LastName
    FROM HumanResources.Employee E
    INNER JOIN HumanResources.EmployeeDepartmentHistory EDH
        ON EDH.BusinessEntityID = E.BusinessEntityID
        AND EDH.EndDate IS NULL -- current active department does not have EndDate filled in
    INNER JOIN HumanResources.Department D
        ON D.DepartmentID = EDH.DepartmentID
    INNER JOIN Person.Person P
        ON P.BusinessEntityID = E.BusinessEntityID;
END

Creating The Report

The next step is to create a new Report in a Report Server project using Business Intelligence Development Studio 2008 (aka BIDS).  To add a report my preferred way is to right-click the Reports folder in the Solution Explorer (this is assuming that you’ve already created a Report Server project) and then select Add > New Item… :
 
Creating a new Reporting Services report
 
Make sure that the Report template is selected.  I’m calling my report StoredProcDataset.
 
Add New Item - Report
 

Creating A Shared Data Source

Now that we’ve got an empty report we still need to get some data.  We’ll set up a Shared Data Source first.  Shared Data Sources are convenient when you’re planning to create several reports on the same database.  It removes the connection string from the report itself and puts it in a separate Shared Data Source object, making it easy to switch between databases.  (Imagine putting your reports into the production environment – instead of needing to modify each report to connect to the production database, you just need to modify the Shared Data Source).
 
In the Solution Explorer, right-click the Shared Data Sources folder and select Add New Data Source.
 
Solution Explorer Shared Data Sources - Add New Data Source
 
 
Shared Data Source Properties
 
I’m calling the Shared Data Source AdventureWorks2008.  A Data Source can connect to several different sources, such as Oracle or an Analysis Services cube.  In fact, it can connect to any source through OLE DB, as long as there’s an OLE DB provider that has all the expected functionality implemented.  For a good list of drivers, have a look at the Books Online: Data Sources Supported by Reporting Services.
 
The one that we’re interested in now is called Microsoft SQL Server, which is by default selected in the Type dropdown.
 

Creating The Data Source

Next we’ll add a Data Source to the report based on the Shared Data Source.
 
Report Data pane - New Data Source
 
In the Report Data pane, select New > Data Source….
 
If you don’t see the Report Data pane, you can open it by going to the View menu item and selecting Report Data right down at the bottom of the menu window.  Or hit CTRL + ALT + D.  That’s an interesting shortcut to memorize because the Report Data pane has a tendency to disappear, especially if you’ve closed and re-opened the BIDS.
 
Data Source Properties
 
I’ve called my Data Source srcAdventureWorks2008 and I’ve told it to use the existing Shared Data Source.
 

Creating The Dataset

So, now we’re ready to query our stored procedure.  In the Report Data pane, right-click the newly created Data Source and select Add Dataset….
 
Right-click Data Source to Add Dataset

 

Dataset Properties

I’m calling the Dataset dsEmployeeData.  The Data Source edit box is already prefilled with the right data source because we’ve right-clicked the data source to which we want to add a dataset, how easy can it be?

To query a stored procedure from a dataset is really very straightforward.  All you need to do now is select the Stored Procedure radio button.  Doing that replaces the bottom part of the window.  Instead of an edit box that expects a query, we now get a simple dropdown where we need to select our SP.

Once you’ve done that, select the Fields page on the left.

Dataset Properties - Fields

As you can see, the list of fields has been pre-filled.  The BIDS queries the stored procedure’s metadata so we do not need to manually specify the fields returned by our SP, saving us quite some time!

Once you click OK to close the Dataset Properties window, you’ll see that the Report Data pane gets populated with our list of fields.

Report Data pane - list of fields in dataset

Show Me The Data

To prove that everything is working as expected, let’s set up a quick report using a Table.  From the Toolbox pane, drag a Table object onto the report canvas:

Report with a Table

Next, from the Report Data pane, drag some fields into the cells in the Data part of the Table.  As we’re dealing with employee data, it would be interesting to see their names.  I’ve also added their job and department.

Resize the columns a bit so that everything will fit nicely, and color the Header cells different from the Data cells to easily distinguish them.

Table with some fields added and basic layout

Now we’re ready to run the report, so click the Report’s Preview tab.

Table showing some employee data

So, that was our first procedure.  Wasn’t too complicated, was it?  So, ready for another one?

Parameterized Procedure Call

This time we’ll make it a little more complicated.  As you know, stored procedures can take parameters.  And SSRS knows how to pass them into a stored proc.  So let’s do that!

Setting Up Stored Proc Version 2.0

The stored procedure shown below is based on our previous one.  Except now it takes one parameter: @HireDate.  The SP will only return employees that have been hired after given HireDate.

ALTER PROCEDURE GetEmployeeData
    @HireDate date
AS
BEGIN
    SELECT E.NationalIDNumber, E.JobTitle, E.BirthDate, E.MaritalStatus, E.Gender,
        E.HireDate, E.SalariedFlag, E.VacationHours, E.SickLeaveHours,
        D.GroupName as DepartmentGroupName, D.Name as DepartmentName,
        P.FirstName, P.MiddleName, P.LastName
    FROM HumanResources.Employee E
    INNER JOIN HumanResources.EmployeeDepartmentHistory EDH
        ON EDH.BusinessEntityID = E.BusinessEntityID
        AND EDH.EndDate IS NULL -- current active department does not have EndDate filled in
    INNER JOIN HumanResources.Department D
        ON D.DepartmentID = EDH.DepartmentID
    INNER JOIN Person.Person P
        ON P.BusinessEntityID = E.BusinessEntityID
    WHERE E.HireDate > @HireDate;
END

 

As the HireDate field in the HumanResources.Employee table is of type date, I chose to make the parameter that same data type.

Please note that the above script uses ALTER PROCEDURE instead of CREATE PROCEDURE.  This will only work if you’ve already created the previous procedure.  If not, just replace the word ALTER with CREATE.

Modify Report To Pass Parameter To Stored Procedure

The next step is to modify our existing Dataset so that it passes a date into our stored procedure.

Open up the Dataset’s properties by double-clicking it in the Report Data pane.  Then select the Parameters page:

Dataset Properties - Parameters empty

As you can see, it is still empty.

Now switch to the Query page and click the Refresh Fields… button:

Dataset Properties - Query page - Refresh Fields button

Then switch back to the Parameters page:

Dataset Properties - Parameters page populated

The parameter has been automatically added when we clicked the Refresh Fields… button!

The BIDS again used the stored proc’s metadata to do this.  No need for manual intervention.  For now, leave the Parameter Value box as it is, empty.  Click OK to close the properties window.

Clicking OK caused another action in our report!  If you open up the Parameters folder in the Report Data pane, you’ll see that it has gotten an item as well:

Report Data pane - Parameters

You can double-click the @HireDate parameter to get its properties:

Report Parameter Properties

The BIDS has created a report parameter and linked it to the parameter in our Dataset.  It automatically chooses the correct data type for the report parameter, Date/Time in this case.  (Unlike in T-SQL, there are no separate Date and Time data types in SSRS 2008.)

To confirm that the report parameter is linked to the parameter in the Dataset, close the Report Parameter Properties window and open up the Dataset Properties again, selecting the Parameters page:

Dataset Properties - Parameters page - Parameter Value filled in

The Parameter Value for our parameter has now gotten a value, more precisely it’s referring to the Report Parameter called @HireDate.  If you don’t believe that it is actually the report parameter (I admit, it looks similar to the parameter in the Dataset, except for the square brackets surrounding the report parameter’s name.), click the fx button to see the formula:

=Parameters!HireDate.Value
 

Indeed, it is referring to the parameter on the report.

Test The Changes

Now that the parameter has been set up, it’s time to test our report again.

As the report is now filtering on the HireDate, it would be interesting to actually show this field in the table.  Add an extra column and drag this field into the data cell.

Our newly added field’s type is datetime, but we’re only interested in the date part.  So we’ll set up a format code.  Select the HireDate data cell and locate the Format property in the Properties pane.  Enter a small d into it:

Formatting a datetime to only show the date

This instructs the BIDS to show the field using the “short date pattern”.  A list of possible codes is available on MSDN: Standard Date and Time Format Strings.  As you notice, SSRS is using the regular formatting strings as they are known in .NET.

Note also that the format used to display the data depends on the report’s Language property.  I’ll leave it at its default: en-US.

Right, time to have a look at the report.  Select the Preview tab.  You can now see the Hire Date parameter which is expecting a value.  You can either type a value or select it from the calendar which is shown when you click the button on the right of the textbox:

Calendar control for a datetime report parameter

To be sure that you’re using the correct format for the date, I suggest to select a date using the control.  Then you can always modify it to whatever day you’d like to select.

For our test I know that some employees started before 2000 and others later, so I’ll select a date somewhere in the year 2000.

Report with datetime filter

And indeed, our report shows only employees that started after our selected HireDate.

Conclusion

With this article I hope to have shown you that it’s fairly straightforward to report on data coming from stored procedures.

In case the above hasn’t fulfilled your appetite on this subject yet, watch out for part 2.  In that sequel I will build further on the example used in this article to show you how you can use multi-value parameters to filter your report’s data.

In the meantime: happy reporting, and thank you for reading my article!

References

BOL 2008 – How to: Create an Embedded or Shared Data Source

BOL 2008 – How to: Create a Dataset (Reporting Services)

BOL 2008 – How to: Refresh Fields for a Dataset

BOL 2008 – Implementing Stored Procedures

BOL 2008 – Data Sources Supported by Reporting Services

Share

Tags: , , ,

  1. Sid’s avatar

    Thanks for this — I’m finally back into SSRS after a long time.

    I’m finding problems using the report designer
    Report Designer in 2008 SSRS R2 times out when adding a dataset which calls a long running stored procedure. Error Message:

    Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

    I can tell that when adding a stored procedure SSRS report designer is calling the proc.

    I have captured the trace on SQL profiler and I can see the procedure call.

    The stored proc on it’s own takes just over one minute to complete execution.

    Is there anyway to increase (looks like 30 seconds is the default) the time out when the report designer executes this proc call when designing the report?

    Thanks,
    Sid

    Reply

    1. Valentino Vranken’s avatar

      Hi Sid,

      (Sorry for not getting back to you sooner, the notification must have gotten lost in the bunch of emails during my holidays.)

      I’m not aware of a setting that would influence this. But what I would probably do in such a situation is to change the slow SP. The best change would be to rewrite it so that its speed is acceptable. When that is not an option due to the specific situation, I would temporarily add a SELECT statement at the top to return some dummy records. Obviously the fields returned should match with what would be returned in normal circumstances. Then once development is finished you can put the original SP back on the server.

      Best regards,
      Valentino.

      Reply

  2. Frank’s avatar

    I am new to SSRS. This article was extremely helpful in getting my first report (with a paramterized stored procedure) to execute. Thanks for providing this information.

    Reply

    1. Valentino Vranken’s avatar

      Thanks Frank, I’m glad it got you started!

      Reply

  3. Edmond’s avatar

    I just started learning about SSRS and I am extremely thankful for the step-by-step process that was posted. THANKS again.

    Reply

  4. rakesh’s avatar

    nice really this blog helped me out,thanx
    i will take ur help for further queries

    Reply

  5. DeeDee’s avatar

    Excellent! This really helped. None of the other documentation explained that stored procedures can accept parameters.

    Reply

  6. KK’s avatar

    What an amazing documentation! Well explained , chesive and very very useful! Thanks!

    Reply

  7. adrian’s avatar

    I have a problem when I used stored procedure because the ssrs take the first sql inside de procedure,
    do you know how I can do it to take the last one?

    Thank you

    Adrian

    Reply

    1. Valentino Vranken’s avatar

      Hi Adrian,

      Perhaps by using a parameter and an IF statement?

      IF @param = 1
      SELECT statement 1
      ELSE
      SELECT statement 2

      IF statement: http://msdn.microsoft.com/en-us/library/ms182587.aspx

      Please share more details if you’re expecting a more detailed reply.

      Regards,
      Valentino.

      Reply

© 2008-2014 BI: Beer Intelligence? All Rights Reserved