Sonoma Partners Microsoft CRM and Salesforce Blog

Programming Microsoft Dynamics CRM 4.0 book review

We just noticed this review of our recently released Programming Microsoft Dynamics CRM 4.0 book. We received some really kind words from Catherine Eibner, who works as a CRM consultant Down Under. Here's a snippet of her review:

"Initially I just flicked through it to see what was in it, but then kept getting drawn into the examples & sample code and soon had visual studio open trying to work through some of them. I have to say – if you are thinking of doing any programming for CRM 4.0 it really is a fantastic book for you! It starts at the very basics – includes a hitchhikers guide to common questions when you're getting started and extends to some advanced topics like building your own advanced workflows.

I am now tempted to work through the book from start to finish as there are so many cool examples in here that I haven’t yet had the opportunity or excuse to delve into. There are some great code samples & utilities tucked into the different chapters as well as addressing the differences in the 3 deployment options."

You can read her full review if you're interested, and of course you can order a copy of the book on Amazon.com. At more than 600 pages and weighing almost 3 lbs, it makes a great stocking stuffer this Holiday season! (Just jokes, just jokes)

Customizing the Report Wizard Template

The report wizard functionality of Microsoft Dynamics CRM 4.0 allows end users to quickly and easily create basic Reporting Services reports. After the user completes the wizard, CRM creates an RDL file that can then be rendered within the CRM Reporting Services viewer. A common question we get is how to change the template used by the report wizard. Well, my colleague, Brian, found a solution to this request. In this post, we discuss how to add your company logo to the report template used by the wizard.

Note: This approach is definitely unsupported, so use at your own risk!

We will go through the following steps to update the template:

  1. Backup the existing template
  2. Create a simple tool to extract the template from the database
  3. Customize the template by adding our logo to the header
  4. Import the template back to the SQL database

Step 1 - Backup existing template

The report wizard template is located in the body field of the ApplicationBaseFile table within the _MSCRM database. We recommend that you back up this data prior to any alterations. You can do this simply by executing the following SQL statement in the _MSCRM database which creates a backup table in your _MSCRM database to store the template data:

select body into dbo.ApplicationFileBase_Backup from ApplicationFileBase

Creating your own tables within the _MSCRM database is typically frowned upon, so you could instead copy this to a backup table in another database.

Step 2 - Extract template from SQL

Unfortunately, since SQL Management Studio limits its output to 64KB, retrieving the template is not as simple as just selecting the body text and copy and pasting into your favorite XML editor. While there are a number of ways to accomplish this, we decided to write a very simple .NET application to extract the template. Create a console application in Visual Studio, and paste in the following code. Be sure to update the sqlServerName and databaseName variables with your information. Run the application and your template will be saved on the c: drive in a file called report_template.xml.

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.IO;

namespace CrmExtractTemplate
{
 class Program
 {
  static void Main(string[] args)
  {
   //update the sql server name
   string sqlServerName = "sqlserver";
   string databaseName = "organization_mscrm";
   string connectionString = String.Format("Data Source={0};Initial Catalog={1};Integrated Security=SSPI", sqlServerName, databaseName);

   SqlConnection conn = new SqlConnection(connectionString);
   conn.Open();

   string sqlText = "select body from applicationfilebase";
   SqlCommand cmd = new SqlCommand(sqlText);

   cmd.Connection = conn;
   cmd.CommandType = CommandType.Text;

   SqlDataReader rdr = cmd.ExecuteReader();

   while (rdr.Read())
   {
    TextWriter log = TextWriter.Synchronized(File.AppendText(@"c:\report_template.xml"));
    log.Write(rdr["body"].ToString());
    log.Close();
   }
  }
 }
}

Step 3 - Add your logo to the template file

The report wizard template is a custom XSL stylesheet that CRM uses to transform into a valid RDL file. To update it, you need to find the actual RDL code, which is located within the <Report> node. This area contains all of the RDL XML. The default template doesn't include a <PageHeader> node, so you need to add one with your image information.

Now that you know where in the template file to add the image, you need to determine what XML to add. The easiest way to do that is to actually create a new report in a tool like Visual Studio .NET or Business Intelligence Design Studio. Also, by creating it first in a tool, you can be sure of the placement and sizing settings.

