Custom Reporting | Working with Microsoft Dynamics(TM) CRM 3.0

In addition to all of the reporting options we've already covered, you can create your own custom Web pages for reporting on Microsoft CRM data. Then you can integrate these custom Web page reports directly into the Microsoft CRM user interface. This reporting option might appeal to companies that have strong Web development skills but not a lot of experience with writing reports using SQL Services Reporting Services.

In the following example, we will show you how to tie a custom Web-based report in to Microsoft CRM. This example consists of a simple .NET Web page that displays data from Microsoft CRM using the security credentials of the user viewing the report. Because we will be using the filtered views and integrated Windows authentication in our Web page, the report security will be conveniently handled by Microsoft CRM.

Best Practices

Although a Microsoft ASP.NET Web reporting approach might help companies without expertise in creating SQL Server Reporting Services reports, you will miss out on powerful features such as pre-filtering, report caching, snapshots and e-mail delivery. Therefore, we recommend that you create your custom reports using SQL Server Reporting Services whenever possible.

Creating a Custom External Report

Start Visual Studio .NET 2003.
On the File menu, point to New, and then click Project.
In the Project Types list, select Visual C# Projects, and in the Templates list, select ASP.NET Web Applications.
In the Location box, type http://localhost/crmreports.
Click OK.
Enter the following code in WebForm1.aspx HTML view. Be sure to enter your SQL Server database and Microsoft CRM organization name as indicated.

 <%@ Page language="c#" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <html> <script language="C#" runat="server"> protected void Page_Load(Object sender, EventArgs e) {        string sql = "select fullname as 'Full Name', address1_telephone1 as 'Business Phone', owneridname as 'Owner' from FilteredLead";  SqlConnection conn = new SqlConnection("server=<yourcrmdatabaseserver>;database=<orga nization>_mscrm; Integrated Security=SSPI"); SqlDataAdapter myCommand = new SqlDataAdapter(sql, conn); DataSet ds = new DataSet(); myCommand.Fill(ds, "Leads"); report.DataSource = ds.Tables["Leads"].DefaultView; report.DataBind(); } </script> <body>  <h3><font face="Verdana">Lead Report</font></h3>  <asp:datagrid  runat="server"  width="700"  backcolor="#ccccff"  bordercolor="black"  showfooter="false"  cellpadding=3  cellspacing="0"  font-name="Verdana"  font-size="8pt"  headerstyle-backcolor="#aaaadd"  enableviewstate="false"  autogeneratecolumns="True"  /> </body> </html>

Now we have to deploy our report to the Web server running CRM. To do this, we must first create a virtual Web on the Microsoft CRM server and then deploy the file to that new Web. For our virtual directory, we use the workingwithcrm virtual Web. (You can find instructions on creating this in the book Introduction.)

After you create the workingwithcrm virtual Web, copy the Webform1.aspx, the Global.asax, and the Web.config files from your project directory to the C:\Inetpub\workingwithcrm directory on the server running Microsoft CRM.

Because we created this virtual directory underneath the Microsoft CRM Web site, our virtual Web site will inherit the Web.config settings of Microsoft CRM. This could generate some errors as Microsoft CRM loads assemblies not necessarily available to your Web site. To prevent this problem, you might have to update your Web.config file and remove the extra assemblies. In your default Web.config, locate the compilation node.

 <compilation     defaultLanguage="c#"     debug="true" />

In the Microsoft CRM Web.config file, you will see the following lines in the Reporting Services section. Simply replace the compilation node of the Web.config in your example virtual Web with the following code.

 <compilation defaultLanguage="C#" debug="true">    <assemblies>     <remove assembly="Microsoft.Crm, Version=3.0.5300.0, Culture=neutral, PublicKeyToken=31b f3856ad364e35"/>     <remove assembly="Microsoft.Crm.Entities, Version=3.0.5300.0, Culture=neutral, PublicKey Token=31bf3856ad364e35"/>     <remove assembly="Microsoft.Crm.ManagedInterop, Version=3.0.5300.0, Culture=neutral, Pub licKeyToken=31bf3856ad364e35"/>     <remove assembly="Microsoft.Crm.MetadataHelper, Version=3.0.5300.0, Culture=neutral, Pub licKeyToken=31bf3856ad364e35"/>     <remove assembly="Microsoft.Crm.MetadataService, Version=3.0.5300.0, Culture=neutral, Pu blicKeyToken=31bf3856ad364e35"/>     <remove assembly="Microsoft.Crm.NativeInteropProxy, Version=3.0.5300.0, Culture=neutral,  PublicKeyToken=31bf3856ad364e35"/>     <remove assembly="Microsoft.Crm.ObjectModel, Version=3.0.5300.0, Culture=neutral, Public KeyToken=31bf3856ad364e35"/>     <remove assembly="Microsoft.Crm.Platform.ComProxy, Version=3.0.5300.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>     <remove assembly="Microsoft.Crm.Platform.Proxy, Version=3.0.5300.0, Culture=neutral, Pub licKeyToken=31bf3856ad364e35"/>     <remove assembly="Microsoft.Crm.Platform.Server, Version=3.0.5300.0, Culture=neutral, Pu blicKeyToken=31bf3856ad364e35"/>     <remove assembly="Microsoft.Crm.Platform.Sdk, Version=3.0.5300.0, Culture=neutral, Publi cKeyToken=31bf3856ad364e35"/>     <remove assembly="Microsoft.Crm.Platform.Types, Version=3.0.5300.0, Culture=neutral, Pub licKeyToken=31bf3856ad364e35"/>     <remove assembly="Microsoft.Crm.Scheduling, Version=3.0.5300.0, Culture=neutral, PublicK eyToken=31bf3856ad364e35"/>     <remove assembly="Microsoft.Crm.Tools.ImportExportPublish, Version=3.0.5300.0, Culture=n eutral, PublicKeyToken=31bf3856ad364e35"/>    </assemblies> </compilation>

Caution

The actual version numbers might change, so be sure to check the Microsoft CRM Web.config file for the actual values used.

Now that we have our custom report developed, we want to add it to Microsoft CRM. Although technically you could upload it through the Reporting Services Manager, we recommend that you always add your reports through the Microsoft CRM interface. Let's do that now.

Adding a Custom External Report to Microsoft CRM

In the Workplace area, select the Reports subarea, and then click New.
Select Link to Web page as the report type.
In the Location box, type http://<crmserver>/workingwithcrm/webform1.aspx.
In the Name box, type Custom Lead Report.
In the Categories box, select Sales Reports.
In the Related Record Types box, select Leads.
In the Display In box, select Reports area, Forms for related record types, and Lists for related record types. We will explain these options later in this chapter.
Click Save.

Now that you've added the custom Web report to Microsoft CRM, your users can run this report directly within the Microsoft CRM user interface.

Accessing the Custom External Report

In the Workplace area, select the Reports subarea.
Double-click Custom Lead Report.

This example showed how you can create your own custom Web-based reports using ASP.NET. Remember to create your Web pages so that they use integrated Windows authentication and reference the Microsoft CRM filtered views, and the Web reports will automatically display the correct data to users based on their security settings.