Hide multiple Columns based on Parameter value in SSRS 2004

  • yudy.varma

    Right there with Babe

    Points: 755

    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.

  • Brandie Tarvin

    SSC Guru

    Points: 172535

    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.

  • Divya Agrawal

    Hall of Fame

    Points: 3144

    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

  • Divya Agrawal

    Hall of Fame

    Points: 3144

    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

  • yudy.varma

    Right there with Babe

    Points: 755

    Thnks divya..its working

  • Divya Agrawal

    Hall of Fame

    Points: 3144

    yudy.varma (9/14/2012)


    Thnks divya..its working

    I am glad it helped you 🙂

    --Divya

  • Tom_Sacramento

    SSCertifiable

    Points: 5104

    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/

  • Kinjal BI

    Newbie

    Points: 7

    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."

  • Brandie Tarvin

    SSC Guru

    Points: 172535

    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