You can add an image as an external link or embed it in the report. The code for an external image would look similar to:

<PageHeader>
 <PrintOnFirstPage>true</PrintOnFirstPage> 
 <ReportItems>
  <Image Name="image1">
  <Sizing>AutoSize</Sizing> 
  <Width>4.01042in</Width> 
  <MIMEType /> 
  <Source>External</Source> 
  <Style /> 
  <Value>http://www.sonomapartners.com/images/logo.jpg</Value> 
  </Image>
 </ReportItems>
 <Height>0.79167in</Height> 
 <PrintOnLastPage>true</PrintOnLastPage> 
</PageHeader>

However, if the image is small enough, you could also choose to embed it in the report. To do this, you would not only add the <PageHeader> node as shown before, but also add an <EmbeddedImages> node with the image. The code for the embedded image approach would look similar to:

   <PageHeader>
    <PrintOnFirstPage>true</PrintOnFirstPage>
    <ReportItems>
     <Image Name="Image15">
      <Source>Embedded</Source>
      <Value>sonomalogosmall</Value>
      <Sizing>AutoSize</Sizing>
      <Width>4.01042in</Width>
     </Image>
    </ReportItems>
    <Height>0.79167in</Height>
    <PrintOnLastPage>true</PrintOnLastPage>
   </PageHeader>

   <EmbeddedImages>
    <EmbeddedImage Name="sonomalogosmall">
     <MIMEType>image/jpeg</MIMEType>
     <ImageData>
      /9j/4AAQSkZJRgABAQEAYABgAAD... remaining encoded image removed for brevity
</ImageData> </EmbeddedImage> </EmbeddedImages>

This code can be placed anywhere within the parent <Report></Report> node in the template file.

Step 4 - Import the template file back to SQL Server

Luckily, you don't need to rely on .NET to get the file back into SQL Server. Copy your template file to the c:\drive of your SQL Server, and then from SQL Management Studio, execute the following SQL:

create table dbo.ApplicationFileBase_tempLoad
(
 Body xml
)
insert ApplicationFileBase_tempLoad (body) 
select BulkColumn from openrowset( bulk 'C:\Report_Template.xml', Single_Blob) as Body
update ApplicationFileBase
set Body = (select top 1 convert(nvarchar(max), Body) from ApplicationFileBase_tempLoad)

drop table dbo.ApplicationFileBase_tempLoad

Now when you create a new report wizard, your new logo appears in the top left corner of the report!

 

Microsoft Dynamics CRM Incubation Week

The first ever Microsoft Dynamics CRM Incubation Week will be taking place in Reston, VA on December 15. I am pleased to have the opportunity to participate in this exciting event as one of the CRM advisors. The CRM Incubation Week is designed for early stage companies to learn and prototype their solutions on the Dynamics CRM platform with direct assistance from the Microsoft team and partners. Further, the companies that participate will have the opportunity to present their solutions and business plans to investors and other knowledgeable experts providing a new channel of marketing and relationship building.

For more information about this event, please visit Sanjay Jain's blog at: http://blogs.msdn.com/sanjayjain/archive/2008/11/14/microsoft-dynamics-crm-incubation-week.aspx.

Been delaying a Microsoft CRM purchase? Microsoft now offers zero percent financing

Microsoft today announced zero percent financing for 36 months for new customers of Microsoft Dynamics ERP and CRM solutions. The limited time offer is available to Microsoft Dynamics customers who receive Microsoft Financing credit approval on all purchases of $20,000 up to $1 million.

In today's uncertain economy, this offer obviously makes it easier for customers to invest in Microsoft CRM. We're really excited to be able to offer this!

You can read the official Microsoft release here...

Displaying the Number of Notes on the Notes Tab

The Microsoft CRM default form layout displays the Notes section on a separate tab. I often hear complaints from users that they don't know if any notes have been added without first clicking that tab. Many people don't realize that just as with any other section, you can move the Notes section to another tab, just as you would any other section. So one approach that many of our users have liked is to place the Notes section on the first tab as shown below.

This works, but I personally find it annoying as the the cursor will 'jump' down to the Notes section, sometimes scrolling past the info on top set of information, and you have less room to see multiple notes. Since I tend to keep the Notes section on its own separate tab, I wanted to find a way to let users know that data exists on that tab prior to clicking it. I created the following script to display the number of notes on the tab label as shown in the screen shot below.

