The FetchXML method of report development is now
the recommended way to write custom SSRS reports
for CRM Online and of course it also works for On
Premise as well. To get started there are two
prerequisite packages to install:

SQL Server 2008 R2 Business Intelligence
Development Studio (BIDS). Installing BIDS will also
install Visual Studio 2008 stripped-down version for
developing SSRS reports.

Microsoft Dynamics CRM 2011 Fetch Authoring
Extension for BIDS. This provides the to add
Microsoft Dynamics CRM Fetch data source in SSRS
reports. To configure this data source for your report
you need to provide CRM Server URL, Organization
Name and Windows Live Credentials or
Domain\Login.

Once the environment is prepared you can start
creating new reports. For this example I will start in
CRM by building my report query in Advanced Find,
then generating the FetchXML code that will be used
in the report definition file.

CRM 2011 makes it easy to generate FetchXML by
providing a button on the toolbar within Advanced
Find.

So you can use the familiar Advanced Find tool to
build your report query and generate the FetchXML
code. In the following example I will create a new
Case report based on a simple query for Cases that
were created in the last 7 days, where the
country =’United Kingdom’:

After verifying the query is returning what I want
by viewing the ‘Results’, I select ‘Download Fetch
XML’. The results are saved to a file, the contents
of which look like this in notepad:

Looking through the xml you can see the entity,
the attributes, the order and the filter conditions,
all of which are set through Advanced Find. The
next step will be to copy/paste this code into a new
report project in BIDS. The following steps illustrate
creating the report project:

1)      Launch the BIDS tool and select
File | New | Project – then select
Report Server Project:

Provide a name for the report and ‘Save’. The
report will now appear as a project.

2)      Highlight the report under ‘recent reports’
and right-click on ‘Reports’ in Solution
Explorer:

3)      Select Add New Report and the New Report
Wizard will launch:

4)      Clicking ‘next’ will take us to the DataSource
page (see below image). This is where we
setup the connection to the CRM Server:

- Choose ‘New data source’ (shared data
sources won’t work after uploading to
CRM server)

- Name the DataSource whatever you like

- Make sure the Type is ‘Microsoft
Dynamics CRM Fetch’

- Fill in the connection string in the
following format:
ServerURL;OrganizationName

5)      Click Credentials and enter Windows Live ID
or Domain credentials depending on the
instance (Online or On Premise):

6)      Click OK and Next. If the connection string
is validated you will be at a blank Query Builder
window. This is where we paste the FetchXML
we saved from CRM:

7)      Click next and continue on through the
Report Wizard to format the report layout,
grouping and style.

8)      The end result will be the ‘RDL’ file, which
is uploaded into CRM to generate the new report.

Back in CRM we are ready to upload the RDL file.
Navigate to Workplace | Reports and select ‘New’
on the ribbon toolbar:

From the new report screen:

From the CRM Service Report category you can now
see and run the new report.

This report query was quite simple and could have
been accomplished through the CRM Report Wizard.
The advantage of using the FetchXML method is
being able to design more complex queries with
data from multiple entities through Advanced Find.
The CRM Report Wizard has significant limitations in
regards to query building. So, if you are going to be
writing custom reports for CRM Online, you will likely
be doing so with FetchXML. Have fun!

- Doug Hunter

We will walk through the process of configuring this new
integration point. It only takes a few minutes to setup. We will
cover two scenarios:

1) Auto creation of SharePoint document folders
2) Linking CRM records to existing SharePoint folders 

The prerequisites to get this working: administrative access to
both CRM and the SharePoint site where you wish to store the
documents. Install the List Component  before configuring CRM.

Dynamics CRM 2011 supports SharePoint 2010 or 2007, but the
List Component only works with SharePoint 2010 and is what
facilitates the automatic creation of SharePoint folders.

1) Go into the Settings area and click on “Document Management“
in the left navigation pane.

2) Click on the “Document Management settings” link.

3) Select the entities for which you want to enable document
management:

4) Enter the SharePoint URL where you have installed the
SharePoint List Component

5) The ‘Based on entity’ option creates a folder hierarchy, under
either an Account or Contact.

For example, if I choose ‘Account’ here the auto-creation of
folders would start at the Account level and go down to any
related entities from there. I choose to add a document I have
created for an Opportunity (Test Sale) related to an Account
(Test Company). The resulting folder structure would
utomatically be created in SharePoint:

Dynamics CRM 2011 SharePoint Integration

Not all organizations will use Accounts or Contacts as primary
entities so this is optional. If you don’t select Account or Contact
the resulting folder structure (from the above example) would
be: http://ServerName/Opportunity/Test Sale

6) Once this step is complete you can add documents to any of
the entities you selected in step 3.  

In addition to letting the system handle the creation of the
document folders, you will probably want to utilize existing
SharePoint Collection Sites as well.

To link a record to an existing SharePoint Location:

1) Click on ‘Add Location’ on the record toolbar:

2) Paste the URL of the SharePoint location into the
“Specify the URL of an existing SharePoint Folder’ line.

That’s it! Just two steps to link to an existing location.

This concludes the walkthrough for the ‘Out-of-Box’ integration
between Dynamics CRM 2011 and SharePoint 2010. There are
many more integration possibilities between these two
applications. I will be posting more next month.

- Doug Hunter 

‘Data Enrichment’ is part of the Data Management feature set.
It allows you to export a set of records like opportunities
for example, edit them in Excel and re-import (update) them
back into CRM. You can even add new records to the same
Excel worksheet and they will be imported as new records.
Data Enrichment is extremely useful for bulk updates of
existing data, even if the people doing the updates are not
CRM users! You can just export the CRM data to Excel and
have them modify the worksheet data before reimporting it.
 
Let’s walk through a scenario where I want to update some
product records stored in a custom entity ‘Other Products’.
I need to edit the values for ‘Program’, Price Level’ and
Product Classification’ for many records. I prefer to do this
kind of editing in Excel rather than CRM.
 
The high-level steps:
 
1) Select the set of records to update. Use an existing view
such as ‘Active Other Products’ or create an Advanced Find
query to build the record set.

2) From the ‘Grid’ view select ‘Export to Excel’ from the
toolbar:

3) A dialog box opens, from which you can select the export
type. Only choose a ‘static’ type for Data Enrichment
(Dynamic will not work). Be sure to check the box below
making the data ‘available for re-import’.

4) After selecting ‘Export’ you will be prompted to ‘save’
the file, which you can then open in Excel.

5) Pull-down menu items are included in the export to
maintain data consistency:

 6) After updating the data in Excel, you can reimport
the file by clicking the ‘Import Data’ button.

7) Follow the Data Wizard prompts to complete the
re-import. After the import you can review the import job
for more details.

The updated records appear in the grid as expected.

Nice, simple, but powerful editing capabilities are now
available through ‘Data Enrichment’.

- Doug Hunter