Export to Excel

While we covered automated integration with Excel through templates last week, the user will need to create ad-hoc reports or analyse data in a new way than planned.

In this step, we will explore exporting data for the use in Excel.

The integration between model-driven apps and Microsoft Excel is a favourite feature for many users. Excel is one of the most popular data analysis tools for business users, and the ease of exporting Common Data Service (CDS) data to Excel is a significant benefit for users at all levels of an organisation.

In some cases, it may be necessary to share CDS data with individuals or organizations that do not have a Power platform license. Export to Excel functionality is a great tool to support these scenarios.

Static and Dynamic Worksheets

Most views can be exported as static or dynamic worksheets. Exporting a static worksheet creates a local copy of the data. No connection is maintained between the CDS environment and the workbook, and the CDS security model no longer regulates access to the data.

An exported dynamic workbook or pivot table maintains a link between the CDS data and the Excel file. The CDS security model is maintained, and users need appropriate privileges to refresh the data.

An additional export option is exporting to Excel Online. This option opens the Excel file for the user in a window within Dynamics 365 or custom model-driven apps. Users with appropriate permissions can save data changes back to CDS.

Security

The ability to export is controlled by security roles. The privilege is found on the business management tab in the privacy-related privilege tabs. It is an all or nothing privilege. If granted, a user can export any data they have the security rights to view.

Once exported, the Excel file is outside the CDS security model, and anyone with access to the file can view all of the data. As with any file containing sensitive data, it is vital that documents are handled in a way that respects the organisation’s data privacy rules.

Records per Page

By default, views will only display 50 records per page. You can increase the number of records displayed on the general tab of the personal options menu.

Considerations

• If you are planning to reimport the data after making changes in Excel keep in mind that you won’t be able to import changes to composite fields such as the full name field or calculated and rollup fields. In the case of composite fields, you can update the individual fields such as first name and last name and the composite will be updated after the import.

• To view and refresh dynamic data, Microsoft Dynamics 365 for Outlook must be installed. If it is already installed and configured, click refresh from CRM to sign in to Dynamics 365 for customer engagement. If you do not want to be prompted again to sign in, click save my email address and password in the sign-in page.

• Some system views, such as accounts: no campaign activities in the last 3 months, can be exported only to a static Excel worksheet.