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:
[OperationContract]
public List<DataPointDTO> GetChartSeries()
{
List<DataPointDTO> dataPoints = new
List<DataPointDTO>();
SqlConnection sqlCon = null;
SqlCommand sqlCom = null;
SqlDataReader dataReader = null;
try
{
sqlCon = new SqlConnection(
ConfigurationManager.ConnectionStrings[
"MicrosoftCRM_MSCRMConnectionString"].
ConnectionString);
sqlCom = new SqlCommand(OPPBYSTATUS, sqlCon);
sqlCon.Open();
dataReader = sqlCom.ExecuteReader();
while (dataReader.Read())
{
dataPoints.Add(new DataPointDTO { XSeries =
dataReader[0].ToString(),
YValue = Convert.ToDouble(dataReader[1]) });
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlCon != null)
sqlCon.Close();
}
return dataPoints;
}
[DataContract]
public class DataPointDTO
{
[DataMember]
public string XSeries {get; set;}
[DataMember]
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()
{
InitializeComponent();
Proxy.GetChartSeriesCompleted += new
EventHandler<GetChartSeriesCompletedEventArgs>
(Proxy_GetChartSeriesCompleted);
Proxy.GetChartSeriesAsync();
}
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:
Previous Page
