Tag Archives: Show zero in SSRS

ByHariharan Rajendran

Display Zero When No Rows in Dataset – Power BI Paginated Report Tip -1

In recent times, I had a chance to work with Power BI Paginated Reports which is same as SQL Server Reporting Services (SSRS). I have worked with SSRS long back and the same knowledge was helped me to achieve any sort of solution in Power BI Paginated reports. I can see people are started using Power BI paginated reports heavily so I thought it will be good for developers if I share the tips and tricks that I know. I am going to post multiple article in this series so keep follow.

Tip – 1 – Show the value 0 is there is no data. This looks simple but sometimes difficult to achieve. If you have some data then you can write IIF statement and show 0 but if there is no rows in your dataset then your expression will not show anything.

Scenario

I got a requirement to show a layout like below in Power BI Paginated Report.

  Amount – Div1 Amount – Div2
Production 200 100
HR 400 300
IT 500 250
Marketing 600 150

 

In the above table – Column header and row values are hard-coded value except the numbers (200, 400 ,….) which is coming from dataset.

If we have proper value then we can achieve the above result easily. If there is no rows in dataset then need to show the below layout.

Expected

  Amount – Div1 Amount – Div2
Production 0 0
HR 0 0
IT 0 0
Marketing 0 0

 

Actual

  Amount – Div1 Amount – Div2

 

If you try to write any expression in the cell then it will not display and you will get like above. How to get the expected layout.

Why we are not getting the rows?

All the Tablix should be mapped with dataset and if there is no data then nothing will be displayed in the rows and will be hidden.

There are two methods to you can achieve the expected result.

Method 1 – if there is no data in dataset then you can add UNION with your existing script and can add one empty or zero values. If you have data then you can remove the dummy row in the expression else the dummy row only will be available as a dataset result and your table will show all the rows and you will get the expected result.

Method 2 – Since the layout is hard-coded or static layout, we can insert many header rows and achieve the result. To make it clear, we have header and data row. Header row will not show option to choose the dataset column but data row will show the option to choose the column. Here, we need to delete the data row and insert multiple header and write expression you will get the expected result.

 

1