The Setup

For this example we are going to use the standard report “Customer Account Details” as a base report (renumbered so we don’t overwrite anybody’s default report). This report already has the option to show customer account details in the customer’s own currency, however it does not include the region specific currency formatting except for the number of decimal places. For kicks, and because it provides a better example, we are also going to take the dates and format those to the localized short date format. Note that this latter customization is one that might cause confusion if used in an actual client environment, especially if you have a particularly diverse set of customer regions being reported on with a single report.

It is worth mentioning that this example does not make any changes to the behavior of the classic version of this report except to add some hidden data fields for the RTC to use. To the best of my (admittedly newbie) knowledge, formatting of this type is not possible using the classic version without creating a complex function to apply the formatting prior to outputting the data to the report.

The Hoops

First Hoop – Letting your report use external assemblies

If you look at the MSDN page regarding currency formatting codes you will see that references are made to a “CultureInfo” object. Yet if you try to define such an object or make use of one per the VB examples shown, you will get an error. This is because we have to add the assembly for it. Before we can do that though, we need to edit the report properties to tell the report to allow external assemblies. Load up the report in classic, put the cursor on a blank line and hit ALT+F4 to bring up the report properties. Once you have that up, set the “EnableExternalAssemblies” option to “Yes”.

If you forget this step then later on you will see an error about ‘not a trusted assembly’ that looks a lot like this:

Second Hoop – Adding the assembly to the RTC version of the report

Once you have external assemblies enabled, the next step is to actually add that assembly to the report so that we can call upon the CultureInfo object within in.

To do this you have to load up the report designer (using the “View | Layout” menu option) and right click on the top left corner of the report in the report designer and select properties:

In the properties dialog, click on the “References” tab. Click on the ellipses to bring up the list of modules, click on the “.NET” tab and locate “sysglobl”:

Note: If you’ve not used sysglobl before, you may have to use the Browse tab the first time.  Browse to your local Microsoft.NET folder and locate a “sysglobl.dll” in the relevant Framework folder.

Once you have added the globalization assembly, it should appear in the list as shown below:

Next we have to add in some code to let us make use of the CultureInfo object.

Third Hoop – Creating functions to use the format codes with the globalization object

In a future blog post we will discuss some of the interesting ways to use the ability to add code to the report. For now though, we just want to create two custom functions that will let us format dates and currencies to a specific localization. As we did in Fig 4., above, we are going to right click the top left corner of the report and select properties. This time, instead of going to the “References” tab we are going to go to the “Code” tab.

Inside the code tab, we will define two functions:

FormatCurr (ByVal Amount as Decimal, ByVal Precision as Integer, ByVal Region as Integer, ByVal BlankNegZero as Boolean)

FormatDate (ByVal TransDate as Date, ByVal Region as Integer)

The first function will take the decimal amount to be formatted, the number of digits of precision (which we will pass in from the Currency.”Amount Decimal Places” setting), the Windows integer region code (taken from Language.”Windows Language ID”), and whether or not to return blanks for zero or negative values. For the second function we do not need to worry about rounding precision on a date so we just pass in the date in question and the Windows integer region code.

The code itself is very simple VB .NET code:

Public Function FormatCurr(ByVal Amount As Decimal, ByVal Precision as Integer, ByVal Region as Integer,

 ByVal BlankNegZero as Boolean)

   Dim TempString as String

   if (Amount <= 0 and BlankNegZero) then

     return ""

   end if

   TempString = "C" + Precision.ToString

   Return Amount.ToString(TempString, New Globalization.CultureInfo(Region, False))

End Function

Public Function FormatDate(ByVal TransDate As Date, ByVal Region as Integer)

   Return TransDate.ToString("d", New Globalization.CultureInfo(Region, False))

End Function

Now all we have to do is add some data to the classic version of the report and adjust some of the RTC fields and we will be done.

Making the customization

In the classic version of the report, we now have to add decimal precision and the Windows language ID as hidden fields. The decimal precision we can look up and pass from the Currency table (ID 4), the Windows language ID we will first have to do a lookup on the customer’s language in the Customer table (ID 18) and use that language code to make a lookup into the Language table (ID 8 ) for the Windows language ID.

Both of these lookups make the most sense to do in the Customer – OnAfterGetRecord of the classic report (the .fob of the report is included at the bottom of this post, search on “SSS ABS” in that module to find the relevant modifications).

We will add the data in hidden text boxes in the section designer in the Customer, Body (8) section.

