CODEWITHSUNDEEP
reporting-services
TWood
TWood

Reputation: 2583

How do I set the visibility of a text box in SSRS using an expression?

I have a subtotal field for a calculated column that I want to hide when my report has not run yet, because on days with no data, it shows up as NaN on the report.

I have tried two methods, but neither are working. I just don't know what is wrong with the expression.

I tried hiding if my dataset had no rows:

=IIf((CountRows("ScannerStatisticsData")=0),False,True)

I also tried making a calculation:

=iif((fields!Scans.Value / fields!numberOfCases.Value) = 0, False, True)

I also tried checking isnothing on one of the columns in the calculation

=iif(IsNothing(fields!Scans.Value), False, True)

What am I doing wrong?

Upvotes: 45

Views: 246732

Answers (8)

Volodymyr
Volodymyr

Reputation: 171

=IIf((CountRows("ScannerStatisticsData")=0),False,True)

Should be replaced with

=IIf((CountRows("ScannerStatisticsData")=0),True,False)

because the Visibility expression sets up the Hidden value.

Upvotes: 17

user756519
user756519

Reputation:

I tried the example that you have provided and the only difference is that you have True and False values switched as bdparrish pointed out. Here is a working example of making an SSRS Texbox visible or hidden based on the number of rows present in a dataset.

Step-by-step process: SSRS 2008 R2

  1. In this example, the report has a dataset named Items and has textbox to show row counts. It also has another textbox which will be visible only if the dataset Items has rows.

  2. Right-click on the textbox that should be visible/hidden based on an expression and select "Text Box Properties...". Refer to screenshot #1.

  3. On the Text Box Properties" dialog, click on "Visibility" from the left section. Refer to screenshot #2.

  4. Select "Show or hide based on an expression".

  5. Click on the expression button fx.

  6. Enter the expression =IIf(CountRows("Items") = 0 , True, False). Note that this expression is to hide the Textbox (Hidden).

  7. Click OK twice to close the dialogs.

  8. Screenshot #3 shows data in the SQL Server table dbo.Items, which is the source for the report dataset Items. The table contains 3 rows. Screenshot #4 shows the sample report execution against the data.

  9. Screenshot #5 shows data in the SQL Server table dbo.Items, which is the source for the report data set Items. The table contains no data. Screenshot #6 shows the sample report execution against the data.

Screenshot #1:

1

Screenshot #2:

2

Screenshot #3:

3

Screenshot #4:

4

Screenshot #5:

5

Screenshot #6:

6

Upvotes: 68

Justin
Justin

Reputation: 1

the rdl file content: 

<Visibility><Hidden>=Parameters!casetype.Value=300</Hidden></Visibility>

so the text box will hidden, if your expression is true.

Upvotes: 0

Marie
Marie

Reputation: 9

Twood, Visibility expression is the expressions you write on how you want the "visibility" to behave. So, if you would want to hide or show the textbox, you want to write this: 

=IIf((CountRows("ScannerStatisticsData")=0),True,False)

This means, if the dataset is 0, you want to hide the textbox.

Upvotes: 0

user2299191
user2299191

Reputation: 1

Visibility of the text box depends on the Hidden Value

As per the below example, if the internal condition satisfies then text box Hidden functionality will be True else if the condition fails then text box Hidden functionality will be False

=IIf((CountRows("ScannerStatisticsData") = 0), True, False)

Upvotes: 0

Mahesh.D
Mahesh.D

Reputation: 71

instead of this

=IIf((CountRows("ScannerStatisticsData")=0),False,True)

write only the expression when you want to hide

CountRows("ScannerStatisticsData")=0

or change the order of true and false places as below

=IIf((CountRows("ScannerStatisticsData")=0),True,False)

because the Visibility expression set up the Hidden value. that you can find above the text area as

" Set expression for: Hidden "

Upvotes: 7

TWood
TWood

Reputation: 2583

This didn't work

=IIf((CountRows("ScannerStatisticsData") = 0),False,True)

but this did and I can't really explain why

=IIf((CountRows("ScannerStatisticsData") < 1),False,True)

guess SSRS doesn't like equal comparisons as much as less than.

Upvotes: 14

bdparrish
bdparrish

Reputation: 2774

Switch your false and true returns? I think if you put those as a function in the visibility area, then false will show it and true will not show it.

Upvotes: 0

Related Questions