none
SSRS 2008 Hide a parameter based on another parameter

    Question

  • Can we do like below in SSRS 2008 ?
    • Prompt for Company OR User selection.
      • If the user selects Company, allow selection of one or more specific companies.
      • If the user selects User, allow selection of one or more specific users.
    Tuesday, June 19, 2012 8:32 AM

Answers

  • Hi There

    you can do something like this

    if @Selection='Company'
    SELECT     distinct company 
    FROM         DWH_Time
    else
    SELECT   distinct  ''as  company 
    FROM         DWH_Time


    if @Selection='Project'
    SELECT     distinct project
    FROM         DWH_Time
    else
    select distinct ''as project

    in this case your parameter might look like this screenshot, at one point of time just one parametr got some values please make sure allow blank values to your both parameter.

    If you would like only one parameter for both company and user then it would be easily enough

    if @Selection='Project'
    SELECT     distinct project
    FROM         DWH_Time
    else
    SELECT     distinct Company as project
    FROM         DWH_Time
    You can change this according to your needs

    I hope this will help

    Many thanks

    Syed


    Tuesday, June 19, 2012 10:15 AM
  • Hi Shihan,

    Are you referring to the dataset that fetches the data for display on your report? You'd need to take these same parameters into account in that query.  Without more details (or your actual query) it's difficult to give an example but I'll give it a go anyway.

    So let's say that the user has selected "User" and the second parameter (@YourSecondParameter) displays the list of users with some of them selected.

    IF @YourParameter = 'Company'

      SELECT fields

      FROM yourtable

      WHERE company IN (@YourSecondParameter)

    ELSE

      SELECT fields

      FROM yourtable

      WHERE user IN (@YourSecondParameter)

    This way you're automatically selecting "all companies" when "user" is selected because the WHERE clause only filters on what has been selected.


    MCITP SQL Server 2008 (DEV); MCTS SQL Server 2008 (BI, DEV & DBA)

    • Marked as answer by Shihan Holmes Wednesday, June 20, 2012 9:33 AM
    Wednesday, June 20, 2012 7:50 AM

All replies

  • Hi There

    Can you please give some more details what are you trying to achieve.

    What do you mean by Prompt for Company OR User selection

    Please give some more details then someone might be able to help you out

    Many thanks

    Syed Qazafi

    Tuesday, June 19, 2012 8:37 AM
  • Hi

    I basically want to do is give a parameter in the report to select Company OR User, Based on that enable the correct drop down list (company or user) to retrive the report.

    SH


    Shihan Holmes

    Tuesday, June 19, 2012 8:58 AM
  •  

    Hi There

    You would not be able to hide or show the parameter based on another parameter at run time. I am afraid this is by design and current version of SSRS does not support this

    Many thanks

    Syed Qazafi Anjum

    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful

    Tuesday, June 19, 2012 9:11 AM
  • Hi Syed,

    Thanks for the reply. is there an alternative way to do this in the report, that you can suggest ?

    Many thanks

    SH


    Shihan Holmes

    Tuesday, June 19, 2012 9:17 AM
  • Hello Shihan,

    Ofc Syed is right, there is no way to hide parameter in SSRS.

    my suggestion is .. :

    make 1 drop down parameter having option to select Company or User.
    and in second parameter : based on selection of approrpiate option,

    you can get data of company or user. and make prompt 'Select Company/User: '

    make 1 Dataset/SP having switch case to find the data of Company or User.

    i.e. exec GetCompanyUserInfo(flag)  flag 1 is for Company and 2 for Users. make switch case in that SP.

    I think this is one of the solution to overcome this issue.


    20CF


    Tuesday, June 19, 2012 10:10 AM
  • Hi There

    you can do something like this

    if @Selection='Company'
    SELECT     distinct company 
    FROM         DWH_Time
    else
    SELECT   distinct  ''as  company 
    FROM         DWH_Time


    if @Selection='Project'
    SELECT     distinct project
    FROM         DWH_Time
    else
    select distinct ''as project

    in this case your parameter might look like this screenshot, at one point of time just one parametr got some values please make sure allow blank values to your both parameter.

    If you would like only one parameter for both company and user then it would be easily enough

    if @Selection='Project'
    SELECT     distinct project
    FROM         DWH_Time
    else
    SELECT     distinct Company as project
    FROM         DWH_Time
    You can change this according to your needs

    I hope this will help

    Many thanks

    Syed


    Tuesday, June 19, 2012 10:15 AM
  • In addition to 20CF: you don't necessarily need to use one SP but one dataset certainly is the goal.  If you'd like to use two SPs (or queries), you can just use an IF in the dataset, something like:

    IF @YourParameter = 'Company'

      SELECT your_companies here...

    ELSE

      SELECT your_users here...

    To make this work you do need to ensure that both queries return the same fields (names and number, most likely data type too).


    MCITP SQL Server 2008 (DEV); MCTS SQL Server 2008 (BI, DEV & DBA)

    Tuesday, June 19, 2012 11:22 AM
  • Hi Syed

    Many thanks I have sorted out the issue as your suggestion. Thanks for your effort to resolve my issue

    SH


    Shihan Holmes

    Tuesday, June 19, 2012 11:43 AM
  • Hi Valentino

    In this case how to you set the dataset filters to get(hardcord) all companies  if the user has selected 'user' from the drop down and has selected few users.

    And to get all users when user has selected 'company' from the dropdown and selected some of the companies?

    SH


    Shihan Holmes

    Wednesday, June 20, 2012 7:19 AM
  • Hi Shihan,

    Are you referring to the dataset that fetches the data for display on your report? You'd need to take these same parameters into account in that query.  Without more details (or your actual query) it's difficult to give an example but I'll give it a go anyway.

    So let's say that the user has selected "User" and the second parameter (@YourSecondParameter) displays the list of users with some of them selected.

    IF @YourParameter = 'Company'

      SELECT fields

      FROM yourtable

      WHERE company IN (@YourSecondParameter)

    ELSE

      SELECT fields

      FROM yourtable

      WHERE user IN (@YourSecondParameter)

    This way you're automatically selecting "all companies" when "user" is selected because the WHERE clause only filters on what has been selected.


    MCITP SQL Server 2008 (DEV); MCTS SQL Server 2008 (BI, DEV & DBA)

    • Marked as answer by Shihan Holmes Wednesday, June 20, 2012 9:33 AM
    Wednesday, June 20, 2012 7:50 AM
  • Thanks Valentino This looks fine now one parameter to list both company or user names

    Shihan Holmes

    Wednesday, June 20, 2012 9:33 AM