Once we have those fields added we can then go to Visual Studio and make changes to the expression for the currency fields. It is worth mentioning that I have left most of the original function intact, such that the report will print with the existing default formatting if the “Print Amounts in Customer’s Currency” option is left unchecked. Some of the expressions are thus greatly expanded.

For example, the original expression for a debit amount is as follows:

=Code.BlankNegAndZero(Fields!AmountToPrint.Value)

And the modified version is shown below (with spacing added for readability):

=iif(NOT Fields!PrintAmountsInLocal.Value,

Code.BlankNegAndZero(Fields!AmountToPrint.Value),

Code.FormatCurr(Fields!AmountToPrint.Value,Fields!CurrPrecision.Value, Fields!LanguageID.Value,TRUE))

The last step before we are ready to roll is to look at the formatting of the table cells. While the classic version of the data is likely going to follow currency conventions, it will not include any special currency symbols that can help make it obvious what currency we are looking at. If the user selects not to show local currency we should retain the original formatting, but if they do use the culture specific formatting, then our FormatCurr and FormatDate functions will handle that instead of the Format property. For the same cell as used in the above sample for the expression of the debit amount, the “Format” field of the cell that originally reads:

=Fields!AmountToPrintFormat.Value

Will be modified to this:

=iif(Fields!PrintAmountsInLocal.Value, nothing, Fields!AmountToPrintFormat.Value)

Results

We can now look at the output and see what looks different when we show amounts in local date and currency and not in local date and currency and compare that with the base version of the report.

I am running the standard USA Cronus database filtering only on customers 01445544 (USD), 32656565 (Euro) and 42147258 (Czech koruna). The base version of the report will display as follows with the option to show amounts in customer currency:

The modified version now displays with region specific date and currency formatting:

Conclusion

If you have an international client that does want some multiple region-specific formatting on the same report or even formatting for a single region that is not the client’s installed region, this method will do the trick. The code functions are also easy enough to copy and paste into a new report, which means that once you jump through the hoops of letting the report use the sysglobl assembly, it is pretty easy to do again on another report.

Download Modified Customer Account Detail Report

For a full list of the formatting options, the MSDN site has a handy reference page for both numbers and dates/datetimes/times. I will not be covering all of the various formatting options in my example due to the fact that there are so many of them, but the most commonly used ones are represented.

Cautions

The RDLC formatting options typically will use your default localization info for formatting. If you are a USA developer working for a French client you may need to change your current locale in order to see the formatting options as your client will see them. One of the nice things about the standard formatting options vs. the custom formatting strings is that they will take care of this change for you without you having to write a lick of code and without you having to define multiple formatting strings for each locale you service.

It bears mentioning that if you need to include multiple different locale formats in a single report there are some hoops to jump through to get that to work properly. We will cover that subject in a blog post very soon. Suffice it to say that it will not work to do multiple locales with just the standard formatting options.

How To

Create your report data items in classic as you normally would. Our example report skips some of the good design steps for localization and multi-language in favor of simplicity. The entire report is just several different type data items in a single section:

Once you fire up RDLC and place your data item in the desired location you will want to look at the properties, specifically the “Format” property:

In the example above, I am formatting a decimal value to the locale default currency format (denoted by the “C”). Since I have my locale set to the USA, it will print in $#,###.00 format: Leading USD dollar sign, comma group separator, period decimal place designator and two decimal place precision. As I mentioned previously, if I send this report off to a French client I will not have to redo the report to print properly in Franc or Euro, the localized default format for currency will automatically handle that for me (Note: This does not convert the amount, only changes the format).

If, for some reason, I have to call a Code.SomeFunction to do some special processing and want to return the formatted text from that function it is worth noting that you can use these standard formatting codes in the VB.Net Format function call like so:

Public Function SomeFunction(ByVal Value As Decimal)

…processing code here…

Return Format(Value,”C”)

End Function

See the attached sample report and PDF printout of the report that shows the most commonly used codes for more information.

A Tale of Two Start Screens

Most of the visual elements of the Classic client have been the same for the past several years:

1. Toolbars
2. MenuSuite
3. Working Area
4. Status Bar

The Role Tailored Client, however, is an entirely new interface:

There’s a lot to take in:

1. The History buttons and the Address Bar (also known as the Breadcrumb Bar).
2. The Search functionality (Searching for functionality, not data)
3. The Menu Bar, including the Microsoft Dynamics NAV menu, the Actions Menu, the Reports Menu, the Customize Button, and the Help button.
4. The Navigation Pane, which is now more along the lines of a catalog of lists (and Saved Filters).
5. The Departments section, which allows you to go beyond the readily accessible parts of the system to any you need to get to (security still applies)
6. The Activities panel for a given Role – more on this shortly.
7. The My Customers / My Vendors / My Items panels
8. The Outlook panel (covered RTC Outlook Panel).
9. The Notifications panel (covered in The Power of Notes and Notifications).
10. (Unmarked) The Status Bar. Company Name and Workdate are not only displayed, but you can click on them to change company or date now.

Let’s take a closer look at the Activities panel:

1. Each Activities panel has one or more Cue Groups. In this snippet, we can see a For Release Cue Group and a Sales Orders Released Not Shipped Cue Group
2. As one might expect, within a Cue Group, you find one or more Cues. Each Cue has a visual component (the varying stack size) for an easy glance indicator, as well as a quantity indicator and a description.
3. Each Cue Group also can have related Actions. In this Cue Group, we can jump straight to making a New Sales Quote or New Sales Order.

In Part 2, we’ll talk about how Lists and Cards have changed for the better.

In Microsoft Dynamics NAV 2009 RTC Report Design, matrixes can provide a lot of easy functionality for a newbie report writer who wants to collate a lot of data without doing a lot of work. With that in mind, there are some significant drawbacks to using a matrix if your report has certain hoops to jump through that often mean that it may be easier to use a table. Note that while advanced NAV report designers can likely get around some of these obstacles by employing some of the less intuitive tricks available, this article is intended for novice users looking at whether or not a matrix control would be faster and easier to implement than the more traditional table control.

The what and how of building a matrix report

If you are not already familiar with what a matrix control is, the easiest explanation is that it is a way to bring the versatility of a spreadsheet pivot table to a report. The joy of working with pivot tables is that you can essentially take a mass dump of data and then organize it however you like and the pivot table logic takes care of the collating and summation without you having to write a lick of code. It bears mentioning that if you’re doing a onetime-only report that requires this sort of processing, you may actually be better off creating a dummy report that simply outputs all your uncollated data and then use the “About this report” feature to dump it into Excel and use an Excel PivotTable to have your way with it. Excel in that particular case is, in my opinion, far more versatile and flexible for the neophyte than trying to figure out a matrix.

For the novice, I do strongly recommend reading up on Small Square Services’ own introductory “how to” for setting up a matrix. If you are like me and you like to have points of comparison so that you can see how multiple authors approach matrixes I can also recommend Claus Lundstrøm’s tutorial on the MSDN blog.

Why use a matrix?

While the focus of this post is to show when not to use a matrix, it’s important to understand why you would want to use one in the first place. Simply put, matrixes are easy to make. You don’t have to process, sort or collate data – all you have to do is create a simple classic report layout that contains all the data you need and then put a matrix control with the proper groups on a page. For many folks that don’t favor writing a lot of processing code, this is certainly an appealing notion, especially as you can define much of the look, feel, and content through the visual designer interface. If you want fancy processing but do not want to spend a lot of time on it, a matrix can be a “dream come true” control in the Visual Studio Report Designer.

So why the blue pill?

If a matrix is so powerful, why not use one all the time? What are some of the features of a report that would cause a newbie report designer to bang their head against the nearest solid object?

Non-uniform output

One of the great things about matrixes is that they collate data and create dynamic rows and columns to display that output. When something is created at run time, it greatly limits our ability to modify how that data is shown, if some of the data is to be singled out for special treatment. You can do some very useful things with the expression based attributes of matrix cells but if you have anything in a matrix that needs to have different data (as opposed to different appearance) in certain columns, then a matrix is going to likely not be the best solution. An example would be if you want to provide monthly totals with quarterly totals and averages. Trying to add those averages into a mix of totals is possible to do, but you’ll have to employ some more advanced tricks. If that’s what you want to do, tables are probably going to be easier in the long run than trying to squeeze this square peg into a round hole. It is worth mentioning that the very snazzy looking Tablix* control will eventually remove this concern, allowing us to mix matrixes with tables with impunity.

This reason is likely the most important one of the three I list here. It is possible to spend a lot of time wrestling with the matrix control to try to get it to insert an “average” column after the main body but before the totals. Or if you have three columns per reporting item but your totals only make sense for two of those columns and you want to eliminate or hide the third.

