Mindmajix

Passing Parameters to a report in SSRS

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.

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

  1. General check allow multiple value option.
  2. Dataset → where clause should be “where partyloc in [@varloc]”

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

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.

Screenshot_126

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

Enroll for Live Instructor Led Online MSBI Training


0 Responses on Passing Parameters to a report in SSRS"

Leave a Message

Your email address will not be published. Required fields are marked *

Copy Rights Reserved © Mindmajix.com All rights reserved. Disclaimer.
Course Adviser

Fill your details, course adviser will reach you.