Saturday, May 23, 2009

CRM Demos, SSD Drives and Virtual Machines

When doing a demo you never want the software to look slow, and when doing CRM development against virtual machines performance is always critical to being efficient.

The Hardware
Samsung recently released their new 256Gb SSD drive and for me this drive signals that SSD drives have become mature. Intel has had very fast SSD drives available, but they were not very large. Like most other SSD drives they suffered from a condition where over time the drive would drop dramatically in performance. Samsung’s new drive has algorithms to minimize this to the point of it being of it being much less noticeable while still performing very similarly to the Intel SSD’s in access time and throughput.

So hoping that this drive lived up to my expectations, I replaced my laptop’s 7200 rpm 160Gb drive with Samsung’s new 256Gb SSD drive. My main concern was that running my VM’s off the system drive would still be unacceptable.

Virtual Machines
Before: I have run my vhd files from separate Firewire 800 Raid 0 drives. Keeping the system drive separate from the VHD was critical to getting good virtual machine performance.

After: The MS CRM 4.0 VHD 2009 now loads up in a mere 14 seconds while still running on the system drive versus 52 seconds on my external Firewire 800 drive. VS 2008 loads up after a clean boot(nothing cached) in 17 seconds and closes in 2 seconds compared with 57 and 13 seconds on my old drive.

Developing against a VM while on battery
This allows real development work to be done while running on battery power. The new SSD drive uses less power than the original drive did, and lugging around external drives for demos is now unnecessary.

As I write this the new Samsung drive is currently available at Dell as part 341-9999 for $699.99.  For this you get 256Gb at the speed of a 10,000 rpm RAID system that fits in a laptop while sipping at your batteries. In some ways, especially average access time, it actually blows an expensive and bulky RAID system away.


Even MORE Speed Possible
I’ve seen reports twin drive laptops getting transfer rates of 300 MB/sec with two of these Samsung drives in a RAID 0 configuration. For comparison a single Samsung drive has a transfer rate of 200MB/sec which is still a lot faster than the 60Mb/sec of a 7200rpm internal laptop drive my less than a year old laptop was using.

The Installation
The replacement was easy. I used my Acronis backup software to make a complete disk backup to one of my external RAID drives. For my Dell M6300 I had to remove 4 screws, pull the drive out. Remove two more screws for the surrounding case. Screw the new drive into the case, slide it into the computer and put the 4 attaching screws back in. Boot from the Acronis CD. It recognizes all of my external drives USB and Firewire. Use the Acronis software to “add a drive” and partition it. Then restore the backup, remove the CD and reboot. The backup and restore(much faster) took me about 2.5 hours. Everything else took about 15 minutes.

Monday, May 18, 2009

MS CRM 4.0 Generate SSRS reports by webservice ( pdf , HTML , CSV , etc..)

Below is a Generic SSRS web service reporting method that generates a byte[] perfect for use in an annotation or email attachment. The example method above it shows how to call this method and how to create an annotation attachment.

You can combine this with my post on driving email templates and generating email attachments to create some very powerful solutions. If you need a primer on activity parties see my post on creating activity parties.

Generating SSRS reports by web service is a very valuable tool. I frequently use this to generate custom reports from custom web applications running in iframes such that the report is run with the permissions of the user running the report. To take advantage of this the reports must be run against MS CRM’s filtered views.

To Generate an SSRS report by web service do the following:

1. Add a report execution web reference to your solution. Make sure this link is accessible. The url should be something like the following:

2. Build up the parameters that you need to generate the report.

3. Select the Report Name of your Report

4. Specify the Type of Report

5. Specify the Device information ( formatting )

The example method CallReport() below demonstrates these steps. It in turn calls GenerateSRSbytes which makes the actual web service call.

using System.Net;
using Microsoft.Win32;
using ReportExecutionService;

