In Cascading Parameter scenarios, there is a known (generally undesirable) behavior around the default child parameter value not changing after a parent parameter change.
First, to answer this original scenario, make sure the Start parameter's default value is set to "Get values from query". I was able to get the child parameters dates to change correctly with that set:
Secondly, a very common scenario that users run into, with default child parameter values NOT changing when a parent parameter is changed, is due to the fact that MS does not force the child's default value to be re-evaluated if it's current value is already listed as a valid value.
This was reported back in 2007 as a bug...
Original "Bug" Description, as reported via Microsoft Connect (note Workarounds link on this page)
Parameter2 has a default [value] and is dependant on Parameter1. When you
change Parameter1, the default value for Parameter2 does not change
even if the default value for Parameter2 is dependant on Parameter1.
Microsoft closed the reported issue as being "by design" (as user Hannover Fist points out in the comment above)
They stated:
"If the value of Parameter 2 is still valid for the new value of the
parent parameter1, then we'll not re-evaluate the default value of
parameter 2."
as well as:
"We do not re-evaluate the default value for a subsequent parameter *
unless * the selected value is no longer in the valid values list... "
Sample "bug" Scenario
-----------------------------------------------------------
|Parameter1: @PeriodEnd
|Date Type: Date/Time
|
|Available Values (specified):
|
|Label | Value
|"Current Period End" | [<<Expr>>] =CDate("5/31/2021")
|"Prior Period End" | [<<Expr>>] =CDate("4/30/2021")
|
|Default Values
|[<<Expr>>] =CDate("5/31/2021")
-----------------------------------------------------------
|Parameter2: @BeginningPeriodEnd
|Available Values (get from query):
|
|Dataset:
|PeriodEndDate
|
|Value field:
|PeriodEndDate
|
|Label field:
|PeriodEndDate
|
|Default Values:
|[<<Expr>>] set to Parameters!PeriodEnd.Value
-----------------------------------------------------------
|DataSets: PeriodEndDate
|Query: "SELECT DATEADD(d,-1,DATEADD(month, DATEDIFF(month, 0,@PeriodEnd), 0)) as
| PeriodEndDate
| UNION
| SELECT @PeriodEnd"
-----------------------------------------------------------
In the above scenario, the Parameter2 default value will only change the first time Parameter1 selected value changes, despite the fact that it's default value is set to Parameters!PeriodEnd.Value.
After Parameter1 changes again from "Current Period End" to "Prior Period End" or vice versa, Parameter2's value will not change.
Example Workaround (note the link to alternate workarounds on the MS Connect page linked above)
Force Parameter2's default value to be re-evaluated by ensuring that its current default value is not found in it's own (current) list of available (valid) values.
In this scenario, we simple change Parameter1's available values to include time:
-----------------------------------------------------------
|Parameter1: @PeriodEnd
|Date Type: Date/Time
|
|Available Values (specified):
|
|Label | Value
|"Current Period End" | [<<Expr>>] =CDate("5/31/2021 17:30:30")
|"Prior Period End" | [<<Expr>>] =CDate("4/30/2021 17:30:30")
|
|Default Values
|[<<Expr>>] =CDate("5/31/2021 17:30:30")
And if we don't want the time portion of the date included, we ensure that the main report's date parameters are set to date datatypes as opposed to datetimes.
