Hi there ,

I'm using SSRS 2012 and I've 2 level cascading parameter (section depends on division parameter).

Division 10 has 2 sections which are 10 and 11.

Division 20 has 2 section which are 20 and 21.

Division 30 has 1 section which is 31.

Division 60 has 2 sections which are 60 and 61.

I want to set default values to min - max values of Section parameter depends on current values of division parameter.

In the picture below is the correct result.

Problem will occur when changing Division.To from 20 to 60.

I expect the Section.To must changed to max value of it(61) instead the current value(21) like picture below.

I've checked the query operation using SQL Profiler that tell me the Min - Max dataset of Section parameter NOT BEING FIRED when I change Division.To selection.

FYI.

I'm using 2 datasets separate for available,default values in each parameter.

i.e. Section.To parameter  

1. available values

SELECT DISTINCT(SectionCode)
FROM Section
WHERE DivisionCode BETWEEN @DivFrom and @DivTo

2. default values

SELECT MAX(SectionCode) Max
FROM Section
WHERE DivisionCode BETWEEN @DivFrom and @DivTo

Can you give me some advice how to fix this problem ?

Thank you in advance.

SweNz

• Edited by SweNz Tuesday, April 08, 2014 7:13 AM

Are Section parameter related to Division? How do you determine value should be changed to 61 when you change division to 60? what are the rules for that?

Yes, Section parameter depends on Division parameter.

There are 2 sections(60,61) in Division 60. So the max value of Section.To must be 61. 

If I change Division to 30 the Section.To must be 31 (you can see in the 2nd picture)

I'm using following query for find the max value of Section.To

SELECT MAX(SectionCode) Max
FROM Section
WHERE DivisionCode BETWEEN @DivFrom and @DivTo

FYI : I add information in the first post.

• Edited by SweNz Tuesday, April 08, 2014 7:14 AM

Hi SweNz,

After testing the issue in my environment, I can reproduce it. And based on my research, this issue is caused by design. If the value of Parameter2 is still valid for the new value of the parent parameter1, then we'll not re-evaluate the default value of parameter2. The report does not re-evaluate the default value for a subsequent parameter *unless* the selected value is no longer in the valid values list. The report does not know whether the current value was specifically requested by the user or it is there because of the default.

In your scenario, we can use the second dataset to return values for available values in Section.To parameter to fix this issue. It means that the default value and available value use the same dataset with only one value. Then when we change the Division.To from 20 to 60, the Section.To parameter will automatically change value from 21 to 61. Because there is no 21 value in the drop-down list for the new Section.To parameter.

If there are any other questions, please feel free to ask.

Thanks,
Katherine Xiong

If you have any feedback on our support, please click here.

Hi Katherine,

Thank you for your time to reproduce and find the way out. But, I still need to let user choose other choices from drop-down list.

It can't re-evaluate default value parameter if selected value is still valid right ?  (That's fine.)

Thank you 

SweNz

• Edited by SweNz Wednesday, April 09, 2014 4:13 AM

Hi SweNz,

Yes. If in this case, it cannot evaluate default value parameter if selected value is still valid right. The only way you can do is re-run the report with the new selections.

Thank you for your understanding.

Regards,
Katherine Xiong

If you have any feedback on our support, please click here.

Hi,

I had used one sample table to populate your data on top of it i had created report with parameters and got the result as u expected (Hope). Check the below contents to simulate the requirement.
=====================================================
CREATE TABLE div_sec

(
division int,
section int,
range int
)
INSERT INTO div_sec (division,section,[range]) values ()

INSERT INTO div_sec (division,section,[range]) values (10,10,0)
INSERT INTO div_sec (division,section,[range]) values (10,11,1)
INSERT INTO div_sec (division,section,[range]) values (20,20,0)
INSERT INTO div_sec (division,section,[range]) values (20,21,1)
INSERT INTO div_sec (division,section,[range]) values (30,31,0)
INSERT INTO div_sec (division,section,[range]) values (30,31,1)
INSERT INTO div_sec (division,section,[range]) values (60,60,0)
INSERT INTO div_sec (division,section,[range]) values (60,61,1)

SELECT * FROM div_sec

--Created 3 Datasets with the below queries

--ds_Div
SELECT distinct division FROM div_sec

--ds_Sec_Min
SELECT division, section
FROM div_sec
WHERE (range = 0) AND division = @div
--here for @div assign the value from @DivFrom Parameter

--ds_Sec_Max
SELECT division, section
FROM div_sec
WHERE (range = 1) AND division = @div
--here for @div assign the value from @DivTo Parameter

--Create the 4 parameters
--DivFrom
--in available value select the ds_Div dataset
--DivTo
--in available value select the ds_Div dataset
--SecFrom
--in available and default value select the ds_Sec_Min dataset
--SecTo
--in available and default value select the ds_Sec_Max dataset

=====================================================

Hi Sridhar,

Thanks for your effort. I already moved to new report so I'll try it later. I'll let your know the result :) 

Thank you,

SweNz