CRM Online Custom Built Dashboards

Building Custom dashboards for Dynamics CRM Online is not as complex of an effort that it's reputation has gotten. It does require some developer skills and this blog will be a great launching pad for you to build your own.

Here's a screen shot of a dashboard I built using Visual Studio.net and a free MS chart control that I'm running in a external hosted site and serving up in my demo Microsoft Dynamics CRM Online org. The dashboard has 3 charts that show Open Leads by Owner, Estimated value of open opportunities in a funnel by owner and last, a funnel chart that shows the estimated value of opportunities by probability state.

While I was developing the dash board I was thinking that it would be a pretty simple model that could be consumed for a hosted add-on product as well.

Let's look at that first. To host an add-on product, you would need to have a site that customers could register for your product. The registration process could offer up a service account that the customer would invite to their CRM Online org. The user account in the customer org for the service account could be set to non-interactive as not to incur a license cost. Next, you could offer a template that the customer could import to hold configuration data for the add-on. This template might contain entity definitions that the customer could use to store configuration data for the add-on and/or the customer template could include a sitemap link to open the hosted add-on .

That way you wouldn't have to store any data on your hosted add-on site and all the options you add-on application would need would be stored in the customers CRM org.

If you'll recall I wrote first about custom dash boarding ( not water boarding ) here at https://blogs.msdn.com/crm/archive/2008/02/25/crm-live-dashboards.aspx

It's been some time but I've kept it on my plate. I'd also been waiting for some time to get busy with Azure. Azure is going to be another paradigm shift in computing. The first was DOS-Windows, the next was the Internet, and dotnet. The earlier focus on the Internet was about connecting, it then moved to presentation of data to data sharing. The focus today is hosted applications that scale and integrate with development environments. It's about vendors building out platforms and system that can be integrated with to provide in depth, high business value. Azure is the Microsoft computing and services platform. If you aren't familiar, www.microsoft.com/azure/default.mspx.

Back to dash boards...

The heart of any dash board or reporting is query and presentation. The Microsoft Dynamics CRM SDK provides all the query processing you need. From straight queries to joins. I like to use the web services to fetch data sets and then store them in a .NET data set that I can use LINQ against. After you get your data sets, you create series data for your charts.

Here is a code chunk that demonstrates the CRM query, storage in a dataset and then using LINQ query against it.

// Build the filter based on a blank condition to fetch all
FilterExpression aFilter = new FilterExpression();
aFilter.Conditions = new ConditionExpression[] { };

// Create the query.
QueryExpression query = new QueryExpression();
query.Criteria = aFilter;

// Set the properties of the query.
query.EntityName = "lead";
ColumnSet Cols = new ColumnSet();
Cols.Attributes = new string[] { "createdon", "ownerid","estimatedvalue","statecode"};
query.ColumnSet = Cols;

// Create the request object.
RetrieveMultipleRequest retrieve = new RetrieveMultipleRequest();

// Set the properties of the request object.
retrieve.Query = query;
retrieve.ReturnDynamicEntities = false;

// Execute the request.
RetrieveMultipleResponse retrieved = (RetrieveMultipleResponse)crmService.Execute(retrieve);

Next, I build a in memory data table

LeadRecordsDT = new DataTable();
LeadRecordsDT.TableName = "Leads";
LeadRecordsDT.Columns.Add("Owner");
LeadRecordsDT.Columns.Add("StateCode");
LeadRecordsDT.Columns.Add("Estimatedvalue");
LeadRecordsDT.Columns.Add("CreatedOnDate");

store CRM records in the data set

// Add all the Lead records to a data table
CrmService.lead LeadEntity = null;
for (int x = 0; x < LeadList.BusinessEntities.Length; x++)
{
    LeadEntity = (CrmService.lead)LeadList.BusinessEntities[x];
    DataRow aRow = null;
    if (LeadEntity != null)
    {
        double dbVal = 0;
        if (LeadEntity.estimatedvalue != null)
            dbVal = LeadEntity.estimatedvalue.Value;
        aRow = LeadRecordsDT.Rows.Add(LeadEntity.ownerid.name, LeadEntity.statecode.Value, dbVal, LeadEntity.createdon.Value);
    }
   
}

Now I can use LINQ queries to pull out specific data

EnumerableRowCollection<DataRow> EstValueOpenLeads =
    from leads in LeadRecordsDT.AsEnumerable()
    where leads.Field<string>("Owner") == OwnerName && leads.Field<string>("StateCode") == "Open"
    orderby leads.Field<string>("StateCode")
    select leads;

double EstimatedValue = 0;
foreach (var Leadrecord in EstValueOpenLeads)
{
    string SumVal = Leadrecord.Field<string>("estimatedvalue");
    EstimatedValue += Convert.ToDouble(SumVal);
}

And now the part you've been waiting for....how do you build the charts ? Of course we don't want to build them from scratch. Home made wine/beer is awesome but home made charting is bit less productive. I prefer to use MS Chart Control. Be sure to download the Visual Studio.net add-in for it also.

Here's some sample code MS Chart Samples and of course a link to other chart controls. You can Bing for other blogs on the control also. The MS Charts has great documentation and samples so I won't spend time discussing the chart usage.

My goal for a chart control was no cost, Visual Studio support and Azure support. 2 out of 3 isn't too bad. According to the Azure forums it'll be on the next upgrade of the .NET framework in Azure to get MS Chart support. Bummer but I did find other hosting sites that support it in the mean time.

cheers

-jonw