Parameters provide user interactions with the report. There are 4 ways we pass the 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.
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.
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]”
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
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
SSIS | Power BI |
SSAS | SQL Server |
SCCM | SQL Server DBA |
SharePoint | BizTalk Server |
Team Foundation Server | BizTalk Server Administrator |
Name | Dates | |
---|---|---|
MSBI Training | Nov 02 to Nov 17 | View Details |
MSBI Training | Nov 05 to Nov 20 | View Details |
MSBI Training | Nov 09 to Nov 24 | View Details |
MSBI Training | Nov 12 to Nov 27 | View Details |
Ravindra Savaram is a Technical Lead at Mindmajix.com. His passion lies in writing articles on the most popular IT platforms including Machine learning, DevOps, Data Science, Artificial Intelligence, RPA, Deep Learning, and so on. You can stay up to date on all these technologies by following him on LinkedIn and Twitter.