NAV AS 101 Lesson 14: Exporting to Excel

Using the Export to Excel function is the single most important time-saver when using NAV account schedules because once you create your first Excel workbook and set all the formatting you’ve created a “shell” that never needs to be set up or changed again. Each month, when you produce your reports for the next fiscal cycle, all you need to do is update that month’s numbers. Not only does this save you a huge amount of time, but your reports also look consistently the same month after month.

This post has video demonstrations showing three different ways to use the export to Excel function. The videos are all done using NAV 2009 R2 Classic, but the functions work exactly the same in all versions of NAV. The only thing that is different is how you get to the Export to Excel function in each version of the software.

NAV 2009 Classic – Choose Functions button at bottom right then choose Export to Excel

NAV2009 RTC – Choose Actions button at top left then choose Functions then choose Export to Excel

NAV2013 – On the Ribbon choose Export to Excel

Produce the same report for three different months

Produce the same report for three different dimensions

Produce financial statement package with three different reports, for two different months

Correction: Oh no! In NAV2013, we’ve lost the ability to update the workbook and still retain formatting. See my post on Account Schedule bugs here for more details on the new recommended best practice on how to deal with this change. I’ll get a new video posted out here with a demo of how to do this later this year.

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 21: Account Schedule Bugs

3d man with fly swatterJust like any piece of software, there are some bugs in NAV account schedules, and the best way to deal with them is to know they exist. I’ll let the Microsoft folks debate whether these are a bug or not. To me, as an end-user, anything that removes a necessary feature or keeps a necessary feature from operating reliably without replacing it with something better is a bug.

Refresh Error in NAV Classic Account Schedules This bug causes the account schedule to come up blank when changing to a different account schedule. It can sometimes be partially remedied by scrolling up and down or left and right, but this sometimes only brings back some of the expected numbers. This is a refresh error that has existed for many of the Classic NAV versions. I’m really not sure how long it’s been there – I started using NAV with version 3.5 and I’ve seen this bug in that version as well as in 4.0, 5.0, and 2009 Classic. The best way to get past this one is to exit out of your account schedule and open it back up again. With the introduction of the matrix to account schedules in 2009 RTC versions, this bug (finally) went away.

Copy and Paste in 2009 RTC While the introduction of the matrix brought us some very nice things, it brought us one nasty disappointing bug – in NAV 2009 RTC versions, copy and paste cannot be used in account schedules. This has by far been the worst bug I’ve seen in NAV account schedules, and really is a huge loss of efficiency to users who have that version of NAV. I rarely get worked up about this kind of thing, but I did put a post out there explaining why this was such a big loss. There is no way to get past this bug other than to upgrade to the next version of NAV, NAV2013, where thankfully, copy and paste are returned to us.

Buttons that don’t work and filters that work but are super clunky in 2009 RTC There were a few other bugs in 2009 RTC that made this version of NAV the worst version ever for account schedule users. On the account schedules overview page, there were some mysterious buttons labeled as Next Set and Previous Set that don’t do anything. In addition, the filtering is terrible. Date filters always pick up the work date, don’t retain the date you were just using if you leave the screen and go back, and if you want to change the dates, you’ve got to type the entire date range, every time. There are no quick navigational buttons to move from one period to another. In addition, dimension filtering is difficult to get to and difficult to populate with the names of the dimensions you are using, in particular, if you use multiple analysis views. Luckily, every single of these bugs was fixed with the release of NAV2013.

To date, NAV2013 is by far the best version of NAV Account Schedules. It seems like Microsoft really got it right with a large number of capabilities related to the improved matrix, filtering, and navigation with account schedules, making it streamlined, intuitive and easy. Alas, NAV2013 is not perfect, and there are two bugs I’ve found. Unlike other versions, hopefully we won’t have to wait for a next release to see these ones fixed.

Refresh error in NAV2013 This one is a brand new and different type of refresh error than the one we saw in NAV Classic versions. When switching from an account schedule with many columns (let’s say a 12 month trended view) to an account schedule with fewer columns (let’s say an actual versus budget with variance), you’ll end up with a bunch of left over data from the 12 month view in the columns that are no longer a part of the actual versus budget view. There’s a quick and easy fix, which is to hit F5 or the brand spanking new Refresh button, and those pesky numbers will disappear. However, you’ve got to be on the watch for them and know what you’re looking at – a risk that’s not so great. As a NAV2013 user you’ll get used to using the F5/Refresh button frequently, so not a big deal, but I still don’t like any financial report showing up with numbers it shouldn’t.

Update workbook function no longer retains formatting in Excel when exporting account schedules with NAV2013 This one is a bigger deal and again, is a pretty big loss of efficiency for NAV account schedule users. Anyone who uses account schedules a lot should be using the export to Excel function all the time in order to product account schedules quickly and with consistent formatting. NAV2013 users can no longer do this and retain existing formatting. The update to Excel function still works in this version, but instead of just updating numbers in a formatted Excel worksheet, it dumps unformatted updated numbers into that worksheet, overriding the formatting you might have there. The recommended best practice is to continue to use the update worksheet function, but change one aspect of design of your Excel workbook. What you now need to do when you design your Excel workbook the first time are the following steps:

1) Use create workbook for any schedule to get an unformatted starting copy (Copy 1) of your schedule in Excel.

2) Copy this worksheet to another worksheet (Copy 2) and format it exactly like you’d like to see it.

3) Delete all the numbers out of Copy 2 and put in a link to Copy 1 that will pull all the numbers into Copy 2.

4) Hide the worksheet tab for Copy 1 so you only see the nicely formatted Copy 2.

5) When you update your Excel workbook the next time, only update the numbers to Copy 1. You will never update numbers to Copy 2. The links in Copy 2 will always pull forward from Copy 1.

I can’t say I’m especially looking forward to redesigning my 18 page financial statement package this way, but I’ll do it because now I have to. The one nice thing about this is I can get a little fancier with some of my formatting and won’t need to continue to tinker with some of the small things I do, like taking underlines out of column A and readjusting the width of column B, things I need to do universally in NAV2009 Classic because while the update workbook function keeps most of the formatting, it does override a couple of pesky things. So ultimately, this method will be faster on a monthly basis, but will take more design time and Excel comfort level, up front.

This might be a better solution for account schedules produced with many separate tabs for filtered dimensions. Imagine an Excel workbook with 20 tabs showing the same report, one for each dimension. Because we must use this new method, one way to make it more efficient will be to design a single report with the needed dimensions in each column. This report will go to the one hidden worksheet in the workbook and the 20 user facing tabs will be beautifully formatted reports that link only to the applicable column from a single report. So, for that example, this actually turns into a time saver since you don’t need to export/update each filtered dimension to a new tabbed worksheet.

One concern is that the linked formulas could become unlinked/deleted/moved, but this could be safeguarded by locking cells on the formatted worksheet to keep them from being accidentally changed. Another concern is that your account schedule could change and then the linked copy won’t show the error that is not being picked up. There are a few more things to watch for with this change in place, but there are some positives as well as negatives that go along with this bug.

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 7: Column Layout

The column layout is where you will define periods of time to be used in your reports. Most financial reports are showing us some period of time compared to the data (in this case, general ledger accounts) we are reporting against. You can apply any column layout you design against any of the row setups you design. As an example, when I produce my income statement, I will have one row setup that reflects a summarized income statement and at least four different column layouts that reflect different configurations of month to date, year to date, comparisons against budget, and twelve month trended views.

Just like with row setups, there are many available options in the column layouts. This large variety of options can sometimes be overwhelming to the new account schedule user. I’ll show you which columns to choose in the column layouts for account schedules as a beginning point, and go through some simple explanations of how they are used.

Fields for beginning users of account schedules:

Column No. – The column number is completely optional, but highly recommended.  This simple element of the column layout will eventually be one of the key features of your account schedule, allowing you to calculate and organize with ease.

Column Header – This is where you’ll define, in words, what you’re showing in each column of your report.  Keep it short; there is a 30 character limit.

Column Type: Net Change, Balance at Date, or Formula – Limit yourself to these three options when you’re just getting started. The key here is knowing what type of accounts you’re reporting on. If you’re using income statement accounts (Revenue/Expense), then you need to use net change. If you’re using balance sheet accounts (Assets/Liabilities), then you need to use Balance at Date. Formula allows you to perform calculations in a column.

Ledger Entry Type – This column will allow you to define what type of ledger entries you will show. This is where you can choose actual general ledger entries or budgeted general ledger entries.

Formula – If you’ve chosen Formula as the Column Type, this is where you’ll put the formula.

Comparison Period Formula – This column allows you to define date formulas that are used to calculate the amounts shown.  I generally recommend that beginning account schedule users start out using the comparison period formula field instead of the comparison date formula field.  The comparison period formula field references the accounting periods set up in the fiscal year, so this option seems the most consistent, and is especially necessary for companies that may not follow a calendar fiscal year.  Common data labels used in this field are CP for current period or -1P for previous period and FY for fiscal year or -1FY for prior year.

Shown below are both the design view and the user view of a column layout for a summarized income statement showing year to date and prior year to date information in the columns to demonstrate the use of these six basic options in an account schedule.

Role Tailored Client / 2013                                             

Classic Client                                                                          

Fields for experienced users of account schedules:

If you’re looking for what else you can do with Microsoft Dynamics NAV column layouts, then this is where you want to be. Keep reading to understand each and every field available for account schedule column layouts. The functionality of these fields are exactly the same between the Classic and Role Tailored Clients unless specifically noted. The only difference is in how they look on the screen and how you navigate between screens. Take a look at the pictures below to find the look you’re familiar with, but then keep reading for the explanations of what these fields will do in your financial reporting.

Role Tailored Client / 2013                                             

This is a view of what I recommend you start with for basic column layout options.

1

This is a view of all available column layout options (click on the picture for a larger view).

rtc

 

Classic Client _________                                                   

This is a view of what I recommend you start with for basic column layout options.

2

 

 

 

This is a view of all available column layout options (click on the picture for a larger view).

classic

 

 

Column layout options – apply to both clients

Column No. – The column number is completely optional, but highly recommended.  This simple element of the column layout will eventually be one of the key features of your account schedule, allowing you to calculate and organize with ease.

Column Header – This is where you’ll define, in words, what you’re showing in each column of your report.  Keep it short; there is a 30 character limit.

Column Type: 7 options to choose from

  • Formula allows you to perform calculations in a column.
  • Net Change should be used, generally, if you’re reporting on income statement accounts (revenue/expense).
  • Balance at Date should be used, generally, if you’re reporting on balance sheet accounts (assets/liabilities).
  • Beginning Balance will be sparingly used, but you’ll want this for your statement of cash flows at some point.
  • Year to Date should be used to show a total in the column of the balance from the start of the year through the current filtered date.
  • Rest of Fiscal Year can be used to show, for budgeted information, what is planned for the remainder of the fiscal year.
  • Entire Fiscal Year can be used to show, for budgeted information, what has been planned for the entire fiscal year.

Ledger Entry Type – This column will allow you to define what type of ledger entries you will show. This is where you can choose actual general ledger entries or budgeted general ledger entries.

Formula – If you’ve chosen Formula as the Column Type, this is where you’ll put the formula.

Comparison Period Formula – This column allows you to define date formulas that are used to calculate the amounts shown.  I generally recommend that beginning account schedule users start out using the comparison period formula field instead of the comparison date formula field.  The comparison period formula field references the accounting periods set up in the fiscal year, so this option seems the most consistent, and is especially necessary for companies that may not follow a calendar fiscal year. Common data labels used in this field are CP for current period or -1P for previous period and FY for fiscal year or -1FY for prior year.

Comparison Date Formula – This column allows you to define date formulas that are used to calculate the amounts shown.  However, the comparison date formula field references the calendar months of the year instead of the accounting periods set up in the fiscal year. Common data labels used in this field are CM for current month or -1M for previous period and CY for calendar year and -1Y for prior calendar year.

Amount Type gives you three options:  Net Amount, Debit Amount and Credit Amount. You’re going to use Net Amount almost all the time, but I suppose if you were getting very granular in your account schedules and perhaps using them for detailed reconciling reports, this might be useful. If I’m getting down to this level of transactional detail, I’m usually going to the chart of accounts instead of account schedules.

Business Unit Totaling acts like a bonus dimension when producing consolidated financial statements from more than one company, designating a unique business unit per company.

Cost Center Totaling and Cost Object Totaling *New with NAV2013* These are new functions related to totaling of new cost center and cost objects.

Line No. is automatically designated by the software.

Rounding can be set for none, 1, 1000, or 1000000. Just like the show function, this only affects numbers shows on the printed account schedule. You’ll still see numbers rounded to two decimals on-screen or when you export to Excel.

Show allows you to designate if a row will show (the default) or be hidden on the printed account schedule only. You can click this box as much as you want, but your rows will still show on-screen or when you export to Excel. If you print directly out of account schedules, this feature will work very well for you.

Show Opposite Sign is the option you can select to change revenue credit balances or expense debit balances to the opposite sign for the purposes of presenting this data in a more non-finance friendly manner. No salesperson wants to see sales presented with a negative sign; use this to change that credit to a positive number on your reporting.

Dimension Totaling is the last option available and you might have noticed that I didn’t show them on pictures above. This is because they are unique for just about every user, and depending on your analysis view setup, may even be different from schedule to schedule. You can use just one, or you can use all four of the available dimension totaling fields, in whatever combination you like. These fields act very much like the Totaling field because this is where you will tell NAV what Dimensions you want shown and totaled from your general ledger accounts.

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 6: Row Setup

The row setup is the area of the report where you’ll show either single general ledger accounts or groups of general ledger accounts. If you imagine a trial balance, the rows of a trial balance correspond to single general ledger accounts. The row setup of an income statement would be an example of where you might use groups of general ledger accounts.

The perpetual problem of the new NAV user is when you get started in a new area of the application, there are way too many options to choose from. How many times have you opened up a new menu option, only to find twenty or more columns displayed as the default, and many more lurking behind the show columns menu? While we appreciate having all options for all people at some point, new users can find this especially daunting when trying to just get started with something new.

I’ll show you which columns to choose in the row setup for account schedules as a beginning point, and go through some simple explanations of how they are used.

Fields for beginning users of account schedules:

Row No. – The row number is completely optional, but highly recommended. This simple element of the row setup will eventually be one of the key features of your account schedule, allowing you to calculate and organize with ease.

Description – This is the one place you have to communicate, in words, what you’re showing on each line of your report.

Totaling Type:  Posting Accounts or Formula – This field tells NAV what you’re going to do here. You’re either going to pull data from your general ledger posting accounts or calculate a formula.

Totaling – Which general ledger accounts you want to pull or what formula you want to calculate.

Row Type:  Net Change or Balance at Date – The key here is knowing what type of accounts you’re reporting on. If you’re using income statement accounts (Revenue/Expense), then you need to use net change. If you’re using balance sheet accounts (assets/liabilities), then you need to use Balance at Date.

Shown below are both the design view and the user view of a summarized income statement, showing the use of these five options.

Role Tailored Client / 2013                                               

Classic Client                                                                          

Fields for experienced users of account schedules:

If you’re looking for what else you can do with Microsoft Dynamics NAV row setups, then this is where you want to be. Keep reading to understand each and every field available for account schedule row setups. The functionality of these fields are exactly the same between the Classic and Role Tailored Clients unless specifically noted. The only difference is in how they look on the screen and how you navigate between screens. Take a look at the pictures below to find the look you’re familiar with, but then keep reading for the explanations of what these fields will do in your financial reporting.

