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();
}
}
This code goes through every Entity in the EntityCollection
and generates a new row for each. Then a calculation is performed on each CRM
data type within the entity to retrieve the value as a string and put it into
a Column of the DataRow. The code was adapted to CRM
2011 from an existing forum post that showed how to do this in CRM 4.0 (http://www.codeproject.com/Articles/42334/Convert-BusinessEntityCollection-to-a-DataTable
)
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
Hi Nick,
ReplyDeleteCan you share the Asp.net page with example? How do you host the aspx page inside crm 2011.. i am not able to add it as a web resource.