SSRS Report Prefiltering fetchxml/SQL

FetchXML and SQL querying are some of the best possible ways to retrieve data from MSCRM for BI or Data manipulation.

Previous articles around fetchxml querying are as follows, now lets jump into fetchxml/sql prefiltering

1. Timezone based querying
2. Hierarchical querying
3. Multi entity or conditions

What is prefiltering and why should I use it?

 As the name suggests, before execution of the report we restrict the scope to work based on relevant records. This is improves performance and also enables advanced find filtering before report execution.

How to enable prefiltering for FetchXML datasets?

• Under entity node, add an attribute enableprefiltering attribute and set its value to true

 <fetch distinct="false" mapping="logical">
   <entity name="account" enableprefiltering="1">
      <attribute name="accountid" />
      <attribute name="name" />      
   </entity>
 </fetch>

How to enable pre-filtering for SQL datasets?

• Add an alias with prefix CRMAF_ to the filtered view name

SELECT accountid, name  
FROM FilteredAccount AS CRMAF_FilteredAccount

For more details please refer,
https://technet.microsoft.com/en-us/library/dn531099.aspx

SSRS Report with Prefilter in MSCRM

SSRS Report run on multiple records in Dynamic CRM

Create SSRS(SQL query) prefilter based report for dynamic CRM and it can run on single or multiple records.
Below example related to the "incident entity" and getting incident Id, title and ticket number in report.

Create a report project in VS and two new reports "mainreport" and "subreports".
Design subreport as ref below screenshot1

(screenshot1)

Dataset1 query(use filtered view)

SELECT  top 1      Title, IncidentId, TicketNumber

FROM            FilteredIncident

WHERE        FilteredIncident.IncidentId = @CRM_IncidentId

Design 2nd report "mainreport" and add subreport in rectangle or list data item.

Dataset1 query:

SELECT        TOP (20) IncidentId as caseid

FROM            FilteredIncident AS CRMAF_FilteredIncident

Add a parameter in subreport

Add group properties in list or rectangle. 

Add report in mscrm and run report for multiple records.

report result for multiple records.

 run report from the form, it will run for that record only.

Enjoy :)

Encode/Decode in JS

// Create Base64 Object

var Base64={_keyStr:"ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/=",encode:function(e){var t="";var n,r,i,s,o,u,a;var f=0;e=Base64._utf8_encode(e);while(f<e.length){n=e.charCodeAt(f++);r=e.charCodeAt(f++);i=e.charCodeAt(f++);s=n>>2;o=(n&3)<<4|r>>4;u=(r&15)<<2|i>>6;a=i&63;if(isNaN(r)){u=a=64}else if(isNaN(i)){a=64}t=t+this._keyStr.charAt(s)+this._keyStr.charAt(o)+this._keyStr.charAt(u)+this._keyStr.charAt(a)}return t},decode:function(e){var t="";var n,r,i;var s,o,u,a;var f=0;e=e.replace(/[^A-Za-z0-9+/=]/g,"");while(f<e.length){s=this._keyStr.indexOf(e.charAt(f++));o=this._keyStr.indexOf(e.charAt(f++));u=this._keyStr.indexOf(e.charAt(f++));a=this._keyStr.indexOf(e.charAt(f++));n=s<<2|o>>4;r=(o&15)<<4|u>>2;i=(u&3)<<6|a;t=t+String.fromCharCode(n);if(u!=64){t=t+String.fromCharCode(r)}if(a!=64){t=t+String.fromCharCode(i)}}t=Base64._utf8_decode(t);return t},_utf8_encode:function(e){e=e.replace(/rn/g,"n");var t="";for(var n=0;n<e.length;n++){var r=e.charCodeAt(n);if(r<128){t+=String.fromCharCode(r)}else if(r>127&&r<2048){t+=String.fromCharCode(r>>6|192);t+=String.fromCharCode(r&63|128)}else{t+=String.fromCharCode(r>>12|224);t+=String.fromCharCode(r>>6&63|128);t+=String.fromCharCode(r&63|128)}}return t},_utf8_decode:function(e){var t="";var n=0;var r=c1=c2=0;while(n<e.length){r=e.charCodeAt(n);if(r<128){t+=String.fromCharCode(r);n++}else if(r>191&&r<224){c2=e.charCodeAt(n+1);t+=String.fromCharCode((r&31)<<6|c2&63);n+=2}else{c2=e.charCodeAt(n+1);c3=e.charCodeAt(n+2);t+=String.fromCharCode((r&15)<<12|(c2&63)<<6|c3&63);n+=3}}return t}}

// Define the string

var string = 'Hello World!';

// Encode the String

var encodedString = Base64.encode(string);

console.log(encodedString); // Outputs: "SGVsbG8gV29ybGQh"

// Decode the String

var decodedString = Base64.decode(encodedString);

console.log(decodedString); // Outputs: "Hello World!"

Add double quotation mark " " in string c#

"\"" + value.ToString() + "\""

MSCRM Get the description text value from optionset field in c#

