Create SSRS report with FetchXML for Microsoft Dynamic CRM 2011 online (Fetch Based reports)

Microsoft Dynamic CRM 2011 online edition does not support SSRS reports with SQL queries. This is a limitation in Microsoft Dynamic CRM 2011 online. But it’s possible to use FetchXML instead of SQL. Here all the developers, Business Analyst and even customers should understand that FetchXML cannot implement complex SQL logics. Another thin is FetchXML can only process maximum 5000 records.

From this article I’m trying to give some important tips and instruction to create SSRS reports with FetchXML queries.

First of all, we should prepare the development environment for SSRS report development. VS 2008 provide all the necessary components for SSRS reports. But that is for SQL queries. So we have use additional components to create FetchXML reports and this component will provide the connection data source. Here data source will be Dynamic CRM 2011 Online web service

This component called Microsoft Dynamics CRM 2011 Report Authoring Extension which support fetch based custom SSRS reports and this will work with VS Business Intelligence Development Studio.

Here our development environment base on VS 2008. Click in the following link and download Microsoft Dynamics CRM 2011 Report Authoring Extension. Follow the instructions to installed the extension

Microsoft Dynamics CRM 2011 Report Authoring Extension

Now our development environment is ready and we can start.

I’m not going to explain how you create a SSRS report from SSRS. Add a new project and add a new report file (.rdl) to the projects. Now we should add a data source for this. Since we have installed the Microsoft Dynamics CRM 2011 Report Authoring Extension, data source type “Microsoft Dynamic CRM fetch” will be displayed as follows.

Select the Data Source Type as above. Now we should set the Connection String. Connection string consists of two parts.

CRM Online site URL + “;” + Organization unique name

It shows as below.

https://softwaredx1.crm4.dynamics.com;eb2361f04afe4706mc086b644d939102

Organization unique name can be found
Settings -> Customizations -> Developer Resources -> Your Organization Information

Now our setting will be displayed as below

Enter your windows Live ID credentials for credentials as below.

Click Ok to save.Now our Data source is completed.

Let s create the FetchXML query. Check the following advanced find view and the downloaded FetchXML query.

Fetch XML :

Here you can see that all the retrieved data fields will be from Customer entity. If you need more additional fields from other entities just add

under relevant entity or Link-entity tags.

Fetch XML always link entities as “Inner Joins”. If you need to get “outer join” use following option

link-type='outer'

Likewise you can edit this fetch xml query from any xml editor and ones you finish it you can copy it and paste it in to Query Editor in SSRS and run. This will return all the fields relevant to this query.

Edited query:-

Important: Don’t try to implement a complex logic in FetchXML query. Just get the whole data set you want and try to implement the Logic by using SSRS functions and standard options. When you create the advanced find view try to add some important condition like Date comparisons. You can use parameters for queries. Please see the above sample query.
For example multiple selection parameters has been implemented using Table filter as follows

Expression:-
=iif(Parameters!Filter.Value = "Customer", Fields!suk_accountname.Value, (iif(Parameters!Filter.Value = "Rank", Fields!suk_rank.Value, (iif(Parameters!Filter.Value = "OrderDate", Fields!suk_lastinvoicedate.Value, (iif(Parameters!Filter.Value = "SalesPerson", Fields!suk_salespersonid.Value, "")))))))

Value:-
= "*" + Parameters!Value.Value + "*"
I hope these tips and instruction will help you. I will add some more article on Dynamic CRM online in future.

How to access Microsoft CRM 2011 online organization service using Live ID (Pass port authentication)

Microsoft CRM 2011 online is a widely used online CRM system with wide range of features. Like On promise installation you can access this service and do CRUD operations.
Let’s start.
Below example is for your understand and its up to you to arrange it with better programming concepts. Some part of the code is same as the code which we use for Active Directory Authentication. Additional part in this code is Device Credentials. Please see the code below.

public OrganizationServiceProxy GetOrganizationServiceProxyLive(string organisationName)
{
Uri organizationUri = new Uri(Your CRM Online site + "/XrmServices/2011/Organization.svc");
Uri HomeRealmUri = null;

var service = new OrganizationServiceProxy(organizationUri, HomeRealmUri, GetCredentials(), GetDeviceCredentials());
service.ServiceConfiguration.CurrentServiceEndpoint.Behaviors.Add(new ProxyTypesBehavior());

service.EnableProxyTypes();
return service;
}

private ClientCredentials GetDeviceCredentials()
{
ClientCredentials Credentials = new ClientCredentials();

Credentials.UserName.UserName = Generated Device Credential Username;
Credentials.UserName.Password = Generated Device Credential Password;

return Credentials;
}

private ClientCredentials GetCredentials()
{
ClientCredentials clientCredentials = new ClientCredentials();

clientCredentials.UserName.UserName = YOUR Live ID;
clientCredentials.UserName.Password = YOUR Live ID Password;

return clientCredentials;

}

Here you can provide your Live ID and Password as client credentials. (Live ID and Password - Which you used to create the CRM online site)
To create Device Credentials, Please follow following steps.
1. Download and install Microsoft Dynamic CRM 2011 SDK
2. Go to following folder in you SDK “Installed folder\sdk\tools\deviceregistration”
3. Open “deviceregistration.sln” and Run
4. DeviceRegistration.exe file will be created “Installed folder\sdk\tools\deviceregistration\bin\Release”
5. Open Command Prompt and got to “Installed folder\sdk\tools\deviceregistration\bin\Release” folder
6. Then type this command - deviceregistration.exe /operation:Register
7. Then is will generate user name and the password for Device Credentials.
8. Copy and Paste this in to your code
private ClientCredentials GetDeviceCredentials()
{
ClientCredentials Credentials = new ClientCredentials();

Credentials.UserName.UserName = Generated Device Credential Username;
Credentials.UserName.Password = Generated Device Credential Password;

return Credentials;
}

Now it's ready to access the Organization Service..