:: Home     :: MS Dynamics CRM     :: .Net 1.1     :: .Net 2.0     :: Sharepoint Portal     :: Ajax

  login:        
  passwords:  

Reporting and Analysis

Custom Reporting


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

  1. Start Visual Studio .NET 2003.

  2. On the File menu, point to New, and then click Project.

  3. In the Project Types list, select Visual C# Projects, and in the Templates list, select ASP.NET Web Applications.

  4. In the Location box, type http://localhost/crmreports.

  5. Click OK.

  6. 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 id="report" 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>
Adding a Custom External Report to Microsoft CRM

  1. In the Workplace area, select the Reports subarea, and then click New.

  2. Select Link to Web page as the report type.

  3. In the Location box, type http://<crmserver>/workingwithcrm/webform1.aspx.

  4. In the Name box, type Custom Lead Report.

  5. In the Categories box, select Sales Reports.

  6. In the Related Record Types box, select Leads.

  7. 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.

  8. Click Save
Accessing the Custom External Report

  1. In the Workplace area, select the Reports subarea.

  2. Double-click Custom Lead Report.
::  Home :: Services ::  Prices ::  Request Quote
Copyright 2007, Megasolutions Ltd