Microsoft Dynamics NAV

Building Charts in Microsoft Dynamics NAV 2013 R2


Customers and Partners regularly ask me about building charts in Dynamics NAV. There are a few different ways in which you could do it.  Here, in my first blog, I have listed 5 different ways in which you can build and view charts:

(Feel free to jump directly to any of the specific methods)

1. Show as Chart feature

2. Generic Charts

3. Business Charts

4. Charts in NAV Reports

5. Charts in Excel

1. Show as Chart feature – The simplest way to do it  [Go to the top]

Almost all the List pages in Dynamics NAV have an action button to show the list data as a List or as a Chart.

Show as List/Chart option in Dynamics NAV List pages
Show as List/Chart option in Dynamics NAV List pages

Lets use the Show as Chart option in Customers list to look at the customer sales.

All we need to do is

a. Open Customer List in NAV and click on Show as chart

b. Choose x-axis measure at the right bottom. (For ex: No.)

c. Choose y-axis measure at the top left. (For ex: Sales(LCY). You can have multiple y-axis measures as well which is awesome)

d. Optionally, you can also select a z-axis measure at the left bottom and have a 3D chart.

e. You can filter your data on charts similar to filtering data on lists.

In the below Chart, I have filtered on  Sales (LCY) <>0:

Customer sales shown as chart
Customer sales shown as chart

This feature uses the Generic chart customization in the background. You can right click on the chart to see multiple options like save picture as, Print, customize etc., You can click on Customize which opens Generic chart customization window (as shown below). Here you can add more measures, change the graph type (pie, Doughnut, Line etc.,) , copy chart (for e.g., to use it as a chart part in your role center) etc..

Customize chart to change the Graph type, add titles, optional measures etc.,
Customize chart to change the Graph type, add titles, optional measures etc.,

 

2. Generic charts  [Go to the top]

To display charts on role centers or alongside other pages, we need to save the chart under Generic charts. Dynamics NAV has multiple generic charts out of the box built on table and query objects. We can also add more generic charts to this list. Generic charts are simple. All we need to create a generic chart is the Source (Either a Table or a Query object) and specification of x-axis and y-axis. Optionally, we can specify z-axis measure, filters and multiple y-axis measures on the chart.

Below I’ve defined a generic chart based on Customer table to show Customer Sales vs. Profit.

a. Search for Generic charts in the search area of NAV windows or web client and open the Generic chart Page. We see a list of charts here. You can select any line and click on edit to explore how the existing generic charts are defined

b. Click on New and enter the values as shown below:

Here we are trying to use the Table Customer with Customer Name in the X-axis and Sales (LCY) and Profit (LCY) in the y-axis. I have also added a filter to display customers whose Sales (LCY) value is <>0

Defining a Generic chart on Table object
Defining a Generic chart on Table object

c. Click on OK at the bottom to complete the design of the chart.

d. To view this chart, add it to the Role center. (click on Blue button at the top left –> Customize–> Customize this page–> Chart part–> Add. Select the Blank chart–> Customize part–> Select T-00001–> Click on OK) Now we see the below chart part in the role center:

Generic chart using table  source type
Generic chart using table source type

In the following steps, I have built another generic chart based on Query object (in contrast to using tables as shown previously) to show the Top 5 selling items.

a. In NAV development environment, design a new Query (ex: 50000) to show top 5 selling items and save.

Query Designer and Query Properties
Query Designer and Query Properties

b. Search for Generic charts in the search area of NAV client and open the Generic charts page.

c. Click on New to define a new chart and specify values as shown below.

Defining a Generic chart on Query object
Defining a Generic chart on Query object

d. To view this chart, add it to the Role center. (click on Blue button at the top left –> Customize–> Customize this page–> Chart part–> Add. Select the Blank chart–> Customize part–> Select T-00001–> Click on OK) Now we see the below chart part in the role center:

Generic chart using Query source type
Generic chart using Query source type

More resources on Generic charts: How to: Create Generic Charts, How to: Add Charts to Role Centers and List Places; How Do I: Build a Chart Based upon a Query

3. Business charts  [Go to the top]

