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.

Wasps: Are they really Yellow?

Ashely has written a great blog posting on the Intergen blog site titled Wasps: Are they really Yellow?

This reminded me of why I wanted to start blogging. I wanted to write and I thought that blogging was a good way to practice writing. I think most bloggers find that, however motivated they once were, they run out of time and ideas and posting gets harder and harder.

The Intergen blog is a great place for Intergenites to post ideas and thoughts. If you like technology, you should subscribe. At the very least, you should take the 5 minutes or so to read Ashley's post. Who knows, you could maybe even leave a comment.

Microsoft Dynamics NAV "6.0" Video - You have got to see this!

I have just finished watching the video that I downloaded from the link on the NAV 6.0 Technical Preview page, if you don't have access to PartnerSource, you can download it from mibuso. Microsoft, I salute you! The video is stunning - never mind the whitepaper (which is just "interesting") this is amazing stuff.

Some highlights for me are:

• The interactive date picker that animates zooming in and out between month and day views.
• The Freeze columns feature for sales lines (Very Cool - geddit? Freeze Columns, Cool? - Never mind!)
• Additional fields feature - yes I know we've seen this before but I had forgotten how neat it is.
• The web service demo. Wow! This is so incredible. Microsoft have really got their act together on this. It seems to be everything we could want.
• The SharePoint integration. Wow again! This is just astounding. I am running out of superlatives. Employee Portal is a good idea but a bit of a pig to implement. This is just amazing in its simplicity, beauty and elegance.

Just when you think it can't get any better, the piece de resistance is the jaw-dropping, pant-wettingly-stunning demo of using WPF to manipulate supply and demand. OK so this is future stuff but - what a future!

Developing Solutions for Microsoft Dynamics NAV "6.0"

Interestingly it seems Microsoft are starting to get more info on the next release of NAV out into the channel. If you have access to partnersource, you can download a new whitepaper called Developing Solutions for Microsoft Dynamics NAV "6.0". If you don't have partnersource access, you should keep an eye out for a download on mibuso or dynamicsuser.net.

The document includes a description of the architectural changes in Dynamics NAV "6.0", Development Enhancements, Functionality Redesign Needs, Multiple Client Support, Step-by-step Walkthroughs for creating and modifying pages and reports.

The whitepaper is also available on a new technical preview page that includes a video of the new NAV 6.0 features.

Intergen Silverlight Twilight

I have just returned home from Chris Auld's Twilight session on Microsoft Silverlight and was so impressed by some of the demo applications, I felt the need to blog. Chris is a very entertaining presenter and is always a pleasure to watch; and whilst his moustache was definitely lacking in substance, his presentation included enough eye-candy to keep this addict satisfied.

Sadly the demo demons stopped Chris from being able to show the best of the bunch: an xbap application developed for the British Library in order to show some of their rare books. If you have Vista or Windows XP with version 3.0 of the framework, you can view this for yourself here. It's quite scary that I had never heard of xbap before tonight. Chris also showed the New York Times reader which has often been touted as the first WPF killer-app and the usual array of Silverlight based video tools.

Silverlight is cool, there is no doubt about it and the future of computing is set to be great fun. Sometimes I wish I could go back and study again - the resources that are available for our children and the technology at their disposal is quite incredible. The lucky buggers they don't know they're born. Now when I was a lad we had it tough. We used to 'ave to get up out of shoebox at twelve o'clock at night and lick road clean wit' tongue. We had two bits of cold gravel, worked twenty-four hours a day at mill for sixpence every four years, and when we got home our Dad would slice us in two wit' bread knife.

If you want to read the rest of that sketch you can find the transcript of the Four Yorkshiremen here.

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