Implementing Microsoft Dynamics NAV 2009

What a great way to wake up! You can now pre-order our book. I woke up this morning to see that the link to pre-order the book with a discount was up on the PACKT site. Wow!

I have co-authored this book with my good friend Vjekoslav Babić, and I can see that he's been busy blogging about the book and letting everyone know. One of the reasons I have been so quiet in the blogoshpere and on my favourite forum over the last few months is because I've been working so hard to get this book finished.

We've really tried to make this book as informative as possible, while keeping the fun elements from our blogs. There have been some fantastic people involved in producing this book, with feedback from members of the product group and technical reviewing from seasoned NAV experts and bloggers (like Eric 'Waldo' Wauters). Thanks to everyone that has read this blog over the years and to those that have left the occasional comment.

The book is aimed at consultants and developers of NAV and gives an insight into the new features of NAV 2009, and also a series of tips and tricks and practical examples for configuring, modifying, and extending the application. I can honestly say that this is the best book on NAV 2009 I have ever written.

So what are you waiting for? Go and pre-order your copy now.

3-part Format String

I mentioned in the previous post that you could use a 3-part formatting string in the format property of your numeric field to display blank when the number is 0.

Here is an example of a 3-part format property setting. If you put this in the Format field:

="$#,###;($#,###);#"

Then this says:

• When Positive use $#,### which is “Prefix with Dollar sign, blank when zero, comma thousands separator, no decimal places”
• When Negative use ($#,###) which is “Same as positive but within brackets”
• When 0 use # which is “Blank”.

This works quite nicely. Don't try using special characters like "C" for currency in your 3-part format string because this special character is itself interpreted as a 3-part string and you'll find it doesn't work.

Reporting Services Divide by Zero Error in Report Expression

It’s a real pain – if I have a field that I want to calculate as one field divided by another, I have found that I get a divide by zero error when one of the fields is 0.

Normally, I would do a quick check on the field I am dividing by (the divisor) before using it in a calculation. In a field expression, the only way to include this kind of check is with an inline if statement. So I would have something like this in my report expression:

=IIF(Fields!Budget.Value = 0, "", Fields!Budget.Actual / Fields!Budget.Value)

But there is a problem with this. The False part of the IIF function still gets evaluated and your field shows #Error instead of a blank as expected.

There is a really simple way around this – create your own VB.NET function and call this from within the expression. Creating a VB.NET function is really easy as long as you are playing nicely in the sandbox (i.e. not trying to access any of the machine’s resources.) Here is a sample function that you can paste into the Code property of the report:

Public Shared Function VarPercent(ByVal Actual As Decimal, ByVal Budget As Decimal) As Decimal
If Budget = 0 Then
Return 0
End If
Return (Actual / Budget)
End Function

To use the function, just put the following in your expression:

=code.VarPercent(Fields!Actual.Value,Fields!Budget.Value)

That’s it!

If the divisor is 0, we return 0. We could of course return an empty string – although you can just as easily do this by formatting the field using a formatting expression that shows blank for zeros.

vbNewLine in Reporting Services Expression

Just a reminder for me because I keep forgetting. If you want to have new lines in a text box control in reporting services, you can add several fields together and use the vbNewLine reserved word.

For example:

=Fields!Address.Value+ vbNewLine
+ Fields!Address_2.Value+ vbNewLine
+ Fields!City.Value+ vbNewLine
+ Fields!Country_Name.Value