John L. Hall posted on September 17, 2010 16:13
I recently had a report that ran fine with no error and then we reprocessed the cube with a different data source and the report had many cells displaying #Error. After playing with it for a while I noticed that it was also getting a warning message “This field is missing from the returned result set form the data source. The columns displaying errors were the ones reference in the error message and had a value derived by an expression to replace nulls with 0, something like Iif(Field!MTD_Incident_Count.Value is nothing,0,Field.MTD_Incident_Count.Value).
It turns out the problem is caused by the way Analysis Services(SSAS) data provider and SSRS handle a column in a result set that has nulls in every row. What the SSAS data provider does is not send the column down to the client because the value in every row is null. What that means is you cannot even check the field!MTD_Incident_Count.Value in any way because it does not exist in the result set so the Value method on the fields object will give an exception if reference in anyway.
When the report runs you see the warning about fields missing in result set.
[rsMissingFieldInDataSet] The dataset ‘Dataset1’ contains a definition for the Field ‘MTD_Incident_Count’. This field is missing from the returned result set from the data source.
[rsErrorReadingDataSetField] The dataset ‘Dataset1’ contains a definition for the Field ‘MTD_Incident_Count’. The data extension returned an error during reading the field. There is no data for the field at position 4.
You only get this warning if the value of every row for a column is null. The way to fix this is to change the expression that is used to replace the null with 0.
First we must create a report function (Code tab in Report Properties dialog) that can look at the IsMissing field property before it looks at the Value property. An Immediate If (IIF) statement cannot be used because all parts of the statement are evaluated which causes an exception.
Here is an example of the function to add. We may need multiple versions if the data type it returns need to change.
'If a column in a AS resultset contains all nulls. It is dropped from the dataset so you
'cannot reference the value field in any way so IsNothing(Field!A.Value) will throw an
'exception. In order to get around this error you can define a function to in the report
'you can define a function to check if the field is missing before checking for nulls
Function NullAsZero(ByRef F As Field) As Double
If F.IsMissing Then
Return 0
Else
If F.Value Is Nothing Then
Return 0
Else
Return F.Value
End If
End If
End Function
The Report Properties Dialog is used to add the function.
Next you need to replace the expression used on the report cell Iif(field!MTD_Incident_Count.Value is nothing,0,field.MTD_Incident_Count.Value) with something like this Code.NullAsZero(Fields!MTD_Incident_Count).
And finally notice the NullAsZero returns a double which provides the most flexible numeric result but it must be formatted to what you want. Use the TextBox Properties Dialog Number tab to get it to display correctly, with the number of decimal places as needed.
This problem was very aggravating and I hope this entry will help someone keep their sanity.