DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

The software you build is only as secure as the code that powers it. Learn how malicious code creeps into your software supply chain.

Apache Cassandra combines the benefits of major NoSQL databases to support data management needs not covered by traditional RDBMS vendors.

Generative AI has transformed nearly every industry. How can you leverage GenAI to improve your productivity and efficiency?

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Related

  • JSON-Based Serialized LOB Pattern
  • Architecture and Code Design, Pt. 1: Relational Persistence Insights to Use Today and On the Upcoming Years
  • SQL Commands: A Brief Guide
  • Kafka JDBC Source Connector for Large Data

Trending

  • Mastering Fluent Bit: Installing and Configuring Fluent Bit on Kubernetes (Part 3)
  • The Human Side of Logs: What Unstructured Data Is Trying to Tell You
  • Four Essential Tips for Building a Robust REST API in Java
  • Mastering Advanced Traffic Management in Multi-Cloud Kubernetes: Scaling With Multiple Istio Ingress Gateways
  1. DZone
  2. Data Engineering
  3. Data
  4. How To Create a Multi-option Parameter Report In SQL Server Reporting Services (SSRS)

How To Create a Multi-option Parameter Report In SQL Server Reporting Services (SSRS)

This tutorial will help you in how to create multi-option parameter report in SQL Server Reporting Services (SSRS).

By 
Kiran Gutha user avatar
Kiran Gutha
·
Nov. 08, 16 · Tutorial
Likes (1)
Comment
Save
Tweet
Share
69.5K Views

Join the DZone community and get the full member experience.

Join For Free

This tutorial will help you in how to create multi-option parameter report in SQL Server Reporting Services (SSRS). This tip assumes that you have experience building a simple SSRS report and T-SQL skills. In SSRS we can't enable or disable report parameters based on other parameter values. If we have a report parameter, then we have to provide a value. SSRS doesn't provide any functionality to choose query parameters from report parameters, but we have a workaround.

What Is The Problem?

I have a reporting requirement where users want to enter report parameters by either selecting values from a drop down list or entering To and From range values. How can I do this in a Reporting Services report?

Solution

This tip assumes that you have experience building a simple SSRS report and T-SQL skills. In my previous tips I have explained optional query parameters and cascaded report parameters in SSRS, it is recommended to read these tips as well. In SSRS we can't enable or disable report parameters based on other parameter values. If we have a report parameter, then we have to provide a value. SSRS doesn't provide any functionality to choose query parameters from report parameters, but we have a workaround. In this article we show how to choose query parameters based on report parameters.

I will use the AdventureWorksDW2008R2 sample database for SQL Server and SQL Server 2012 Reporting Services to demonstrate the solution. To demonstrate the solution, I will create a report which will give two different choices to filter the data:

  • Users can either filter the data using a Multi Select Parameter or
  • Users can filter the data using a Range Parameter, using To and From values.

Step 1 (A): Add Dataset

I have already created an embedded data source connection to the AdventureworksDW2008R2 database. Let's create a new dataset for our report. DataSet Query

IF @QueryParameterType=1
(
SELECT       ProductKey, EnglishProductName
FROM            DimProduct Where ProductKey IN(@Product)
)
else if @QueryParameterType=2
(
Select ProductKey, EnglishProductName From DImProduct 
Where Productkey >=@ProductFrom and Productkey <= @ProductTo
)

This dataset has four query parameters and it returns Productkey and EnglishProductName. The @QueryParameterType query parameter value will decide the dataset query.

  • If user provides 1 as a value for @QueryParameterType then the user can filter the data by providing values for @Product query parameter.
  • If user provides 2 as a value for @QueryParameterType then the user can filter the data by providing values for @ProductFrom and @ProductTo query parameters.

As you can see from the below image the dataset has been created and SSRS has automatically created four report parameters.

Report Main Dataset
Report Data Pane after creating main dataset

Step 1 (B): Add Parameter Dataset

As you know our recently created dataset has one @Product query parameter which can accept multiple values, so we have to create a new dataset for our Product report parameter. This dataset will be used to return a list of available values for the Product report parameter. Product DataSet

WITH MYCTE AS 
(SELECT  -2147483648 AS ProductKey, '(Parameter Not Applicable)' AS EnglishProductName, 
2 AS ParameterType)
SELECT        ProductKey, EnglishProductName, ParameterType FROM MYCTE AS MYCTE_1
WHERE        (ParameterType = @QueryParameterType)
UNION ALL
SELECT        ProductKey , EnglishProductName, ParameterType FROM  
(SELECT DISTINCT ProductKey, EnglishProductName, 1 AS ParameterType FROM   DimProduct) AS A
WHERE        (ParameterType = @QueryParameterType)
ORDER BY EnglishProductName

This Dataset has one query parameter. Based on @QueryParameterType query parameter value, this dataset will return the records as follows:

  • If @QueryParameterType query parameter value is 1 then it will return all values from the database.
  • If @QueryParameterType query parameter value is 2 then it will return only one record whcih is "Parameter Not Applicable".

Adding DataSet for Product parameter

Step 2: Report Parameter Configuration

