Dynamics 365 & Office Integration Series - PART 3 - Excel Integration

This article series is about integration practices between Dynamics 365 and office apps. Integrations have been explained in 4 posts:

PART 1: Odata Queries Through Dynamics 365
PART 2: Connect to an OData feed through Get & Transform in Excel 2016
PART 3: Excel Integration
PART 4: Data Import/Export by Considering Your Business Purpose

Let's start PART 3

CONTENTS

Introduction
Export to Excel
Open In Excel
Excel data connector add-in
Data import/update
Filtering
Time tracking - Performance troubleshooting
Off-line working
Workbook designer
Adding another table
Automatic demo data generation
Adding a formula column

INTRODUCTION
This article provides a comprehensive explanation for some of excel functions in Dynamics 365. All functions has been illustrated clearly.

You can also reach previous articles via the following links.
Dynamics 365 & Office Integration Series - PART 1 - Odata Queries Through Dynamics 365
Dynamics 365 & Office Integration Series - PART 2 - Connect to an OData feed through Get & Transform in Excel 2016

You can also find different approaches in following article regarding excel usage for data import/export.
Dynamics 365 Data Import/Export by Considering Your Business Purpose

EXPORT TO EXCEL
Also known as Static Export to excel. Just gets data out to excel - a quick mechanism to get data in grids on a form. This is the standard mechanism for triggering the export to Excel in Dynamics AX. It is listed in number of places. Such Open in Microsoft Office menu, right-click context menu.

You will see a dialog box as follows after you select one of "Export to excel/Export all rows/Export marked rows" options.

In addition to your desktop, you can save your files to OneDrive for Business or SharePoint for now. Other options like Drop Box will be available in the future.

Exported data will be shown as follows. This provides a quick mechanism to get data in grids on a form. This is the standard mechanism for triggering the export to Excel in Dynamics AX using the open in Microsoft Office menu on the form.

OPEN IN EXCEL

Instead of static data, the idea is opening an excel in a dynamic dataset. In this case, you will be able to refresh and publish the data.

Open in Excel button option can be reached via "Open in Microsoft Office Menu" button.

Open in Excel options can be
• Automatically generated for an entity that shares the same root datasource as the current form.
• Automatically added for a template that shares the same root datasource as the current form.
• Programmatically added for an Entity or Template using the Excel Export API

Excel Data Connector Add-in
The Excel Data Connector app interacts with Excel workbooks and OData services that are created for publicly exposed data entities.

This app is built using the new apps for Office framework, and it provides a JavaScript-based Web API for apps to communicate with Office applications. And the biggest advantage to this new framework is that the apps can run inside of Excel on-premise for Windows in addition to Excel Online and Office 365 as well as Excel on the iPad. And other Excel apps will be supported in the future. So this app leverages OData feeds based on underlying data entities and allows inserting, updating, and deleting data so users have the ability to publish data back to AX.

Data Import/Update

You can add a new line or change the information and publish it by using task pane buttons. Let's do that in fleet customers form.

Insert the line.

Publish the document.

Customer information directly goes to customer form as you see below.

Filtering

In addition to regular excel filtering, you will be able to filter your data by using task pane filter portion - excel add-in filter. The difference between regular filter and excel add-in filter is the excel add-in filters at the Odata service level and pulls back list of data.

Click on the Filter icon on task pane.

Enter your filtering criteria and click on done.

Time Tracking - Performance Troubleshooting

By clicking on the message center icon, you can see the exact publish time of records.

In message center you can see the timing of all of those operations occurring throughout the system. This may help you to detect delays and take necessary actions.

Off-line Working

In case of device connection or going offline, original data information is kept in data_cache sheet. This allows users to make changes and save the workbook. You can go offline and do whatever you want, come back and open up the workbook again. Your changes will still be there so that you can publish them at a later time. In previous versions of new Dynamics AX those changings were kept in memory but with Dynamics 365, now changings are kept in the workbook. Thus, Dynamics 365 became more convenient for mobile devices in case of connection lost.

Workbook Designer

You can use the Workbook Designer page to design an editable custom export workbook that contains an entity and a set of fields. To open the Workbook Designer page, click Common > Common > Office Integration > Excel workbook designer.

It is a nice way to scroll through and look at different entities in the system and get workbooks that are able to be used in excel.
It also has a nice future in term of "view related form". If you are looking at these entities and you see an entity you interested in. Then you pull data out to excel. You can use `view related form`s button to see the form that is related to entity.

Use the common search and open excel workbook designer by typing "workbook" or navigate to Common > Common > Office integration > Excel workbook designer.

On the left hand side, you can see available entities and related fields.

Use filter bar to select an entity. Here, we will select FleetCustomers. 

Send all fields to "selected fields" portion.

Now it is time to create workbook by clicking on "Create workbook" button.

Download the created workbook.

Click on design icon on task pane.

Design button will take you to design experience.

Click on the edit icon in design experience.

You can remove the filed that you are not interested in.

Click on update button after you finish field removing.

System will show you the last appearance of the form.

Adding another table

You can also add a new table by adding a new sheet.

Click on Add table icon.

Select the table that you want to add.

Type the table name. Excel can easily find it. (There might be a filter future in one of next versions)

Let's select vendors.

Click on NEXT button.

Select the fields that you want to add (CTRL + A combination works here) and hit the add button.

System will ask you to refresh the table like it does in 2012 excel add-in version.

Close the design experience by clicking on DONE.

Refresh the form.

An informative warning pops up and says that current data in the workbook will be overwritten.

You can see the vendors as below.

When you change the fields you will see that task pane shows different filter options correspondingly with each data type.

Automatic demo data generation

Following demonstration requires to create demo data. You can create demo data as follows.

Fleet management > Setup > Fleet setup

Adding a formula column

Back to the topic, If you want to add a formula column, open design experience by clicking on design.

Click on the edit icon to edit datasource.

Select formula icon to add a field to be used for formula.

Change the label of the field. For our example, change it to Monthly Rent.

Monthly rent formula should be = 20 business days x daily rent.

Click Done and refresh the form.

Click HERE to read the full post.