roviding flexible reporting capabilities has been one of the key goals for the Microsoft Dynamics series of products. Microsoft signaled a significant shift in this regard with the launch of Microsoft Dynamics CRM 4.0. Coupled with powerful features such as advanced find, data filters and drill through, and a complete reporting infrastructure built on SQL Server 2005 Reporting Services (SSRS), Microsoft Dynamics CRM 4.0 is capable of providing a robust reporting paradigm.

Out-of-the-Box Reporting using Report Wizard

The Report Wizard lets users of Microsoft Dynamics CRM 4.0 create on-the-fly reports. Microsoft Dynamics CRM 4.0 also comes with some built-in reports that you can easily extend using the Report Wizard. You can access the built-in reports from:

• The system entities (Accounts, Contacts etc).
• The Reports section under the Workspace tab (see Figure 1).

Figure 1. Built-in CRM Reports: You can access the built-in reports from either the system entities (Accounts, Contacts, etc.) or the Reports section under the Workspace tab as shown here.

Figure 2. New Report Wizard: Fill out this wizard page to create a new report.

Creating a New Report

To create a new report select the Workspace tab on the Reports page, and then click New. The New Report dialog shows up (see Figure 2).

In the New Report dialog you have the following options to create a report:

• Report Wizard report: Uses the wizard to create a report.
• Existing file: Upload an existing report created using SSRS.
• Link to a web page: Specify the URL of a custom report web page.

To create a new report using the Report Wizard, select the “Report Wizard Report” option in the Report Type dropdown as shown in Figure 2, and then click the Report Wizard button. A series of steps guides you through the process of creating a new report or extending an existing one.

Advanced Reporting with SSRS

More often than not, enterprises require custom reports built on CRM data. You can build custom reports using a SQL Server Reporting Service (SSRS) report editor such as Visual Studio.

Although you can create custom SSRS reports by directly querying the CRM database, that’s not recommended. Instead, CRM Database exposes a set of views that are pre-filtered for report generation purpose. Using CRM Database views, you don’t have to understand the CRM entity model completely; you can leverage the views (see Figure 3) to generate reports quickly.

Figure 3. Filtered Views: Use existing CRM Database views to simplify report generation.

Follow these steps to create a report from an existing one:

1. In Workspace ? Reports, click on the report that you want to extend.
2. Click Edit Report.
3. In the report dialog, under Actions, click Download Report.
4. Download and save the report, then rename it to a meaningful name.
5. Create a Report Server project in Visual Studio, and add the saved report to the project.
6. Modify the connection string information in the data source appropriately for your environment.

Now you can start customizing the report. As an example, the following query returns the list of Accounts in the CRM database.

   select [name] from FilteredAccount

To store the newly created report in CRM, click New from Workspace ? Reports and set the Report Type to “Existing File.” Specify the location of the report in the File Location field, and save the report.

Now you are ready to run the custom report.

Report Parameters

While modifying the existing report, note that the wizard defined a set of report parameters (in the report parameters dialog). CRM uses a set of special parameters for features such as drill through and filtering. Note that parameters prefixed with CRM_ are treated as hidden by default in CRM, and users will not be prompted to enter them. The wizard creates the following special parameters for the specific purposes defined below:

• CRM_FilterText: The default filter is the initial value for this parameter. The parameter value is displayed in the report summary text box present at the report header.
• CRM_URL: This parameter is used for drilling through to CRM from the report. For example a report on the Account entity can provide a link on the account name and clicking the link will take you to the details page of the record for editing.
• CRM_Filtered: These parameters are displayed in the “filtering criteria” section of the report and you use them to specify pre-filtering conditions. You can use multiple CRM_Filtered parameters such as CRM_FilteredContact and CRM_FilteredAccount in a report. It is important to note that the queries need to be suitably formatted for the pre-filtering criteria to work. For example, the query to return the Account names should be modified to:

   DECLARE @SQL nvarchar(4000)   SET @SQL = '   SELECT [name] FROM ('+@CRM_FilteredAccount+') AS FA'   EXEC (@SQL)

Alternatively, you could write the same query as:

   SELECT [name] FROM FilteredAccount AS CRMAF_FilteredAccount  

Filtered views aliased as CRMAF_FilteredEntity are automatically picked up for pre-filtering and don’t need the pre-filtering parameters defined.

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.

Here’s the code for the OperationContract and DataContract:

   [OperationContract]   public List GetChartSeries()   {      List dataPoints = new          List();      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        (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.

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:

• The Microsoft Dynamics CRM 4.0 SDK (be sure to check the Report Writer’s Guide section)
• Microsoft Dynamics CRM Home
• 11 Things to Know about Customizations
• VisiFire Data Visualization Components
• More on Silverlight