Excel Templates: Another Powerful Reporting Tool in Microsoft Dynamics 365
Last year, I shared another blog entry called, “Dynamics CRM 2016 Online Reporting: Breaking down the Fine Print." At that time I had not had the chance to work with Excel Templates— a super attractive feature for Excel lovers. This feature was introduced in the Dynamics CRM 2016 Update (Online/On Premises). Here we will explore Excel Templates as another reporting capability in Microsoft Dynamics 365.
Excel templates can use Dynamics 365 as a data source and perform calculations, pivot tables, pivot charts, macros and many other Excel operations. You may be wondering, can’t you just do the same thing with Dynamic worksheets? Yes, you can! However, there’s a big difference that you can take advantage of; Excel templates are hosted on the Dynamics 365 platform. That means these Excel templates can be shared and used across your organization— forget that folder share solution on your intranet.
- Easy steps to configure
- Control access to each template created in Dynamics 365
- Refresh data manually or automatically
- Excellent alternative tool for reporting
The Fine Print:
- You can visualize the template data in Excel Online, but you need a Dynamics 365 online version. This option is not available for on-premises versions.
- Adding logo images can cause errors.
- If you work with Dynamics 365 on-premise, you will need to install the Dynamics 365 for Outlook plug-in to refresh data correctly.
- When editing the template, if you would like to update/remove a field in the data source, it won’t work.
Sometimes it is easy to re-create everything, but if your template is complex, there is a quick workaround. Open the Excel template, press ALT + F11 (Visual Basic Mode). Select hiddenDataSheet and change the Visible property as -1 xlSheetVisible.
The hiddenSheet will be displayed and you will see the Dynamics 365 query along with OptionSets values.
It’s a bit hard to read, but I recommend to use Notepad++ for searching the attribute you want to update or remove. This should work for adding new values as well.
Don’t forget to type ALT+F11 again and backup visibility changes made on the HiddenSheet. Save the Excel template file.
Sometimes the Refresh data option does not work as expected. Here’s one trick that might work for you:
Excel Options -> Trust Center
I recommend this feature to empower your customers and end users. Unlike SSRS you do not need programming skills to pull data from Dynamics 365 and you can achieve even more than Wizard reports. Your clients will thank you for it.
Want to learn more? Feel free to let us know by submitting the contact form below, we’d love to hear from you!
Check Out the Microsoft Walkthrough here.