September 1, 2012 at 12:42 am
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
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.
September 13, 2012 at 6:57 am
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
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
Thnks divya..its working
September 14, 2012 at 1:10 am
yudy.varma (9/14/2012)
Thnks divya..its working
I am glad it helped you 🙂
--Divya
September 14, 2012 at 9:18 am
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
Divya Agrawal - Thursday, September 13, 2012 6:59 AMDivya 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
Kinjal BI - Thursday, September 20, 2018 1:19 AMDivya Agrawal - Thursday, September 13, 2012 6:59 AMDivya 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.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply