Reporting in Dynamics NAV

We would like to start this blog with a summary of different reporting options that NAV provides out of the box.NAV-Reporting: NAV provides a number of static reports such as Top 10 Customers or Trial Balance. RLDC-Reports that are available since NAV 2009 allow to create reports with some visualization such as bar or pie charts and dynamic elements (dynamic sort order, hyperlinks between reports). This type of reports is good for printing and - in the tradition of many companies to report certain figures to the CEO.Creating NAV-Reports since NAV 2009 requires a good knowledge about the structure of NAV-tables, some know-how on C/AL and on RDLC-Layout what is why NAV-Reports are mainly created by Dynamics-Partners rather than NAV-users.Pros:

Fully integrated into NAV (no other tool needs to be used)
high flexibility in terms of layout of reports
a lot of Standard reports are available out-of-the-box
data can easily be printed and exported as PDF-files
entire business-logic of NAV is available (e.g. in order to use pre-existing NAV-functions to make certain calculations that are not stored in tables)

Cons:

Deep knowledge of NAV's table structure and the reporting language RDLC is necessary
Creating reports is time-consuming and - since it is nearly always done by NAV-solution Partner - expensive
NAV reports are not very dynamic in nature (slicing and dicing the data is not possible)
Changes to existing reports (both regarding layout and additional reporting dimensions) always require development

Export to Excel: NAV's Office Integration has been improving from version to version. It has been possible for a couple of years to export each table and page to Excel in order to continue working on the data. NAV 2013 also provides the option to export each report to Excel (Screenshot). Furthermore lists that have been exported to Excel maintain their relationship to the source-NAV-Table. This allows to refresh the list from time to time.Ad hoc reporting is a suitable way to report without the need of database- and programming-knowledge. Ad hoc reporting however is limited to simple tables and a modest number of records to export to Excel.Pros:

Included in base NAV
Easy to use - what you see in NAV is what you get in Excel
No IT-knowledge is necessary
Flexibility of Excel can be used

Cons:

only suitable for simple exports (exporting tables with multiple indented sub-tables for example is not possible)
limited number of records to be exported

Account schedules: NAV provides powerful features in terms of financial reporting - this comes at no surprise since NAV originates from a financial solution that used to be called Navision Financials. Account schedules allow controllers to report on the chart of accounts in a very flexible manner. Based on the chart of accounts individual report layouts can be created for example to create a comparison of the balance of the current year versus the previous year including budgeted values. Kerry Rosvold is probably one of the most knowledgeable NAV-Users on account schedules and has some very insightful information about them here.Pros:

Good for financial reporting
Budgeted values and actuals can easily be compared
High flexibility in terms of column layout of reports (Net Change, Balance at Date, Year to Date, Entire Fiscal Year)
Up to four NAV-dimensions may be used to filter at the same time
Different granularity of reporting period (day, week, month, quarter, year, accounting period)
Allows using more compact chart of accounts since NAV-dimensions can be used for reporting
Drill-down into source data (G/L-entries) is possible

Cons:

Limited to financial reporting
Reporting accuracy relies heavily on NAV-Dimensions
Sales figure for example cannot be split up by items or customers if those are not set up as dimensions
Extending the reporting measures is difficult and only possible for NAV developers
reporting is possible on amounts - not on quantities or other measures

Analysis by Dimensions: Analysis by dimensions is NAV's attempt to provide some sort of cube-like analysis. Based on pre-defined Analysis Views (that may also be used for Account-schedules) up to four NAV-dimensions can be used at the same time to analyze G/L-Entries. The result is a two-dimensional matrix, whereas the Information that are supposed to be shown on rows and columns can be freely changed during each run. By exporting the result to Excel a "real" Pivot-table is created what allows further reporting on the data.Pros:

Advantages of account schedules combined with additional flexibility to show a second dimension as columns

Cons:

same as account schedules

Sales/Purchase Analysis by Dimensions: Sales Analysis by Dimensions (and the counter-part Purchase Analysis by Dimensions) are similar to Financial Analysis by Dimensions. They allow to create pre-defined Sales Analysis views where up to 3 custom dimensions, an item-, customer-, and customergroup-filter can be set up. Based on this set up a matrix that shows sales amounts, sales quantities and Cost of goods sold can be used for reporting purposes.Pros:

Budgeted values and actuals can easily be compared
Reporting on items is possible
Different granularity of reporting period (day, week, month, quarter, year, accounting period)
Drill-down into source data (G/L-entries possible)

Cons:

only up to three NAV-dimensions can be used
only limited measures are available (only sales amounts, sales quantities and cost of goods sold)

Sales Analysis: Sales Analysis is another reporting Feature of NAV. Contrary to account schedules and Analysis by dimensions that are entirely based on G/L-Entries, sales Analysis work on Item Ledger Entries. These item ledger entries provide the source of measures such as Sales Amount, Sales Quantity, Unit Price, Standard Cost or Indirect Cost. Similar to aforementioned financial analysis, column layouts (what figures to report on) and Analysis lines can be defined and used in a two-dimensional matrix. Lines may be items, item Groups, customers, customer groups or salesperson.Pros:

Different aspects of sales, purchase and inventory transactions can be analyzed - such as average price, amount shipped not invoiced or profit margin %
The layout of columns may be freely configured without the need to do programming
Budget and actual values may be compared

Cons:

Access to NAV's dimensions is limited
limited to perspective of items (other types such as resources are out of scope)
lists of products and customers that is used for reporting need to be maintained manually

Power Pivot: During the last few years, Microsoft invested heavily into its Business intelligence tool Power Pivot. Power Pivot is a self-service business intelligence tool integrated into Excel. It allows analyzing huge amount of data without the need to anticipate business-related questions. Since it is a general reporting tool and not specifically made for NAV, different sources from Excel-files to SQL-Tables or Web Services can be combined into one data model. This data model is used to create pivot-tables in Excel in order to dynamically change the dimensions that are shown as rows and columns but also the measures to report on.NAV data can be pulled into Power Pivot in different ways:

NAV's SQL-Tables can directly be queried by Power Pivot. This works without any set up or additional data layer. Power Pivot just needs access to the SQL-Database. This direct access provides a very good performance to retrieve the data. However directly querying the SQL-Tables provides some unexpected results: Options fields such as the field Blocked on the Customer table for example are not returned with the well-known values Invoice, Ship, All but with 0, 1 or 2 as the raw SQL-Values. Empty date values are not empty in the SQL-Table but set to 01.01.1753.
SQL-Views can be created to pre-aggregate data and in order to do some data-cleansing (such as the mentioned problem of option values to Show as integer values). SQL-Views provide the same performance as the direct access to SQL tables but allows to bring the data into a shape that is more suitable for reporting
Beginning with NAV 2013, NAV allows to expose so called Query objects as OData-Web Services. Query objects are similar to SQL-Select-Statements but can be created directly inside of NAV. This allows NAV developers and power users to combine NAV-tables into a single query. These OData-Queries can be used in Power Pivot to read the data from NAV.

Power Pivot uses an in-Memory-Engine (meaning that the data to analyze is stored entirely in memory to allow instant query responses) that is also used in Microsoft's mature Data Warehouse SQL Server Analysis Services (SSAS). Power Pivot however does not require the set up of any data warehouse - it compresses the data to high degree and uses the local Excel to process and store the cube.Pros:

Well-known Excel is used as the reporting frontend
Good performance to query data
Slicing and dicing of data can be done in a very flexible manner: it is not limited to NAV-dimensions - each information that can be retrieved from NAV can be used
Information from other IT-systems can be combined with NAV's data
Migrating a Power Pivot solution to a "real" SQL Server Data Warehouse is easily possible
Power Pivot's own query language DAX is similar to Excel-formulas and provides flexibility to create all sort of measures (e.g. time-related measures such as Sales Variance compared to same period in previous year)

Cons:

Retrieving the right data from NAV requires knowledge on the table structure of NAV
Creating simple measures in DAX is easy, using more advanced features however requires good knowledge of the underlying concepts

In future postings we will go into more detail into using Power Pivot to analyze NAV.

Reporting in Dynamics NAV

English Version released