The script I used is shown below and should be added to the entity's form onLoad function. Since this approach is entirely script based, it should also work on CRM Online. The tab where the Notes section exists must be called Notes for the script to work.

var totalNotes = getTotalNotes(crmForm.ObjectId);
setNoteTabName(totalNotes);

function setNoteTabName(count) {
    /* update note tab */
    if (crmForm.FormType != 1) {
        var cells = document.getElementsByTagName("A");

        for (var i = 0; i < cells.length; i++) {
            if (cells[i].innerText == "Notes") {
                if (count > 0) {
                        cells[i].innerText = "Notes (" + count + ")";
                        document.all.crmTabBar.style.width = "auto";
                }
                break;
            }
        }
    }
}

// Helper method to return the total notes associated with an object
function getTotalNotes(objectId) {
        // Define SOAP message
        var xml =
        [
        "<?xml version='1.0' encoding='utf-8'?>",
        "<soap:Envelope xmlns:soap=\"
http://schemas.xmlsoap.org/soap/envelope/\" ",
        "xmlns:xsi=\"
http://www.w3.org/2001/XMLSchema-instance\" ",
        "xmlns:xsd=\"
http://www.w3.org/2001/XMLSchema\">",
        GenerateAuthenticationHeader(),
        "<soap:Body>",
        "<RetrieveMultiple xmlns='
http://schemas.microsoft.com/crm/2007/WebServices'>",
        "<query xmlns:q1='
http://schemas.microsoft.com/crm/2006/Query' ",
        "xsi:type='q1:QueryExpression'>",
        "<q1:EntityName>annotation</q1:EntityName>",
        "<q1:ColumnSet xsi:type=\"q1:ColumnSet\"><q1:Attributes><q1:Attribute>createdon</q1:Attribute></q1:Attributes></q1:ColumnSet>",
        "<q1:Distinct>false</q1:Distinct><q1:Criteria><q1:FilterOperator>And</q1:FilterOperator>",
        "<q1:Conditions><q1:Condition><q1:AttributeName>objectid</q1:AttributeName><q1:Operator>Equal</q1:Operator>",
        "<q1:Values><q1:Value xsi:type=\"xsd:string\">",
        objectId,
        "</q1:Value></q1:Values></q1:Condition></q1:Conditions></q1:Criteria>",
        "</query>",
        "</RetrieveMultiple>",
        "</soap:Body>",
        "</soap:Envelope>"
        ].join("");
        var resultXml = executeSoapRequest("RetrieveMultiple", xml);
        return getMultipleNodeCount(resultXml, "q1:createdon");
}

// Helper method to execute a SOAP request
function executeSoapRequest(action, xml) {
    var actionUrl = "
http://schemas.microsoft.com/crm/2007/WebServices/";
    actionUrl += action;

    var xmlHttpRequest = new ActiveXObject("Msxml2.XMLHTTP");
    xmlHttpRequest.Open("POST", "/mscrmservices/2007/CrmService.asmx", false);
    xmlHttpRequest.setRequestHeader("SOAPAction", actionUrl);
    xmlHttpRequest.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
    xmlHttpRequest.setRequestHeader("Content-Length", xml.length);
    xmlHttpRequest.send(xml);

    var resultXml = xmlHttpRequest.responseXML;
    return resultXml;
}

// Helper method to return total # of nodes from XML
function getMultipleNodeCount(tree, el) {
    var e = null;
    e = tree.getElementsByTagName(el);
    return e.length;
}

Naturally, all of the caveats apply...this code is presented as is and may not upgrade with future releases of Microsoft CRM.

Finally, since we are discussing Notes, a hot fix exists if you see that your Notes area just displays the spinning icon as referenced in this KB article:
http://support.microsoft.com/kb/951174

I've been everywhere, man

ChoiceLogos

Sure you know that Choice Hotels has those catchy TV commercials with the "I've been everywhere man" song, but did you know that Choice Hotels is implementing Microsoft Dynamics CRM 4.0? Even though we're excited that another organization selected Microsoft Dynamics CRM as their enterprise platform...we're really excited that Choice Hotels selected Sonoma Partners to run their CRM implementation for them.

You can read the official Microsoft press release if you want to get more details!