Microsoft Dynamics 365 Report Authoring Extension

Click here and download “Microsoft Dynamics 365 Report Authoring Extension

To create a simple FetchXML report, follow my post FetchXML Report.

Advertisements

Retrieve records using FetchXML & C# SDK in CRM

Task: Write a Console Application to Retrieve the following attributes from Contact record, where Full Name = Arun Potti & Status = Active, using FetchXML.

Retrieve Multiple - FetchXML SDK

Solution: Follow the below Steps,

Step 1: Include the below References in your project, you can get the same from Latest SDK.
Goto the path, SDK->Bin for the dlls

Microsoft.Crm.Sdk.Proxy
Microsoft.Xrm.Sdk

Step 2: Include the below Framework Assemblies in your project,

Reference Manager - ConnectToCRM

Step 3: Add the below namespaces in your class file,

using Microsoft.Crm.Sdk.Messages;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Client;
using System.ServiceModel.Description;
using Microsoft.Xrm.Sdk.Query;

Step 4: Download the fetchxml from CRM Advance Find,

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
<entity name="contact">
<attribute name="fullname" />
<attribute name="contactid" />
<attribute name="parentcustomerid" />
<attribute name="gendercode" />
<attribute name="birthdate" />
<attribute name="creditlimit" />
<attribute name="donotsendmm" />
<order attribute="fullname" descending="false" />
<filter type="and">
<condition attribute="statecode" operator="eq" value="0" />
<condition attribute="fullname" operator="eq" value="Arun Potti" />
</filter>
</entity>
</fetch>

Step 5: Use FetchXML Formatter Tool, to format the fetchXML. For More Details about FetchXML Formatter Tool Click Here

Finally the Formatted FetchXML is as follows,

@"<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>
<entity name='contact'>
<attribute name='fullname' />
<attribute name='contactid' />
<attribute name='parentcustomerid' />
<attribute name='gendercode' />
<attribute name='birthdate' />
<attribute name='creditlimit' />
<attribute name='donotsendmm' />
<order attribute='fullname' descending='false' />
<filter type='and'>
<condition attribute='statecode' operator='eq' value='0' />
<condition attribute='fullname' operator='eq' value='Arun Potti' />
</filter>
</entity>
</fetch>"

Step 6: First we have to connect to CRM, for details Click Here 

Step 7: Below Method is useful for retrieving the values from FetchXML,

public static EntityCollection ExecuteFetch(string fetchXmlString)
{
return _service.RetrieveMultiple(new FetchExpression(fetchXmlString));
}

Step 8: Final code is here, 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Crm.Sdk.Messages;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Client;
using System.ServiceModel.Description;
using Microsoft.Xrm.Sdk.Query;

namespace ConnectToCRM
{
class Program
{
static IOrganizationService _service;
static void Main(string[] args)
{
try
{
ConnectToMSCRM("arunpotti@XXXXXX.onmicrosoft.com", "XXXXXXX", "https://XXXXXX.api.crm5.dynamics.com/XRMServices/2011/Organization.svc");
Guid userid = ((WhoAmIResponse)_service.Execute(new WhoAmIRequest())).UserId;
if (userid == Guid.Empty) return;//Check for CRM Connection Establishment. If Not return, other wise will proceed to next step
string fetchXmlString = @"<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>
<entity name='contact'>
<attribute name='fullname' />
<attribute name='contactid' />
<attribute name='parentcustomerid' />
<attribute name='gendercode' />
<attribute name='birthdate' />
<attribute name='creditlimit' />
<attribute name='donotsendmm' />
<order attribute='fullname' descending='false' />
<filter type='and'>
<condition attribute='statecode' operator='eq' value='0' />
<condition attribute='fullname' operator='eq' value='Arun Potti' />
</filter>
</entity>
</fetch>";

EntityCollection ec = ExecuteFetch(fetchXmlString);
if (ec.Entities.Count > 0)
{
string output = string.Empty;
foreach (var item in ec.Entities)
{
//String
if (item.Attributes.Contains("fullname")) //Check for fullname value exists or not in Entity Collection
output += "Full Name : " + item.Attributes["fullname"] + "\n";

//Lookup
if (item.Attributes.Contains("parentcustomerid")) //Check for parentcustomerid exists or not in Entity Collection
output += "Company : " + ((EntityReference)item.Attributes["parentcustomerid"]).Name + "\n";

//OptionSet
if (item.Attributes.Contains("gendercode")) //Check for gendercode exists or not in Entity Collection
output += "Gender : Name - " + item.FormattedValues["gendercode"] + ", Value - " + ((OptionSetValue)item.Attributes["gendercode"]).Value + "\n";

//Date
if (item.Attributes.Contains("birthdate")) //Check for birthdate exists or not in Entity Collection
output += "Birthday : " + ((DateTime)item.Attributes["birthdate"]).ToLocalTime().ToShortDateString().ToString() + "\n";

//Currency
if (item.Attributes.Contains("creditlimit")) //Check for creditlimit exists or not in Entity Collection
output += "Credit Limit : " + ((Money)item.Attributes["creditlimit"]).Value + "\n";

//Two Options
if (item.Attributes.Contains("donotsendmm")) //Check for donotsendmm exists or not in Entity Collection
output += "Send Marketing Materials : Name - " + item.FormattedValues["donotsendmm"] + ", Value - " + ((Boolean)item.Attributes["donotsendmm"]).ToString();
}

Console.WriteLine(output);
Console.ReadKey();
}
}
catch(Exception ex)
{
Console.WriteLine(ex.Message);
}
}
public static EntityCollection ExecuteFetch(string fetchXmlString)
{
return _service.RetrieveMultiple(new FetchExpression(fetchXmlString));
}

public static void ConnectToMSCRM(string UserName, string Password, string SoapOrgServiceUri)
{
try
{
ClientCredentials credentials = new ClientCredentials();
credentials.UserName.UserName = UserName;
credentials.UserName.Password = Password;
Uri serviceUri = new Uri(SoapOrgServiceUri);
OrganizationServiceProxy proxy = new OrganizationServiceProxy(serviceUri, null, credentials, null);
proxy.EnableProxyTypes();
_service = (IOrganizationService)proxy;
}
catch (Exception ex)
{
Console.WriteLine("Error while connecting to CRM " + ex.Message);
Console.ReadKey();
}
}
}
}

