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 :
0
0
0

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

Mahesh 

What if you cast to string like 

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

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

Thanks,

Mahesh MSBI

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)

OR
=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.

Regards,
Vicky Liu