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.

Advertisements

Get Logged In User Security Roles using Javascript OData 2013

Using OData, will see an example to retrieve Security Roles of Logged In User.

Task: Retrieve Logged In User Security Roles on Onload of Contact Entity

Solution:

Step 1: Include Json2 web resource in the Contact Entity.

If you don’t have this file, download Latest SDK and go to below path and create,

\CRM SDK\SDK\SampleCode\JS\RESTEndpoint\JavaScriptRESTDataOperations\JavaScriptRESTDataOperations\Scripts

Step 2: Create new JavaScript web resource (Ex: new_contact) and include the below script,

function getLoggedInUserRoles() {
var Id = Xrm.Page.context.getUserId();
retrieveMultiple("SystemUserSet", "?$select=systemuserroles_association/Name&$expand=systemuserroles_association&$filter=SystemUserId eq (guid'" + Id + "')", getSecurityRoleNames, null, null);
}

function retrieveMultiple(odataSetName, filter, successCallback, errorCallback, _executionObj) {
var context = Xrm.Page.context;
var serverUrl = context.getServerUrl();
var ODATA_ENDPOINT = "/XRMServices/2011/OrganizationData.svc";

//odataSetName is required, i.e. "AccountSet"
if (!odataSetName) {
alert("odataSetName is required.");
return;
}

//Build the URI
var odataUri = serverUrl + ODATA_ENDPOINT + "/" + odataSetName;

 //If a filter is supplied, append it to the OData URI
if (filter) {
odataUri += filter;
}

//Asynchronous AJAX function to Retrieve CRM records using OData
$.ajax({
type: "GET",
async: true,
contentType: "application/json; charset=utf-8",
datatype: "json",
url: odataUri,
beforeSend: function(XMLHttpRequest) {
//Specifying this header ensures that the results will be returned as JSON.
XMLHttpRequest.setRequestHeader("Accept", "application/json");
},
success: function(data, textStatus, XmlHttpRequest) {
if (successCallback) {
if (data && data.d && data.d.results) {
successCallback(data.d.results, textStatus, XmlHttpRequest);
} else if (data && data.d) {
successCallback(data.d, textStatus, XmlHttpRequest);
} else {
successCallback(data, textStatus, XmlHttpRequest);
}
}
},
error: function(XmlHttpRequest, textStatus, errorThrown) {
if (errorCallback)
errorCallback(XmlHttpRequest, textStatus, errorThrown);
else
errorHandler(XmlHttpRequest, textStatus, errorThrown);
}
});
}

function errorHandler(xmlHttpRequest, textStatus, errorThrow) {
alert("Error : " + textStatus + ": " + xmlHttpRequest.statusText);
}

function getSecurityRoleNames(data, textStatus, XmlHttpRequest) {
var totalCount = data[0].systemuserroles_association.results.length;
var userString = null;
if (totalCount > 0) {
userString = "User Role : ";
for (var i = 0; i < totalCount; i++)
userString = userString + data[0].systemuserroles_association.results[i].Name + "\n";
alert(userString);
} else
alert("No Role Associated with LoggedIn User");
}

Step 3: Add new_contact Jscript web resource in the Contact Entity. Add getLoggedInUsersRoles function on Onload of Contact.

Contact Form Webresource

Step 4: Click on Ok. Save and Publish Contact Entity.

Step 5: Open any Contact record and see the below Popup.

LoggedInUserResult

Please provide your valuable comments on this article.