SSRS calculated field strange behavior

ssrs

I have a dataset with a calculated field:

 = IIF(String.IsNullOrEmpty(Fields!Event.Value), "\t", Fields!Event.Value) & ", " &   
   IIF(String.IsNullOrEmpty(Fields!Action.Value), "\t", Fields!Action.Value) & ", " & 
   IIF(String.IsNullOrEmpty(Fields!RequestedBy.Value), "\t", Fields!RequestedBy.Value)

This field is then accessed inside a Table within the report using a lookup expression:

=Join(LookupSet(Fields!contactid.Value, Fields!ContactParticipantValue.Value, Fields!RowSummary.Value, "EventRequests"), Environment.NewLine)

When I run this, initially everything works fine and the correct data is shown in the cell. However there is an warning displayed:

Warning 1   [rsRuntimeErrorInExpression] The Value expression for the field ‘RowSummary’ contains an error: (processing): (field.ExprHost != null)  0   

Then if I go to the next page, all of the cells now contain #Error instead of the 'RowSummary' info. If I then go back to the previous page, which used to have all the correct data in it, all of the cells are now replaced with #Error.

I have found a work around whereby I add a hidden list container to the END of the report and set DataSet property to the 'EventRequests' dataset with the calculated field in it. I don't have to add any fields or make this list visible, but somehow just having the list on the report prevents the #Error from replacing all of my data. This does not solve the problem completely because when I go to export to PDF, I still receive errors.

Anyone know why this is happening?

Best Answer

It is because it may not have a value at that pariticular field/place so first replace the value providing box with:

iif(IsNothing(sum(field)),"0",(sum(field))

Then replace row summary field with the below expression:

=iif(cInt(ReportItems!textbox1name.value)=0,100,(ReportItems!textbox2.value/iif(cInt(ReportItems!textbox1name.value)=0,100,ReportItems!textbox1name.value)*100))