Analysis Report: A Powerful NAV Analytical Tool

8/10/2011 Konnie Seago Dynamics NAV

I often get questions from NAV users that are looking for a report that will give them specific information regarding customers, vendors and/or items. They are often surprised to find that NAV has an analysis tool (Analysis Reports) that will allow them to design a report with the exact information they are looking for. The user creates Line Templates to define the lines on the report (Items, Customers, Salespersons/Purchasers, Vendors or groups of each) and Column Templates to define the columns (quantities, sales amounts, cost, consumption, output and much, much more). Multiple Line Templates and Column Templates can be created then mixed and matched to create a variety of custom reports that are completely defined by the user.

The Line Template has an insert function that makes it quick and easy to define the lines without manually entering each item, customer or vendor to be included on the report. The use of filters in the item/customer/vendor list allows for line templates to be created for specific groups such as Raw Materials or Finished Goods only. An Item Category Code filter could be used instead or in conjunction to further limit the items to be included in the report.

When setting up the Column Templates there are a number of pre-defined Analysis Type Codes from which the user can select to simplify the setup process. For example, the user could select Purchase QTY, Sales Amount, or COGS from a long list of column options. When the selection is made several fields of the column template will be auto-populated with the correct values needed to calculate the appropriate values. This makes it simple to setup a report that could include columns for things like: Qty Sold, Sales Amount and COGS. The user can then add columns with calculations so that the average unit cost, average unit price and the gross profit can be calculated as well.

Some simple reports that I get common requests for that can be quickly generated via an Analysis Report are:

  • All items and quantities sold to a specific customer within a given date range. Note: sales amounts, COGS and profit can easily be included.
  • All items purchased from a specific vendor within a given date range.
  • Usage (consumption and sales) of an item, a group of items, or of all items within a given date ranges.
  • The Qty. on hand plus usage by month for the past 6 months.

The Analysis Report can be printed or can be exported to Excel. Analysis Views can be tied to an Analysis Report which will allow dimension filters to be applied. The ability to use formulas gives the user the flexibility to calculate additional information that can be valuable to the reader.

See a sample report below and note that this report can be run for any one customer, multiple customers or for all customers combined. It would be just as simple to setup a report that would show total sales, COGS and gross profit for each customer.

The reports I have mentioned are very basic in nature. Analysis reports can also be used to generate much more sophisticated reports.

Navigation

Social Media