public class SRSReporting

    public void CallReport()
        string reportServiceUrl = "http://<SRS_Server_Name>/ReportServer/ReportExecution2005.asmx";

        // Create the Report Service Url from the registry
        RegistryKey key = Registry.LocalMachine.OpenSubKey("Software\\Microsoft\\MSCRM", false);

        if (key != null)
            reportServiceUrl = (string)key.GetValue("SQLRSServerURL") + @"/ReportExecution2005.asmx";

        // Report parameter Name and Value fields are strings.

        string reportParameter = "type";
        string reportValue = "annual";

        // Create as many parameters as you need.
        var parameters = new[]
             new ParameterValue {Name = "entityid", Value = "0031a390-0d42-de11-9aa9-0003ff517b20"},
             new ParameterValue {Name = reportParameter, Value = reportValue}

        // Pathname to the report from Reporting Services, where TestReport is the rdl uploaded using http://<SRS_Server_Name>/Reports
        // Make sure that you set the Data Source properly
        // Make sure to set security properly
        string reportName = "/AutomatedCRMReporting/TestReport";

        // Specify what type of report you want to create HTML,PDF, CVS, Excel, Word, XML, MHTML, Image
        string reportFormat = "PDF";

        // Specify the device information to control the output of your report.
        //For device information See
        string deviceInformation = "<DeviceInfo><DpiX>240</DpiX><DpiY>240</DpiY><StartPage>0</StartPage></DeviceInfo>";

        // Generate a report in a format for a CRM annotation or email attachment
        byte[] generatedReport = GenerateSRSbytes(reportName, parameters, reportFormat, deviceInformation, reportServiceUrl, null, null, null);

        // An example attachment created from an SRS report
        //var newAnnotation = new annotation
        //  {
        //      objectid = CrmTypes.CreateLookup(regardingEntity, regardingId),
        //      objecttypecode = CrmTypes.CreateEntityNameReference(,
        //      isdocument = CrmTypes.CreateCrmBoolean(true),
        //      ownerid = CrmTypes.CreateOwner(EntityName.systemuser.ToString(), ownerId),
        //      filename = reportFileName,
        //      documentbody = Convert.ToBase64String(generatedReport),
        //      filesize = CrmTypes.CreateCrmNumber(generatedReport.Length),
        //      subject = reportFileName
        //  };


    /// <summary>
    /// Generates an SSRS Report and returns a byte[] for a CRM attachment
    /// </summary>
    /// <param name="reportPath">Name of the report.</param>
    /// <param name="parameters">The parameters.</param>
    /// <param name="outputFormat">The output format.</param>
    /// <param name="deviceInformation">The device information. See</param>
    /// <param name="ReportServiceUrl"></param>
    /// <param name="userName">if null, DefaultNetworkCredentials are used</param>
    /// <param name="passWord">if null, DefaultNetworkCredentials are used</param>
    /// <param name="domainName"></param>
    /// <returns></returns>
    public static byte[] GenerateSRSbytes(string reportPath, ParameterValue[] parameters, string outputFormat, string deviceInformation, string ReportServiceUrl, string userName, string passWord, string domainName)
        string encoding;
        string mimeType;
        string extension;
        string[] streamIDs;
        string SessionId;
        string historyID = null;
        Warning[] warnings;

        // By default the Report will run with the permissions of the AD authenticated User.
        var rs = new ReportExecutionService.ReportExecutionService
            Credentials = System.Net.CredentialCache.DefaultNetworkCredentials,
            Url = ReportServiceUrl

        // Impersonate credentials if they are specified. 
        if (userName != null && passWord != null)
            if (domainName == null)
                rs.Credentials = new NetworkCredential(userName, passWord);
                rs.Credentials = new NetworkCredential(userName, passWord, domainName);

        // Set timeout in seconds of the report takes a long time.
        //rs.Timeout = 600000;

        ExecutionHeader execHeader = new ExecutionHeader();
        rs.ExecutionHeaderValue = execHeader;

        var execInfo = new ExecutionInfo();
        execInfo = rs.LoadReport(reportPath, historyID);

        rs.SetExecutionParameters(parameters, "en-us");

        SessionId = rs.ExecutionHeaderValue.ExecutionID;

        // Render Report
        return rs.Render(outputFormat, deviceInformation, out extension, out mimeType, out encoding, out warnings, out streamIDs);


Happy Coding!

Wednesday, May 13, 2009

MS CRM 4.0 Many to Many Query helper

Rather than trying to explain how to query a many to many relationship, I thought it might be easier for many people to take a working generic method that retrieves a collection of entities from a Many to Many relationship and modify it as needed.

When a many to many relationship is created CRM creates a link table. Default naming conventions could give you a name like this


which is a link table between new_entity1 and new_entity2

The three entities involved are the following:

  • linkTableEntity - new_new_entity1_new_entity2
  • filterEntity – new_entity1
    • put your conditions against this
  • returnedEntity – new_entity2
    • specify your return attributes against this

The arguments to call this method are the following:

  • service = ICRMService
  • linkTableEntityName= "new_new_entity1_new_entity2"
  • filterEntityName = "new_entity1"
  • filterEntityIdName = "new_entity1id"
  • filterAttribute = "new_name"
  • filterValue = "my entity’s name"
  • returnedCollectionEntityName = "new_entity2"
  • returnedCollectionIdName = "new_entity2id"
  • returnedAttibutes = null  //null returns all columns
    or returnedAttributes = new[]{"new_entity2id","new_name"}
/// <summary>
/// Generic Method to retrieve a collection of all DynamicEntities related to another Entity in a Many to Many relationship
/// </summary>
/// <param name="service"></param>
/// <param name="linkTableEntityName"></param>
/// <param name="filterEntityName"></param>
/// <param name="filterEntityIdName"></param>
/// <param name="filterValue"></param>
/// <param name="returnedCollectionEntityName"></param>
/// <param name="returnedCollectionEntityIdName"></param>
/// <param name="filterAttribute"></param>
/// <param name="returnedAttributes">if null, AllColumns() is used</param>
/// <returns></returns>
public static BusinessEntityCollection RetrieveEntityCollectionFromManyToMany(ICrmService service, 
    string linkTableEntityName,
    string filterEntityName, string filterEntityIdName, string filterAttribute,  string filterValue,
    string returnedCollectionEntityName, string returnedCollectionEntityIdName, string[] returnedAttributes)
    // Selection against linked Filter Entity
    var con = new ConditionExpression
        AttributeName = filterAttribute,
        Operator = ConditionOperator.Equal,
        Values = new[] { filterValue }

    var filter = new FilterExpression
        FilterOperator = LogicalOperator.And

    // the Entity that you are filtering with
    var filterLinkEntity = new LinkEntity
           LinkToEntityName = filterEntityName,
           LinkFromAttributeName = filterEntityIdName,
           LinkToAttributeName = filterEntityIdName,
           LinkCriteria = filter
    // The linktable Entity that CRM generates
    var linkTableEntity = new LinkEntity
          LinkToEntityName = linkTableEntityName,
          LinkFromAttributeName = returnedCollectionEntityIdName,
          LinkToAttributeName = returnedCollectionEntityIdName


    // The Entity returning the results
    var expression = new QueryExpression
        EntityName = returnedCollectionEntityName

    // Set columns being returned
    if (returnedAttributes == null)
        expression.ColumnSet = new AllColumns();
        expression.ColumnSet = new ColumnSet(returnedAttributes);


    var request = new RetrieveMultipleRequest {Query = expression, ReturnDynamicEntities = true };

    var response = (RetrieveMultipleResponse)service.Execute(request);
    return response.BusinessEntityCollection;