SSRS Report Output Displays Blank and SQL Result Set displays Correctly

Hi All,

I have the SQL code for one of the select list columns as shown below in the SSRS Data set query level.

 Example : Select IsNUll(max(ET.[# Utilizations by Utilization Method]),0) as [Employee Period Total]

SQL Result Set Output :

The above code when run in SQL displays correctly with zeroes if NULL values are available from the Source tables.

Now at the SSRS Report level design I have grouped with more than one values.

Even at the expression  I have given  =Fields!Employee_YTD_Total.Value but report output displays blank
In order to evaluate 0 from the SQL result set I even tried equating the values to 0 like 

= Iif(Fields!Employee_YTD_Total.Value = 0 ,0,Fields!Employee_YTD_Total.Value) but still the report outputs is blank.

Issue/Query : Why it displays blank and I should have this as 0 in the report output to eliminate blank as this would be not appropriate to the end users to validate. What has to be changed either at the SSRS level or at the SQL level

Thanks in Advance


May 22nd, 2015 11:27pm

What if you cast to string like 

Iif(Fields!Employee_YTD_Total.Value = "0" ,"0",Fields!Employee_YTD_Total.Value) 

Free Windows Admin Tool Kit Click here and download it now
May 23rd, 2015 3:37am

Employee_YTD_Total is of Money Datatype in Source table and so is that it should be converted to String in order for the SSRS report to display 0.

From the SQL Result set output there are 21 rows and I get 0 s for all the 21 rows and in the SSRS report  it displays 0 for only the first row and why is it so? 

I am just concerned that something else has been the root cause or not sure why.

Any thoughts please let me know


Mahesh MSBI

May 24th, 2015 7:22am

Hi Mahesh,

Per my understanding that the field "Employee_YTD_Total" display blank but actually you want to display 0 in the report, you are trying to use the expression "Iif(Fields!Employee_YTD_Total.Value = 0 ,0,Fields!Employee_YTD_Total.Value) " but still got blank, right?

As you mentioned that the datatype of Employee_YTD_Total is Money, so, please try to modify the expression like below to have a test:
=Iif(Fields!Employee_YTD_Total.Value is nothing ,0,Fields!Employee_YTD_Total.Value)

=Iif(ISNOTHING(Fields!Employee_YTD_Total.Value), 0,Fields!Employee_YTD_Total.Value)

If your problem still exists, please try to provide more details information about the unexpect result you got and also the sample data in the DB.

Vicky Liu

Free Windows Admin Tool Kit Click here and download it now
May 25th, 2015 3:45am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics