I see a lot of similar questions posted, but none quite exactly like what I am experiencing.
The parameter pick lists for the SSRS report work properly when running the report as you normally would, but certain ones do not work when trying to create or edit a subscription.
I am unable to post the specific example, but will describe in detail.
- There are 6 parameters that may be used in the report.
- All 6 are a single selection pick list.
- The list includes the value NULL, which represents "All" to the underlying SQL query if selected.
- Three of the pick lists populate perfectly fine on the report and on the subscription page:
- "Product Number" has available choices based on an underlying SQL query
- "Type" has available choices hard coded
- "Type of Location" has available choices hard coded
- Three of the parameter pick lists do not populate properly on the subscription set up page, but do populate properly running the report manually. I have included the screenshot below.
- Country is populated by an SQL query
- Region is populated by an SQL query that relies on Country (but accepts a NULL country value to just get all)
- Location is populated by an SQL query that relies on Regiona and Country (but accepts a NULL country and/or NULL region to just get all)
So the report is strange because it works fine in SSRS Report Builder, works fine running manually from SSRS website, but the subscriptions page disables the drop down and does not allow me to select the value when the "Source of value" is set to "Enter value". The affected pick lists are technically the two cascading pick lists and the top level one they utilize (which does not rely on any parameters).
Additional Details
- There is an existing subscription that does have the country pick list set (must have worked at some point) and I can change it to another country still, but if I change it back to All at any point, the list now becomes disabled and I can't get it back.
- I have tried in Firefox/Edge/Chrome. Behaviour is the same in all three.
- In Chrome, I entered developer mode and inspected the elements. When I examine the pick lists, all the values are present for the pick lists and they even update when I change the selection (from the existing subscription where country was selected I could witness this).
- The behaviour is seen in multiple environments as well (developer servers, qa, and production).
So basically I know the queries are returning the choices, the pick lists are updated if I change them, but the pick list in the UI is just disabled from selecting anything.
This isn't a complete answer, but the issue was definitely related to the cascading parameters. I removed the dependency inside Region and Location that used preceding parameters and now all the pick lists works again (even Country which was depended on, but did not have any dependencies itself).
Seems like a bug, but not sure.
So my report works fine, but you can technically enter parameters that are mutually exclusive and will return no results.