SQL Server 2008 R2 – How to Keep the Structure of the Tablix When There is No Data

sql-server-2008-r2ssrs

We have a SSRS report which shows the result of a dataset in a tablix. If the query in the dataset returns no data, the header of the Tablix gets displayed but not the tablix cells below the header. I know there is a property to NoRowMessage, but in fact it is not what we want. Because when I specify a message, SSRS shows the message instead of the empty tablix.I must show the tablix structure as it is, but with empty cells.

I even set the rule for each cell value of the tablix using IsNothing function, to show blank (""), if the value is null, but it didn't help.

Do you have any idea?
Thanks for your help in advance.

Best Answer

You can add a row outside of the outermost group right under the tablix header row.
enter image description here

Set the row visibility to Show or hide based on an expression. The expression would like like:

=iif(CountRows("DataSet1") > 0, true, false)

When there is no data, the table will show the headers and empty row.

enter image description here

When there is data, the empty row will be hidden.

Related Solutions

SSRS calculated field strange behavior

It is because it may not have a value at that pariticular field/place so first replace the value providing box with:

iif(IsNothing(sum(field)),"0",(sum(field))

Then replace row summary field with the below expression:

=iif(cInt(ReportItems!textbox1name.value)=0,100,(ReportItems!textbox2.value/iif(cInt(ReportItems!textbox1name.value)=0,100,ReportItems!textbox1name.value)*100))

SSRS Chart – How to Include Categories with No Values

The best solution I've found to this type of issue is to create a date dimension table that shows various information for dates- in this case it could simply be date and month, but other implementations I've seen include whether or not a particular day is a holiday or a day in which employees will work.

Then, apply the date criteria to this dimension table and left join the results onto it-

CREATE TABLE date_dim (Date_eval, Month)

...

SELECT  [t.DataPoint], [dd.Month]
FROM   date_dim dd
LEFT JOIN Table t 
   ON t.[When] = dd.Date_Eval
WHERE
    Date_eval >= DATEADD(MONTH, -7, @CurrentMonth)
    Date_eval < @CurrentMonth

The downside of this approach is that you have to populate a table with data and that if you use a date dimension attribute that can change, such as whether or not employees work a certain day, the table is difficult to maintain automatically.

The upside is that you have a framework to build reports like this on, and one that can be reused as often as you need it.

Best Answer

Related Solutions

SSRS calculated field strange behavior

SSRS Chart – How to Include Categories with No Values

Related Question