Step by Step: Creating Microsoft Dynamics CRM 4 Reports Using Silverlight, RIA Services and VisiFire

I recently read an article on how to build advanced custom CRM reports using ASP.NET and Silverlight.  Since it was using Visual Studio 2008 and Silverlight 2, I decided to write a more updated version using Visual Web Developer 2010 Express and Silverlight 4.  Also I decided to make the report work with the on-premise version of CRM 4.0 and as you will see it looks very similar to one of the dashboards enabled for the on-demand version of CRM 4.0.

First you will need to make sure you have the necessary tools installed to build a Silverlight application.  I decided to go with Visual Web Developer 2010 Express because it is free and Silverlight 4 Developer tools for VS.NET 2010.  You will also need to download VisiFire Once you have your development environment setup you can follow these steps to easily create a new dashboard for your on-premise CRM instance.

Figure 1

Figure 1

Step 1: Create a Silverlight 4 Project.

Open up Visual Web Developer 2010 Express and select New Project from the Start page or File menu.  Expand the Visual C# project templates and select Silverlight.  From the Silverlight project types, choose Silverlight Application (see Figure 1).  Name your project CrmSalesPipelineDashboard and click OK.

Figure 2

Figure 2

Now you will see a popup which allows you to choose how to host your Silverlight application. Choose to host the Silverlight application in a new Website with type ASP.NET Web Application Project (which should be the default).  Choose Silverlight 4 and check the Enable WCF RIA Services option (see Figure 2).  After you click OK it will create your two new projects (CrmSalesPipelineDashboard and CrmSalesPipelineDashboard.Web) in one solution.

Step 2:  Add an ASP.NET Entity Data Model

Figure 3

Figure 3

Add a new Models folder to the CrmSalesPipelineDashBoard.Web project where you can store your data models.  Right click on the new Models folder and select Add New Item which will open a dialog box.  Select the Data section under Visual C# and select the ADO.NET Entity Data Model.  Change the name to CrmModel.edmx and click Add (see Figure 3).

Figure 5

Figure 5

Figure 4

Figure 4

Now you will see the Entity Data Model Wizard popup dialog.  Select Generate from database and click Next (see Figure 4).  Either create a new Connection to your CRM database or choose an existing one from the dropdown list.  After you click Next, select the StringMap table from the list of Tables.  Then select the Opportunity view from the list of Views.  Leave the default options checked and change the Model Namespace name to CrmModel and click Finish (see Figure 5).  The new model window will be displayed with the new StringMap and Opportunity entities.

Build the solution to make sure everything is still in order.

Step 3: Add a Domain Service Class

Figure 6

Figure 6

Add a new Services folder to the CrmSalesPipelineDashboard.Web project where you can store your services.  Right click on the new Services folder and select Add New Item which will open a dialog box.  Select the Web section under Visual C# and select the Domain Service Class.  Change the name to CrmDomainService.cs and click Add (see Figure 6).

Figure 7

Figure 7

Now you will see the Add New Domain Service Class popup dialog.  Check the checkbox by the Opportunity and StringMap entities, uncheck the Generate associated classes for metadata and click OK (see Figure 7).

Insert the GetSalesPipeline method (see Listing 1) after the GetStringMaps method in the CrmDomainService class.

Listing 1
//Return the total estimated value for each sales pipeline step
public IQueryable<SalesPipeline> GetSalesPipeline()
{
IQueryable<SalesPipeline> salesPipeline = from opportunity in this.GetOpportunities()
join stringMaps in this.GetStringMaps() on opportunity.StatusCode equals stringMaps.AttributeValue
where stringMaps.AttributeName == "statuscode" && stringMaps.ObjectTypeCode == 3
group new { opportunity, stringMaps } by stringMaps.Value into g
where g.Sum(o => o.opportunity.EstimatedValue).HasValue && g.Sum(o => o.opportunity.EstimatedValue).Value > 0
select new SalesPipeline { SalesPipelineStepName = g.Key, TotalEstimatedValue = g.Sum(o => o.opportunity.EstimatedValue).Value };

return salesPipeline;
}

