Creating a Parameterized reports in SSRS

We learned custom report generation in our previous post i.e Creating report without using Report wizard. So, now in this article we will go further in report generation using MS SQL Server Reporting Services (SSRS) 2008. This article will take you through the steps needed for creating a parameterized report using SSRS.

What do we mean by a Parameterized reports?

1. Parameters are used in a report to provide a dynamic feature by providing user input to the report execution process i.e. the report is generated as per the inputs supplied by the user.
2. Parameters are usually used to filter large set of records so as to narrow down the end report for better analysis.
3. Parameters can be data-driven, or they can have a static list of valid values.
4. Parameterized reports are frequently used for drill-through and sub-reports, connecting reports with related data.

Let’s validate our theoretical concepts with the practical implementation of a Parameterized reports.

STEPS TO FOLLOW: –

Step 1. Go to start, then All Programs and click on Business Intelligence Development Studio (BIDS).Click on create a New project. This will show New project screen. Choose Report Server Project from the template under Business Intelligence Projects tab. Specify desired Name, Location, Solution name for the new report server project.

Step 2. Go to Reports under Solution Explorer, then go to Add and choose New Item option from the menu. Choose Report and provide the name for the report. This will show you the working environment where we will design and preview our report.

Step 3. Drag and drop the table option from the toolbox into the design window. This will pop up Data set properties window. Now, we need to configure this window with correct credentials. If everything is supplied correctly then clicking on Test connection button will pop a successful message box.

After testing the connection, click OK button. This will create a new data source connection. Now, we need to have data from the connected database which we made recently. For this click on Query Designer button.

Step 4. Click on Query designer button to generate the query automatically. Now, click Add table button and choose your tables from the database and execute the query. This will show you the query and columns from the database.

Adding Parameter to the report: – Now, we will add Gamer_Id as a Parameter field for this report. So our query will look like –

Select Gamer_Id, Name, Gender, Location, Game, Age

From Gamers_Data

Where (Gamer_ID=@param_id)

NOTE: – We can also write the query manually in the text area without going into the query designer part but it is a good way to write in a query designer as we can debug the query easily.

Step 5. Once you click OK button, go to the parameters folder in “Report Data” – you will find that@param_id automatically appears in the “Parameters” Fields. Double click on this parameter (@param_id). This will show Report parameter properties screen.

This is the place to configure, how parameter should behave – should it allow blank values or Null values or Multiple value. You can write any text under the prompt field to get the input from the user. Also, we can configure the Visibility, Data-types, Available values, Default values etc. Let’s leave it as it is for now.

Step 6. It’s the time to hit the preview button and see the report. Clicking Preview will ask for the input field ( As in here, it is asking “Enter Gamer’s Id”). Lets enter 1 as Gamer’s Id and view the Report.Now, Enter Gamer’s Id = 3 and view the Report.

NOTE:- The parameter appears as a Text box in the preview pane. If we need to have pre-configuredvalues in a drop down menu then go to Report parameters properties by double-clicking parameter(@param_id) and click “Available Values”/”Default value” and click “Get values from a query”. Choose the datasets created and specify the other fields.

With this we validate our theoretical knowledge on creating a Parameterized reports using MS SQL Server Reporting Services (SSRS) 2008. Using parameters in a report provides a dynamic feature to the report and makes it interactive and attractive in many ways. Your rating and comments are welcome.

1.  First of all create SSRS report project as describe in below link.

http://bhushan.extreme-advice.com/create-ssrs-report

2.  Now in Report Data Window, right click the Dataset1 Entry. You will see the context menu as shown here:

3. In context menu, Select Query. You will see the Query Designer screen with the query built for this report while creating this report.

4. Now In the criteria pane, click the cell City and under Filter column type=@City.
So your query designer will look like below.

5. If you want to test the query then click on Run Button.

6. It will Prompt Parameter Window. Give your parameter’s value over there.

7. When you click on ok button Results will shown in Results Pane.

8. After viewing the result set, click OK to exit the Query Designer window.

Now click on Report Preview tab.

9. Here you find input for City Parameter. Give Seattle in that text.

10. Now click on View Report Button.
It will give you results like below.

Congratulations! Our SSRS Report with Parameter is completed.