Customizing the Report Wizard Template

Posted by Jim Steger on November 21, 2008

---

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 ApplicationFileBaseset 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!

 

Posted by Jim Steger on November 21, 2008 | Permalink