Where can I learn more about NAV analysis reports and analysis by dimensions?

Analysis reports and analysis by dimensions are the native reporting options that extend NAV reporting to the item ledger entries generated from the sales, purchasing, and inventory areas of the application. I find that many users don’t know that this reporting option exists and think it is one of the more underutilized areas by NAV financial users.

These reporting options can be found on the general ledger, sales & marketing, purchasing, and inventory menus.

The main advantage this reporting tool has over account schedules is that it reaches a further level of detail that just isn’t recorded on the general ledger. Being able to get to quantity information at the item or location level in addition to the dollar values posted makes these tools great as a way to get operational reporting.

What is the difference between the two?

Analysis by dimensions is a query tool. The key to using this tool effectively is to use aggressive filtering, especially if you have a large number of items. It can be a very quick way to get information about what may be going on with a single or small group of items. You can also export to Excel and it shows up in a pivot table.

Analysis Reports allow you to configure and save row setups and column layouts for later use, which makes it a reporting tool. These reports work a lot like account schedules, with some added features to accommodate the additional data you can reach because you’re reporting against the item ledger entry tables instead of the general ledger.

A few hints on analysis reports and analysis by dimensions:

  • Skip the analysis report on the general ledger menu and use accounts schedules; you’re not going to get any added value here.
  • When posting sales or purchase orders, you must be fully utilizing the sub module, i.e. do not allow any posting of lines to general ledger accounts on the sales or purchase order documents. Any posting that goes “around” these modules will cause your analysis report to show a lower number than what is on your general ledger. Avoid the argument of reporting credibility by understanding this, and either being able to explain the variance, or prevent it from happening altogether.
  • Know that analysis reports will not automatically update if you add new items. You’ll need to go in and do this manually to each report every time you add new items.

For those of you who are looking for more resources on Analysis by Dimension and Analysis Views, reference the documents below, found on Customer Source:

Documentation => User Guides => Overview of Training Manuals and Hands-On Labs for Microsoft Dynamics NAV 2009

  • Trade in Microsoft Dynamics NAV 2009 – Chapter 9
  • Business Intelligence for Information Workers in Microsoft Dynamics NAV 2009 – Chapter 5

Documentation => User Guides => Overview of Training Manuals for Microsoft Dynamics NAV 5.0

  • Trade in Microsoft Dynamics NAV 5.0 – Chapter 10
  • Business Intelligence for Information Workers in Microsoft Dynamics NAV 5.0 – Chapter 6

This posting is one of the Top 20 Most Viewed in the last year! Follow this link to see the entire list.


Account Schedules class being offered on Wednesday, September 16th

I’m offering a class on Account Schedules next week through NAVUG Academy! This class is a great way to get familiar with account schedules if you’ve never used them before, or if you just need a refresher because it’s been a while since you’ve set up a new one. It’s also a great way to fit in those CPE credits!

I’ve got more classes offered this year on a wide variety of topics. Check out my 2015 classes page for more details on how you can learn more about financial setups, posting groups and dimensions, greater optimization of finance features, and month end, year end, and budgeting.

academy

Financial Reporting Using Account Schedules in NAV2015

Instructor-led training on-line!

Microsoft Dynamics NAV account schedules are a powerful tool used to create custom financial statements directly from your general ledger. In this in-depth training session, you will learn the fundamental principles of account schedule construction.

Using hands-on exercises, you will have the opportunity to apply what you learn during class to a basic trial balance and master the three basic account schedule building blocks: row setups, column layouts, and analysis views. You’ll discover how to filter your financial reporting by dimensions, layer in budgeted information, and produce reports by exporting to Excel!

Preparation: The detailed instructions for connecting and attending the class will be sent one to two days prior to class. Students have the option of connecting to the class via conference call or VOIP. If using VOIP, a headset with a microphone is strongly recommended. If your instructor will use the hands-on training environment you will receive a separate email with the setup instructions, and a dual monitor is strongly recommended to facilitate navigation.