Insert the SalesPipeline class (see Listing 2) after the CrmDomainService class.

Listing 2
public class SalesPipeline
{
[Key]
public string SalesPipelineStepName { get; set; }
public decimal TotalEstimatedValue { get; set; }
}

Build the solution to make sure everything is still in order.

Step 4: Add the Pie Chart to Silverlight

Download the latest version of VisiFire (http://www.visifire.com) and unzip it to C:\VisiFire.  Add a references to the both the FJ.Core.dll and SL.Visifire.Charts.dll to the CrmSalesPipelineDashboard project.

Insert the XAML necessary to display the pie chart on the page (see Listing 3).

Listing 3
<vc:Chart Name="salesPipelineChart" xmlns:vc="clr-namespace:Visifire.Charts;assembly=SLVisifire.Charts" View3D="True" Theme="Theme1" Width="800" Height="400">
<vc:Chart.Titles>
<vc:Title Text="Sales Pipeline" FontFamily="Verdana" FontSize="20" FontWeight="Bold" />
</vc:Chart.Titles>
<vc:Chart.Series>
<vc:DataSeries RenderAs="Pie" DataSource="{Binding Data, ElementName=salesPipelineDomainDataSource}">
<vc:DataSeries.DataMappings>
<vc:DataMapping MemberName="AxisXLabel" Path="SalesPipelineStepName" />
<vc:DataMapping MemberName="YValue" Path="TotalEstimatedValue" />
</vc:DataSeries.DataMappings>
</vc:DataSeries>
</vc:Chart.Series>
</vc:Chart>
<riaControls:DomainDataSource Name="salesPipelineDomainDataSource" xmlns:riaControls="clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls.DomainServices" xmlns:my="clr-namespace:CrmSalesPipelineDashboard.Web.Services" AutoLoad="True" d:DesignData="{d:DesignInstance my:SalesPipeline, CreateList=true}" QueryName="GetSalesPipelineQuery" Width="0" Height="0">
<riaControls:DomainDataSource.DomainContext>
<my:CrmDomainContext />
</riaControls:DomainDataSource.DomainContext>
</riaControls:DomainDataSource>

Build the solution to make sure everything is still in order.

Finished Pie Chart

Finished Pie Chart

Step 5: View the Sales Pipeline Pie Chart

Run the solution by pressing F5 and view the pie chart in the browser.  You will notice that out-of-the-box VisiFire allows you to click on the different pieces of the pie to pull them out.

 

Other Resources

If you are interested in learning more about .NET RIA Services and how to use them in Silverlight I found these videos helpful:
http://www.silverlight.net/learn/videos/all/net-ria-services-intro/
http://www.silverlight.net/learn/videos/all/ria-services-support-visual-studio-2010/

10 thoughts on “Step by Step: Creating Microsoft Dynamics CRM 4 Reports Using Silverlight, RIA Services and VisiFire

  1. Hi
    I have tried the solution but have a couple of errors left ( Using Visual Studio 2010 )

    Error 2 Event handler ‘salesPipelineDomainDataSource_LoadedData’ not found on class ‘CrmSalesPipelineDashboard.MainPage’ c:\users\jds\documents\visual studio 2010\Projects\CrmSalesPipelineDashboard\CrmSalesPipelineDashboard\MainPage.xaml CrmSalesPipelineDashboard

    And this

    Error 3 Type ‘my:SalesPipeline’ was not found. c:\users\jds\documents\visual studio 2010\Projects\CrmSalesPipelineDashboard\CrmSalesPipelineDashboard\MainPage.xaml 23 276 CrmSalesPipelineDashboard

    • Jack,
      Thanks for your comment. It appears there is an autogenerated method I didn’t include in my listings. Try adding this method to the MainPage.xaml.cs file and recompiling:

      private void salesPipelineDomainDataSource_LoadedData(object sender, System.Windows.Controls.LoadedDataEventArgs e)
      {
      if (e.HasError)
      {
      System.Windows.MessageBox.Show(e.Error.ToString(), “Load Error”, System.Windows.MessageBoxButton.OK);
      e.MarkErrorAsHandled();
      }
      }

      This should fix the first error and if it doesn’t fix the second error please let me know.

      Thanks,
      Paul

  2. Hi
    That solved the first error and I can Build the solution but when I try to load mainpage.xaml this error is showing:

    Error 1 Type ‘my:SalesPipeline’ was not found.

    When I try to run the solution I get this error:

    + InnerException {System.ArgumentException: The type CrmDomainContext does not expose a method called ‘GetSalesPipelineQuery’.
    på System.Windows.Controls.DomainDataSource.DomainContextPropertyChanged(DependencyObject depObj, DependencyPropertyChangedEventArgs e)
    på System.Windows.DependencyObject.RaisePropertyChangeNotifications(DependencyProperty dp, Object oldValue, Object newValue)
    på System.Windows.DependencyObject.UpdateEffectiveValue(DependencyProperty property, EffectiveValueEntry oldEntry, EffectiveValueEntry& newEntry, ValueOperation operation)
    på System.Windows.DependencyObject.SetValueInternal(DependencyProperty dp, Object value, Boolean allowReadOnlySet)
    på System.Windows.DependencyObject.SetValue(DependencyProperty dp, Object value)
    på System.Windows.Controls.DomainDataSource.set_DomainContext(DomainContext value)} System.Exception {System.ArgumentException}

    Have you tried following the steps and got it working.

    • Jack,

      I am sorry you are having issues getting my example to work. This time I created another example following my steps exactly and found the same problem you mention. The good news is it is a simple typo. In Listing 1 there is a typo in the GetSalesPipeline method. The method should return IQueryable not IQueryable and the salesPipeline variable should be of the same type IQueryable not IQueryable. If you make those two changes to the method in the CrmDomainService.cs file it should solve your problem. I apologize for this typo and want to thank you helping me catch it.

      Again thanks for the feedback and if you have any other questions please feel free to let me know.

      Thanks,
      Paul

      PS I will be updating the post here shortly to reflect the issues that Jack found. Thanks again, Jack, for your help.

  3. I have updated Listing 1 and Listing 3 with the changes Jack and I discussed. The minor difference in the solution I gave Jack and the change I made to Listing 3 is I removed the reference to the Loaded event all together which is why I didn’t add the other code listing I mentioned in my comment above.

    However if you prefer to have the Loaded event handled you can just add this attribute (LoadedData=”salesPipelineDomainDataSource_LoadedData”) back to the riaControls:DomainDataSource tag. You will also need to add the code snipped (the salesPipelineDomainDataSource_LoadedData method) I posted in my earlier comment.

    Thanks,
    Paul

  4. The next great thing would be to access CRM services instead of SQL in order to make it work with IFD and to be able to make CRUD on CRM which is not allowed in SQL. Keep up the good work 🙂

  5. everything is fine till i get to step 4. Am I supposed to take listing 3 and completely replace all the XAML in MainPage.xaml?
    if so i get teh squiglies under the word Binding – the type Binding was not found

  6. WARNING!!
    Paul,
    Your application only works for consulting data. It doesn’t work for insert new data because in those cases you need to use the CRM Web Services to include businessunit , roles, permission, etc.

    Thanks

  7. Good Morning my name is Luis, I’m starting with that of Silverlight, I followed your steps and making notes when compiling I get the following errors

    1. the tag ‘CrmDomainContext’ does not exist in XML namespace ‘clr-namespace: CrmSalesPipelineDashboard.Web.Services’
    2. not found the type ‘my: CrmDomainContext’. Check for missing a reference to an assembly and have generated all assemblies regerencia ago.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s