SSRS report parameter : parameter's value change upon other parameter's value selected event

Question

Here's my scenario,

I have a report in which it has two subreports in it.
each subreports visibility depends on a parameter(A) value.
each event for selecting the parameter(A) value from all available value will refresh other two parameter's(B,C) list of available value with same set of data but different than before the refresh event.
B and C will use one value from the list as its default value.
default value is controlled by expression in default properties of B,C
when A is changed again, cycle repeat.

A, B and C are used to specify date range for query.

A is selection to choose from two options ('WEEKLY','MONTHLY'). The default is 'WEEKLY'.

'WEEKLY' will return list of work week in current year to B and C. subreport1 displayed. subreport2 hidden.
'MONTHLY' will return list of month in current year to B and C. subreport1 hidden. subreport2 displayed.

and expression for default value of B and C is =IIF(Parameters!A.Value = "WEEKLY",datepart("WW",today()),IIF(Parameters!A.Value = "MONTHLY",datepart("M",today()),0))

Now when it loads, A is 'WEEKLY', B and C have correct default value datepart("WW",today()). Then...

I change A to 'MONTHLY', B and C got monthly data right and have right default value.datepart("M",today())
I then change back to 'WEEKLY' B and C got correct weekly data but still retain the default value from the first switch (which is datepart("M",today()) rather than supposed value datepart("WW",today()))

How do I get B and C to get the correct default value per category selected from A?

EDIT:

all subreport have same set of parameter as main report.
im using query to get the list for B and C.
so far query is working fine producing the list.
query is a procedure which take in parameter A then returning recordset of whether work week or month in current year.
every subreport have same parameter definition as in main report.

i actually doesn't know much how to use subreport. try and error here. so when ssrs prompt me about some parameter definition is not defined for some subreport i matched parameter in subreport to parameter in main, at parameter tab of subreport properties.that's the only error i got through when building this report.

Is the default value of B and C, when A is 'MONTHLY',also a possible value in the list of 'WEEKLY' ? Instead of using an expression for changing the default value, try setting the actual selected value in B and C by the expression. Keep in mind that this means you can't refresh your entire report if B and C values change, else you'd reset the value every time. — Oceans
is not same value, 'WEEKLY' would give latest work week value to B and C and likewise 'MONTHLY'. I tried to set default value using max() value from date list. . i got this error Fields cannot be used in report parameter expression. i fetch the list from query. — user2300035

Jonnus Jonnus · Accepted Answer · 2015-11-18T17:56:05

So after a bit of faffing I have managed to reproduce and the behaviour you require from our parameters. Apologies if this goes over ground you have already completed, or doesn’t work, but it behaves as expected for my configuration (SSRS 2008 R2).

Parameter A is set up with available values of “WEEKLY” or ”MONTHLY” for both the Label and Value, with a default Value of “WEEKLY”.
Parameter B is then set up with the available values’ for Label, Value and the Default values’ Value all set to your expression
```
=IIF(Parameters!A.Value =         
    "WEEKLY",datepart("WW",today()),IIF(Parameters!A.Value = 
    "MONTHLY",datepart("M",today()),0))
```
Available Values

Default Values
This is all repeated for parameter C

Now when I first run the report I am faced with the following

When I choose Monthly I get

Then when I choose weekly again I get

Only when I choose monthly and select "View Report" does the visibility change on the report

I'm not certain you filled out all these steps when you asked the question - you may have done - but it is worth giving the above a go from fresh and see if the behaviour is as you require

Any problems, let me know, and I'll try to help further

SSRS report parameter : parameter's value change upon other parameter's value selected event

1 Answers