Delivery Method: Group Internet-based

CPE Credits: By participating in this class you are eligible to earn up to 4 CPE credits which will be distributed automatically following class completion. Class participation will be monitored and you will not receive CPE credit for any portion of the class that you do not appear fully engaged.

You can see all the classes NAVUG Academy has to offer, including this one, by following this link.


Account Schedules class being offered May 13th

I’m offering a class on Account Schedules next week through NAVUG Academy! This class is a great way to get familiar with account schedules if you’ve never used them before, or if you just need a refresher because it’s been a while since you’ve set up a new one. It’s also a great way to fit in those CPE credits!

I’ve got five more classes I’ll be offering this year as well on a wide variety of topics. Check out my 2015 classes page for more details on how you can learn more about financial setups, posting groups and dimensions, greater optimization of finance features, and month end, year end, and budgeting.

academy

Financial Reporting Using Account Schedules in NAV2015

Instructor-led training on-line!

Microsoft Dynamics NAV account schedules are a powerful tool used to create custom financial statements directly from your general ledger. In this in-depth training session, you will learn the fundamental principles of account schedule construction.

Using hands-on exercises, you will have the opportunity to apply what you learn during class to a basic trial balance and master the three basic account schedule building blocks: row setups, column layouts, and analysis views. You’ll discover how to filter your financial reporting by dimensions, layer in budgeted information, and produce reports by exporting to Excel!

Preparation: The detailed instructions for connecting and attending the class will be sent one to two days prior to class. Students have the option of connecting to the class via conference call or VOIP. If using VOIP, a headset with a microphone is strongly recommended. If your instructor will use the hands-on training environment you will receive a separate email with the setup instructions, and a dual monitor is strongly recommended to facilitate navigation.

Delivery Method: Group Internet-based

CPE Credits: By participating in this class you are eligible to earn up to 4 CPE credits which will be distributed automatically following class completion. Class participation will be monitored and you will not receive CPE credit for any portion of the class that you do not appear fully engaged.

You can see all the classes NAVUG Academy has to offer, including this one, by following this link.


NAV AS 101 Lesson 22: Dealing with Zeros

3d man cleaning with broomEvery numbers person needs to deal with zeros, and the most frequent thing we all do with zeros is to get them out of the way. NAV gives us very few ways to deal with this, but frequently the best way to get rid of zeros in account schedules is to do that task in Excel. Here are a few tips and tricks to help.

If you print account schedules directly out of NAV, you’ve got some great options. You can check the option found under Show in the row setup for If any column not zero. If this option is checked, the row will be suppressed if all columns have a zero value. Just like the other options on the show menu, this only affects printed account schedules and makes no change directly on-screen in NAV, either in the design view or in the overview.

There is an option in the column layout under Show where you can choose Never, but this isn’t directly applicable to the behavior of zeros. It is mighty handy if you want to hide a column whose only function is to perform a calculation that you don’t want shown.

If you are viewing account schedules on-screen, there is no way to hide zero values. If you’re looking in the design view or the overview, you will see rows that contain zero or no values and you can’t hide them.

If you are exporting account schedules to Excel, you’ve got some other options. These take a little setup, but will get the job done. If you want to consistently filter out zero values from your account schedules using Excel, here’s one way to do it: use filtering! I’ve got a report that I produce that has 50 rows on it. I regularly filter this report by dimensions, and any tab that is generated with a dimension can have the majority of lines with zero values, but it is not the same for each dimension. Each month, here is what I do:

1) Copy the prior month’s report and save as the current month’s report.

2) Remove all Excel filtering from each tab of the new report.

3) Export all the tabs I need from Account Schedules in order to update the numbers.

4) Go back into the new report that now has new numbers and apply an Excel filter to a column using the option to Select All and then uncheck the blanks or zero value option.

