YES! You CAN maintain multiple CRM entity views in a single Dynamic Excel Workbook!
Nick Elders, one of my favorite clients who works for Microsoft Dynamics CRM 4.0 user Community Reinvestment Fund, USA taught me something awesome today.
I was under the impression that you needed to create ODBC data connections to Microsoft Dynamics CRM filtered views manually in Microsoft Office Excel to create links to multiple Microsoft Dynamics CRM views in a singe dynamic Excel file on multiple worksheets (tabs). Not something you’re average CRM end user is equipped to do.
But Nick proved me wrong. And it’s so simple. If you know how to export to Excel from Dynamics CRM and if you know how to copy and paste data, then you can do it too!
First, what is a “Dynamic Excel Workbook”?
When you export Dynamics CRM data to Microsoft Excel, you have the option of exporting a STATIC worksheet or DYNAMIC worksheet. Exporting a static worksheet means that it’s a point in time snapshot of the data from CRM. A dynamic worksheet becomes an Excel file that refreshes the data from the Microsoft Dynamics CRM database each time it’s opened.
Furthermore, if you export a dynamic worksheet (or PivotTable) that you think will be useful to other Microsoft Dynamics CRM users, you can add the list as a Report in CRM, and then share it with others, or make it available to all users.
And, if the recipients are in the same domain as you, and are also Microsoft Dynamics CRM users, you can e-mail a dynamic Excel file, or store it as a shared file. When recipients open the dynamic file, they will see only the data they have permission to view in Microsoft Dynamics CRM (based on their security role), so the data they see may be different from what you see. But you’re all accessing the same dynamic Excel workbook!
So, how do you do it?
It’s simple:
1) Export View 1 as a “dynamic worksheet” to an Excel workbook.
2) Add a new sheet to the workbook created in step 1.
3) Export View 2 (from the same or a different entity) as a “dynamic worksheet” into a new Excel workbook.
4) Copy all rows and columns from View 2’s worksheet to your “clipboard”
5) Paste the contents of the “clipboard” (the copied rows and columns from View 2’s worksheet) into View 1’s new sheet, created in step 2.
Note: When you’re opening with the Microsoft Office Excel file:
If you are using Excel 2003, click Enable automatic refresh when prompted.
If you are using Excel 2007, you get a message that says the file you are trying open is in a different format than specified by the file extension. Click Yes. Then if you get the security warning Data connections have been disabled, click Options, and then click Enable this content, and then click OK.