Role Tailored Client / 2013                                               

This is a view of what I recommend you start with for basic row setup options.

This is a view of all available row setup options.

Classic Client                                                                          

This is a view of what I recommend you start with for basic row setup options.

This is a view of all available row setup options.

Row setup options – apply to both clients                     

Row No. is completely optional, but highly recommended. This simple element of the row setup will eventually be one of the key features of your account schedule, allowing you to calculate and organize with ease.

Description is the one place you have to communicate, in words, what you’re showing on each line of your report.

Totaling Type:  10 options to choose from

  • Posting Accounts pull data from your accounts designated as posting accounts on the G/L Account Card Account Type field.
  • Total Accounts pull data from your accounts designated as a total or as an end-total on the G/L Account Card Account Type field.
  • Formula allows you to calculate a basic formula on this row.
  • Underline places a single underline.
  • Double Underline places a double underline.
    •  NOTE:  This nifty addition showed up with NAV 2009 R2. If you are NAV 2009 SP1 or earlier, this won’t be available to you.
    • Both of the underline functions are there to give folks who print account schedules directly out of NAV some formatting options. If you’re exporting to Excel you’ve got some better format options to work with than these.
  • Set Base for Percent allows you to designate which row from your row setup will be used as the beginning point for a section to be included in a percent of total calculation. This feature showed up in version 5.0 and must be used together with a formula on another row to designate the end point of the section as well as some setup in the column layout. It’s a little fussy in the setup and inelegant in execution, but is still effective to get those vital comparative percentages onto your account schedules.
  • Cost Type and Cost Type Total *New with NAV2013* These are new functions related to the posting accounts and total and end-total accounts from the chart of cost accounts.
  • Cash Flow Entry Accounts and Cash Flow Total Accounts *New with NAV2013* These are new functions related to the posting accounts and total and end-total accounts from the chart of cash flow accounts.

Totaling will be which general ledger accounts you want to pull or what formula you want to calculate, depending on what you selected in totaling type.

Row Type:  3 options to choose from

  • Net Change should be used, generally, if you’re reporting on income statement accounts (revenue/expense).
  • Balance at Date should be used, generally, if you’re reporting on balance sheet accounts (assets/liabilities).
  • Beginning Balance will be sparingly used, but you’ll want this for your statement of cash flows at some point.

Amount Type gives you three options:  Net Amount, Debit Amount and Credit Amount. You’re going to use Net Amount almost all the time, but I suppose if you were getting very granular in your account schedules and perhaps using them for detailed reconciling reports, this might be useful. If I’m getting down to this level of transactional detail, I’m usually going to the chart of accounts instead of account schedules.

Formatting Fields:  Bold, Italic, and Underline  These basic formatting fields are there to allow you to emphasize some of the rows of your account schedules. When you select these options on any row, the whole row is effected. You are not able to effect a single “cell” for formatting. What’s the difference between underline here and underline found in the Totaling Type options? You can get a more snug fit on your underline using this option since it appears in the same row of your data instead of in a row if its own.

New Page inserts a page break. This is commonly used when separating the pages of the balance sheet showing assets on the first page and liabilities and equity on the second page. I have also seen it used when folks build multi page account schedules.

Show Opposite Sign is the option you can select to change revenue credit balances or expense debit balances to the opposite sign for the purposes of presenting this data in a more non-finance friendly manner. No salesperson wants to see sales presented with a negative sign; use this to change that credit to a positive number on your reporting.

Show allows you to designate if a row will show (the default) or be hidden on the printed account schedule only. You can click this box as much as you want, but your rows will still show on-screen or when you export to Excel. If you print directly out of account schedules, this feature will work very well for you.

