How to create a basic SSRS report with Parameters

Posted on Jan 18 2014 - 11:50am by admin

Today, let us discuss how to create a basic SSRS report in BIDS or in other words Visual Studio 2008R2. This post is mainly intended for developers who have a basic idea on T-SQL but haven’t worked on Business Intelligence tools. The primary objective of this post, is to walk you through all the screens, and create a basic SSRS report using AdventureWorks2008R2 Database. Lets get started.

*** Click on the images to make them bigger in size to view good ***

Step 1: Open BIDS from the Start Menu. Start –> Programs –> Microsoft SQL SERVER 2008 R2 –> SQL Server Business Intelligence Development Studio.

Step 2: Once you open BIDS, Click Create a new Project and new window pops up as shown below.
create-1
Select the Report Server Project and give it a name. In our case we chose SQLSaga and click OK to continue.

Step 3: Once you create the project, you should see a Solutions explorer window to the right side of the screen with three empty folders.

  1. Shared Data Sources
  2. Shared Data Sets
  3. Reports

Shared Data Sources is the folder where you will specify all the different data sources you want to use in the reports. Also note that, you can specify Embedded data sources per report if you don’t want to make them shared.

Shared Data Sets are like queries that give you data for your parameters / reports etc. You can also specify them inside your report making them exclusively available for that report only.

Reports folder is where you add / create all your reports.

Step 4: Let’s add a Data Source to our Report on which we use our queries to pull the data from. Please follow below images to create a data source.
ds-2
Once you click “Add New Data Source” another window opens up asking you to pass the credentials and the type of Database you are going to use. Refer below image for understanding.
ds-2.1
You need to pass the connection string to connect to a data source. You can either do it Manually, or click Edit button which opens up the following window as shown below.
ds-2.2

Step 5:
We will not add any datasets in this example, but we will cover that in a later example. Now it’s time to add a report to our Solution we are creating.
re-3

This opens a new window to create a new report as below.

re-3.1

You can modify the name as per your requirement, but in our case we called it TestReport. A report created in SSRS will be created with an Extension “.RDL” which stands Report Definition Language. Click Add to add an empty report to the Solution.

Step 6:
Now its time to add a query to the report to pull data from. As discussed above we use a Dataset for that purpose. We will use a Embedded Dataset instead of a Shared Dataset in this example.

On the left side of the screen, in the Report Data window, identify the folder DataSets. Right Click it and Click on Add Dataset as shown below.
rd-4
Once, you click that a new window opens up which looks like below.
rd-4.1

Even though you have a shared data source, you have to select the “New” option as pointed with 3 because it will open a new window which will allow you to select the shared data source. Once you click New, next window will be as shown below.

rd-4.2

After selecting the Shared Data source as shown, Click OK and You will be taken back to the screen where you started.

rd-4.3

If you are using a T-SQL Query, use Text radio button. If you are using a Stored Procedure, use the radio button that is available for Stored Procedure. In our case we are using a straight T-SQL Query with two parameters, so we selected Text. Input the query, click Refresh fields and say OK.

Step 7: After you click OK, your Report Data tab will change completely than before as shown below.
param-5

Step 8: Now its time to set up some values for the parameter’s so that the users can use them easily. Normally we use the feature of Shared Datasets / Embedded Datasets to populate the possible values for Parameters but in our example we will manually input them.

To populate the parameters available follow the images below.
param-5.1

When you click the parameter properties, it will open a new window which allows you to input the Available values or Default values available for that parameter. You can also change the data type of the parameter and also have options whether to show it or hide it in the screen etc. Refer the below image.
param-5.2

To pass a list of available values click on Available values tab in the window and it will take you to the location where you can enter all the available values for the parameter either manually or through a query(in other words a dataset). Refer to below image on how to add available values to a parameter.
param-5.3

By following same steps as above we pass the values for our other parameter as well as shown below.
param-5.4

In this example, we don’t pass any default values to the report. When you pass a default value to the report, the report will run automatically with that set of inputs set as default when it is previewed. Remember that, You can change the inputs and re run the report at any point of time.

Step 9: Now, its time to create a report using SSRS. Let us create a Table report since we are just building a very basic report. We will see more other types reports going forward. Using the Toolbox, drag and drop a Table into the Report design window.
final-6

Select all the columns as required as shown below.

final-6.1

Click on Preview pane, when you are ready which will show preview us our report we have designed so far.

When you open the preview pane, you will have your parameters set to nothing. Select your parameters and click view report on the right side which will render the report based on your parameters as shown below.

final-6.2

*** Like Us on Facebook to get instant notifications about new posts ***
*** Please leave your feedback below in the comments section if you have found this post useful ***
*** If you have any questions, please leave a comment ***
*** Click on the images to make them bigger in size to view good ***

Leave A Response