SQL Server Reporting Services, or SSRS for short, is a set of tools provided by Microsoft that allows you to create, deploy and run reports against your database. Simply put, it gives you a simple front-end portal for the end user to pull data directly from SQL Server without having to know how to query data, but more importantly, without them requiring access to the database. Though creating reports in SSRS is actually pretty easy, some users who are new to SSRS tend to struggle to understand all the moving parts and how they fit together.
The purpose of this post is to provide a detailed outline of the necessary steps from creating the stored procedure that will run the report to building a front-end report using the Business Intelligence module in Visual Studio.
Before you can follow the steps outlined below, make sure you have access to a SQL Server database as well as a copy of Microsoft Visual Studio with the Business Intelligence module installed. If you do not have BI installed you should be able to download it from Microsoft. For the purposes of this post I will be using Visual Studio 2013.
The first step is to develop the report logic by creating a stored procedure that will drive the report. In the example below I am using the
TSQL2012 database provided by Microsoft and querying the
HR.Employees table to pull back employee data.
CREATE Procedure [dbo].[sp_hr_employee_report]
IF @empid IS NULL
IF @empid IS NOT NULL
WHERE empid = @empid
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
In addition to simply querying the
HR.Employee table I have also assigned a parameter to the report to allow the user to either run the report open-ended or to specify a particular employee ID. This will allow me to demonstrate how to handle parameters within SSRS in Visual Studio.
If you have access to the
TSQL2012 database you can copy/paste the above script to create the stored procedure on your database. Otherwise you can just build a simple stored procedure with at least one parameter to pull data from a table you are familiar with.
Once the stored procedure has been created you may want to run a quick test to make sure you can call it. The script below will execute the newly created stored proc and pass a variable to the procedure to assign to the parameter. In this case I am passing NULL so it will return all records in the table.
EXEC sp_hr_employee_report NULL
If you are using using the
TSQL2012 database the output should look something like this.
Now that you have a working stored procedure it’s time to create a front-end report to use the procedure to pull back data and display it to the end user.
The next step is to open Microsoft Visual Studio. I will be using Visual Studio 2013 but you should be able to use any recent version as long as you have the corresponding Business Intelligence suite installed.
Once Visual Studio is open you’ll want to create a new project. In most cases you should see the option on the left pane of the window. If not, you can always create a new project by clicking File>>New>>Project.
On the New Project window you will need to select a few specific items. On the left hand side select the drop-down for Business Intelligence and select Reporting Services. In the center pane select Report Server Project. At the bottom of the window, provide a Name for the project as well as a name for the Solution. A solution can have multiple projects within it. In this example I am naming the Solution Reports as I may want to include additional reporting projects within this solution at a later time.
Once you have selected the appropriate items and named your project and solution click OK.
Now that you have created the Solution as well as the Project within the Solution you will need to add a new report to the project. To do this, right-click the Reports folder under the newly created project within the Solution Explorer and select Add>>New Item.
A new window will be displayed which will allow you to select a Report Project and name the new report. On this screen select Report in the center pane and name the report at the bottom. Be sure to keep the .rdl extension as this is the standard file type for all SSRS reports.
Once you have named the report click Add.
To confirm the report was added go back to the Solution Explorer and click the Reports folder. You should now see you’re new report displayed in the drop-down.
At this point you now have an empty report as shown in the following screenshot.
The next step is to configure the Data Sources and Datasets to allow your front-end report to connect to the stored procedure that was previously created.
We’ll start by creating a Data Source. In the Report Data pane on the left hand side of the screen right-click Data Sources and select Add Data Source…
The Data Source Properties window will be displayed. The first step is to name your data source. If this is the first data source created for this project the default name will be DataSource1. When building reports I typically use the default name unless I plan on having multiple data sources. Once the name has been specified, click the Edit… button next to the Connection String box to open the Connection Properties window.
On the Connection Properties window you will specify the server name and database that correspond to the stored procedure that was previously created. In the example below I am using my
lab-sql2012 SQL Server and specifying the
TSQL2012 database. By default Windows Authentication is used to log into the server. Unless otherwise required, this should be sufficient for most deployments of SQL Server Reporting Services.
Click the Test Connection button to verify that the report can successfully connect to the database. If successful, click OK to close the Connection Properties window.
You should now see the newly created connection string displayed within the text box on the Data Source Properties window.
Click OK to close the Data Source Properties window.
Now that you are back on the main screen in Visual Studio you should see the newly created DataSource1 (or whatever you named it) under DataSources in the Report Data Pane.
The next step is to create a new Dataset. Right click Datasets in the Report Data pane and select Add Dataset…
The Dataset Properties window will be displayed. This is where you will configure SSRS to pull data from the stored procedure that you created.
The following screenshot has already been configured to reference the
sp_hr_employee_report stored procedure. By default the new dataset will be named DataSet1 if this is the first dataset being created within the project.
Next you’ll want to select the radio button next to Use a dataset embedded in my report. This will modify the screen options to allow you to select the newly created DataSource1 as well as specify the Query type as Stored Procedure.
Select the Stored Procedure radio button and then copy/paste the name of the stored procedure into the provided text box as shown below.
If you had not previously created a stored procedure you could select the Text option under Query Type and create an in-line SQL query to pull your report. Personally I do not recommend this as best practice is to always create a stored procedure for any reports….just worth mentioning though!
Once the stored procedure has been specified you can click the Fields option on the left hand side of the screen and you should see the available fields that will be queried from the stored procedure. From here you can provide custom names for the fields if desired.
Though not always necessary, you can also further configure the default report settings by referencing the Options, Filters, and Parameters screens.
Once the dataset has been configured click OK to close the Dataset Properties window. You should now see the newly created DataSet1 along with all the available columns included in the stored procedure.
Now that a Datasource and Dataset have been created the next step is to design the report.
Open or expand the Toolbox within Visual Studio. You should see the Toolbox option listed next to the Report Data pane. If not, just click View on the menu and select Toolbox.
There are a variety of options available within to Toolbox that can be used when building more complex reports. For the purposes of this example I will be creating a simple report viewer using the Table item within the Toolbox.
Click the Table item and drag it onto the main Design pane within Visual Studio. By default this will create 3 columns and 2 rows. The top row will be titled Header and the bottom row will be titled Data. Since the stored procedure actually includes 9 columns I’ll need to add additional columns to the table before I begin assigning the columns to the report table. To do this right-click anywhere in the column header section and select Insert Column>>Left. Since no data has been added at this point you could also choose to insert the column to the Right. Repeat this step until you have 9 columns.
Once you have the appropriate number of columns it’s time to add the individual dataset fields to the report. You’ll want to add each dataset field to it’s own column on the report. To do this, pull up the Report Data pane and click on each of the fields listed under your dataset and drag and drop each to a separate column in the report designer.
Once this has been done, your report design should resemble something like the following screenshot.
As you can see each field from the dataset has been added to a unique column within the Table added to the designer.
At this point, for a standard report without any parameters you would be done. The report could then be previewed, saved and deployed to your Reporting environment.
To take this a step further, as previously discussed, I created a parameter within the original stored procedure to allows the user to run the report for a specific employee ID. However, I also want the user to be able to run the report open-ended. In order to do this I need to modify the parameter within the report designer to allow it to accept
From the Report Data pane, select the Parameters drop-down and locate the parameter that was created in the stored procedure. Right-click the parameter and select Parameter Properties.
The Report Parameter Properties window will be displayed. Though there are a variety of ways to configure the various properties of individual parameters, in this case I simply want to check the box next to Allow null value so that the report can successfully pass a NULL to the stored procedure if a value is not specifically specified.
Once the option has been checked click OK.
You can now click the Preview pane within Visual Studio to preview the report. By default the report will run with the
NULL option selected. Once it completes you can uncheck the
NULL option and specify a value to pass to the parameter.
In the example below I have selected an
empid of 1 and kicked off the report.
Once the core report has been built and configured you may need to tweak the layout of the report to meet your specific needs. You can use the designer to drag and expand various column widths as well as change the parameter name and column names to be more user friendly. The best way to learn all the features that are available within SSRS is to just dig in and build reports. I hope, at the very least, this has given you some basic understanding of how to build and design an SSRS report.