Once a dynamic list has been exported, you can manipulate it however you wish. You can add columns, delete columns, add graphs, add extra worksheets etc. Essentially everything you can do to a normal Excel spreadsheet you can do with a dynamic list. Please note the default format when CRM exports is OpenXML, not xls. Therefore to do things like add graphs you will need to save the export file into an xls/xlsx-type format.
While this is powerful, you can go further. The major limit of the above is that you can only bring into Excel what you can define in an Advanced Find in Dynamics CRM. Plenty of queries you can write in Excel, you cannot write through Dynamics CRM's Advanced Find. For example, if you want to aggregate data from CRM and list it e.g. show the number of open cases per account, you cannot do this through the Advanced Find interface.
So how can we 'hack' the query? Its actually pretty easy.
- Right-click cell A2 and select Edit Query...
- Click the 'OK' on the warning you can't use the query wizard
- Click the 'SQL' button on the Microsoft Query box that pops up
And here it is, the power of SQL at your command. The first thing you might want to do is remove the 'top 10000' after the select. This will stop only 10000 records coming back.
Please Note: This only works for on-premise as IFD does not use SQL in its Excel queries.
Once you've adjusted your SQL, click 'OK' and click the 'X' in the top right corner of the Microsoft Query window. The query will now be run and will populate the spreadsheet. One thing to watch out is if you've added extra columns to the query as these will appear in the spreadsheet and obliterate anything already in there.
The only real trickiness is with things like working out the correct name of the attributes, working out the right value for picklists and the correct names of the tables.
The names of the attributes, you can get by going to Settings-Customization-Customize Entities, selecting the entity in question and opening up the attributes. The name you want is the one in the 'name' column.
The right value for picklists is a little trickier, especially for entity status values. While you can figure most of them out by going to attributes and editing picklist entries to get the 'value', the easiest way I find is to do a new Advanced Find query, using the picklist value as a condition in the query, clicking the find button, exporting to another dynamic excel spreadsheet and examining the query.
For tables, you should only refer to the Filtered Views. This ensures security is preserved in terms of the records the spreadsheet user is allowed to access from CRM. It also ensures a supported solution i.e. the spreadsheet query should survive future upgrades of CRM.
For the most part the view name is of the form 'FilteredEntityName'. There are a few exceptions such as for the Case entity (FilteredIncident) and Notes (FilteredAnnotation). If in doubt, open up SQL Server Management Studio and have a browse.
And there it is, the ability to write SQL queries against CRM without all that fiddling around in Visual Studio. In my opinion the use of Excel for queries instead of SSRS also fits in with Microsoft's aim of giving the power to configure to the end user. There are few end users who are brave enough to tackle Visual Studio but there are plenty that will play with Excel and most companies have at least one person that can write basic SQL SELECT queries.
One final note, Excel does not submit the SQL query in a secure way so, in principle, if someone was on your network sniffing the packets they could access the data being returned. To learn how to tighten the security of Excel queries, read the following blog post:
1 comment:
Awesome, this works brilliantly :)
Post a Comment