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

reporting-services
TWood picture TWood · Jun 8, 2011 · Viewed 187.7k times · Source

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

I have tried two methods but neither are working and I am sure it is almost correct. 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?

Answer

user756519 picture user756519 · Jun 11, 2011

I tried the example that you have provided and the only difference is that you have True and False values switched as @bdparrish had 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. This example uses SSRS 2008 R2.

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 screenshot #1.

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

  4. Select Show or hide based on an epxression.

  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 data set 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.

Hope that helps.

Screenshot #1:

1

Screenshot #2:

2

Screenshot #3:

3

Screenshot #4:

4

Screenshot #5:

5

Screenshot #6:

6

Related questions

The permissions granted to user ' are insufficient for performing this operation. (rsAccessDenied)"}

I created a report model using SSRS (2005) and published to the local server. But when I tried to run the report for the model I published using report builder I get the following error. Report execution error:The permissions granted …

Read More
How do I format date and time on ssrs report?

on SSRS report I need to show todays date and current time i tried this =FormatDateTime(Now,"MM/dd/yyyy hh:mm tt") but this is not working for me giving an error. Anyone please help me for expression ? I …

Read More
SQL variable to hold list of integers

I'm trying to debug someone else's SQL reports and have placed the underlying reports query into a query windows of SQL 2012. One of the parameters the report asks for is a list of integers. This is achieved on the report …

Read More