question

db042190-2983 avatar image
0 Votes"
db042190-2983 asked db042190-2983 commented

would like parameter greyed out if source dataset returns no values

Hi we run 2014 developer in our dev environment and we use vs 2017 to develop reports.

I have a certain parameter that will show values based on user permissions stored in the data.

So as you can imagine, one of my use cases is "this user has nothing to drop down".

I'm noticing in vs's preview mode that i am allowed to type a value into that box when nothing drops down. Thankfully RS ignores it but is there a way to gray it out for that use case?

I'm not sure what behavior i'll see once its deployed to a server.

sql-server-reporting-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

cooldadtx avatar image
0 Votes"
cooldadtx answered cooldadtx commented

I assume you went into the Available Values settings for the parameter and specified a query to run to return the data to display to the user. This would cause a drop down to appear with the options and a "please select" choice. The user wouldn't have any "blank" options.

Note that the preview UI is for the preview version of Winforms. Web UIs may do something different so you should always test in the environment you intend to actually use the report in.

I also assume that, since there may be no data, you have enabled the option of Allow null value on the parameter. This should cause a checkbox to appear to allow for NULL. You could also, in theory, use Allow blank value but that would only be for textual parameters.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thx cooldadtx, good assumptions. we dont want null or blanks allowed

0 Votes 0 ·

If you don't support null for a parameter value then how does a user who has no options in the drop down run the report? If the answer is they don't then not allowing null would prevent them from running the report as they cannot leave it unselected and there are no values to choose from. They would never be able to run the report.

If you want them to be able to run the report anyway then you'll need to accept NULL and users with no drop down options would have to go that route.

0 Votes 0 ·
LukasYu-msft avatar image
0 Votes"
LukasYu-msft answered

No, we cannot grey out parameters in SSRS. What you can do is set it to set parameter to NULL. I think @cooldadtx has provide you good suggestion on this.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

db042190-2983 avatar image
0 Votes"
db042190-2983 answered db042190-2983 commented

thx cooldadtx. i think the question went off track.

we don't want nulls and we don't want them wasting their time entering data that will be ignored or running the report if there isn't even one drop down value.

the original question has to do with them being able to type something into the box. it seems odd that ssrs would allow that when the term "available values" doesn't mean available values or "type something that will be ignored".

i think Lukas has given a pretty good answer saying ssrs cant do what i'm asking. And I appreciate your discussion here. the rest of yours and Lukas' arguments are interesting but I think you both have to agree that ssrs 's behavior in this use case is a bit odd (actually misleading) and not consistent with what is otherwise behavior that generally follows common sense.

Right now it seems adding a selection (if nothing else dropped down) labeled "You are not authorized" would at least remove some of the mystery.

· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

If you set the available values then a user cannot enter anything else. There is no option to enter a blank value, at least in VS 2019 and I believe also in the winforms UI but I could be wrong about that. A screenshot might help along with the properties for the parameter(s) if you aren't seeing this same behavior. Given that SSRS is now hosted in the SQL Server Reporting Services extension and it is shared between VS 2017/2019 you shouldn't be seeing anything different than I am.

If you don't want the user to be able to run the report then setting the values to select from and then having none would prevent the user from running the report.

1 Vote 1 ·

thx cooldadtx. we are running 2014 and developing with vs 2013. I'll post back here when i deploy and let the community know what the behavior was on the server.

0 Votes 0 ·

sorry we use vs 2017 and sql 2014 as originally posted.

0 Votes 0 ·

once deployed, the behavior is different. ssrs drops down a recangle of white space and luckily the user cannot enter anything when it is collapsed.

0 Votes 0 ·
Show more comments