Hiding table or assigning temp data based on visibility expression ssrs 2008

Question

I have a table in ssrs 2008. This table has a row visibility expression like:

=IIF(max(Fields!VExpected.Value) <> "", 1, 0) + 
 IIF(max(Fields!MExpected.Value) <> "", 1, 0) + 
 IIF(max(Fields!PExpected.Value) <> "", 1, 0) = 3, false, true)

Sometimes the datasource returns no data, or the returned data is not matching with this expression. In this case what I see is that a table with borders and column names but no data on it like:

id Vex Mex Pex

However, I want to show it as

id Vex Mex Pex
-  -   -   -

Or if possible:

id Vex Mex Pex
No Data

Another question is, is there any way to hide the complete table if there is no returning data or any matching data with the expression?

Thanks

alejandro zuleta alejandro zuleta · Accepted Answer · 2016-04-11T01:30:27

You can use CountRows function to determine how many rows your dataset is returning. If it is zero hide the table otherwise show it.

=iif(CountRows("DataSetName")=0,true,false)

Replace DataSetName by the actual name of your dataset.

For not matching expression data you can use the this expression.

=IIF(
max(Fields!VExpected.Value) <> "" AND
max(Fields!MExpected.Value) <> "" AND
max(Fields!PExpected.Value) <> "",False,True
)

The whole expression for matching expression and no rows cases could be something like this:

=Switch(
CountRows("DataSetName")=0,true,
max(Fields!VExpected.Value) = "",true,
max(Fields!MExpected.Value) = "",true,
max(Fields!PExpected.Value) = "",True,
true,False
)

Supposing VM, ME and PE expected values are numeric type I'd use ISNOTHING() function to determine when null values are being returned.

=Switch(
CountRows("DataSetName")=0,true,
ISNOTHING(max(Fields!VExpected.Value)),true,
ISNOTHING(max(Fields!MExpected.Value)),true,
ISNOTHING(max(Fields!PExpected.Value)),True,
true,False
)

Additional you can set a message when no rows are being returned from your dataset. Select the tablix and press F4 to see properties window. Go to NoRowsMessage property and use an expression to say your users there is no data.

="There is no data."

In this cases the tablix will not appear in your report but the message you set will be rendered in the location where the tablix should be.

Let me know if this helps.

Hiding table or assigning temp data based on visibility expression ssrs 2008

1 Answers