Powerful Reporting with Excel Templates

Great news for you analysts and visualization gurus out there! With the recent release of Microsoft Dynamics CRM Online 2016, it has never been easier to deep dive into your data thanks to the new Document Generation feature. As expert Microsoft Dynamics CRM consultants, we at xRM can’t wait to help our clients leverage Document Generation to unearth valuable KPI’s in a robust reporting tool like Microsoft Excel.

Excel, Only Better!

Document Generation enhances the Excel Online integration that was introduced with CRM Online 2015 Update 1. Basically, it allows users to upload a templated version of an Excel report to CRM Online. Users can then open the template which is populated with up-to-date data, giving users deep insights into their business at the click of a button. Let’s see how it works.

1.   Preparing the Template

The first thing you need to do is set up a baseline view in CRM Online. In this example, I’ve created a view made up of sample data from an email campaign that captured metrics such as opens, clicks, and unsubscribes. I then click the Excel Templates button in the command bar, and then click Create Excel Template.

Powerful Reporting with Excel Templates

2.   The Create template from CRM data window opens. Notice that CRM Online gives us the option to filter by entities and to choose a previously saved view. Since I’m already at the view I want to use, I’ll leave the defaults selected and then click Download file.

Powerful Reporting with Excel Templates

An Excel file is then downloaded locally to my machine, containing the columns and data from the view. I can then design a report using Excel tools.

Good for Newbies and Experts Alike

If you are already an Excel wizard, try applying some of your favorite analytical tools to the data. If you’re new to this, I recommend some analyses that are difficult or impossible using the out-of-the-box reporting tools of CRM Online such as totaling sums and averages, conditional formatting, sparklines, and PivotTables. Don’t forget, you can create multiple sheets in your Excel template.

3.   Upload the Template

One you’ve prepared your report, save it locally. Back in CRM Online, navigate to the Create template from CRM data window. This time however, click the Upload button. Drag the Excel file into the upload box that appears. A confirmation window then appears and you’re good to go.

4.   Run the “Report”

Navigate back to the entity where you want to run the report. This time around, you’ll see your uploaded template which you can then open right in Excel Online, or you can download a copy of the latest data.

Powerful Reporting with Excel Templates

Here’s an example of a report I designed and can access right from the browser. In the first sheet I broke down the data with conditional formatting, something that cannot be done in CRM Online natively.

Powerful Reporting with Excel Templates

On a separate sheet, I have set up a simple pivot table and chart that breaks down the average number of clicks, opens, and bounces per campaign.

Powerful Reporting with Excel Templates

Create for One, or Create for All

These templates can be set up for personal use, or administrators can create templates for all users to consume.

You can let your imagination run wild with the reporting possibilities when a powerful database like Microsoft Dynamics CRM has such seamless integration with an analytical tool like Excel.

Want More Dynamics CRM Tips, Tricks and Tutorials?

Check out our xRM blog and our Success Portal, a library of over 400 free educational Dynamics CRM videos.

Other Helpful Articles

Quick Admin Tip: Share Charts and Views in Microsoft Dynamics CRM Online


Read More

Editing Multiple Records in CRM Online with Excel Online

Editing multiple records in Microsoft Dynamics CRM at once hasn’t always been the easiest of tasks. It’s a common request of our developers at xRM (www.xrm.com) to find ways to update existing data without third-party solutions. Bulk editing has always been supported, but every included record had to take the same new value. If you wanted to make non-uniform changes, this required exporting records to an Excel spreadsheet that was capable of being reimported back into the system using a template. Previously, the template was often finicky, making the process frustrating at times. Fortunately, CRM Online Update 1 introduced a welcome new feature: Open in Excel Online.

This feature is often billed as a means of performing ad-hoc analysis—take a look at this Microsoft Dynamics CRM Help & Training page—rather than an editable grid. Clients of ours at xRM oftentimes don’t even notice the feature, let alone know it can be used an import tool. However, Open in Excel Online can be a tremendous timesaver. The key is to know how to use it. Please note that this task requires an Office 365 license.

Let’s use an example of updating Account records in CRM Online. We have a set of Accounts that have the state in their address entered inconsistently or incorrectly. This can lead to inaccurate reporting and unreliable search results, among other issues.

Editing Multiple Records in CRM Online with Excel Online

If we want to update these we can click the Export to Excel button in the command bar, and then click Open in Excel Online. However, if we want to make other modifications to the data we need to make sure that all of the fields that need to be modified are represented as column headings in the view. For example, if we want to edit the cities and change ownership of these accounts we need to add Address 1: City and Owner as columns before exporting to Excel Online. This can be easily done by using the Advanced Find feature, found in the upper, right-hand corner of the browser.

Editing Multiple Records in CRM Online with Excel Online

In the Advanced Find window, we click Edit Columns in the ribbon. In the Edit Columns window that opens, we click Add Columns, select the fields we want to update, and then click the OK button. We should see a preview of the columns you have selected and click OK once again. Back at the Advanced Find window, we must be sure to save our changes before closing the window.

We should now be back at our view with the newly added columns. If they aren’t visible, we can always refresh the web browser and the changes should take effect. We are now ready to click Export to Excel, and then select Open in Excel Online. The browser will refresh, and the CRM records will be rendered as rows in an Excel spreadsheet. We can now update the data inline as if we were updating any normal Excel data, by clicking a cell and typing. We can even use Find and Replace, Copy and Paste, or other Excel editing functions as needed. As we update records, we want to be sure that we are following the data type of the respective fields. For example, we don’t want to enter “Fifty Dollars” into a field that tracks currency. The correct entry in that example would be “$50.00”.

Editing Multiple Records in CRM Online with Excel Online

As you can see in the image above, we’ve updated our records and even assigned some accounts a new owner. For the reassignment to be successful, it’s important for the name in the Owner column to match the exact spelling as the name of the User record in CRM Online. I know that Liz’s name is spelled correctly, but if I enter “Elizabeth Smith,” the reassignment will not be successful. The same goes for any lookup reference when using Excel Online to update records.

The data is ready, so we can click the Save Changes to CRM button. We should see a notification like the image below:

Editing Multiple Records in CRM Online with Excel Online

We can click the Close button and return to the CRM Online interface. The changes will not happen immediately. They are brought in as an import, so it will take a minute or two depending on the size of the update.

Editing Multiple Records in CRM Online with Excel Online

After we give it a moment, and then refresh the View, we see that the data was successfully updated.

You can learn more tips, tricks, and tutorials for Microsoft Dynamics CRM, please check out our xRM blog and our Success Portal. If you would like to receive training from our team of experts, please inquire about our QuickStart Training.

Read More