Dimension Totaling is the last option available and you might have noticed that I didn’t show them on pictures above. This is because they are unique for just about every user, and depending on your analysis view setup, may even be different from schedule to schedule. You can use just one, or you can use all four of the available dimension totaling fields, in whatever combination you like. These fields act very much like the Totaling field because this is where you will tell NAV what Dimensions you want shown and totaled from your general ledger accounts.

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 4: Basic Elements of Account Schedules

123There are three foundational concepts you must understand in order to use account schedules to their full potential.

1.  Row setup

The row setup is the area of the report where you’ll show either single general ledger accounts or groups of general ledger accounts. If you imagine a trial balance, the rows of a trial balance correspond to single general ledger accounts. The row setup of an income statement would be an example of where you might use groups of general ledger accounts.

2.  Column layout

The column layout is where you will define periods of time to be used in your reports. Most financial reports are showing us some period of time compared to the data (in this case, general ledger accounts) we are reporting against. Common column layouts may be current month versus year to date, or a twelve month net change (income statement accounts) or twelve month balance (balance sheet accounts). You can apply any column layout you design against any of the row setups you design. As a result, most NAV account schedule users end up with many more column layouts designed than row setups.

3.  Analysis views

Analysis views are the element of account schedules that lend flexibility and power to your account schedules by accessing your dimensions. Account schedules can always use the two global dimensions you have set up even if you aren’t using analysis views. Once you start using analysis views, you can get to the shortcut dimensions you have defined. When using analysis views, you can access many combinations of any of your dimensions, but you are limited to only four at a time. By setting up multiple analysis views in different groupings, you can set up almost any combination you can think of to produce your financial reports using any of your defined dimensions.

There are all kinds of ways to expand on these three foundational concepts, but let’s start with these simple concepts first before we talk more about additional things you can do with row setups, column layouts, and analysis views.

In addition, you need to understand three other concepts in order to use account schedules in the most efficient way possible.  Mastering these three concepts will allow you to build and utilize account schedules quickly and easily.

4. Printing or exporting to Microsoft Excel

You have the option of either printing account schedules directly out of NAV or exporting them to Microsoft Excel. I recommend learning how to use the export to Excel update option so you can easily and quickly export your account schedules every month by using a pre-formatted Excel “shell”. The shell only needs to be created the very first time. Then, each time you update your reports, you only need to update the numbers – there is no need to update the formatting each time.

5. Formulas

There are two types of formulas used in account schedules. The first type are basic mathematical formulas, generally based on row number references, that allow you to do simple calculations against your data. These are used in both the row setup and the column layout.

The second type are formulas which are only used in the column layout and are based on comparison period or comparison date fields. Most users find getting used to the conventions of these fields difficult as they are not intuitive at first. I’ve got three suggestions to help with that: 1) use the NAV help menu to look at “comparison period formula” and “comparison date formula”; 2) get in there and play around – you’re not going to hurt anything, and if you know what numbers you should have, you’ll figure it out after some experimenting; and 3) use the account schedules formulas page from this blog. I developed the account schedules formula page because the number one request I have gotten in all my account schedules classes is for a cheat sheet of account schedule formulas.

6. Manipulating the matrix

The largest change between Classic NAV versions (NAV2009 Classic, 5.0, or earlier versions) and Role Tailored Client NAV versions (NAV2009 RTC, NAV 2013, and future versions) is the development of the matrix. The new matrix allows for a whole lot of functionality that we just didn’t have before which makes account schedules a little more Excel-like for many users. If you used one of the Classic versions previously, you’ll find the navigation pathways of RTC versions are significantly different and can take some getting used to. Microsoft did not do a great job with account schedules in the NAV2009 RTC version – there are some things there that are clunky, non-functional, and cumbersome. The good news is they fixed all those problems with NAV2013 and threw in some additional features on top of it, making account schedules even better with that version.

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.


Blogiversary Top 20 (#9) Where can I learn more about NAV analysis reports and analysis by dimensions?

We’re celebrating our one year blogiversary by reposting the Top 20 Most Viewed in the last year, as determined by you, our readers! Follow this link to see the entire list. Enjoy!

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