I was recently tasked with implementing some “dashboard”
functionality within the standard Dynamics CRM 2011 Customer Portal to allow
clients to see an overview of current cases, using various metrics. After
thinking through the possibilities (Silverlight, JQuery, 3rd party
controls) I decided to utilise ASP.NET Charts as a) they have been greatly
improved in the latest versions of the .NET Framework and b) well, the standard
dashboard in CRM uses them so they can’t be that bad! The initial plan was to
have an ASP.NET Chart with a LINQ data source (pointing at CRM) however, due to
some limitation in CRM for LINQ I had to resort to using FetchXML. The problem was
around the “groupby” LINQ method not being supported by the CRM for LINQ
libraries (at least not at the time of writing this). FetchXML however does have
aggregate behaviour, so was an obvious backup plan.
Now, if you know ASP.NET and its data bound controls you
will probably think there is no way you could have an EntityCollection (the
result of performing a FetchXML operation) as a DataSource for an ASP:Chart (or
ASP:Gridview for that matter). Well you would be right. The trick is to convert
the resulting EntityCollection into a standard .NET Datatable, and then set
that as the DataSource for the chart.
To summarise, this is the requirement: a pie chart in the
Customer Portal showing the client an overview and summary of their current
active cases, grouped by status (statuscode). There are more complex graphs and
metrics required for the overall dashboard, but I have chosen the simplest one
to demonstrate as part of this post.
The process for achieving this is as follows.
1. Add a chart to your aspx
page (from the ToolBox), set its type to pie. If you are using Visual Studio
2010 or greater the chart controls will already be right there in the Toolbox:
If you are using an older version you may need to download
and install the .NET Framework Chart Controls add on.
The mark up for the pie chart should be:
<asp:Chart ID="ChartActiveCasesByStatus" runat="server" Width="460px" >
<Series>
<asp:Series Name="Cases"
YValueMembers="incidentcount"
IsValueShownAsLabel="true"
XValueMember="status"
IsVisibleInLegend="true"
ChartType="Pie">
</asp:Series>
</Series>
<ChartAreas>
<asp:ChartArea Name="ChartArea1"
Area3DStyle-Enable3D="false">
<AxisX
LineColor="DarkGray">
<MajorGrid LineColor="LightGray"
/>
</AxisX>
<AxisY
LineColor="DarkGray">
<MajorGrid LineColor="LightGray"
/>
</AxisY>
<Area3DStyle Enable3D="false"></Area3DStyle>
<InnerPlotPosition Height="100" Width="100" />
</asp:ChartArea>
</ChartAreas>
<Legends>
<asp:Legend Title="Status">
</asp:Legend>
</Legends>
</asp:Chart>
All of the elements within this mark-up can be tailored to
suit the look and feel you require. This is just the standard look with a
couple of minor tweaks to add a title to the legend, and also set the “InnerPlotPosition”
to have the pie chart more central within the control.
2. Once the chart is in place the next thing that is required is the
code to get the data we need from CRM in the right format, converted to a
DataTable, and set as the DataSource for the chart (and then a DataBind() call on
the chart of course). The code is remarkably simple:
string casesByStatus = @"
<fetch distinct='false'
mapping='logical' aggregate='true'>
<entity name='incident'>
<attribute name='incidentid'
alias='incidentcount' aggregate='count'/>
<attribute name='statuscode'
groupby='true' alias='status' />
<filter type='and'>
<condition
attribute='customerid' operator='eq' value='{" +
Contact.ParentCustomerId.Id.ToString() + "}'
/>" +
"<condition attribute='statecode'
operator='eq' value='0' />" +
"</filter> " +
"</entity> " +
"</fetch>";
EntityCollection quotes_result =
ServiceContext.RetrieveMultiple(new FetchExpression(casesByStatus));
ChartActiveCasesByStatus.DataSource = ConvertToDataTable(quotes_result);
ChartActiveCasesByStatus.DataBind();
We have our FetchXML string, retrieving all the active cases
(incidents) for the logged in client, a count of them, grouped by statuscode.
The ConvertToDataTable(quotes_result) is where we call our
method to convert our EntityCollection into a DataTable ready to be a valid
DataSource for our chart. The code for this:
private static DataTable ConvertToDataTable(EntityCollection entityCollection)
{
DataTable dt = new
DataTable();
int total = entityCollection.Entities.Count;
for (int i = 0; i
< total; i++)
{
DataRow row = dt.NewRow();
Entity myEntity = (Entity)entityCollection.Entities[i];
var keys = myEntity.Attributes.Keys;
foreach (var
key in keys)
{
if (key.EndsWith("id"))
continue;
string columnName = key;
string value =
GetValuefromAttribute(myEntity.Attributes[key], myEntity.FormattedValues.FirstOrDefault(y
=> y.Key == key));
if (dt.Columns.IndexOf(columnName) ==
-1)
{
dt.Columns.Add(key, Type.GetType("System.String"));
}
row[columnName] = value;
}
dt.Rows.Add(row);
}
return dt;
}
private static string GetValuefromAttribute(object
unformattedAttribute, KeyValuePair<string, string> formattedAttribute)
{
if (formattedAttribute.Value == null)
return
GetValueFromCRMTypeAttribute(unformattedAttribute);
else
{
return formattedAttribute.Value;
}
}
private static string GetValueFromCRMTypeAttribute(object p)
{
if (p.ToString() == "Microsoft.Xrm.Sdk.OptionSetValue")
{
return ((OptionSetValue)p).Value.ToString();
}
if
(p.ToString() == "Microsoft.Xrm.Sdk.Money")
{
return ((Money)p).Value.ToString();
}
if (p.ToString() == "Microsoft.Xrm.Sdk.EntityReference")
{
return ((Microsoft.Xrm.Sdk.EntityReference)p).Name;
}
if (p.ToString() == "Microsoft.Xrm.Sdk.AliasedValue")
{
return
GetValueFromCRMTypeAttribute(((Microsoft.Xrm.Sdk.AliasedValue)p).Value);
}
else
{
return p.ToString();
}
}
That’s it. This is what it looks like in the portal:
Pretty basic with regard to presentation, but this is where
you can play with that mark-up, get the colour scheme you want, make it 3d etc.
etc.
A fairly painless introduction to ASP.NET charts and CRM,
but things do get a little hairier with line charts with multiple series &
categories….maybe I will save that for another post.
Cheers,
Nick
