Passing Parameters

Parameters provide user interactions with 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 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.

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
A general check allows multiple value options.
Dataset → where clause should be “where partyloc in [@varloc]”

MSBI Course Offer

Learn to analyze data sets, software programs and build a comprehensive bi solution by enrolling in our MSBI Training

Queried Parameters

Subscribe to our youtube channel to get new updates..!

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

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.

 Cascaded Parameters

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

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

List of Related Microsoft Certification Courses: