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.



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s