Well, you could of course argue that just because I have starting to using this, it is not best practice, but I have now had so many issues with the below 2 filters which are used all over the standard reports. So I have started using another approach.
Here are the 2 filter patterns used everywhere in standard reports in Dynamics NAV.
1. Fields![Field].Value > ””
These filters are often used when filtering rows on Tables and very often in Visibilty expresssion. In many scenarios they work fine, and I guess that is why the report developers decided to to use this way of filtering, but they do not work in all scenarios.
Let me explain!!!
From the standard Report 108 I have taken 2 columns, a Code and Decimal columns, to show that you want to be careful when using above expressions.
For both the Code Value and the Decimal Value I have created 5 columns:
Row: Row number in Dataset
Code Value: The actual value of the field. I have cheated a little so the NULL value “<>” also shows here in my report, normally they look like blank data when the report is rendered.
Field > ””: The actual field with an if statement to tell us if the value is empty or not using Fields![Field].Value > ””
IsNothing: The actual field with an IsNothing statement to tell us if the value is empty or not using IsNothing(Fields![Field].Value)
Len: The actual field with a Len statement to tell us if the value is empty or not using Len(Fields![Field].Value). Len returns an integer containing either the number of characters in a string or the number of bytes required to store a variable.
As you can see in left part of the above picture you have to be careful to use IsNothing, when you are working with Code fields, the same goes for Text fields. IsNothing only returns the right result when the text actual is present in the row and when the value is NULL, represented with a “<>” in the dataset. When the value is blank, it is incorrectly saying false. I have marked the False red in the rows where it actually should be True. Notice that both using Fields![Field].Value > ”” and Len(Fields![Field].Value) produces the right result in the left part of the picture.
As you can see in the right part of the above picture you have to be careful to use Fields![Field].Value > ”” , when you are working with Decimal fields, Fields![Field].Value > ”” only produces the right result when the decimal value is Null(“<>“) and when the value is not NULL the expression returns an #Error. Errors like these we want to avoid, and you cannot see these errors unless you copy the Visibility or Filter Expression to a textbox which is visible in the layout. Hint! I always do this if the expression is complex and I want to make sure that it returns TRUE and FALSE the right places. Also Visual Studio will not warn you when you build the Solution / Web Site with errors in you filter or visibility expressions. But Report Viewer will very often complain at run time, and you are presented with Blank screen in Report Viewer. When you see the Blank screen you have made an error in a filter or visibility expression. Report Viewer will show you the error in under a millisecond so really hard to see, but if you want to see the error, print to PDF or copy the Report.rdlc and Dataset.xml to an Offline Project.
If we look a little more at the right side of the above picture you can see that IsNothing is now producing the right result for each row. And also we see that Len is producing the right result. So no matter what value I come with, Len will produce the right result no matter what, and that is the reason I have started using Len in all my Filter and Visibility expression.
When you use Len in a Visibility expression, you need have the syntax to look like this:
=iif(Len(Fields![FIELD].Value) = 0, TRUE, FALSE)
When you use Len in a filter expression, you need have the syntax to look like this:
=Cstr(Len(Fields!SalesHeaderCurrCode.Value)) and then in the Tablix Properties page you set the Operator to “<>” and the Value to “0”. If you want to avoid using Cstr to convert all to Text, you change the value to the right of the Expression to Integer. Since we are now using the Len on all fields, the Cstr() is just unnecessary overhead.
Unfortunately Visual Studio changes the value Integer to Text each time you open the Expression, so you might want to keep Cstr in front of the Len function, so you do not create any confusion for other developers coming after you, and want to see what is in the Filter Expression. I guess it is just one more of the things in Visual Studio we have to live with…
You can find the report I used for this blog post here at my OneDrive: http://sdrv.ms/1a1RlBk. It is in a Offline Project so it is not required to have NAV Dynamics installed to look at this report, and the expression I used. You just need to have Visual Studio 2010 installed to be able to run this, as I previous outlined here: Offline, preview and debug reports in Microsoft Dynamics NAV 2013
/Thanks, Claus Lundstrøm