Chapter 4. Reporting and Business Intelligence

So far we have seen the data entry and data process side of Dynamics NAV in the previous chapters. Once data is introduced into the system, you should be able to analyze it.

Are our departments generating value for the company? Which items or services are the most profitable? What area is experiencing a bigger sales growth? Analysis and reporting can help you to answer these questions.

In this chapter, we will see the tools available to analyze Dynamics NAV data, both inside and outside the application. This chapter covers the following topics:

• Dimensions

• Filters, charts, and statistics

• Reports

• Account schedules

• Analysis views

• Business Intelligence with Excel and PowerPivot

Understanding dimensions

The term dimension is used to describe how analysis occurs. A two-dimensional analysis, for example, would be sales per area. In a more complex scenario, we could also analyze sales per sales campaign, per customer group, and per area.

In order to be able to analyze those dimensions you have to make sure that each and every single entry contains the information needed. In Dynamics NAV, a dimension can be seen as information linked to an entry, such as a tag or a characteristic. The purpose of dimensions is to group entries with similar characteristics so that you can report on the data in a way that is meaningful to the company.

You can define your own dimensions according to how you need to analyze your data. Each dimension can have unlimited dimension values that are subunits of the dimension. For example, a dimension called Department can have subunits called Sales, Administration, and so on. These departments are dimension values.

In Dynamics NAV, you can create...

Using filters and flowfilters

A good and powerful way to view and analyze data is to use filters and flowfilters inside the application. Both of them are used to narrow down the information seen on screen or the information a report produces. Filters can be applied on all pages and on most of the reports. In this section, we will see how to apply filters and flowfilters on pages. In the Using reports section, we will see how to apply them on reports.

Applying filters on pages

The filtering pane can be found on the upper part of the page, as shown in the following screenshot:

There is a quick filter section in the upper-right corner where filters can be applied over any of the fields that are shown on-screen. Filters can also be applied over two or more fields at a time, or over fields that are not shown on-screen. To do so, the Advanced Filter section should be shown by clicking on the down arrow found at the right of the Quick Filter section. A Show results: subsection should be shown on...

Using reports

Dynamics NAV has a bunch of reports that can be used out-of-the-box. Some other reports may have been added by a partner and can also be used.

Most reports can be found under the Reports and Analysis category on all functional areas. They can also be found in many application pages, where only the reports that are valid for the data shown on the page will be found.

When running a report, the request page for the report will usually be shown. On the request page, you can usually specify different options. You can apply filters to narrow down the information that will be shown or processed and you can choose whether to preview the report or print it to different devices or applications (use a printer or print to PDF, Excel, or Word).

In the following screenshot, you can see the request page for the Customer Top 10 List report:

Not all the reports have an options section and not all reports allow filters to be applied. Whenever these happen, the corresponding sections will not be...

Displaying charts

Graphical information is always useful when analyzing data. Dynamics NAV offers various ways of viewing data in a graphical way.

The show as chart option

Whenever the information shown on the screen can be viewed as a chart, on the Home tab of the ribbon you will see a section called View where users can switch the view of the information from list to chart and vice versa as shown in the following screenshot:

When you switch to chart view, you can select which fields you want to use as a measure and which ones you want to use as dimensions.

Adding charts to the Role Center page

Dynamics NAV has a set of predefined charts that can be added to the Role Center page. To add a chart to the home page, perform the following steps:

1. Click on Home.

2. Click on the Application icon , click on Customize, and then click on Customize This Page.

3. Select Chart Part from the Available parts column and click on the Add button.

4. A Blank Chart will appear in the Role Center layout column.

5. Select Blank...

Using account schedules

The account schedule functionality is part of the Analysis & Reporting section of the Financial Management area. It is meant to create customized financial reports based on general ledger information, budget information, or on the analysis views information. Account schedules can group data from various accounts and perform calculations that are not possible directly on the chart of accounts.

