Generation of Dynamic Parameterised Report with DropDown List using SSRS

Introduction

This article helps you in creating parameterized report which loads the data dynamically. In real time the usage of this parameterized reports is very high. That initiated me to write this article.

Content

• Generation of Parameterized Report
• Creating Shared Data Source
• Parameter will be a drop down which loads Dynamic Data.
• Adding Target Server URL for Deploying

Sequential Workflow

Click Business Intelligence Development Studio >>Choose Report Server Project >>Create Project and Click >>OK.

Adding a Report:

Project has been created and we have seen it end of right hand side. Right Click on Reports>> Add reports with desired name (Report1.rdl)

Creating Shared Data Source:

On Clicking OK after creating a Report, it will displays an another popup which facilitates you to create data source.

Click Edit Button>>A new popup will appear and provide Instance name in first text box >> Next Select Data base name from the Drop down populated corresponding to the instance.

Click on >>Test Connection to Check provided instance is valid or Not. Don’t forget to select the check box>>Make this as a shared data source to reuse of this data source further.

Query Design:

Design your query with a parameter and Click>>Next Choose Report Type 1.Tabular 2.Matrix>>Click

Next>>Then design Table>>Choose Style for the table.

After following all the above steps we can see the designed table with selected style.

Dynamic Loading of Data to the Created Parameter

To Load values for parameter in Drop down we need to create an another Data set .Rick Click on Data Source>>Add Data Set>>

Creating another Data Set:

Write an appropriate query to to load parameter details. We can also check the query by Clicking Query designer Button.

Now we can see the two data set one for loading the data and second data set for loading parameter values into Drop Down.

Changing Parameter Properties:

Right Click on Parameter>>Select Parameter Properties then it will displays Property Window.

Loading Values Into Drop Down:

Select Available Values>>Choose get values from a query >>Select Corresponding data set to


load values for Parameter>>Then Select Value Field and Label Field>>Click OK.

Parameter Displaying Loaded values:

Once after Clicking Preview button we can view a Parameter name with associated Drop down values being loaded from DataSet2.

Previewing Designed Report:

By providing parameter value from drop down we can get the corresponding data as shown below.

Setting Properties Of the Project:

Right Click>>Project Name(Dynamic Report)>>This Page will appear>>Select Start Item>>Set OverwriteDatasource property to “True”>>Then provide TargetServerURL.

Target Server URL:

For configuring URL go to>>MS SQL Server>>Configuration Tool>>Select Report Services Configuration Manager>>. Then it will opens the below Popup>>Provide necessary fields and Click>>Connect>>Go to Report Manager URL >>Copy the URL and Paste it in TargetServerURL.

Click on to the URL it will takes you to a page through which we can use up the report.