Passing Parameters to a report in SSRS

 

Passing Parameters

Parameters provide user interactions with the report. There are 4 ways we pass the parameters.

1. Non Queried parameters
2. Multi-valued parameters
3. Queried parameters
4. Cascaded parameters

In this case, parameter values are not getting from the database based on the query. That means the parameter values are written manually.
Eg:- provide flexibility to the user to go to the corresponding location based on the location selection.

If you would like to become a SSRS (SQL Server Reporting Services) certified professional, then visit Mindmajix - A Global online training platform:"SSRS Training and Certification Course". This course will help you to achieve excellence in this domain.

#1) Non queried(Default values)

View → Report data

1) Parameters → RT click → Add parameter general

Name: VARLOC

Prompt: Please select the location

Set parameter visibility select visible option.

Available values

Specify values

ADD the below

Label               value

Hyderabad      HYD

Mumbai           MUM

Chennai           CHE

Default Values → No default value

2) Data set → RT Click → data set properties

Query → ADD the where condition to the existing query where partyloc = @varloc

OK

Browse and see the result

Note:

Label: It is a disyllable field

Value:  The value which is passed to the data set.

#2) Multi-valued parameters:

We can create multi-valued parameters for both queried & non-queried reports.

It allows multiple values to select in the parameter list.

Navigation: In the above steps do the below changes

A general check allows multiple value options.

Dataset → where clause should be “where partyloc in [@varloc]”

 

#3) Queried Parameters

Here the parameter values are taken from the query. This is a recommended mechanism in real-time.

1) Create a new data set to take only distinct location values

View → Report data → Add data set →

Name: DSTLOC

Query: Select DISTINCT (party loc) from the location.

2) Create a parameter based on the above data set result parameters → RT click → Add parameter

General

Name: VARLOC

Prompt: Please select the location

Data Type: Text

Set parameters visibility.

Select visibly

Available values

Get values from the query

Dataset: DSTLOC

Value field: Party Loc

Label field: party Loc

Default Values → No default value

3) Use the above parameters in the actual report data set (which is displaying values in the report)

Dataset → rt Click → Dataset properties

Query: ADD the where condition where party Loc =@VARLOC

Click OK

Frequently Asked MSBI Interview Questions & Answers

#4) Cascaded Parameters

Based on one parameter value if we display data in other parameter values then it is cascaded.

Ex:- Based on the location selection, corresponding party coeds should be selected, and based on the party code selection the report should be displayed.

 

1) Create a new data set to take only distinct location values.

View → Report Data → Add Data set →

Name: DSTLOC

Query: Select DISTINCT (party Loc) From Location

Create a parameter based on the above data set result parameters → rt Click → add a parameter.

General

name: VARLOC

Prompt: Please select the Location::

Data type: Text

Set parameter visibility:

Select visibly

Available values:

Get values from the query

Dataset: DSTLOC

Value field: party LOC

Label field: party LOC

Default values à No Default value.

3) Create a new dataset to take only DISTINCT CODES based on location

View → Report Data → Add data set

Name: DST Code

Query: Select DISTINCT (party code) from party where partyloc+@VARLOC

4) Create parameter based on the above data set result parameters → rt Click → add a parameter

General:

Name: Varcode

prompt: Please select the party code::

Data Type: Text

Set parameter visibility select visibly

Available values:

Get values from Query.

Data set : DST code

Value field: party code.

Label field: party code.

Default values

No Default value.

5) Use the above parameter in the actual report data set (which is displaying values in the report)

Data set → rt click → data set properties

Query: Add the where condition where

PartyLoc=@VARLOC and

Partycode=@VARCODE

Click ok and

Execute the report

List of Related Microsoft Certification Courses:

SSIS	Power BI
SSAS	SQL Server
SCCM	SQL Server DBA
SharePoint	BizTalk Server
Team Foundation Server	BizTalk Server Administrator