NAV AS 101 Lesson 9: Formulas

Formulas in Microsoft Dynamics NAV Account Schedules are the hardest concept for most users to grasp, because their use is just not intuitive. There are a few basic concepts that can help get you started.

When getting started with a row setup, you need to choose either Posting Accounts or Formula in the Totaling Type column. These two options indicate whether you will be pulling numbers from general ledger accounts (posting accounts) or calculating against other rows (formula). There is another option Total Accounts that lets you pull numbers from total accounts from your chart of accounts instead if you are using total accounts instead of or in addition to posting accounts. Once you have defined your option in the Totaling Type column, then you need to populate the Totaling column with either general ledger accounts or formulas, depending on what you have chosen.

If you are a dimension user, you need to expand your selections to specific columns available in the row setup for dimensions. These are Dimension Totaling columns. The two global dimensions will always be available for use, but any other dimensions will appear with a label that is Dimension 3 Totaling or Dimension 4 Totaling (etc.) until you define which specific dimensions you want account schedules to use by defining them in analysis views. By filtering on dimensions in these columns, you can report at a deeper level and break down the segments of general ledger accounts to their dimension components in your financial reporting.

When getting started with a column layout, you need to choose either a definition of time like Net Change or Balance at Date or Formula in the Column Type column. These two options indicate whether you will be pulling numbers from general ledger accounts (posting accounts) or calculating against other columns (formula). If you have chosen Formula as your Column Type, you need to fill in the formula. The same options for Dimension Totaling are available here, just as they are for the row setup.

All the rest of the information you need about account schedule formulas happens in the Comparison Period Formula and Comparison Date Formula columns. There’s so much information there, we need to address it in a post of it’s own. Stay tuned, it’s coming up next!

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.