Assigning NO DATA instead showing blank cells in ssrs 2008 - based on row visibility expression

Question

I have a problem in SSRS 2008 described as below:

I have a matrix which is connected to a query, lets say Select * from Table. It is returning, lets say 50 rows of data. In my matrix, there is only one row. Lets say:

id   name    grade
[id] [name]  sum[grade]

The matrix is grouped by 'id' and 'name' in Row Group. There is a row visibility expression for this row like =IIF(sum(Fields!grade.Value)>95,false,true). Assuming that for this situation, this table shows no data, all the returned 50 rows of data has a grade lower than 95 in total. Therefore, I see only the columns without any information on the screen like:

id name grade

What I want is to write "No Data" instead' like:

id name grade
No Data

Normally, when there is no data returning from the query, I would do it by going Tablix Properties and assign "No Data" to the NoRowsMessage property. This is not working for this situation, and I could not figure out how I can count the displayed row number in a matrix. Any help would be appreciated.

Answer 1

You can do this by adding a row add the end of the tablix and outside the Row Group.

Once the row is created type No Data in the first cell of the row.

Select the No Data row and go to Row Visibility property and set this expression:

=IIF(
Sum(IIF(Fields!Grade.Value>95,1,0))>0,True,False
)

When all rows have Grade 95 or less the No Data row will be shown but the data rows will be hidden.

UPDATE Update based on OP's feedback. Grade column is an sum expression.

In that case it is useful use the LookupSet function to get the grades by ID. They will be returned in an array data type so we require custom code to sum the ID grades.

Go to Report Menu / Report Properties..., select the Code tab and paste the following code.

Dim HiddenFlag as Integer = 0
Function CalculateHiddenFlag(ByVal items As Object()) As Integer
   If items Is Nothing Then
      Return HiddenFlag
   End If
   Dim sumItems As Decimal = New Decimal()
   sumItems = 0
   For Each item As Object In items
      sumItems += Convert.ToDecimal(item)
   Next
   If (sumItems > 95 and HiddenFlag=0) Then
      HiddenFlag = 1
   End If
   Return 0
End Function

Function GetHiddenFlag() As Integer
   Return HiddenFlag
End Function

Now modify the [Sum(Grade)] cell expression an use this one:

=Sum(Fields!Grade.Value)+
Code.CalculateHiddenFlag(
LookupSet(Fields!ID.Value,Fields!ID.Value,Fields!Grade.Value,"DataSet15"))

Replace DataSetName by the actual name of yours.

Your matrix should look like this:

For the No Data row visibility property use the following expression:

=IIF(Code.GetHiddenFlag()=1,True,False)

It will return this when at least one row has Grade > 95.

And this when there is no rows which Grade is greater than 95.

Let me know if this helps.