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

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.


Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.