When defining account schedules, the information that will be displayed on both rows and columns can be defined.

Just to see how it works, we will create a simple account schedule that will compare budgeted amounts versus real amounts. To do so, we perform the following steps:

1. Navigate to Departments | Financial Management | Reports and Analysis and choose Account Schedules.

2. Click on New to create a new account schedule. For the new account schedule, select EXAMPLE as the Name, Comparing budget versus reality as the Description, and ACT/BUD as the Default Column Layout.

3. Click...

Analysis views

Analysis views are used to analyze information about dimensions from General Ledger entries, budgets, and cash flow forecast entries. As we have seen, not all dimensions are easily accessed. Analysis views are meant to access all the dimensions in the same easy way, in groups of a maximum of four dimensions at the same time. The four dimension groups may seem a limitation but aren't, since we can create as many analysis views as needed, combining all the dimensions we want.

Creating an analysis view

Follow the given steps to create an analysis view:

1. Navigate to Departments | Administration | Application Setup | Financial Management | Dimensions | Analysis Views. The Analysis Views page will open showing the existing analysis views.

2. Click on an existing analysis view. View the analysis view card with the data shown in the following screenshot:

   

3. Click on the Update option found on the ribbon bar to create analysis views entries based on the criteria that you set up on the card.

   Note...

Business Intelligence with Excel and PowerPivot

With Dynamics NAV, we can easily create our own report in Microsoft Excel using Business Intelligence (BI) tools. Let's create a report to analyze the total sales and profits, grouped per customer or per item:

1. Open Excel. On the PowerPivot tab, click on the PowerPivot Window option.

2. A new page opens. Navigate to From database | SQL database. Type the server and database name of your Dynamics NAV. Click on Next.

3. Choose the Select in the table and views list to choose data to import option.

4. From the table and views list, select the following tables:

   Table Name	Comments
   CRONUS International Ltd_$Value Entry	Click on Preview & Filters. Filter the Item Ledg. Entry Type field to only show lines with the value 1.
   CRONUS International Ltd_$Item
   CRONUS International Ltd_$Customer

   

5. Click on Finish. Then click on Close. Close the PowerPivot Window to go back to the Excel sheet.

6. Now that we have selected the data source, let's create a Pivot...

Summary

In this chapter, we have learned what dimensions are and we have seen that they are a tool for tagging all our ledger entries. They allow you to analyze the data saved in the system in a manner that is useful to your company. We have learned to analyze data on screen, using filters, flowfilters, and charts. We have also learned about reports, an out of the box way of analyzing data. Account schedules are a tool that helps us analyze data from ledger entries, and so are analysis views. Analysis views are also useful to analyze financial groups by dimensions. Finally, we saw how to link Excel with our Dynamics NAV database to be able to use the Business Intelligence tools included in Excel.

In the next chapter, we are going to take a look at different predictive tools included in Dynamics NAV, such as budgets or cash flow foretelling.

The rest of the chapter is locked

Unlock this book and the full library FREE for 7 days

Get unlimited access to 7000+ expert-authored eBooks and videos courses covering every tech area you can think of

Renews at €14.99/month. Cancel anytime

Start free trial

Previous Chapter

Chapter 1 of 15

Next Chapter

Summary

In this chapter, we have learned what dimensions are and we have seen that they are a tool for tagging all our ledger entries. They allow you to analyze the data saved in the system in a manner that is useful to your company. We have learned to analyze data on screen, using filters, flowfilters, and charts. We have also learned about reports, an out of the box way of analyzing data. Account schedules are a tool that helps us analyze data from ledger entries, and so are analysis views. Analysis views are also useful to analyze financial groups by dimensions. Finally, we saw how to link Excel with our Dynamics NAV database to be able to use the Business Intelligence tools included in Excel.

In the next chapter, we are going to take a look at different predictive tools included in Dynamics NAV, such as budgets or cash flow foretelling.