In this article we are going to have a look at some opportunities for automation between Microsoft Dynamics NAV and Microsoft Excel. The goal is to create a visual representation of transactional data stored in Microsoft Dynamics NAV ledgers. However, this example can be utilized in analyzing many other Dynamics NAV data types like budgets, job time tracking, and production measures.
In our demo, we are going to use Microsoft Excel 2013 and Dynamics NAV 2013. The chart that we are going to build will represent an analysis of item quantities by location (and in order to simplify the chart, we will use 3 locations).
The solution revolves around the creation of a codeunit, which will be run directly to test our work. However, in real-time development, this codeunit should be incorporated into a relevant user-visible object, such as a page or Role Center.
- Microsoft Dynamics NAV 2013
- Microsoft Excel 2013
Creating a codeunit
Adding the code
Pushing data to Excel
Creating the chart
Open the Dynamics NAV 2013 Development Environment, and select the codeunit object type in the Object Designer. Create a new codeunit called Excel Automation Graph. In the codeunit design mode, click View, C/AL Globals. We will need to add some variables for our solution:
Note that the Object Library variable version may differ depending on the version of Excel you are using. In this example, we have added an Item Ledger Entry variable and a set of variables required for making our integration with Excel work.
Close the C/AL Globals window.
After the variables are defined, we can start writing the necessary code to implement the chart functionality. In the OnRun() trigger, type the following code:
This part opens an instance of Excel and adds a new workbook to it. The Add(-4167) parameter is responsible for choosing the necessary workbook template which is going to be created.
Now we can write code to bring our data to the chart. To do this, type the following code after creating our Excel instance:
This part is setting the necessary key on the Item Ledger Entry table. This key has the Quantity field defined as a SumIndex field, and is necessary to run the CALCSUMS function on quantities.
Next, we are setting a filter per location code (in this example, we hardcoded the location names as they appear in the company) and calculating the ‘sub-quantities’ for each location. Finally, we are adding a name and a value to the range of cells in Excel. This code is repeated for 3 locations: blue, green and red.
The last step would be to create a graph based on the data we have pushed to Excel from NAV. To do this, type the following code next:
This part specifies the cell range which our chart will use. The chart itself is then created and given a name. The ChartWizard method contains a number of parameters, for example ExcelRange defined as -4102 corresponds to a Pie Chart.
To find out more details about the parameters, consult Excel Help on the MSDN Library.
Next, we need to make our Excel application visible to the user. To do this, type the following code:
Click File and Save the codeunit. In order to test the solution, right click the now saved codeunit and select Run. A chart similar to this result should appear:
This simple example shows how easy it is to being NAV data into Excel for further processing. Such a capability may be useful for employees or management to easily prepare monthly data analysis and reporting. Use your imagination! If you have an idea yourself, please leave it in the comments.