You’ll need to think through how you design your report in order to build in a column that would correctly indicate that a row with all zeros exists. One way is to insert a column that calculates the sum of all the other values and then format the numbers to show up as white text n Excel. That way you can then filter on those values when a zero or a blank occurs for all columns in a row in order to show only the the rows that have values in them.

This posting is part of the NAV Account Schedules 101 series.  Find the entire list of lessons here.

Don’t forget to visit the Account Schedule Formulas and Account Schedule Examples pages if you’re looking for even more ideas on how to improve your financial reporting using account schedules with Microsoft Dynamics NAV.


Week six recap: Account Schedules 101

repeatHere’s the week 6 recap on the NAV Account Schedules 101 series.  Follow the links below to view the posts for each of the lessons we covered this week or view the entire series here.

NAV AS 101 Lesson 13: Printing Account Schedules

NAV AS 101 Lesson 14: Exporting to Excel

 Don’t forget to visit the Account Schedule Formulas and Account Schedule Examples pages if you’re looking for even more ideas on how to improve your financial reporting using account schedules with Microsoft Dynamics NAV.


NAV AS 101 Lesson 13: Printing Account Schedules

printerYou’ve got a few options to get account schedules out of NAV. You can print them, export them to Microsoft Excel, or you can even simply copy and paste the results out of NAV and into Excel.

I generally recommend companies choose to use the export to Microsoft Excel function because it allows maximum efficiency with maximum flexibility to produce their financial statements.  We’ll go over that in the next post in this series.

If you just need a quick dump to Excel so you can play with the numbers a bit, you can simply select all the rows you have on-screen, right-click and copy, and then right-click and paste into Excel. Be warned that you can only copy what’s actually showing on the screen, so if everything is not showing, you’ve got to rearrange your screen a bit to make this work well.

In addition, you can choose to produce your financial reports using XBRL, but I’m not going to consider that an end-user friendly function for the purposes of this post. See your partner to help you get set up if your company wants to do that.

If you must print, here are some tips to help you make those reports look as good as possible. You can print from two different places in NAV. Things are a little bit different between NAV Classic versions and NAV RTC/2013 versions, so I’ll illustrate them all.

[Warning: some of the images in this post get mighty tiny. Click on any picture to bring it up in it’s very own window to save your eyesight. If you’re reading this one on your phone, you’re out of luck, sorry.]

NAV Classic users

If you elect to print from the account schedules overview screen, choose the function button on the bottom right of the screen and choose Print. This brings up the following configuration screen where you can set a few parameters, and then choose Print.

para

The result you’ll get will look like the screen shot below. There are a few things to note about the result. The information at the top of the page comes from a number of sources. Some of the fields you can control directly, like the row setup and column layout names. In this example, I would want to remove the words “hands on exercise” if I was going to print my schedules. Some of the other fields are coded into the report, so if you want them changed, you’ll need to go into the Object Designer or have your partner change that for you.

The last thing is that the default print setup only allows seven columns, including the description. In this example, I’ve got eight, so my report cuts off the difference column for my year to date numbers. In order to fix that, I can either reduce the number of columns I have, maybe by removing the spacer in the middle, or I can print from a different area.

bet

In Classic, go to Financial Management=>General Ledger=>Reports=>Financial Statement=>Account Schedule, Landscape. You’ll see now the report is oriented landscape instead of portrait, so now we can see the difference column that was missing.

p3

This comes in pretty handy when you’ve got a trended month report to print, which allows 14 columns, including the description, on your report.

p4

NAV Role Tailored Client (RTC) users

The RTC has made a few changes to printing account schedules, but not many. The most notable is printing from the overview looks significantly different. I would consider this as unusable as a financial statement. When you actually print it from the reports menu, it looks exactly the same as the Classic version, shown above, and you still need to choose between the portrait and landscape versions of account schedules.

o1

NAV 2013 users

