Run SSRS report against specific CRM record

When we write SSRS reports to Dynamics CRM, it may need to write a report against opened record. For example, order report against selected account. Consider my example for custom entity called office. (Schema name: new_office).

Our usual select statement which returns all the offices;

SELECT
new_name as OfficeName,
new_code as OfficeCode
Filterednew_office

Special query to return the current record specific data;

SELECT    
CRMAF_Filterednew_office.new_name as OfficeName, 
CRMAF_Filterednew_office.new_code as OfficeCode
FROM  Filterednew_office as CRMAF_Filterednew_office

Prefix “CRMAF_Filtered” seems to be doing the filtering. What you really have to do is do your query this way and configure the report so that you can run from opened form of selected entity type. (In my example its Office) Please make sure you select below option so that report is ready to be run from opened record.

By the way, if the query I need to write is complex I read the Guid of current record to a variable so that I am free to use it for any other complex queries without any issue as below;

DECLARE @OfficeId uniqueidentifier

SELECT @OfficeId =CRMAF_Filterednew_office.new_officeid
FROM  Filterednew_office as CRMAF_Filterednew_office

REST Functions from XrmServiceToolkit

Now we are in a situation, that REST calls should do the basic client side functions.

In fact, XrmServiceToolkit (http://xrmservicetoolkit.codeplex.com/) becomes handy. So it’s a library you need to add to your CRM form consist of three files as below;

XrmServiceToolKit website provides some sample coding. Yet, I thought of making them bit more user-friendly and simple as below;

function Retrieve() {
    var re;
    XrmServiceToolkit.Rest.Retrieve(
              "{EE81D2A9-E28E-E311-96DF-D89D6765B238}",
              "AccountSet",
              null, null,
              function (result) {
                  re = result;
                  alert("success");
              },
              function (error) {
                  alert("failed");
              },
              false
          );
    //debugger;
    alert(re.Name);
    alert(re.AccountId);
}

function Delete() {
    XrmServiceToolkit.Rest.Delete(
                "{32815A55-19AF-E311-BF0E-D89D6765B238}",
                "AccountSet",
                function () {
                    alert("successfully deleted");
                },
                function (error) {
                    alert("failed to delete");
                },
                false
            );
}

function Update() {

    var account = {};
    account.Name = "SO and Company A1";
    account.Address1_AddressTypeCode = { Value: 3 }; //Address 1: Address Type = Primary
    account.Address1_City = "Wentworthville";
    account.Address1_Line1 = "153 Dunmore Stret";

    XrmServiceToolkit.Rest.Update(
        "{EE81D2A9-E28E-E311-96DF-D89D6765B238}",
        account,
        "AccountSet",
        function () {
            alert("successfully Updated");
        },
        function (error) {
            alert("failed to Update");
        },
        false
    )

}

function Create() {
    var account = {};
    account.Name = "SO and Company B1";
    account.Address1_AddressTypeCode = { Value: 3 }; //Address 1: Address Type = Primary
    account.Address1_City = "Wentworthville B";
    account.Address1_Line1 = "153 Dunmore Stret B";

    XrmServiceToolkit.Rest.Create(
                    account,
                    "AccountSet",
                    function (result) {
                        accountId = result.AccountId;
                        alert("successfully Created. Acc ID : " + result.AccountId);
                    },
                    function (error) {
                        alert("failed to Create Account");
                    },
                    false
    );

}

Hope this is helpful.

Query to check duplicate field values

Sometimes you need to check if we got duplicate values for a given field. This is a script, we can use to check them.

Suppose I have new_RegNumber field in Account Entity which needs to be unique. So below query will show me if there are any ducplicates by mistake.

SELECT A1.new_RegNumber
FROM Account as A1
WHERE A1.new_RegNumber is not null
GROUP BY A1.new_RegNumber
HAVING (SELECT COUNT(A2.new_RegNumber) FROM  Account A2 WHERE A2.new_RegNumber = A1.new_RegNumber)>1

Hope this is useful.