We have to configure the report parameters and in this step we will configure each parameter one by one.

Parameter 1 - QueryParameterType

Double click on the QueryParameterType report parameter. It will open the Report Parameter Properties window. Change Data type to Integer from Text. You can also modify the parameter Prompt as per your choice. You can refer to the below image.

Report Parameter Properties for QueryParameterType Parameter













Click on Available Values, choose the Specify Values radio button and click on the Add button to add two parameter values as shown below.

Adding available values for QueryParameterType parameter













These parameter values will decide the query for your report. If the Multi Select Parameter value is selected then users can choose parameter values from the list of available values.

If Range Parameter value is selected then users can filter report data using a Range Parameter and the user has to enter values for the ProductFrom andProductTo parameters.

Parameter 2 - Product

Double click on the Product report parameter, it will open the Report Parameter Properties window. Change data type to Integer from Text and check the Allow multiple values checkbox. You can also modify the parameter Prompt as per your choice. You can refer to the below image. Report Parameter Properties for Product Parameter

We have to get a list of values for this parameter, so click on the Available Values tab, choose the Get values from a query radio button and select Product for the dataset, ProductKey for the Value field and EnglishProductName for the Label field. You can refer to the below image.

Adding available values for Product Parameter

Click on the Default Values tab, choose the Specify Values radio button and click on the Add button. We have to set a default value using an expression, so click on the expression button as shown below.

Adding default value for Product Parameter

Once you click on the expression button it will open the Expression window. Set the expression as shown below.

=iif(Parameters!QueryParameterType.Value=2,-2147483648,nothing)

You can refer to the below image as well. Product parameter default value expression

This expression will set (Parameter Not Applicable) as the default value for Product when theQueryParamterType parameter value is chosen as Range Parameter (value = 2).

Parameter 3 - ProductFrom

Double click on the ProductFrom report parameter, it will open the Report Parameter Properties Window. Check theAllow blank value ("") checkbox. You can also modify the parameter prompt as per your choice. You can refer to the below image.

Report Parameter Properties for ProductFrom Parameter

Click on the Default Values tab, choose the Specify Values radio button and click on the Add button. We have to set the default value using an expression, so click on the expression button as shown below.

Adding default value for ProductFrom Parameter

Once you click on the expression button it will open the Expression window. Set the expression as shown below.


=iif(Parameters!QueryParameterType.Value=1,"(Parameter Not Applicable)","")

You can refer to the below image as well. Default value expression for ProductFrom parameter

This expression will set (Parameter Not Applicable) as the default value for ProductFrom when theQueryParamterType report parameter value will be selected as Multi Select Parameter (value = 1).

Parameter 4 - ProductTo

Double click on the ProductTo report parameter, it will open the Report Parameter Properties Window. Check theAllow blank value ("") checkbox. You can also modify the parameter prompt as per your choice. You can refer to the below image.

Report Parameter Properties for ProductTo parameter

Click on the Default Values tab, choose the Specify Values radio button and click on the Add button. We have to set the default value using an expression, so click on the expression button. You can refer to the below image.

Adding default value for ProductTo Parameter

Once you click on the expression button it will open the Expression window. Set the expression as shown below.

=iif(Parameters!QueryParameterType.Value=1,"(Parameter Not Applicable)","")

You can refer to below image. Default value expression for ProductTo parameter

This expression will set (Parameter Not Applicable) as the default value for ProductTo when QueryParamterTypereport parameter value will be selected as Multi Select Parameter (value = 1).

Step 3: Add Tablix

For data viewing purposes I am adding a Tablix into my report. This Tablix will show Productkey and EnglishProductName. You can refer to the below image. Adding Tablix to the report


Step 4: Preview Report

We have made all necessary changes, now let’s preview the report. As you can see from the below image, the first parameter of the report gives two options to filter the data. You can either filter using a Multi Select Parameter orRange Parameter.

Report Preview for Multi select parameter

When I select Multi Select Parameter, my next parameter gets enabled with a list of available values. You can refer to the below image. Product list from multi select parameter

I can choose the desired values from the list of available values. For this report run, the default values for theProductFrom and ProductTo parameters will be (Parameter Not Applicable), so I don't need to enter any value for these. Even if I enter a value for ProductFrom or ProductTo it will not impact the report data. Let's view the report, as you can see below, the report is showing data only for the selected values in the Productparameter.

Report Preview For Multi Select Product Parameter

Now I will select Range Parameter in my first report parameter, this will allow me to filter the data using the Range Parameter. The Product parameter value will be automatically set to Parameter Not Applicable and this is the only value available in this case. I have to enter the ProductFrom and ProductTo values to filter the report data. As you can see from the below image, the report is showing data for the range parameter values from 1 to 10.

Report Preview for Range Parameter

Database sql Data (computing) Data Types Filter (software)

Published at DZone with permission of Kiran Gutha. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • JSON-Based Serialized LOB Pattern
  • Architecture and Code Design, Pt. 1: Relational Persistence Insights to Use Today and On the Upcoming Years
  • SQL Commands: A Brief Guide
  • Kafka JDBC Source Connector for Large Data

Partner Resources

×

Comments

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends: