SSRS: Empty dataset and Tablix Control

The behaviour of the tablix is that whenever the associated dataset is empty, it will not show any detail row. But there are requirements wherein you need to show 0 when the data set is empty. Refer the below screenshot

The best way to achieve this is to prepare 1 dataset with 3 hard-coded rows and then calculate the values and update against each row. However the drawback with this approach is that even to change the “Description” of the metrics, you need to change the SQL Script. We use stored procedure to populate the dataset  and hence we have to change the Stored Procedure even for a small change in the Description.

So my friend choose to go another way. Use 3 tablix with description hard-coded in it and create 3 separate data set to populate the count. This worked fine for first two metrics. As the 3 dataset is empty , the 3rd row did not appear.

We overcame this issue by creating one more data set (dataset4), which will return only one row, say current date. Assigned this data set to all 3 tablixes. Hard-coded the description in tablix and used COUNT function against corresponding dataset (dataset 3)to calculate the value.

In fact we could have solved this with only one tablix by deleting the detail row and adding 3 rows in the header.

Advertisements

About Suresh Kumar D

Hardcore SQL Developer and aspiring Database Architect.
This entry was posted in SSRS. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s