Step 9: Build the project and Click on Start, you can see the below output,

Retrieve Multiple - FetchXML SDK Output

Provide your valuable feedback.

FetchXML Formatter Tool

It is a Light weight windows application and this tool will be helpful when you are extensively working with FetchXML in Javascript / C# code to get desired result.

Functionality:

It will take FetchXML from Advance Find as an input and convert that into desired format, which can be used into Javascript / C# for further coding.

Pros:

  1. No Need to spend time for FetchXML formatting
  2. It is easy to use.
  3. Works for both Javascript and C# code

Example:

Let us take a simple example of the below FetchXML,

 <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="contact">
    <attribute name="fullname" />
    <attribute name="telephone1" />
    <attribute name="emailaddress1" />
    <attribute name="contactid" />
    <order attribute="fullname" descending="false" />
    <filter type="and">
      <condition attribute="ownerid" operator="eq-userid" />
      <condition attribute="statecode" operator="eq" value="0" />
    </filter>
  </entity>
</fetch>

Provide this as an input to FetchXML formatter Tool, 1. Check Javascript radio button and click on Format to see the below output,

"<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>"+
"  <entity name='contact'>"+
"    <attribute name='fullname' />"+
"    <attribute name='telephone1' />"+
"    <attribute name='emailaddress1' />"+
"    <attribute name='contactid' />"+
"    <order attribute='fullname' descending='false' />"+
"    <filter type='and'>"+
"      <condition attribute='ownerid' operator='eq-userid' />"+
"      <condition attribute='statecode' operator='eq' value='0' />"+
"    </filter>"+
"  </entity>"+
"</fetch>"

2. Check C# radio button and click on Format to see the below output,

@"<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>
  <entity name='contact'>
    <attribute name='fullname' />
    <attribute name='telephone1' />
    <attribute name='emailaddress1' />
    <attribute name='contactid' />
    <order attribute='fullname' descending='false' />
    <filter type='and'>
      <condition attribute='ownerid' operator='eq-userid' />
      <condition attribute='statecode' operator='eq' value='0' />
    </filter>
  </entity>
</fetch>"

Click Here to download the FetchXML Formatter Tool Exe file

Screen Shots:

FetchXMLFormatter - Pic 1
FetchXMLFormatter - Pic 2
FetchXMLFormatter - Pic 3
FetchXMLFormatter - Pic 4
Please provide your valuable feedback on this article.

Retrieve records using Fetch XML & Java Script in CRM 2011/13

Task: Retrieve Arun Potti Business Phone on onload of Contact record in Contact Entity using FetchXML & Javascript

Solution:

Step 1: Goto http://xrmsvctoolkit.codeplex.com/, and download the Zip folder. Unzip XrmServiceToolkit the folder.

Step 2: Open the XrmServiceToolkit folder, you can find the below Javascript files.

FetchXML - Pic 1

Goto Microsoft Dynamics CRM –> Settings –> Customization –> Webresources.

Create jquery, json2 and XrmServiceToolkit javascript webresources. While creating web resources browse for the respective files and provide the path of XRMServiceToolkit.

