Have you ever wished to have an Excel spreadsheet that pulls data from CRM every time you open it? In Microsoft Dynamics CRM, this is possible through: a) CRM Outlook Client, b)Excel CRM Add-In and c) a series of easy configuration steps.

excel dynamic reporting

Prerequisites

In order to use Dynamics CRM reports, you need the following:

Computer setup to run Dynamic reports – This can be a client desktop, a laptop, or an RDC server.

The CRM must be either: ADFS (Active Directory Federation Service) install, or if non-ADFS then all clients MUST be on the same AD (Active Directory) as the CRM server.

Dynamic reports will not work on browser-only access (i.e. phone, iPad).

Computer Requirements that will run the dynamic reports:

  • MUST have CRM Outlook Client installed (and to install Outlook Client, you MUST have Outlook installed)
  • Therefore, Outlook must be configured with a valid email account (requirement).
  • Microsoft Excel must be installed (note:  Excel needs to be installed before CRM Outlook Client, otherwise the Excel Add-in will be missing).

Once the above are installed, then configuration must include:

  • Outlook running with CRM Outlook Client. (note: once Dynamic Reports are working, you can disable the CRM Outlook Client Add-in and the dynamic reports will still work)
  • Automatic login – in Dynamic reports you will not need to sign-in if you have setup login credentials so that when you open a browser and navigate to CRM, you are automatically logged in.
  • Excel – make sure the CRM Add-in is running.

The above will ensure that you can run dynamic reports.

The following shows pre-requisite setup:

Outlook Client

Download Outlook client (32 bit or 64 bit) – this MUST match the same version as your Office install.  If you have a 64 bit operating system, but installed 32 bit office, then you MUST install outlook client 32 bit version.

Download URL: http://www.microsoft.com/en-ca/download/details.aspx?id=45015

Once the client is installed, then setup the connection using CRM Configuration Wizard.

dynamics crm outlook configuration wizard

 

When starting CRM from a browser, you should automatically log in. This is typically accomplished by setting up login in the “Credential Manager” in Control Panel:

user accounts and family safety

 

credential manager

 

Then, when opening up a browser and navigating to your CRM URL, you should automatically login.

Excel

When opening Excel, there is the CRM Add-in that is visible on the Data tab.

excel dynamics crm data tab

 

Click Refresh from CRM, and the data should appear. If you are asked to provide login credentials or you get an error, click Connections and confirm that the connection information is correct:

dynamics crm workbook connections

 

Double-click the connection to get connection properties.

connection properties

 

Pressing OK will normally refresh the connection and result in data being populated.

Outlook Client: Note the outlook client can be enabled or disabled and the dynamic reports will still work.

How to enable or disable the outlook client in Outlook

In Outlook, go to File and then Options.

outlook excel connection

outlook excel connection 2

Go to Add-Ins and select Manage COM Add-ins, then click Go.

outlook options

Select the two Microsoft Dynamics CRM add-ins and then press “OK”

outlook options 2

 

Excel and CRM provide some really powerful ways of displaying reporting data through the steps outlined above. Rows of data can be exported to a worksheet which you can graph on and are both refreshed in real-time every time you open the worksheet. If you intend to send these instructions to your client, it might be very daunting at first but the rewards are worth it.