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

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

Microsoft Dynamics CRM Online can be intimidating to new users. It is such a powerful platform with a plethora of features that getting newcomers to adopt it can sometimes be an uphill battle.

Collaboration is a key ally for battling this resistance. Getting every single user to a high level of proficiency with the system immediately is unrealistic. However, getting a smaller number of users to a higher skill level, let’s call them “power users”, can be extremely beneficial.

Power Users Rule

In the early phases of training, we at xRM to emphasize simple shortcuts and features that will make a user’s life a little easier. Power users can make use of these shortcuts and lesser-known features, share them with their coworkers, all without ever needing administrative credentials. This helps those end users who may be reluctant to adopt CRM Online to see how much time they can save and can even get them excited about working with the system.

One of the features we love is the Share charm that can be found in a couple of different places. We’ll look at how to use the Share feature in a sales pipeline first.

Making Sense of the Noise

Oftentimes a complete list of your CRM data can be overwhelming. How do we know which opportunities to target without opening each one?

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

That’s precisely why CRM Online has features such as views and charts—they help to cut through the noise. But we don’t need to rely on all of our end users to know how to create their own views and charts right off the bat. A power user can create and share them with the necessary team members.

Step 1

We’ll start by creating a new view using Advanced Find. It should be noted that a system administrator could create a system view that is available to all users that have access to the given entity, opportunities in this example. However, sharing personal views can be done by anyone. Advanced Find can be found by clicking the button to the right of the global search feature in the top-right corner of the browser.

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

Step 2

Once the Advanced Find window opens, we specify the criteria for our view. We want something that helps users prioritize top opportunities that are expected to close in the near future. Below is an example of a view that filters opportunities down to open records with an estimated close date in the next three weeks, with an estimated revenue equal to or greater than $10,000. Now, it’s time to share this view with a team member. We can click on the Saved Views button in the ribbon of the Advanced Find window.

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

Step 3

We should then see a list of all of our shared views. We can select the one we’d like to share, and then click the Share button in the ribbon.

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

Step 4

The Share saved view button opens. Just click on Add User/Team and search for users or teams to share this view with, and then specify the permissions we want to grant them. In this example we are sharing our view with Jane Doe, and we’re giving her permissions to open and share this view with other users. We can share this view with multiple users or teams at once if we so choose.

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

Step 5

Once the Share button is clicked, the user will find the view listed in their personal views. Just click the dropdown arrow on your View and scroll to the bottom section of “My Views”.

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

Share Charts Too

Sharing charts works very similarly. Notice below that I’ve already created a personal chart called, “Est. Revenue by Est. Close Date”. It’s broken down by opportunity rating. It can be accessed by clicking on the chart bar on the far right side of the browser window. We can click the More Commands button, which looks like an ellipsis (three dots), and then click the Share button.

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

We are then given the same ability to choose which users and teams we’d like to share this chart with, and which permissions we’d like to give them.

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

Views and Charts Help You Zero in on What Matters

Once we’ve shared these tools, users can easily cut through the noise of the data and zero in on the opportunities that they should prioritize. This is how we help users see the value of CRM Online, and drive user adoption.

If you would like to learn more tips, tricks, and tutorials for Microsoft Dynamics CRM, please check out our xRM blog and our Success Portal, a library of over 400 educational Dynamics CRM videos.

Other Helpful Articles

Read More