Step 3: Add all 3 files to the contact entity,

FetchXML - Pic 2

Step 4: Goto Microsoft Dynamics CRM –> Sales –> Contacts, click on Advance find button. Create new criteria as shown below by clicking on New button,

FetchXML - Pic 3

Step 5: Click on Download Fetch XML button, to get FetchXML code. You can see the below XML,

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="contact">
    <attribute name="fullname" />
    <attribute name="telephone1" />
    <attribute name="contactid" />
    <order attribute="fullname" descending="false" />
    <filter type="and">
     <condition attribute="fullname" operator="eq" value="Arun Potti" />
    </filter>
  </entity>
</fetch>

Step 6: We have to change the format of the above FetchXML to use in Javascript.

Use FetchXML Formatter Tool to modify the format.

To Know more about FetchXML Formatter Tool click Here

After modifying its looks like the below,

"<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>"+
"  <entity name='contact'>"+
"    <attribute name='fullname' />"+
"    <attribute name='telephone1' />"+
"    <attribute name='contactid' />"+
"    <order attribute='fullname' descending='false' />"+
"    <filter type='and'>"+
"      <condition attribute='fullname' operator='eq' value='Arun Potti' />"+
"    </filter>"+
"  </entity>"+
"</fetch>"

Step 7: Now create new_contactJscript Webresource and paste the below code,

function contactOnload() {
 var contactFetchXML = "<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>"+
 "  <entity name='contact'>"+
 "    <attribute name='fullname' />"+
 "    <attribute name='telephone1' />"+
 "    <attribute name='contactid' />"+
 "    <order attribute='fullname' descending='false' />"+
 "    <filter type='and'>"+
 "      <condition attribute='fullname' operator='eq' value='Arun Potti' />"+
 "    </filter>"+
 "  </entity>"+
 "</fetch>";
var contactRecords = XrmServiceToolkit.Soap.Fetch(contactFetchXML);
if (contactRecords.length > 0) {
 if (contactRecords[0].attributes.telephone1 != undefined)
   alert(contactRecords[0].attributes.telephone1.value);
  }
}

Step 8: Add the new_contactJscript Webresource to Form Libraries on contact Form, and add the contactOnload function to Onload Event,

FetchXML - Pic 5

Step 9: Click on Ok. Save & Publish the contact Entity.

Step 10: Open the existing contact record to see the below pop up,

FetchXML - Pic 6

 

FetchXML Reports

We can create custom reports using FetchXML, useful for both Online and OnPremise CRM.

Quickly see a simple example, to get the list of all Contacts.

Step 1: Open BIDS (Business Intelligent Development Studio)

Step 2: Click New Project and select “Report Server Project”. Name your report “MyFirstFetchXMLReport”.

Open BIDS

Step 3: In Solution Explorer, Right Click on Reports Folder and Select “Add New Report”.

Step 4: Provide the below details for the Data Source in Report Wizard.

Name   Give Data Source name
Type   Select Microsoft Dynamics CRM Fetch.If you don’t find this option then search for exe “Microsoft Dynamics CRM 2013 Report Authoring Extension (with SQL Server Data Tools support)” download and install.
Connection String It’s a combination of both organization URL and Name separated by semicolon.

Example: https://XYZDEMO.crm5.dynamics.com;DEMOORG

For Organization Name, find the below path

Microsoft Dynamics CRM –> Settings –> Customizations –> Developer Resources –> Organization Unique Name

Step 4

Step 5: Click on Credentials Button and select “Use a Specific user name and password”. Provide userid & password and Click Ok. Click Next Button.

Step 6: Provide Query String which is nothing but FetchXML. Follow the below steps for getting it.

Step 7: Find the below path for opening for Advance Find.

Microsoft Dynamics CRM –> Sales –> Contacts –> Click on Advance Find button

Step 6 - Open Advance Find

Step 8: In Advance find, click on New Button.

Step 9: Click on Download Fetch XML button and download the FetchXML.xml File and open it with Notepad.

Step 9

You can find the below query, copy and paste the below into BIDS Query String textbox


Step 10: Finally Report Wizard looks like the below, Click Next.

Step 9 - Query String

Step 11: Select Report Type “Tabular”. Click Next.

Step 10 - Tabular

Step 12: Click Next.

Step 11 - Design the Table

Step 13: Select Table Style and Click Next.

Step 13

Step 14: Give Report Name and Click Finish.

Step 13 - Final Stage

Step 15: Finally the Report design done.

Step 14 - Report Layout

Step 16: Click on Preview.

Step 16 - Preview

Good Luck. Please revert for any queries.

Please provide your valuable comments on this article.