Last week a case was brought to me where the customer was getting a #Error for a field within their report. The field value was normally a number, but they wanted to change it to something like “1&1”. That is when they would see the #Error.
I created my own report that reproduces the issue they were having. Let’s have a look at what this report looks like normally. We are going to focus on the Holding Prisoners field.
We can see the problem if we change the field in the database from 15 to “1&1”. At the start, the one difference in my report is that it shows blank instead of #Error. We will get to the #Error though. Just pretend that the blank is a #Error. It isn’t really relevant to the actual issue.
Whenever we see a #Error, this comes from two things. Either something is wrong with the data, or something is wrong with the Report (RDL). More specifically, it is usually an expression issue within the Report. Looking at the report design, we can see that we do indeed have an expression for that field.
The expression is the following:
=Iif(Trim(CStr(Fields!Holding.Value)).Equals("Cell Block 1138"),Fields!Deck.Value,Fields!Prisoners.Value)
All this is really saying is that if the Holding value is equal to “Cell Block 1138” then show the Deck value. Otherwise we are going to show the Prisoners value. We know that we are going to get the Prisoners value out of this as we changed that and caused the problem. Also we can see that the Holding value is “Detention Block AA-23”, so the IIF statement will go to the Prisoners value. So, let’s just change the expression to just show the Prisoners value to rule out anything with this expression. We can just change it to the following.
=Fields!Prisoners.Value
We still see the #Error.
At this point, we can see that we are pulling straight from the Prisoners field, so there is no other expression on this textbox that should be getting in the way.
Changing the Database value back to a number allows it to show correctly. If we look at the value in the database that is causing the problem we see the 1&1 value that we placed there. To validate that it wasn’t the & causing a problem, we can just change the value to the letter a, and we still see #Error. So, it is just not liking a string. There is also no formatting going on from a textbox perspective. It is just set to Default .
If we look at the Table definition, within SQL, we can see that the field’s datatype is a Varchar(50). So, the Database side is fine and will accept a string. Otherwise we would have gotten an error when trying to put a string value in that field.
If we look at the actual Dataset in the Report, and look at the fields, we’ll actually see two Prisoner fields.
In the customer’s case, they had a lot of fields and the field with the underscore was way down at the bottom. The field with the underscore is the actual field from the database. The one without the underscore is what is called a calculated field. We can see this if we right click on the field and go to properties . When you go to add a field, you can choose Query Field , which is just a straight field from the database, or Calculated Field which is based on an expression.
If we look at the expression for Prisoners, we see the following. We can break this down a little bit. There are two IIF statements here.
=Iif(IsNothing(Fields!Prisoners_.Value),"N/A",Iif(Fields!Prisoners_.Value=0,"N/A",Fields!Prisoners_.Value))
So, let’s try a few things. First , if we just show the Prisoners_ field, we get the right value regardless of what it is. It will show strings just fine.
=Fields!Prisoners_.Value
The result of this was “1&1”. It works! Second, let’s try the inner IIF statement.
=Iif(Fields.Prisoners_.Value=0,”N/A”,Fields!Prisoners_.Value)
The result of this was #Error. This is our problem child. Then I realized what was happening. The first part of this is an evaluation of Value=0. The 0 is a hint to the Expression engine that the value will be numeric. When trying to compare a number to a string we will get a conversion error.
As a side note, if you use SQL Server Data Tools (SSDT), instead of Report Builder, to create your report, and go to Preview within SSDT, it will actually give you a hint to this effect in the Errors window of Visual Studio.
Warning 1 [rsRuntimeErrorInExpression] The Value expression for the field ‘Prisoners’ contains an error: Input string was not in a correct format. d:\src\Personal\RenderingError\RenderingError\DetentionBlock.rdl 0 0
To give the Expression engine the hint it needs, we can do the following.
=Iif(Fields!Prisoners_.Value=”0”,”N/A”,Fields!Prisoners_.Value)
By putting 0 within double quotes, we tell the Expression engine to treat it like a string instead of a number. This then produces the desired result of “1&1”. Combining this with the full two IIF statements succeeds also. Now let’s go back to the textbox and add in the original expression.
=Iif(Trim(CStr(Fields!Holding.Value)).Equals("Cell Block 1138"),Fields!Deck.Value,Fields!Prisoners.Value)
This also produces the desired result! The answer here is that we need to enclose the 0 within the Calculated Field with double quotes to get the Expression engine to treat it like a string instead of a number.
Adam W. Saxton | Microsoft Business Intelligence Server Escalation Services
https://twitter.com/awsaxton