Summation columns that are not simple totals

Matrixes provide very handy “subtotal” columns that you can add for any column or row grouping. If all you want are subtotals across a data slice, then this is just another marvelous feature of matrixes. If, however, you want totals for some of the data and averages for some of the data, then you’ve essentially got to provide code to the InScope function that will dynamically do one or the other, which likely exceeds the newbie threshold.

Your data is not repetitive or does not need to be collated

Matrixes are primarily a tool for collating repetitive data. If your data is relatively flat and/or doesn’t feature repeating categories, then a table with groups is probably going to offer more flexibility with less hassle than a matrix.

Matrix is a very powerful control, and learning how to use it will be key for great Report Design in Microsoft Dynamics NAV 2009, but always make sure to think if it’s the right powerful tool for the job.

*RDL 2005 is used in Microsoft Dynamics NAV 2009 for the RTC Report Designer. RDL 2008 has more powerful options for controls (such as the Tablix control), grouping, and various other improvements. Hopefully the next major release of Microsoft Dynamics NAV will feature the RDL 2008 improvements.

In our example today, we have a Customer who is pretty behind on their payments and we’re going to block them, note why, and ask our collections person to call them.

Depending on the size of your Customer Card, you may not be able to see the Notes FactBox, like so:

The farthest right scrollbar is for the FactBox area, so you can scroll down to find the Notes FactBox:

As the panel suggests, you can click on the Click here to create a new note, which will give you a tiny little panel to enter info into:

This note area may be small, but it can hold a massive amount of text,
so if you want, you can paste whole emails into the space.

In our example, we’ll just note that we’re blocking this customer. We don’t need to say either the date or who is writing this note. (Skip the To: and Notify options for now) When we press Save, we’ll see our new note:

It provided the author information and the date for us. It’s saved with the Customer, so it’ll be present everywhere NAV users are looking at the Customer record (Note: Not available in Classic!).

Now let’s notify our Collections agent, T. User, that they need to follow-up. Click to create a new note, enter some notice info for Ms. User to see, and then take a look under the To: option:

Other users of the NAV system will be available to select from. We’ll select TestUser, check the Notify box, and select save. Now when Ms. User next opens her Role Center, she’ll see an entry on her My Notifications panel:

By simply double-clicking on this Notification, the Customer Card for customer 30000 will open up. (This applies to Sales and Purchase documents, such as Orders or Credit Memos, as well.)

The note clearly shows who the note was from and to on which date.

When Ms. User is done resolving the issue, oh her My Notifications panel, she can right-click on the Notification and choose to remove the Notification part of the note. The note remains on the Customer Card, but it no longer will show on the Notifications list.

One other great feature mentioned above is that the Note space is very small on the screen, but it can hold a lot of information if needed. For example, Miss Doyle from John Haddock emailed us back with a written agreement for paying down the open balance. It would make sense to store that in a Note as well. So, Ms. User adds a note and pastes it into the new note.

How do we view the whole note? Simply double-click on it to see this great view:

Hopefully this article will help folks see new ways they can move information out of Inboxes (where it can be forgotten, deleted, need someone to forward, etc.) or out of binders and folders into their powerful Microsoft Dynamics NAV system.

We’ve talked before about the data structure behind reports from a technical point of view, but let’s look at the possible use.

Take this Customer and Item Statistics report:

Per Customer, it’s pretty solid information. If we send it to Excel, it’s still pretty nice:

But, it’s very formatted, which can be tricky to work with if doing calculations or filtering.

Run a report in the RTC. Under the Help button, select About This Report, like so:

You will get a message:

Do as it instructs. When you head to About This Report again, you’ll see something akin to:

That’s a giant List of all the data the report is using to produce the results. You’ll see all sorts of things in there you may not realize would be, such as Captions, Formats, Header info.

Most importantly, under the Actions menu, you can Send To -> Excel all the raw data. When you do that, Excel will instead look like:

There will be many columns you don’t need, but this way, you can build some great PivotTables to analyze the data as you need, like so:

Using some of the great PivotTable and Conditional Formatting allows me to quickly analyze what Customer/Item combinations are underperforming. I can see at a glance that we need to review Customer 30000, as it’s dragging us below our target margin.

Save As Excel brings some great power to the table (pun no intended), but being able to build off the raw data can bring your existing analysis tools to the next level.

When using Recurring Journals, there are some ways you can have the document number or description be automatically updated. Let’s look at this example:

In the Document No. field, we have the odd looking setting 2011-%3-COMM. In the Description field, we have normal looking descriptions, but with an odd %4. (To see more about the Recurring Frequency formula shown in this example, please see our post DateFormula Part 1: Closing Tricks.)

When posted, that’ll look like:

%X options in those fields are substituted with Posting Date based information. Here’s the list of options:

The only small downside to the options is that you can’t do ’01′ instead of ’1′ for the month, or padding on other numerical options. All in all, though, it’s a nice little extra touch to making your recurring entries crystal clear.

Please note this is a simple example and depending on your accounting practices, commissions may have to be handled with Accrual/Reversing Entries.

Thankfully, Microsoft gave us a way in the Shortcut to the RTC that allows us to override our default Role Center with a specific one. NAV Consultants often have folders that look like this lying about for easy use:

If your organization also makes use of the Profile Customization level, you might have twice that number of shortcuts. Obviously, making all of these can be fairly time consuming.

To streamline this process, we created a NAV Classic Form that will generate shortcuts for any or all Profiles. (Note: There are also options that let people choose their role during the RTC opening process with some customization.)

Make sure the Locations are set up top (they default to common locations in Windows 7 64 bit environments, you may need to adjust them), select which Profiles (Roles) you’d like shortcuts for, and click Generate. You can also set the shortcuts to be created in Configure mode if needed. The resulting shortcuts tend to be fairly portable, as long as they’re moving between similar environments. This tool also handles custom profiles as easily as built-in ones.

This allows you to give IT staff, department heads, or anyone the ability to access the specific roles that are needed via easy to use (and easy to create) shortcuts.

Download it here: RTC Shortcut Maker Object

You may see entries in your system like this:

Notice the peculiar Posting Date? C12/31/2009? That’s a Closing Date. NAV handles them as a special date that is after 12/31/2009 but before 1/1/2010.

Why?

Simple: By having Closing Entries be on a special date, you can run reports on a whole year, such as 1/1/2009..12/31/2009, and automatically exclude Closing Entries. This makes it very simple to re-create reports for prior periods after a Closing Process has been completed.

Technical Note

Since NAV runs (primarily) on SQL Server, and SQL Server obviously doesn’t support virtual dates snuck in between normal dates, how does that data look on the SQL side?

Easy answer again: Dates in NAV are stored as DateTime fields in SQL, but typically with a time portion of 0:00:00.000. Closing Dates are set instead to 23:59:59.999.

In our post, The Anatomy of a FastTab, we outlined how fields can be different Importance. To recap: Importance Promoted are shown in the FastTab bar when collapsed, Importance Additional are hidden behind the Show More Fields button.

In our example today, we’re going to make the Purchase Order page more tailored to our client. They have asked:

• To hide the Buy-from Contact field
• To hide the Buy-from City field
• To hide the Buy-from State / Zip field
• To show their Department and Project dimensions on the Invoicing FastTab “like the Payment Terms Code” so they can make sure those are always right
• Remove the No. of Archived Versions field entirely, even from Show More

To get started, we want to open up the Purchase Order page, and then locate the Customize This Page option:

The Customize Purchase Order window will open. Select the FastTabs option on the far-left, then press the Customize FastTab… button on the far-right (the General FastTab is selected by default, as it is first, so we’re Customizing the General FastTab):

You’ll now see the Customize General window:

You may need to resize the window a little larger to see everything.

Select the Buy-From Contact No. field. On the DropDown for Importance, select Additional:

Note: You can also click the Importance button multiple times to cycle through the options.

Repeat this demotion to Additional for Buy-From City and Buy-From State/ZIP.

As we mentioned, folks wanted the No. of Archived Versions field removed entirely, so we’ll do that here. Select the field in the list, then press the Remove button. That should move it to the left column:

Click OK (once) to head back to the Customize Purchase Order window. We’ll want to select the Invoicing FastTab and press Customize FastTab… again.

In this case, we’ll want to set the Department Code and Project Code to Importance Promoted:

Click OK (once) to head back to the Customize Purchase Order window. Click OK (once more) to head back to the Purchase Order, which will close and re-open now, with all of our changes:

Hopefully, this will help you and your team make the RTC a really great fit for the exact way you do business – removing unneeded fields makes the key ones stand out.

The best part about these changes is that they’re per-user or per-profile and will not be affected by most object changes, but that’s for another post.