In NAV2013, we’ve got one huge step forward and a couple small steps back with printing. The huge step forward is that we no longer have to choose between portrait and landscape and, we get the same report when printing whether we print from the overview or from the reports menu. We’ve lost the ability to do some small things, like putting brackets around negative numbers, and now the row numbers show up on the report, which I’m not a thrilled about.

r8r9

There’s one hidden bonus feature in 2013. If you click a little box in the setup labeled “show account schedule setup”, you get a report that shows your design view right next to your end-user view, which makes it super easy to compare your design and results. We’ve been able to see the layout previously by printing the account schedules layout report, but it’s nice that it’s been added as an option when printing the schedules, all in the same place.

f10

This posting is part of the NAV Account Schedules 101 series.  Find the entire list of lessons here.

Don’t forget to visit the Account Schedule Formulas and Account Schedule Examples pages if you’re looking for even more ideas on how to improve your financial reporting using account schedules with Microsoft Dynamics NAV.


NAV AS 101 Lesson 8: Formatting

Formatting options for Microsoft Dynamics NAV are pretty basic, but there are a few tricks you should know to get the most out of what you have. Because very little has changed with formatting options in newer versions I’ll show some basics in NAV 2009 Classic, but highlight some expanded features in NAV2009 RTC and NAV2013 so you know the full range of what you can use.

This is an example showing the row setup, screen view, Excel export, and printed views with no formatting of any type assigned. It’s pretty ugly no matter which way you look at it.

no format

Let’s make this look better by applying some basic formatting options. We’ll bold the total line, underline the line above the total, italicize the second line, and choose show opposite sign on all the revenue lines, including the total line, so our sales are reflected as a positive number instead of as a credit balance. Looking better already, but there are a few things going on that need to be explained.

Underline – Can’t see it on the screen view, appears across the entire page including row descriptions in Excel export, and shows just under the numbers in the printed view.

Italic – Can’t see it on the screen view, but can see it on both the Excel export and printed view.

2form

We can improve on what’s going on with the underline with a few additional options. There is an option in the totaling type column for underline, and after NAV2009 SP1, there is an option for double underline. The screen shots below show the following differences. Notice they don’t make much of a difference at all on the screen view or Excel export versions, and have the largest impact on the printed version. NAV2013 has a new additional checkbox for double underline, but it still behaves the same way, only showing up on the printed version.

B – Just the underline checkbox is checked. If you want the underline situated tightly to your numbers without extra space in the printed view, this is the way to do it.

C – The underline checkbox is checked and the totaling type of underline is selected. This gives a wide double underline in the printed view only.

D – The totaling type of underline is selected. If you want a little bit of space below your numbers before the underline appears in the printed view, this is the way to do it.

E – The totaling type of double underline is selected.

u1u2u3u4

Similar to what we see with the different underline options, the show option also has limited utility. Let’s choose No instead of Yes on the second line of our example and see what we get. As you can see, the show option only hides a line in the printed view. There is actually a show option that is also available in the column layout, but again, it only hides a column in the printed view.

show

There is one additional formatting option that only exists in the column layout – the rounding factor. Selecting this option, just like show, only allows the changes to go through to the printed version, but if you are using that option you can round to a factor of 1, 1000 or 1000000.

colform1colform2

One last option here that I haven’t put together an example for is the New Page option. Use this option if you need to insert a page break anywhere in your report. I’ve seen lots of companies use this to produce a two page balance sheet. I’ve also seen companies build some pretty large multi-page account schedules in one named report and break each page by using this option. I think it is easier to maintain and update short simple reports if at all possible, so I recommend creating a few (or many!) separate account schedules instead of doing this. It’s just too easy to miss something, or at least spend a lot of time looking for, something you need to update in a large multi-page report kept in a single account schedule.

This posting is part of the NAV Account Schedules 101 series.  Find the entire list of lessons here.

Don’t forget to visit the Account Schedule Formulas and Account Schedule Examples pages if you’re looking for even more ideas on how to improve your financial reporting using account schedules with Microsoft Dynamics NAV.