Expand your use of dimensions in NAV account schedules by using analysis views

Posted: October 2, 2012 | Author: kpeters | Filed under: Uncategorized | Tags: Account Schedules, analysis views, budgets, Classic Client, codeunit 410, column layout, date compression, dimensions, Dynamics, Microsoft, NAV, row setup, RTC, update | Leave a comment

If I had to pick only three basic elements to Microsoft Dynamics NAV account schedules, I’d have to choose row setups, column layouts and analysis views. Of course, row setups give you access to basic general ledger accounts and column layouts give you options on how to show that data in different time periods. So what do analysis views do for us? Analysis views give us the ability to reach into our dimensions, past the two globals, and into the list of shortcuts, allowing us to combine any four dimensions we want at a time in any account schedule.

For illustration, I’m showing you an account schedule from NAV2009 Classic, which shows the dimension filters on an account schedule where no analysis view has been applied. The two global dimensions for CRONUS USA (Department and Project) are available to be selected on the dimensions filter tab and the remaining options are greyed out, unable to be used.

By selecting a different analysis view on the account schedule name page, you will have expanded options. Perhaps you’d like to apply an entirely different set of dimensions filters to your account schedule or limit the dimensions filters to only two of your shortcut dimensions. Any combination of four dimensions is available to you through analysis views.

There are a few things you need to know about analysis views before you start using them:

You can add a default analysis view to any account schedule. If you always want a certain account schedule to filter on a selected group of dimensions, this is the best way to do this.

Analysis Views must be updated. You can do this at any frequency you wish. Some companies choose to update once a month, some companies update once a day. Be aware that the process of updating pulls in any transactions that have been posted since the last update to your analysis view. This means if you post some entries during your close process, you’ll need to update your analysis view in order to show the change on your account schedule.

Updating can be done manually or it can be automated. To manually update analysis views, just hit the update button. You’ll need to do each one separately. Alternatively, you can choose to schedule codeunit 410 Update Analysis View as a regularly scheduled maintenance item in order to automate this process.

When you set up an analysis view for the first time, it can take a long time to update. Depending on the size of your database, if you don’t limit how far back your update goes, it could take a long time to update initially. Be careful by trying this out in a test system first. This process, which will normally take seconds when run daily, will take many hours if you don’t limit it and will cause table locks for other users.

If you test out an update and are afraid it will take up too much processing time, you have a couple of options. First, you can limit the start date of your update. One reason you might need a new analysis view is because you’ve added a new dimension. In this case, you really don’t need to go back to the beginning of time on your update. Choose the date you started gathering data on your new dimension as your starting date. You can also choose date compression. By compressing your data by day, week, month, quarter, period, or year, you limit how much detail you can see when you drill down. If you use this option, you’ll need to remember that this is a compressed view if you change column layouts to different time frame than your compression setting. Choosing date compression of none will allow you to drill down to full transactional detail.

Avoid using the update on posting button. This option updates your analysis view every time you post something to your system. Every sales order, every sales tax entry, every cash receipt, etc. will update real time. I’ve seen one small company use this option and it brought their system performance to a crawl.

Remember to include your budgets. If you make any changes to your budgets, you need to update that information on your analysis views as well.

The NAV accountant’s little helper: the reconciliation account check box

Posted: September 18, 2012 | Author: kpeters | Filed under: Uncategorized | Tags: balance after posting, Classic Client, general ledger, journal entry, NAV, net change, posting test report, reconciliation account, RTC, tips and tricks | Leave a comment

There’s a tiny feature in NAV that I take for granted. It’s there, in the background, hiding in the setup, and it helps me out just a little almost every time I make a manual journal entry. Especially if I’m in a bit of a rush, or distracted, this option gives me one more quick but effective look at what I’m doing before I commit a journal entry to my books permanently. This nifty little thing can be added to your NAV setup at any time without any help from IT and won’t require any maintenance. The option I’m talking about is the Reconciliation Account check box you can find on your general ledger account card. Here’s where you can find it:

Role Tailored Client

Classic Client

For any account where you’d like this additional double check, check the Reconciliation Account box found on the General Ledger Account Card. Now go put together your journal entry.

Instead of posting directly from the entry, choose Actions=>Posting=>Test Report=>Preview (if you’re RTC) or just choose Posting=>Test Report=>Preview (if you’re Classic). NAV will put together a handy dandy print preview which will now include a section at the bottom labeled Reconciliation. This little section will show you which direction your entry is going in, what sign your ending balance will be, and what the actual balance after posting will look like. This all happens before you commit it as final to the system. This may be just the extra bit of help you need in order to keep you from making an error. If you like what you see, post that journal entry!

