Link to home
Start Free TrialLog in
Avatar of revo1059
revo1059Flag for United States of America

asked on

Hide textbox in SQL report builder

I have a report that has a tablix grid in it and a textbox with some legal info on it. I'd like the legal info textbox to not show when an item type is not in the tablix grid. I have a column "item_type" and those rows contain #s that identify that type of item it is. I'd like it to hide the textbox if item # "804800001" is in any of the rows of the item_type column. I have:

=IIf(First(Fields!item_type.Value, "ProductLineItemsAndAmount") = 80480001 , False, True)

but using this it either always shows or is always hidden (depending on the order of True, False), so it's not reading the rows in the item_type column.

I got the field info from the expression dialogue (clicking on Datasets then Item, then value).
Avatar of Arifhusen Ansari
Arifhusen Ansari
Flag of India image

From your question what i can understand is you have tablix  with some kind of item data. You have "item_type" column in it.You have one text box "legal Info" out of tablix may anywhere in the report.
If any of your item_type has the value "80480001". You want to hide your text box.

It this is a case. You can use following solution.

I have attached the rdl file. Dataset has some example data for demonstration purpose.

I took three text box first will have the Legal information , but it will be visible.
Other text box has the same  Legal information but it will be visible based on the value in your item_type column.

I have used the below expression to set visibility run time. Refer below screenshot.

User generated image
I have also attached a rdl file. Go through it you will get clear idea.

You can try this report by manipulating data in the Dataset Query of report.
Let me know if you have any question.
Avatar of revo1059

ASKER

Hi Arif,

I don't see the .rdl file attached, just the picture you posted.

I tried implementing based on the example in the picture but it shows the agreement even if that item # is in there. Here is what I have:

 =iif(lookup("80480001",Fields!item_type.Value,Fields!item_type.Value, "ProductLineItemsAndAmount")="",false,true)
Sorry i forgot to attach a report. Pls find a report.
HideTextbox.rdl
Hi Arif,

I am still missing something. I'll attach what I have and if you could look at it I'd appreciate it! The field that should dictate if the agreement is shown is dcs_type. The value is 804000001. If that value isn't present anywhere in the dcs_type column then the agreement shouldn't be visible.
HS-Tax-soft-lic-test-empty.rdl
ASKER CERTIFIED SOLUTION
Avatar of Arifhusen Ansari
Arifhusen Ansari
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Well, this is strange. It worked after I downloaded it and inserted it, so I copied the expression to the version that has our agreements and it didn't work. I then re-inserted the one you did and now it doesn't work either. I know it's inserting because I'm changing the text inside the box to make sure. I have no idea why it worked on initial install then stopped working.

Also, in the file you sent the code is different than the example above, so I copied the above code in and still no luck. No idea why it worked once then stopped. Right now it's always showing the agreement.

Here is what is in there now:

 =iif(lookup("804800001",Fields!dcs_type.Value,Fields!dcs_type.Value, "ProductLineItemsAndAmount")="",false,true )

Open in new window


Here is what was in the attached file:

 =lookup("804800001",Fields!dcs_type.Value,Fields!dcs_type.Value, "ProductLineItemsAndAmount") 

Open in new window


Also just to note, if the value of 804800001 isn't there there will be another value or it could be empty.
When we use the lookup function. It will lookup that data set for the value "804800001". If that value is not available in that data set. it will be blank.
Got it. For some reason it's not working after the first time. I know it's reading something because if I reverse false,true then it either always shows or never shows.
Hi Arif,

I got it working! I ran the report and looked at the XML data. Made a slight change based on what was in there and all is good. Thanks for your help.