Cascading Parameters not Working with Date Parameter

Question

Cascading Parameters not Working with Date Parameter

catbeasy

SSC Veteran

Points: 271
More actions
February 16, 2023 at 10:00 pm

#4150769
I have built cascading parameters that work for a Facility Field, Team Name and Role. However, when I added a date field so that it is the top parameter from which Facility, Team Name and Role will cascade from, when I select only 1 facility, it does not restrict the facility to the one facility selected. The date parameter is my top parameter.
The date parameter is defaulted to beginning of the month and the report runs appropriately when first run. However, again, selecting the facility parameter does not further restrict the records to the facility selected.
This is the sql in the Facility Dataset that is supposed to restrict its records to the date defaulted to in the date parameter:
```
SELECT DISTINCT SCHEDULE_FACILITY_NAME FROM asg_pt.Assignment_To_Provider_Team_Details
Where start_instant_dttm_local >=@MonthBeginCurr
and start_instant_dttm_local <= DATEADD(DAY, 0, EOMONTH(@MonthBeginCurr,0))
```
which it does when it is first run as only records in the @MonthBeginCurr show up.
Note that if I take out the date parameter, the rest of the cascading parameters work fine.
What am I missing here? Is it because it's a date parameter?
Thanks for any help! 🙂
- This topic was modified 1 year, 10 months ago by catbeasy.
February 17, 2023 at 12:16 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 1

First of all, the last line of that code needs some "bullet-proofing" to prevent missing most of the entire last day of the month if times are included.

SELECT DISTINCT SCHEDULE_FACILITY_NAME FROM asg_pt.Assignment_To_Provider_Team_Details
Where start_instant_dttm_local >=@MonthBeginCurr
and start_instant_dttm_local < DATEADD(mm, 1, @MonthBeginCurr)

Second of all, what is the DATATYPE of @MonthBeginCurr???

Third of all, how is it set? In other words, what is it's current value?

Last but not least, what is the datatype of the start_instant_dttm_local column?

I also see no "facility parameter" in that code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

catbeasy SSC Veteran Points: 271 More actions · Answer 2

Thanks Jeff, so..

@MonthBeginCurr is datatype: date/time and is defaulted to whatever the current month is, which in this case is 2/1/2023. This works as upon running the report, the data auto populates to include only February data (as one of the criteria in the Main Data that ultimately populates the report goes from between @MonthBeginCurr to + 1 month from @MonthBeginCurr ).

start_instant_dttm_local is datatype datetime.

I'm not sure what you mean by seeing no facility parameter in the code. Why would the facility dataset have the facility parameter in it? None of the other parameters (that cascade from the Facility parameter) use datasets that reference themselves in their dataset sql. It was my understanding that each of the parameters that cascade do so from the parameter that is higher up, so the SQL code in each dataset (that are intended for cascade parameter use) will reference the parameter that is higher up ordinally.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 3

On the question about the facility parameter, I was basing that off your comment in your original post which says...

"... when I select only 1 facility, it does not restrict the facility to the one facility selected."

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

catbeasy SSC Veteran Points: 271 More actions · Answer 4

The Schedule Facility parameter is named ScheduleFacilityName. Its available values are obtained from a query, the values derived from the dataset DSScheduleFacilityName, which has the sql code I posted as above. It allows multiple selections and is a text datatype. Its default values are all values from the same dataset as above, DSScheduleFacilityName.

Please let me know if more info is needed on this.

Thanks for any continued help!

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 5

Not sure how I can help. The earlier level of code is obviously not producing code that will discriminate against facilities. There's nothing in the code you posted that will do it. We'd have to see the rest of the code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gerckens.3 SSC Rookie Points: 42 More actions · Answer 6

I'm wondering if this is a problem with the parameter options refreshing. If you know it isn't, please disregard. However, if you want to humor me - would you mind posting a screen shot of what the parameters look like initially (default), what they look like when it works, and what the parameters look like when it does not work? I'd be interested to see how the "available" values compares to the default values compares to the selected values.

🙂

"When it comes to report design and development, I have a list of pet peeves a mile wide. You might think the list would be a mile long, but I like to turn it on its side, sharpen the end, and poke people with it." - Me