Blog

Passing Parameters to a report in SSRS

  • (5.0)

Passing Parameters

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

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

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

Learn how to use MSBI, from beginner basics to advanced techniques, with online video tutorials taught by industry experts. Enroll for Free MSBI Training Demo!

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.

3) 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
General check allow multiple value option.
Dataset → where clause should be “where partyloc in [@varloc]”

Related Page: SQL Server Reporting Services (SSRS) Overview

Queried Parameters

Here the parameter values are taken from 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 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 visible
Available values
Get values from 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

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 parameter.

General
name                                                : VARLOC
Promkpt : Please select the Location ::
Data type: Text
Set parameter visibility:
Select visible
Available values
Get values from 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 (partycode) from party where partyloc+@VARLOC

4) Create parameter based on the above data set result parameters → rt Click → add parameter
General:
Name    : Varcode
prompt : Please select the party code ::
Data Type: Text
Set parameter visibility select visible
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

Explore MSBI Sample Resumes! Download & Edit, Get Noticed by Top Employers!Download Now!

 


Popular Courses in 2018

Get Updates on Tech posts, Interview & Certification questions and training schedules