다음을 통해 공유

#Error When Rendering Report

  • 아티클

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.

SNAGHTMLcd4ab0

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. 

SNAGHTMLd22b39

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.

SNAGHTMLd45193

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.

SNAGHTMLd8c0d4

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.

SNAGHTMLdce418

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.

SNAGHTMLdf81e5

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.

SNAGHTMLe1ca9c

If we look at the actual Dataset in the Report, and look at the fields, we’ll actually see two Prisoner fields. 

SNAGHTMLe4aa44

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.

SNAGHTMLe61c82

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

Comments

  • Anonymous
    January 27, 2015
    I have seen field expressions trip up even seasoned programmers. We must be cautious. I love that the Imperials cannot spell: Sentance? Terminiation?? :P

  • Anonymous
    November 17, 2016
    SSRS getting 100% cpu utilization and not able to acess in production environment. Any suggestions.

    • Anonymous
      November 17, 2016
      The comment has been removed