If you have an account where you’ve got a lot of daily activity, you may not find this as useful as you do with accounts with just a little bit of activity throughout the month because you’ll have a more frequently changing end balance. Decide what might be useful to you.

Next time you do a journal entry, give it a try. Go to the account card and check the box. Make your journal entry. Before you post, run the posting test report. Is the net change in your journal entry going in the right direction? Did you enter the correct account? Is your ending account balance the right sign? Most importantly, is the balance after posting what you expected? This little helper can give you a quick preview before you hit post and save you from having to reverse and correct an entry.

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

Basic column layout options for NAV Account Schedules

Posted: September 10, 2012 | Author: kpeters | Filed under: Uncategorized | Tags: Account Schedules, balance at date, Classic Client, column layout, comparison date formula, comparison period formula, financial statement, fiscal year, general ledger, NAV, net change, RTC | 5 Comments

Column layouts in NAV account schedules are what you build to add more flexibility to your financial statements. You can have an unlimited number of column layouts to match together with your already existing row setups. In general, most basic row setups contain general ledger numbers and column layouts contain dates. 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 to start with:

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 – There are actually seven options to choose from here, but I recommend that you limit yourself to these three 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 -1CP 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

Classic Client

For more information on row setups, please see these posts: Basic row setup options for NAV Account Schedules and Complete row setup options for NAV account schedules.

If you’re just beginning to use account schedules, see Getting started with a new account schedule.

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

Maximize your Microsoft Dynamics NAV 2009 Classic screen space with user level configuration options

Posted: August 6, 2012 | Author: kpeters | Filed under: Uncategorized | Tags: Classic Client, glued column, increase header, NAV, RTC, user level configuration | 1 Comment

One of the first things I do when I get a new person started using NAV is to show them how to customize their screens using different NAV options available at the user level. The NAV defaults start us off with white space in weird places, columns we may not necessarily use, and headers we can’t read. These are all possible to fix easily at the user level, and I’ll share my six tricks with you today.

Today we’re just going to cover the Microsoft Dynamics NAV 2009 Classic client. The Role Tailored Client, with its updated look and feel, is significantly different and Microsoft has done a great job of addressing many of these small challenges with the new client.

Change the size of the main navigation pane. You can do this by hovering on the vertical blue separator bar until you get a double-sided arrow, and then move your pane to the left or right.
Get rid of unwanted menu options. If you’re not using the Jobs or Service Menus, right-click on that item and choose hide. You can always add something back to the menu by right clicking and choosing show.
Increase your header size. Every screen starts out with the header line as one line tall. This means that you can’t read most of the information in your headers. Hover at the bottom of the blank grey box at the top left of the lines area until you get a double ended arrow, then click and pull down to see more lines within the header.
Check your row height. Depending on your preference, you might prefer rows taller or shorter than what is the default. Choose any grey separator bar on the left side of the lines area between two rows and move it up for shorter or down for taller. Your adjustment will be equally sized for all rows.
Change your columns. Make sure to show only the things you need. There are almost always more options available with the default than what you will use in day-to-day transactions. If you have a coworker in a similar role, it may be best to check to see what they use regularly when you’re just getting started. Hide anything you don’t need just by using right-click and hide. Just like with the menu options, you can right-click and show if you want to put something back on your screen.
Make the glued column smaller. In NAV 2009 Classic, on every screen, one column is always designated as the “glued” column. This column is generally the Description column. You’ll know which one this is when you try to resize it to a smaller size and get the error, “You resized the glued column Description, which then expanded automatically to fill otherwise empty space”. This can get frustrating for new users, because it seems there is not a solution. There are two recommendations that I have to get past this.
1. Place your mouse to the right of the glued column separator, and move from right to left until you get a double-headed arrow. Left click, hold, and aggressively move to the left, farther than where you want to end up. This will make your glued column narrower, but only if you have a bunch of columns off to the right already.
2. Another way to accomplish this is to add a column that you don’t need to the right and make it really really wide, so that it creates visual white space on the right hand portion of your screen.

Before

After

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

Complete row setup options for NAV account schedules

Posted: July 30, 2012 | Author: kpeters | Filed under: Uncategorized | Tags: Account Schedules, balance at date, Classic Client, column layout, dimensions, Excel, export to excel, financial statement, general ledger, microsoft dynamics nav, NAV, net change, posting accounts, row setup, RTC, set base for percent, setup options, show opposite sign, totaling type, trial balance | 1 Comment

About a month ago, I published a post called Basic row setup options for NAV account schedules. This is a great place to look if you’re just getting started. However, if you’re looking for what else you can do with Microsoft Dynamics NAV account schedules, then this is where you want to be. This post will go through each and every field available on the account schedule row setup and talk about the available options. The functionality of these fields are exactly the same between the Classic and Role Tailored Clients. 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

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: 6 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.

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 one of the Top 20 Most Viewed in the last year! Follow this link to see the entire list.

Basic row setup options for NAV Account Schedules

Posted: July 5, 2012 | Author: kpeters | Filed under: Uncategorized | Tags: Account Schedules, balance at date, Classic Client, financial statement, net change, row setup, RTC, totaling type | 1 Comment

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 form, 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 to start with:

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

Classic Client

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

Use insert accounts for a fast start on building a trial balance

Posted: June 11, 2012 | Author: kpeters | Filed under: Uncategorized | Tags: Account Schedules, Classic Client, export to excel, general ledger, insert accounts, NAV, row setup, RTC, trial balance | Leave a comment

You’re diving into using account schedules – you’ve figured out where they are, created a brand new one, given it an easy short name and a longer descriptive name and now you’re ready to get started. Now what? You need to begin with the row setup; the foundation of all account schedules.

The row setup defines what you’ll see on the lines of your report. Generally you’ll want to define general ledger accounts, or groups of general ledger accounts, in the row setup when you’re just getting started. Later, you’ll want to add some column layouts but let’s focus on getting that foundation built first.

The easiest way to get going with a new row setup is to put together a trial balance. Your trial balance should be a listing of all your general ledger accounts, one line each, which will show the balance of each account on a particular date, generally a month or period end. If you add them all up, all the accounts should balance to zero, which is one way to prove that your books are in balance.

Role Tailored Client Instructions

If you’re using the role-tailored client (RTC), choose Edit Account Schedule from the action pane.

You’ll get a new screen with many blank rows ready to be used. From this screen, choose Actions=>Functions=>Insert Accounts.

This opens up your G/L Account List. Use your mouse to select all the rows you want to add to your row setup.

Once you’ve highlighted them all (they should turn light blue), hit OK, and all the accounts you’ve selected will be added to your row setup, one row per account.

At this point, don’t make any changes to the row setup. Let’s see if it matches the canned trial balance that you can print directly out of NAV. Choose the Overview button (shown in the above screenshot). Select a column layout. If you don’t have one yet, hang in there, we’ll cover that in an upcoming post. For now, I’m going to choose a simple one that shows balance only. Select Show Matrix from the action pane.

This will show all the accounts you selected in your row setup, now in your account schedule.

To prove out the balance, dump the account schedule into Excel. Do this by choosing Actions=>Functions=>Export to Excel. On the next window choose the option to Create a New Workbook. Hit OK, and your schedule will export to Excel. Watch out: If you’ve used Begin or End-Total lines in your chart of accounts, you’ll need to remove these lines. These are calculated fields, and should not be a part of your trial balance.

Let’s run the canned trial balance from NAV as a comparison to see if we’ve done this right. Follow this trail to get to the report: Departments=>Financial Management=>General Ledger=>Reports=>Financial Statement=>Trial Balance. Select Actual Balances as your report column and make sure the date filter is set to the same date you used when running the account schedule. Compare the report to the account schedule. Each line should match between the two reports and the total should be zero on both, proving your trial balance is indeed in balance.

Classic Client Instructions

For those who use the classic client, let’s run through the same steps. Starting from Financial Management=>General Ledger=>Analysis & Reporting=>Account Schedules, choose your new account schedule from the list.

Hit OK, and you’ll get a new screen with many blank rows ready to be used. From the Functions button on the bottom right, choose Insert Accounts.

This opens up your G/L Account List. Simply click on the blank cell immediately to the left of the No. field to select all accounts quickly.

Once you’ve highlighted them all (they should turn dark blue), hit OK, and all the accounts you’ve selected will be added to your row setup, one row per account.

At this point, don’t make any changes to the row setup. Let’s see if it matches the canned trial balance that you can print directly out of NAV. Hit OK, and then choose Overview from the Account Schedules button found on the bottom right.

Select a column layout from the Column Layout Name field. If you don’t have one yet, hang in there, we’ll cover that in an upcoming post. For now, I’m going to choose a simple one that shows balance only.

This will show all the accounts you selected in your row setup, now in your account schedule.

To prove out the balance, dump the account schedule into Excel. Do this by choosing Export to Excel from the Functions button on the bottom right.

On the next window choose the option to Create a New Workbook. Hit OK and your schedule will export to Excel. Watch out: If you’ve used Begin or End-Total lines in your chart of accounts, you’ll need to remove these lines. These are calculated fields, and should not be a part of your trial balance.

Let’s run the canned trial balance from NAV as a comparison to see if we’ve done this right. Follow this trail to get to the report: Financial Management=>General Ledger=>Reports=>Financial Statement=>Trial Balance. On the Options tab, select Actual Balances as your report column and make sure the date filter is set to the same date you used when running the account schedule. Compare the report to the account schedule. Each line should match between the two reports and the total should be zero on both, proving your trial balance is indeed in balance.

NAV keyboard shortcuts – Classic to RTC

Posted: May 16, 2012 | Author: kpeters | Filed under: Uncategorized | Tags: Classic Client, keyboard shortcut, Microsoft, NAVUG, RTC, tips and tricks | Leave a comment

Who knew that keyboard shortcuts could be so darn controversial?

I recently got the chance to present a NAV tips and tricks session at the NAVUG Midwest Regional Chapter meeting, and one topic that came up again and again was what keyboard shortcuts were changing in the transition from the classic client to the role-tailored client (RTC).

The first discussion started as a rumor during the social hour the night prior. “Did you hear they’re getting rid of F8?” “No, they can’t get rid of F8!” “What’ll we do without F8?” “Oh my inventory accountant is going to hate that” “Well I heard they’re changing everything.”

Well lucky for us we had quite a few folks there who have already been using the RTC who were able to quite handily put that vicious rumor to rest. F8 is firmly available to copy the field above in the new client, just has it has been in the classic client.

There are quite a few other things that are just simply changing, and I think the changes make a whole lot of sense.

Take F3. F3 has been the constant companion of the NAV user, utilized whenever we needed to designate a new record, a new line, a new document, or a new card. Anything new has been F3. This has been replaced with four new commands in the RTC. Complicated? No, I think that Microsoft has made a concerted effort to simplify by making keyboard shortcuts more consistent with other Microsoft Office products. Even though four new commands are replacing one, I think that Ctrl+N will come quite naturally to someone wanting to create a new record. Ctrl+Insert likewise makes sense for inserting a new line. Ctrl+Shift+C for opening a new card and Ctrl-F2 for creating a new document may be a little taxing, but I’m betting we’ll all get used to it. Frankly, I’m glad to see Microsoft making it easier and more consistent for new users to adopt.

I’m sure I may utter an oath or two when I hit F3 in the RTC and instead of getting a new record, get bumped into a field filter. But I sure will appreciate many of the brand new keyboard shortcuts that support RTC features that we’ve never had before like Alt+Tab to switch among open windows and F5 which now acts as a refresh command, just like it does in other programs.

Check out the link below which goes to a Microsoft .pdf listing out a nice comparative list of keyboard shortcuts between the classic client and the RTC. This will be the first document I give to my end users when we start working on our transition to the RTC. There will always be fear of change, even with small things like keyboard shortcuts.

Encourage folks to look for the consistencies and efficiencies gained with the new ones and remind them; at least they didn’t get rid of F8.

NAV2009KeyboardShortcuts

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

Getting started with a new account schedule

Posted: May 13, 2012 | Author: kpeters | Filed under: Uncategorized | Tags: Account Schedules, Classic Client, NAV, RTC | 1 Comment

If you are just getting started with account schedules, you need some basic information to get going. This post will show you where to find account schedules in NAV, how to create a new one, and how to name them. We’ll cover both the role-tailored client (RTC) as well as the classic client.

Where can you find them?

If you’re using NAV with the RTC, choose Departments, then General Ledger from the Financial Management menu, then choose the Account Schedules link under the Reports and Analysis and Analysis & Reporting menus.

If you’re using NAV 2009 and prior versions with the classic client, choose Financial Management=>General Ledger=>Analysis & Reporting=>Account Schedules.

How do you create a new account schedule?

In the RTC, creating a new account schedule is made easy with the New button located on the action pane. If you’re a keyboard shortcuts person, you can use Ctrl-N, or if you prefer to use menu options, go to Actions=>New.

In the classic client, use F3 to create a new record for the account schedule, or select New from the Edit menu.

How do you name a new account schedule?

Once you’ve created a new report using the RTC, you’ve got 10 characters available for the Name field, and 80 characters available for the Description field.

The classic client has the same parameters.

Both clients will allow you to use any combination of numbers, letters, or special characters.

Keep in mind, that regardless of whether you are using RTC or the classic client, the names you use for the description field will be the default names used when you print account schedules directly out of NAV.
This posting is one of the Top 20 Most Viewed in the last year! Follow this link to see the entire list.