would like parameter greyed out if source dataset returns no values

db042190 1,516 Reputation points
2021-02-24T13:06:22.52+00:00

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
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,798 questions
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Michael Taylor 47,966 Reputation points
    2021-02-24T14:58:27.96+00:00

    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. Lukas Yu -MSFT 5,816 Reputation points
    2021-02-25T08:33:00.823+00:00

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

    0 comments
  3. db042190 1,516 Reputation points
    2021-03-01T12:43:49.797+00:00

    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.