Post reply

Hide multiple Columns based on Parameter value in SSRS 2004

  • September 1, 2012 at 12:42 am

    #266801

    Can anybody tell me how to hide/show multiple columns based on multivalued parameter.

    ex :i have two parameters

    1)item

    2)option(multivalued - ven,itm,qty) and values are (venr,itm,qty).

    i have a dataset contains 3 columns vendor,itm,qty.based on the item i select in the "item" parameter it fetches the respective ven, itm,qty,i want to select which column to be displayed,for this i have created option (multivalued parameter) and used the below mentioned condition in the respective column visibility expression:

    =IIf(Parameters!option.Value(0) = "ven",FALSE,TRUE)

    =IIf(Parameters!option.Value(0) = "itm",FALSE,TRUE)

    =IIf(Parameters!option.Value(0) = "qty",FALSE,TRUE).

    its working fine if i select any one option in option parameter.its not working if i select more than option in option parameter.thta why i cant hide/show more than 1 column at a time.

  • September 7, 2012 at 4:39 am

    #1534983

    I believe you have to nest your IIF code to account for all possible permeations of the parameters. It's been a while since I used SSRS, though, so I'm not 100% on that.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • September 13, 2012 at 6:57 am

    #1537171

    yudy.varma (9/1/2012)

    Can anybody tell me how to hide/show multiple columns based on multivalued parameter.

    ex :i have two parameters

    1)item

    2)option(multivalued - ven,itm,qty) and values are (venr,itm,qty).

    i have a dataset contains 3 columns vendor,itm,qty.based on the item i select in the "item" parameter it fetches the respective ven, itm,qty,i want to select which column to be displayed,for this i have created option (multivalued parameter) and used the below mentioned condition in the respective column visibility expression:

    =IIf(Parameters!option.Value(0) = "ven",FALSE,TRUE)

    =IIf(Parameters!option.Value(0) = "itm",FALSE,TRUE)

    =IIf(Parameters!option.Value(0) = "qty",FALSE,TRUE).

    its working fine if i select any one option in option parameter.its not working if i select more than option in option parameter.thta why i cant hide/show more than 1 column at a time.

    I guess you have taken the first value out of the three values in Multiselect parameter.

    "=IIf(Parameters!option.Value(0) = "ven",FALSE,TRUE)"

    You have to remove that array index values for the expression.

    Instead Join them using "=JOIN(Parameters!option.Value,",")" and then use a like filter in the expression.

    --Divya

  • September 13, 2012 at 6:59 am

    #1537173

    Divya Agrawal (9/13/2012)

    yudy.varma (9/1/2012)

    Can anybody tell me how to hide/show multiple columns based on multivalued parameter.

    ex :i have two parameters

    1)item

    2)option(multivalued - ven,itm,qty) and values are (venr,itm,qty).

    i have a dataset contains 3 columns vendor,itm,qty.based on the item i select in the "item" parameter it fetches the respective ven, itm,qty,i want to select which column to be displayed,for this i have created option (multivalued parameter) and used the below mentioned condition in the respective column visibility expression:

    =IIf(Parameters!option.Value(0) = "ven",FALSE,TRUE)

    =IIf(Parameters!option.Value(0) = "itm",FALSE,TRUE)

    =IIf(Parameters!option.Value(0) = "qty",FALSE,TRUE).

    its working fine if i select any one option in option parameter.its not working if i select more than option in option parameter.thta why i cant hide/show more than 1 column at a time.

    I guess you have taken the first value out of the three values in Multiselect parameter.

    "=IIf(Parameters!option.Value(0) = "ven",FALSE,TRUE)"

    You have to remove that array index values for the expression.

    Instead Join them using "=JOIN(Parameters!option.Value,",")" and then use a like filter in the expression.

    You can use = IIF(JOIN(Parameters!option.Value,",")" Like "*ven*",False,True)

    --Divya

  • September 14, 2012 at 12:04 am

    #1537627

    Thnks divya..its working

  • September 14, 2012 at 1:10 am

    #1537644

    yudy.varma (9/14/2012)

    Thnks divya..its working

    I am glad it helped you 🙂

    --Divya

  • September 14, 2012 at 9:18 am

    #1537942

    I was looking at a similar problem and that is a smart solution Divya - thank you 🙂

    Tom in Sacramento - For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • September 20, 2018 at 1:19 am

    #2006388

    Divya Agrawal - Thursday, September 13, 2012 6:59 AM

    Divya Agrawal (9/13/2012)

    yudy.varma (9/1/2012)

    Can anybody tell me how to hide/show multiple columns based on multivalued parameter.ex :i have two parameters 1)item 2)option(multivalued - ven,itm,qty) and values are (venr,itm,qty). i have a dataset contains 3 columns vendor,itm,qty.based on the item i select in the "item" parameter it fetches the respective ven, itm,qty,i want to select which column to be displayed,for this i have created option (multivalued parameter) and used the below mentioned condition in the respective column visibility expression:=IIf(Parameters!option.Value(0) = "ven",FALSE,TRUE)=IIf(Parameters!option.Value(0) = "itm",FALSE,TRUE)=IIf(Parameters!option.Value(0) = "qty",FALSE,TRUE).its working fine if i select any one option in option parameter.its not working if i select more than option in option parameter.thta why i cant hide/show more than 1 column at a time.

    I guess you have taken the first value out of the three values in Multiselect parameter. "=IIf(Parameters!option.Value(0) = "ven",FALSE,TRUE)" You have to remove that array index values for the expression. Instead Join them using "=JOIN(Parameters!option.Value,",")" and then use a like filter in the expression.

    You can use = IIF(JOIN(Parameters!option.Value,",")" Like "*ven*",False,True)

    Hi Divya,

    It's not working for me. Getting error "Overload resolution failed because no public join can be called with these argument."

  • September 20, 2018 at 4:20 am

    #2006397

    Kinjal BI - Thursday, September 20, 2018 1:19 AM

    Divya Agrawal - Thursday, September 13, 2012 6:59 AM

    Divya Agrawal (9/13/2012)

    yudy.varma (9/1/2012)

    Can anybody tell me how to hide/show multiple columns based on multivalued parameter.ex :i have two parameters 1)item 2)option(multivalued - ven,itm,qty) and values are (venr,itm,qty). i have a dataset contains 3 columns vendor,itm,qty.based on the item i select in the "item" parameter it fetches the respective ven, itm,qty,i want to select which column to be displayed,for this i have created option (multivalued parameter) and used the below mentioned condition in the respective column visibility expression:=IIf(Parameters!option.Value(0) = "ven",FALSE,TRUE)=IIf(Parameters!option.Value(0) = "itm",FALSE,TRUE)=IIf(Parameters!option.Value(0) = "qty",FALSE,TRUE).its working fine if i select any one option in option parameter.its not working if i select more than option in option parameter.thta why i cant hide/show more than 1 column at a time.

    I guess you have taken the first value out of the three values in Multiselect parameter. "=IIf(Parameters!option.Value(0) = "ven",FALSE,TRUE)" You have to remove that array index values for the expression. Instead Join them using "=JOIN(Parameters!option.Value,",")" and then use a like filter in the expression.

    You can use = IIF(JOIN(Parameters!option.Value,",")" Like "*ven*",False,True)

    Hi Divya,

    It's not working for me. Getting error "Overload resolution failed because no public join can be called with these argument."

    This thread is over 6 years old. No one will be actively monitoring / responding to it. You should open a new thread in this subforum (https://www.sqlservercentral.com/Forums/Reporting-Services/Reporting-Services-2005-Development) and reference this thread in your "solutions I have tried" section of your post if you want proper support.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Viewing 9 posts - 1 through 9 (of 9 total)

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy