Using Dimensions in Reporting for Dynamics NAV/365 Business Central

This post continues exploring the topic of Dimensions in Dynamics NAV (further, “NAV”) and Dynamics 365 Business Central (further, “D365BC”). Other posts have already discussed what Dimensions and Dimensions Values are; how to create; and assign them to Master Data and GL Accounts as well as how to block Dimension Combinations. This post focuses on how to use Dimensions in reporting in particular.



The benefits of using Dimensions in reporting are:

1. Be able to “slice and dice” your data as your company needs;
2. Increase flexibility of your reporting;
3. Simplify your Chart of Accounts (COA);
4. Avoid using external (e.g. developer’s) help in building your own reports;
5. Save your time in creating reports by combining Accounts Schedules with Column Layouts and Analysis Views.

You have created your Dimensions and Dimensions Values and have assigned them to your master data. You have been using Dimensions Values on your transactions in the system. How do you now run a report to extract the data, to see where your company has done well and where it can improve? In order to do so, both NAV and D365BC have the following tools:

a. Account Schedules
b. Analysis Views

A detailed discussion of these tools is beyond the scope of this post as each tool requires a separate post to cover all the details; however, the points relevant to Dimensions are going to be highlighted here. The following section focuses on Account Schedules.

Account Schedules

Account Schedules are a tool that enables Users build their own reports. Typically these reports are based on the General Ledger (GL) Accounts (Rows) and are further broken down by month or period (Columns) that can be compared to the same period a year ago, for example or to the GL Budget for the given period of time.

Account Schedules can be found in the Departments> Financial Management> General Ledger> Analysis & Reporting> Account Schedules in NAV OR under the “Finance” section in the Home Page Ribbon (“Accountant” Role) in D365BC OR by searching for “Account Schedules” in the Search Box in both D365BC and NAV.

Account Schedules Reports consist of two parts:

1. Account Schedules – these are the rows of your report which are typically your GL Accounts AND
2. Default Column Layout – these are the columns of your reports which are typically your Accounting Periods, Net Changes, Balances and comparisons to the same Period a year ago or to the Budget.

One of the limitations of Account Schedules is that they typically do not use Rows or Columns as Dimensions which can be useful, for example, in comparing how one of the departments contributed to the revenue of the company compared to another department. It is, however, still possible to do by filtering on each department and running Account Schedule Report twice, one time for each department.

Below is a typical example of an Account Schedule Report run out of the system:

Screenshot 1: Account Schedule Report with “Department” Dimension Filter applied.

As can be seen in Screenshot 1, it is only possible to filter the report by one of the Dimension Values, but the Row and Column titles do not change according to the Dimensions, only the amounts do.

Another limitation of Account Schedules is that they only provide an opportunity to use up to two Global Dimensions. In order to be able to add more Dimensions and to make Dimensions (Dimension Values) as Rows and/or Columns, it is necessary to use Analysis Views Reports. The following section of this post focuses on this tool which is discussed in more details as they provide more flexibility in connection with Dimensions.

Analysis Views

Analysis View Reports are a reporting tool used to build financial reports in NAV and D365BC which allow analyzing data based on Global AND Shortcut Dimensions.

Analysis Views can be found in the Departments>Administration>Application Setup>Financial Management>Dimensions>Analysis Views in NAV by searching for “Analysis Views” in the Search Box in both NAV and D365BC.

Similar to Account Schedules, Analysis Views consist of two parts:

1. Analysis View – it is accessed by clicking on the “New” button in the Ribbon in the Analysis Views List or by viewing or editing an existing one. This is the place where the Dimensions you want to use in your analysis are specified as well as some other options, including:
   • Code – the code for this Analysis View (e.g. REVENUE);
   • Name – the complete name of the report (e.g. Sales Revenue);
   • Account Source – can be either G/L Account or Cash Flow Account;
   • Account Filter – the Accounts which are used in the Report – it is possible to select a single Account, several individual Accounts or a range[1] of Accounts;
   • Date Compression – select the level of detail required the Report (e.g. it is possible to select “Month” to see the revenue by month rather than by day);
   • Starting Date – the date starting from which the Account Entries will be considered for this report;
   • Update on Posting – to update this Report every time a related posting is done, it is necessary to click on the “Enable Update on Posting” button in the Ribbon; otherwise the only way to refresh the data in the report is to click on the “Update”[2];
   • Include Budgets – this function allows updating Analysis View Budget Entries simultaneously with updating the Analysis View (works only with G/L Accounts)[3];
   • Blocked – putting a checkmark in this checkbox prevent Users from using this Analysis View Report;
   • Dimension 1 (2, 3, 4) Code – these are the Dimensions that are going to be included in the Report and can be used as Rows or Columns. Global Dimensions must be included too if they are required for the analysis[4].

Below is a screenshot of an Analysis View Page:

Screenshot 2: Analysis View Card

[1] Use the vertical bar symbol “|” between the Accounts you want to use to select several Accounts. Use two dots (..) between Account to set a range.

[2] It is useful keep Reports up-to-date by enabling the “Update on Posting” function. However, in companies where there are thousands of transactions happening frequently and dozens of Analysis Views Reports set up, it may be beneficial to disable this function to prevent the system from slowing down.

[3] This can be useful if a Budget with the same dimensions as the Analysis View has been created. Enabling this function ensures that only up-to-date information is used in comparing actuals to the Budget.

[4] If a company needs to run a Report based on more than four Dimensions (any number of Dimension Values can be used), which is rare, then the Report has to be run twice with a different set of Dimensions each time or to combine four Shortcut Dimensions in the Analysis View with the two Global Dimensions of an Account Schedule Report to be able to run a Report based on up to six Dimensions at the same time.

2. Analysis by Dimensions – this is the second constituent which is required to run an Analysis View Report and is accessed by clicking on the “Analysis by Dimensions” button in the Ribbon of the Analysis Views List; it provides further tools to design the Report and allows setting up filters to narrow down the results.

Importantly, unlike Account Schedule Reports, Analysis View Reports allows adding up to four Dimensions (these can be further combined with the two Global Dimensions of an Account Schedule Report) and to show Dimensions as Rows and/or Columns as shown on the screenshot below:

Screenshot 3: Analysis View Matrix by CUSTOMERGROUP and AREA

As shown in Screenshot 3, an Analysis View Report can work well for building reports where it is important to be able to compare Dimension and Dimension Values against each other as shown above where revenue sources are compared by the “CUSTOMERGROUP” Dimension (“LARGE”, “MEDIUM”, “SMALL” Dimension Values) by the “AREA” Dimension (“Europe North” and “America North” Dimension Values).

It is worth mentioning that Account Schedule Reports can be combined with Analysis View Reports. For example, by combining the Analysis View Report above with an Account Schedule Report, it is possible to add “AREA” Dimension filter to an Account Schedule Report which has been previously unavailable as “AREA” is a Shortcut Dimension:

Screenshot 4: An Account Schedule Report combined with Analysis View

In conclusion it is worth mentioning that both Account Schedule and Analysis View Reports are easily exportable to Excel (and other formats). Importantly, Account Schedule Reports can be exported in two different formats out-of-the-box. Account Schedule Reports can be exported to a brand new Excel file or the fresh data can be exported to an existing Excel file. The latter is particularly helpful if Users want to create sophisticated Excel reports that use highlighting, different Excel formulas and other advantages while also keeping the data in the report up-to-date. Analysis Views, in turn, Export to an Excel file as a pivot table which may facilitate further processing of the data in Excel. The advantage of exporting Account Schedules and Analysis Views to Excel is that the file can be easily stored on a hard drive, shared internally in a convenient format or sent to external stakeholders who do not have access to NAV or D365BC.

In summary, this post focused on how Dimensions can be used in reporting. Two major reporting tools have been discussed in this connection: Account Schedules and Analysis Views. Account Schedules can help with day-to-day reporting such as a trial balance repot, while Analysis Views allow more flexibility around reporting on Dimensions as they allow adding up to four Global or Shortcut Dimensions which can be shown as Rows, Columns or both. Both Account Schedules and Analysis Views can be easily exported to Excel for further formatting and sharing.

At Encore, Solution Specialists help companies to set up and use Dimensions in the most efficient and effective way. The team of Solution Specialists at Encore are happy to answer your questions and to help you and your company reach your ERP goals – contact us anytime!

Related Articles

• Dimensions in Microsoft Dynamics NAV
• How to Create Dimensions and Dimension Values in Dynamics NAV
• How to Assign Dimensions to Master Data & GL Accounts in Dynamics NAV
• How to Block Dimension Combinations in Dynamics NAV or Business Central

This post was written by Iaroslav Pankovskyi, PhD.

Read Next: 18 Warning Signs Your Business Need the Cloud

This post first appeared first on Encore Business Solutions.