MS Dynamics CRM 4.0 – 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 node. This area contains all of the RDL XML. The default template doesn’t include a 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:

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 node as shown before, but also add an node with the image. The code for the embedded image approach would look similar to:

This code can be placed anywhere within the parent 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!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: