Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Report Parameters

Posted on 2016-09-30
2
Medium Priority
?
41 Views
Last Modified: 2016-10-02
I have two report parameters, region and state

What I desire is to have all the regions selected by default, which would make all states default.

But if you choose a region only the states for that region show up in the drop down ((by stored procedure)

What's happening is if all regions are selected the drop down for states is only showing for the first region

Ex

ATL is the first region and the states are fl, Nc, al, ga

So if I choose all regions only the states for ATL are showing up in the drop down list
0
Comment
Question by:Auerelio Vasquez
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 16

Assisted Solution

by:Megan Brooks
Megan Brooks earned 1000 total points
ID: 41823875
I think I understand what you are asking, but I am having a little trouble understanding the wording. Is it that a) you want the list of states to be restricted if you select a specific region, or that it already works that way and b) you don't want the list of states to be restricted?

I'm pretty sure it is "a" above, and I will proceed on that assumption. I can provide greater detail once you confirm.

You can link two (or more) parameter available (and/or default) value lists together by creating a dependency between the value of the first parameter and the dataset of the second parameter. To do this, the available value list for the second ("state") parameter must be obtained from a query.

That 2nd parameter dataset query must accept a query parameter that is the value of the of the first parameter ("region"), and must use that value to perform the required filtering. The second parameter must also be defined after the first parameter, in the ordering of the parameters (i.e. no forward referencing). This feature is known as "cascading parameters."

When parameters are linked this way, SSRS recognizes a dependency between them and will re-execute the second parameter's query each time the 1st parameter changes. It also will not allow the user to set a value for the second parameter until a value has been set for the first.

Here is a Microsoft TechNet tutorial: Lesson 4: Adding Cascading Parameters (SSRS)

It is not a requirement that the 1st parameter use a dataset query. You can hard-code the list of regions in the first parameter if that makes sense for your application. The 2nd parameter must use a dataset query in order to accomplish the filtering.
0
 
LVL 13

Accepted Solution

by:
Arifhusen Ansari earned 1000 total points
ID: 41824491
While creating your parameter. You might have used the Available value setting for the parameter.

Below is the example that i have used to configure Region.

Parameter-COnfig.png
When you set the Value filed in Default value setting. It will all the value when first time you preview the report.

Region-Available-Value.png
It's done for Region . Now for state you have to create second dataset this get the data from state table based on the value in the region parameter and also when you configure the state data set value in the default value setting of State Parameter default value will be selected automatically.


Create the state data set and use the configuration as below.

Query-to-State-Dataset.png
Configure the parameter of stat data set as below.

Paramter-for-State-dataset.png
You can see i have used the parameter value of region and so when you change the selection of region parameter. State dataset will be refreshed and you will get the value related to that dataset only.

Data set is ready now create State parameter and configure the available and default value so that

your changes in the region parameter will be reflated in the state parameter.

State-parameter.png
Available value configure for State parameter.

Available-value-for-State.png
Default value configuration for state parameter.

Defalt-Value-Config.png.

I have attached the scrip and rdl file as well.

Hope it will help you.
Script-For-Example-data.sql
Cascade-Parameter-In-Report.rdl
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question