public static string GetoptionsetDescription(string entityName, string attributeName, int optionSetValue, IOrganizationService service)

        {

            string AttributeName = attributeName;

            string EntityLogicalName = entityName;

            RetrieveEntityRequest retrieveDetails = new RetrieveEntityRequest

            {

                EntityFilters = EntityFilters.All,

                LogicalName = EntityLogicalName

            };

            RetrieveEntityResponse retrieveEntityResponseObj = (RetrieveEntityResponse)service.Execute(retrieveDetails);

            Microsoft.Xrm.Sdk.Metadata.EntityMetadata metadata = retrieveEntityResponseObj.EntityMetadata;

            Microsoft.Xrm.Sdk.Metadata.PicklistAttributeMetadata picklistMetadata = metadata.Attributes.FirstOrDefault(attribute => String.Equals

            (attribute.LogicalName, attributeName, StringComparison.OrdinalIgnoreCase)) as Microsoft.Xrm.Sdk.Metadata.PicklistAttributeMetadata;

            Microsoft.Xrm.Sdk.Metadata.OptionSetMetadata options = picklistMetadata.OptionSet;

            IList<OptionMetadata> OptionsList = (from o in options.Options

                                                 where o.Value.Value == optionSetValue

                                                 select o).ToList();

            string optionsetDescription = (OptionsList.First()).Description.UserLocalizedLabel.Label;

            return optionsetDescription;

        }

MSCRM Restrict Customer lookup

365 CRM

function defaultcustomer_onLoad(executionContext){

var formContext=executionContext.getFormContext()

var control = formContext.getControl("parentcustomerid");

 control.setEntityTypes(["contact"]);//Contact or account

}

2016/365:

var control = Xrm.Page.getControl("from");
 control.getAttribute().setLookupTypes(["contact"]);


2013/2015:

function CustomerLookupOnlyContact() {
 
    var customerCtrl = Xrm.Page.getControl("customerid");
    customerCtrl.setFocus();

    var customerInput = document.getElementById("customerid_i");
    customerInput.setAttribute("lookuptypes", "2");   //2: Contact , 1: Account
   customerInput.setAttribute("defaulttype", 2);       // Set defaultType to Contact
    //customerInput.setAttribute("autoresolve", 2);       // Partially entered name will be resolved with contact record.
    //customerInput.setAttribute("createpermissiondictionary", "account:false,contact:true");
 //   customerInput.setAttribute("lookuptypenames", "contact:2:Contact");
   // customerInput.setAttribute("DefaultViewId", "{A2D479C5-53E3-4C69-ADDD-802327E67A0D}");

}

SSL TLS

ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls (for all .Net 4.0 and lower applications)

ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12 (for all .Net 4.5 and higher applications)

For reference: https://msdn.microsoft.com/en-us/library/system.net.securityprotocoltype(v=vs.110).aspx

Solution for Error: MSCRM reports Error "The report execution ampbze45rbglyvb4u4qfldr4 has expired or cannot be found. (rsExecutionNotFound)"

Link:
https://blogs.msdn.microsoft.com/dynamicsaxbi/2013/02/14/how-to-addressing-ssrs-session-timeouts/


Run On SQL Server:
rs -i sessionaccess.rss -s http://indel-mscrmuat1/ReportServer_RSUAT

SessionAccessTimeOut.rss file:

Public Sub Main() Dim props() as [Property] props = new [Property] () { new [Property](), new [Property]() } props(0).Name = "SessionTimeout" props(0).Value = 60000 props(1).Name = "SessionAccessTimeout" props(1).Value = 60000 rs.SetSystemProperties(props) End Sub


And

Enable Remote Errors:

Public Sub Main()
  Dim P As New [Property]()
  P.Name = "EnableRemoteErrors"
  P.Value = True
  Dim Properties(0) As [Property]
  Properties(0) = P
  Try
    rs.SetSystemProperties(Properties)
    Console.WriteLine("Remote errors enabled.")
  Catch SE As SoapException
    Console.WriteLine(SE.Detail.OuterXml)
  End Try
End Sub

MS Dynamic CRM Email router service can not start

Error on Email router service:

The Microsoft CRM Email Router service on Local Computer started and then stopped. Some services stop automatically if they are not in use by other services or programs.

Solution:
Go to:  C:\Program Files\Microsoft CRM Email\Service

Please delete below files.

Microsoft.Crm.Tools.EmailAgent.SystemState.xml
Microsoft.Crm.Tools.EmailAgent.xml

if these files not created automatically then copy these files from another server(i.e UAT or Dev) and paste to same location(C:\Program Files\Microsoft CRM Email\Service). Then it will update automatically. 

CRM 365 Getting Current User ID and name

Getting User Details :

var userName = Xrm.Page.context.getUserName();
var userGUID = Xrm.Page.context.getUserId();
alert ("User is:" + userName + "nGUID is:" + userGUID);

Retrieve more than 5000 records using FetchXML in Dynamic CRM

 Sample Code:

string fetchXMLInvoice = @"<fetch version='1.0' page='{0}' paging-cookie='{1}' count='5000' output-format='xml-platform' mapping='logical' distinct='false'>
  <entity name='tec_invoice'>
    <attribute name='tec_invoiceid' />
    <attribute name='tec_name' />
    <attribute name='createdon' />
    <order attribute='tec_name' descending='false' />
    <filter type='and'>
      <condition attribute='createdon' operator='on-or-after' value='2017-01-01' />
      <condition attribute='createdon' operator='on-or-before' value='2018-01-01' />
    </filter>
  </entity>
</fetch>";

            int page = 1;           
            string pageCookies = "";
            EntityCollection result = new EntityCollection();
            do
            {
var fetchXML=   new FetchExpression(String.Format(fetchXMLInvoice, page++, pageCookies));
                result = service.RetrieveMultiple(fetchXML);
                pageCookies = System.Net.WebUtility.HtmlEncode(result.PagingCookie);             

            } while (result.MoreRecords);

Convert string to Date Time Format MM/DD/YYYY HH:MM:SS TT using C#

string date = "12/21/2017 7:46:30 PM";
DateTime dateResult = DateTime.ParseExact(date, "M/d/yyyy h:m:s tt", CultureInfo.CurrentCulture);

Result: {21-12-2017 19:46:30}