Posted on 24. June 2011

SQL Queries from Transactional Plugin Pipeline

Sometimes the LINQ, Query Expressions or Fetch just doesn't give you the ability to quickly query your data in the way you want to. A good example of this is the lack of left outer join support if you want a where clause to filter results based on the joined entity. Sometime, you just need to query your database using good old T-SQL. In CRM 4 you could do this fairly easily by simply opening a connection directly and doing what you need to.

Since Transactional Pipelines were introduced with CRM2011 , I've been dancing a jig every time I don't have write manual rollback compensation code – but – if you try a SQL query from a transactional pipeline that queries the same entity that you are updating/inserting, you'll get a blocking lock that will cause the operation to time out.

To get around this, you have a number of options:

1) Call the SQL from outside a transaction in the PreValidation or an Async Pipeline

2) Use the following method to hook into the CRM Transaction and execute your query from within that.

Note: I should say that this could be considered breaking the following rule in the SDK that defines what is supported or not:

"The use of application programming interfaces (APIs) other than the documented APIs in the Web services DeploymentService, DiscoveryService, Organization Data Service, SOAP endpoint for Web Resources and OrganizationService."

I'm assuming that you are familiar with the System.Data library, so I'm just posing how to get a SqlTransaction, and you can do the rest:

 

Microsoft.Xrm.Sdk.IPluginExecutionContext context = (Microsoft.Xrm.Sdk.IPluginExecutionContext)
serviceProvider.GetService(typeof(Microsoft.Xrm.Sdk.IPluginExecutionContext));
object platformContext = context.GetType().InvokeMember("PlatformContext", System.Reflection.BindingFlags.GetProperty, null, context, null);
SqlTransaction tx = (SqlTransaction)platformContext.GetType().InvokeMember("SqlTransaction", System.Reflection.BindingFlags.GetProperty, null, platformContext, null);
DataSet result = SqlHelper.ExecuteDataset(tx, CommandType.Text, "SELECT ...");

 

You can also call a stored procedure in a different database that points back to the MSCRM database if you have complex queries. You'll need to use 'SET TRUSTWORTHY ON' to ensure that the security context is passed between the two databases.

My advice would be to only use this method only where using the SDK is just not possible or performs too slowly.

Hope this helps.

UPDATE: I've been doing some benchmarking recently on the FetchXml Reporting Services provider and using the Filtered Views. Under some circumstances where a user has only partial read access to records, using the FetchXml provider can perform considerably faster than the Filtered Views. Watch out for a blog post on this subject.

Posted on 3. June 2011

Embedding CRM reports in IFrames using the CRM2011 Report Viewer

It is quite common for users to request that reports be included in Iframes on forms, or in Dashboards. Although this can be done by pointing the Iframe directly at SQL Reporting services, this essentially bypasses the CRM Report connector and relies on the user authenticating directly with SQL Reporting Services and SQL Server. If you have SQL Server and Reporting Services on the same server or Kerberos is set up correctly, this isn't a problem. If however your users can only access the report through the CRM Front end, then you need to use the CRM Report review rather than the Reporting Services native one.

1) Create a html web resource named 'ReportViewer' or similar

<html>
<head>
<title></title>
<script src="ClientGlobalContext.js.aspx" ></script>
<script type="text/javascript">
    function submitForm() {
        var form = document.forms[0];
        var context = GetGlobalContext();
        form.action = context.getServerUrl() + '/CRMReports/rsviewer/reportviewer.aspx';
        form.uniquename.value = context.getOrgUniqueName();

        var query = window.location.search.substring(1);
        var vars = query.split("&");
        var idSet = false;

        for (var i = 0; i < vars.length; i++) {
            var pair = vars[i].split("=");
            switch (pair[0]) {
                case 'data':
                    var params = unescape(pair[1]).split("&");
                    for (var j = 0; j < params.length; j++) {
                        var param = params[j].split("=");
                        switch (param[0]) {
                            case 'id':
                                form.id.value = param[1];
                                idSet = true;
                                break;
                            case 'hideparams':
                                form.PromptAreaCollapsed.value = param[1];
                                break;
                            case 'iscustomreport':
                                form.iscustomreport.value = param[1];
                                break;
                            default:
                                // Add any other values as report parameters
                                var paramInput = document.createElement('input');
                                paramInput.setAttribute('type', 'hidden');
                                paramInput.setAttribute('name', 'p:' + param[0]);
                                paramInput.setAttribute('value', param[1]);
                                form.appendChild(paramInput);
                                break;
                        }
                    }
            }
        }
        

        if (idSet) {
            form.submit();
        }
        else {
            // Show message
            var dvMessage = document.createElement("div");
            dvMessage.innerHTML ="Report Id is not set";
            form.appendChild(dvMessage);

        }
    }
    
</script>
</head>
<body onload="submitForm()">
<form action ="" method="post">
<input type="hidden" name="id" value="{xxx}" />
<input type="hidden" name="uniquename" value="" />
<input type="hidden" name="iscustomreport" value="true" />
<input type="hidden" name="reportName" value="Report Name" />
<input type="hidden" name="isScheduledReport" value="false" />
<input type="hidden" name="PromptAreaCollapsed" value="false" />
</form>
</body>
</html>


2) Include in a Dashboard or Webresource on a form using the following Web Resource Properties (The Customer Parameter Data field):

Id={xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx} (make sure you add the curly braces)

You can also pass any report parameters you have by adding on :

param1=value1&param2=value2

If you want to have the report parameters hidden by default use:

&hideparams=true

 

So the whole data field would be something like:

Id={xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}&hideparams=true&param1=value1&param2=value2

I don't want to spoil all your fun – so I'll leave you to add the query to lookup the id by passing in the report name rather than the ID!

Have fun!