Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Koen Verbeeck

Koen Verbeeck is a Microsoft Business Intelligence consultant at element61, helping clients to get insight in their data. Koen has a comprehensive knowledge of the SQL Server BI stack, with a particular love for Integration Services. He's also a speaker at various conferences.

SSRS Nugget: displaying placeholders when no rows are returned

A few days ago there was an interesting question on the forum:

How can I get None in each cell of a table in the report if no rows are returned? If I use “No rows message” property, header is not displaying on the report but I would like to get headers along with “NONE” in each column if no rows returned.

So the “No rows message” property was not what the OP wanted. He still wanted to display the table with the header, but just a line with the value “None” for every cell. To test it out, I created a simple report pulling back some data from AdventureWorks. I used the following query:

SELECT        ProductCategoryAlternateKey, EnglishProductCategoryName
FROM            DimProductCategory;

Not exactly rocket science. This gives me the following tablix (I spent an insanely amount of time on the layout):

reportwithdata

What if the source query suddenly stops returning rows? I added WHERE 1 = 0 at the end of the query and I ran the report again (to remove caching, delete the .data cache file in the project folder).

reportonlyheader

Only the header is returned. Now we want to add a row with only “None” for values. To do this, we simply hardcode such a row into the table and only show it when necessary.

report_incorrect1

On the row visibility property, I used the following expression:

=iif(CountRows() = 0,False,True)

You can find more info on CountRows here. However, when I ran the report the extra row didn’t show up. Maybe I needed to add a scope as a parameter to the CountRows function? So I changed the expression to this, with TablixTest being the name of the Tablix:

=iif(CountRows(“TablixTest”) = 0,False,True)

The line still didn’t show up. I changed the scope to Detail (in the hope it would count the detail lines), but no luck. To be honest, I don’t know much about scopes in SSRS. Then I had an idea: I added the extra row to the details of the tablix. What if I added it outside that group?

addrow_outsidegroup

I used the original expression again – without any scope – and when I ran the report, the row finally showed up!

reportwithoutdata

When the report is run again with rows returned, the line disappears as it should be.

Comments

Leave a comment on the original post [blogs.lessthandot.com, opens in a new window]

Loading comments...