Business charts are based on Business Chart control add-in which is provided by the Microsoft.Dynamics.Nav.Client.BusinessChart.dll assembly. The Business Chart control add-in is a Microsoft .NET Framework object that creates the user interface of a chart on page and populates the chart with data from C/AL code. By default the assembly is located in the C:\Program Files (x86)\Microsoft Dynamics NAV\71\RoleTailored Client\Add-ins\BusinessChart folder. As compared to other chart types, they provide advanced functionalities such as:

  • Displaying complex data that is calculated on a page.
  • Allowing end users to filter data and drill down to the details from the chart
  • Being supported on Dynamics NAV Web client.

Out of the box, there are a few business charts such as Trailing sales orders, Cash flow chart, Finance performance, Sales performance, Inventory performance etc.. To design/develop a business chart, one should have experience on C/AL programming. You can explore the existing business charts by adding them to your role center using page designer.

In the Cronus demo database, business charts are available on Order processor, President, Accounting manager and few other role centers. For e.g., below picture shows President’s role center which contains 3 business charts (Finance Performance, Cash flow, Sales Performance). These charts have filtering options as highlighted below:

Business charts
Business charts

Also, it is possible to drill down by clicking on the different areas of the chart:

Business chart - Drilldown
Business chart – Drilldown

More resources on Business charts: Displaying Charts Using the Chart Control Add-in, Cash Flow Chart Example

In my next blog, I will be explaining Business charts in detail.

4. Charts in NAV reports  [Go to the top]

Both Visual Studio Report Designer and the SQL Server Report Builder gives us the option of inserting chart controls in our reports. Customer – Top 10 List is a good example which shows a chart in a report along with report data.

Customer - Top 10 report
Customer – Top 10 report

In the below illustration of the NAV report layout designer, 2 types of charts are defined with values and categories.

Customer top 10 report - Layout design
Customer top 10 report – Layout design

To display a chart in NAV report:

  • Drag and drop the Chart control from the toolbox
  • Add/Change the measures under Chart Data
  • Right click on the chart and select Change chart type to select a different graph.
  • Right click on the chart and select Chart Properties to make changes related to visibility, filtering etc.

 

5. Charts in Excel  [Go to the top]

Dynamics NAV tightly integrates with Microsoft Office Excel. Excel has powerful charting capabilities using which we can analyze Dynamics NAV data. Following are some of the ways to export NAV data to excel:

  • Using Send to Microsoft Excel option on a page or expose NAV page as a ODATA web service and retrieve this data in Excel.
  • Using XMLPorts
  • Using  Query results or expose it as a OData web service and retrieve this data in Excel.

Following video shows how we can view Sales and Profit region wise on a map in Excel using Power View.

How Do I: Consume a Microsoft Dynamics NAV 2013 Query from Excel Power View

Power view report in Excel
Power view report in Excel

 

In my next blog, we will explore Business charts in more detail.

Advertisements

15 thoughts on “Building Charts in Microsoft Dynamics NAV 2013 R2”

  1. Pingback: MSDN Blogs
  2. Hello Suvidha,
    I want to test with charts in NAV 2013 urgently but I have the following message:

    Could not locate the control add-in library for “Microsoft.Dynamics.Nav.Client.BusinessChart;PublicKeyToken=31bf3856ad364e35, The page contains a control add-in that is not permitted. Contact your system administrator”.

    Our Nav Partner does not know why. They converted a german database from 2009 to 2013.
    Can this be due to Licence? Installation contains “C:\Program Files\Microsoft Dynamics NAV\70\Service\BusinessChart ”
    Can you help me? Thanks in advance

    Jo Merckx

    Like

    1. Hi Jo, Please check the table 2000000069 (Client Add-in). The table should contain Control Add-n: Microsoft.Dynamics.Nav.Client.BusinessChart. Also, check if the Token key is 31bf3856ad364e35.

      If not, In the Business Chart Page, on the chart field properties, check for ControlAddIn property.
      You can try to Delete the existing value and re-select the controlAddIn property from the look up. Compile and try again! Good Luck 🙂

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s