Chapter at a Glance
In this chapter, you will learn to | |
✓ | Export static data to Excel worksheets. |
✓ | Export dynamic data to Excel worksheets. |
✓ | Export dynamic data to Excel PivotTables. |
✓ | Upload Excel reports to the Reports list in Microsoft Dynamics CRM. |
Microsoft Dynamics CRM provides several options for data reporting. The combination of Advanced Find views and the SQL Server Reporting Services Report Wizard make for a powerful suite of reporting tools. Microsoft Dynamics CRM provides an additional reporting option that many users like to use: you can export data to Microsoft Office Excel. The ability to export your Microsoft Dynamics CRM data to Excel within the Microsoft Dynamics CRM interface allows you to export data into a static worksheet, dynamic worksheet, or dynamic PivotTable for further analysis and reporting. By choosing a dynamic export option, you can ensure that the Excel file maintains a live connection to the Microsoft Dynamics CRM database, allowing you to refresh the data from within Excel. Consider how useful this functionality would be in the following scenarios:
You have an Advanced Find view that you export weekly to print for a meeting. You can export to a dynamic file one time, format the report to your liking, and open it up from a saved location to get the most recent data.
You have a PivotTable report that you use to view aggregated data. You can set up the PivotTable one time and reuse it as needed.
When you export data to Excel, Microsoft Dynamics CRM security settings apply: you can only export those records to which you have access in Microsoft Dynamics CRM.
In this chapter, you will learn how to create static and dynamic Excel reports. You will also learn how to create PivotTable reports that use data from Microsoft Dynamics CRM. Finally, you will learn how to upload an Excel report into the Reports area of Microsoft Dynamics CRM to share your Excel report with other users.
Troubleshooting
Graphics and operating system–related instructions in this book reflect the Windows Vista user interface. If your computer is running Windows XP and you experience trouble following the instructions as written, refer to the "Information for Readers Running Windows XP" section at the beginning of this book.
Important
The images used in this book reflect the default form and field names in Microsoft Dynamics CRM. Because the software offers extensive customization capabilities, it’s possible that some of the record types or fields have been relabeled in your Microsoft Dynamics CRM environment. If you cannot find the forms, fields, or security roles referenced in this book, contact your system administrator for assistance.
Important
You must know the location of your Microsoft Dynamics CRM Web site to work the exercises in this book. Check with your system administrator to verify the Web address if you don’t know it.
Important
The ability to export data to Excel is configurable at the user level. If you cannot see the export buttons and options referred to in this chapter, contact your system administrator for assistance.
Excel is a tool that most people in a traditional business environment are familiar with and use in some capacity. With Excel, you can organize, format, and analyze data. Many business applications give the end user the ability to export or download record-level data into Excel, and Microsoft Dynamics CRM is no exception.
It is very easy to export a list of records into Excel. If you have been using Microsoft Dynamics CRM for some time, you have probably already used this feature to export data to Excel.
For a simple, one-time report, you can export data from any grid in Microsoft Dynamics CRM in a static worksheet. The worksheet is described as static because the data will not be updated in Excel if it is changed in Microsoft Dynamics CRM after it is exported. A static data export reflects a point-in-time snapshot of a set of records in Microsoft Dynamics CRM.
When you export static data into Excel, the data is exported exactly as it appears in the Microsoft Dynamics CRM grid. The exported worksheet includes the fields that are displayed in the grid, using the same field order, sorting, and field widths. You can export most data grids into Excel, including the results of an Advanced Find.
Later in this chapter, you will learn to establish a live link with your Microsoft Dynamics CRM application by exporting dynamic data, which allows you to continually analyze your business data within Excel.
In this exercise, you will export a static Microsoft Dynamics CRM data view into Microsoft Office Excel.
Note
USE your own Microsoft Dynamics CRM installation in place of the Adventure Works Cycle site shown in this exercise.
BE SURE TO use the Windows Internet Explorer Web browser to navigate to your Microsoft Dynamics CRM Web site before beginning this exercise.
In the View field, select Active Accounts.
The data grid updates to display a list of active accounts.
On the grid toolbar, click the Export to Excel button.
Tip
The Export To Excel button is available on most grids within Microsoft Dynamics CRM. You can export system-related information such as lists of reports or data imports, in addition to lists of core records such as accounts, contacts, and opportunities.
The Export Data To Excel dialog box opens.
Leave Static worksheet with records from this page selected, and click the Export button.
The File Download dialog box opens.
Click Open to launch Excel and open the export file. Alternatively, you can also click Save if you want to save the Excel file to your computer.
Important
If you are using Excel 2007, you might get a warning indicating that the file you are trying to open is in a different format than specified by the file extension. Click Yes to proceed with the export.
The first page of active account records has now been downloaded to Excel. You can format, modify, and analyze the data in Excel to meet your needs without affecting your Microsoft Dynamics CRM database.
Tip
If the records in the Active Accounts view span multiple pages, you will be presented with two options in step 4:
Static Worksheet With Records From This Page
Static Worksheet With Records From All Pages In The Current View
These options allow you to choose whether to return all records from the view or just the records on the first page of the view results.
Important
If you do not have Excel installed on your computer, the file will not open. Contact your system administrator if you do not have Excel. Alternatively, Microsoft provides a tool called Excel Viewer, which allows you to open Excel files for viewing. Excel Viewer can be found at the Microsoft Download Center site: http://www.microsoft.com/downloads.