RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Enhance Microsoft Dynamics CRM 4.0 Reporting with Silverlight : Page 3

Reporting in Microsoft Dynamics CRM 4.0 has been significantly enhanced. Find out how to extend the built-in reporting features and create customized visually-enhanced reports using ASP.NET and Silverlight.


Building Advanced Custom Reports using ASP.NET and Silverlight

The ability to access CRM data using the filtered views opens a whole new gamut of opportunities to customize reports. For example, you can leverage security features using the filtered views and windows authentication to filter the displayed data automatically, using the privileges of the logged-in user.

The following example provides a step by step description of creating CRM reports using ASP.NET and Silverlight. The example shows how to build a WCF service that fetches data from CRM using the filtered views, and displays it in a Silverlight control. The graphical visualization of CRM data uses the VisiFire charting control—an open source data visualization component powered by Microsoft Silverlight.

Please note that to create Silverlight 2.0 applications, you need Visual Studio 2008 with Service Pack 1 and the Silverlight 2.0 Toolkit installed. Installing the Silverlight 2.0 Toolkit also installs templates for creating a Silverlight Project.

Step 1: Create the Silverlight Project.

In the New Project dialog in Visual Studio choose Silverlight: Silverlight Application under Project Types (see Figure 4).

Figure 4. New Silverlight Project: Choose the Silverlight Application template from Visual Studio's New Project dialog.
Name the project CRMDashboard. As Visual Studio creates the various components for the project, it will prompt you to specify whether you want to create a separate web application project to host the Silverlight application. Choose to create a new web application.

The CRM Silverlight report you will create in the next steps will display the total number of sales opportunities in the system factored by their status (Warm / Hot).

Step 2: Add a Silverlight Enabled WCF Service.

In the CRMDashboard project add a new file. Choose "Silverlight-Enabled WCF Service" from the New File dialog, and call the service CRMService. The Silverlight-Enabled WCF Service automatically:

  • Creates the Operation Contracts.
  • Generates the attributes and configuration that let the service be hosted on the web server.
Listing 1 shows the service configuration in web.config. Notice that the automatically-generated configuration is suitably modified for security and has a service end-point address reference.

Step 3: Specify the DataContract and OperationContract to fetch information from CRM filtered views.

Modify the CRMService to add the DataContract attribute. When you add the WCF Service, the OperationContract is automatically generated. Rename the OperationContract method as GetChartSeries. The method returns a list of DataPoints that the application uses to create a DataSeries for the Chart.

Author's Note: Silverlight does not support DataTables.

Here's the code for the OperationContract and DataContract:

   public List<DataPointDTO> GetChartSeries()
      List<DataPointDTO> dataPoints = new 
      SqlConnection sqlCon = null;
      SqlCommand sqlCom = null;
      SqlDataReader dataReader = null;
         sqlCon = new SqlConnection(
         sqlCom = new SqlCommand(OPPBYSTATUS, sqlCon);
         dataReader = sqlCom.ExecuteReader();
         while (dataReader.Read())
            dataPoints.Add(new DataPointDTO { XSeries = 
               YValue = Convert.ToDouble(dataReader[1]) });
      catch (Exception ex)
         throw ex;
         if (sqlCon != null)
      return dataPoints;
   public class DataPointDTO
      public string XSeries {get; set;}
      public double YValue {get; set;}
In the DataContract note that there are two methods marked with the DataMember attribute. These specify the X and Y co-ordinate values for the chart. The GetChartSeries method executes an SQL query (to get a list of Sales Opportunities by Status) and returns a list of DataPoints. The following query on the FilteredOpportunity view returns the count of Opportunities by Status:

   Select OpportunityRatingCodeName, 
      Count(opportunityid) as Opportunities 
   FROM FilteredOpportunity 
   GROUP BY OpportunityRatingCodeName

Step 3: Generate the Service Proxy.

Use the svcutil.exe utility that installs with the .NET Framework 3.0 to generate a service proxy file (CRMServiceProxy) and add it to the CRMDashboard project.

Step 4: Create Charts from CRM Data.

Open the Page.xaml.cs file from the CRMDashboard project. Note the Proxy property, a static property that creates an instance of the service client (CRMServiceClient in this case). Also note that the CRMServiceClient allows only asynchronous calls to the GetChartSeries method (because Silverlight doesn't support synchronous calls). The event handler returns the query results—used to generate the Data Series for the chart.

   public Page()
     Proxy.GetChartSeriesCompleted += new 
Listing 2 shows the CreateChart method implementation, which returns a VisiFire Chart Object. You must add a reference to the SLVisiFire.Charts.dll assembly that ships with the VisiFire data visualization components to the CRMDashboard project to create the Chart.

You specify the end point address for the client via the InitParameters property of the Silverlight package. To do that, open the CRMDashboardTestPage.aspx file, and specify the service end point URL in the InitParameters property.

Figure 5. Opportunity by Status Report: Here's the completed graphical report running in Silverlight.

Step 5: Host the Silverlight Web Application and Load the Report.

You need to host the Silverlight application in a Virtual Directory. After hosting the application, create a new report from the Workspace → Reports section, and choose "Link to a File" as the Report Type. Set the link target to the CRMDashboardTestPage.aspx page, and name the report "Opportunity Status." Save the new report, and close the New Report dialog. The report is now accessible from the Reports section (see Figure 5).

Report Snapshots and Sharing

Microsoft Dynamics CRM 4.0 provides additional actions on reports, such as Scheduling and Sharing. You'll find these additional features under Reports → More Actions. The Scheduling feature lets you capture snapshots of data at specific points in time, and save parameters and filter criteria for future use. To create a snapshot, click More Actions → Schedule Report, and then follow the wizard's instructions.

Author's Note: The scheduling feature is available only for SQL Server Reporting Services reports.

The reporting feature in Microsoft Dynamics CRM 4.0 also lets you share reports with other users or teams. Select the More Actions → Report → Sharing to add users or teams and assign permissions for your reports.

Although this article discussed only one sample chart that retrieved data on "Opportunities by Status," you should be able to extend the ideas in that code to create more charts, and add them to your pages, providing useful visual information and a modern dashboard look. Be sure to download the sample code to get a head start on building your own charts.

Finally, you may want to check these sources for more information:

Sandeep Chanda has more than five years of experience in several Microsoft technologies. He currently works for Neudesic, a Microsoft National Systems Integrator and Gold Certified Partner, using technologies such as Silverlight 2.0, SharePoint, and Microsoft Dynamics CRM 4.0. Prior to joining Neudesic, Sandeep built applications for the credit card processing industry, using Microsoft BizTalk Server 2004/2006, Windows Workflow Foundation, and Microsoft SQL Server 2005.
Email AuthorEmail Author
Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date