With the earlier versions of Dynamics CRM you could construct FetchXML queries and the results would be returned as XML. I liked that feature but is has been deprecated in later releases. I had a need for this again recently because I wanted to get data from CRM in a format where it was easy to mail merge. I'm a big fan of Aspose Words for .Net as I have used it several times in the past with great success.
Aspose has the advantage of being able to design normal mail merge templates in Word so it is easy for a user to amend the template. Aspose uses mail merge fields just as the typical mail merge template does but you just add them manually to your template rather than from a data source.
Aspose uses data tables as the data source and I wanted a way to convert the results of a FetchXML query into a data table so it was ready to mail merge. In fact because Aspose can merge multiple data tables I need to create a dataset which I will convert to an XML string using GetXml().
A key factor was I wanted the code to be entirely ignorant of the columns in the result set. I want to be able to select any number of columns from a CRM entity, use lookup values and pick lists and columns from related tables. Unfortunately when querying pick lists you only get the value and not the text. You will need another method to get the text equivalent.
I am not detailing how I do the Aspose merge in this post. It should be easy enough if you follow their samples. Just remember for this approach to work, the field names in the data table need to be unique.
To get started I used the CRMSVCUTL.EXE utility to generate the classes of the custom entities.
crmsvcutil /url:http://localhost:5555/Org1/XRMServices/2011/Organization.svc
/out:Xrm.cs /namespace:Xrm /serviceContextName:XrmServiceContext
I added the Xrm.cs to my project and references to Microsoft.Xrm.Sdk.dll and Microsoft.Xrm.Client.dll which can be found in the SDK\bin directory. I also added System.RunTime.Serialization and System.Data. The connection string for CRM I added to the app.config. I have tried this code on Dynamics CRM 2011 and CRM 2013 and it works fine.
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Text;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Messages;
using Microsoft.Xrm.Sdk.Metadata;
using Microsoft.Xrm.Sdk.Query;
using Microsoft.Xrm.Sdk.Client;
using Microsoft.Xrm.Client;
using Microsoft.Xrm.Client.Services;
using Xrm;
static IOrganizationService _service;
static OrganizationService _orgService;
static string FetchExpression()
{
String connectionString = ConfigurationManager.AppSettings["xrmConnectionString"];
// Establish a connection to the organization web service using CrmConnection.
Microsoft.Xrm.Client.CrmConnection connection = CrmConnection.Parse(connectionString);
// Obtain an organization service proxy.
// The using statement assures that the service proxy will be properly disposed.
using (_orgService = new OrganizationService(connection))
{
string fetchquery = @"<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>
<entity name='contact'>
<attribute name='fullname' />
<attribute name='new_referencenumber' />
<attribute name='new_locationid' />
<attribute name='contactid' />
<attribute name='new_contactmechanism' />
<order attribute='fullname' descending='false' />
<filter type='and'>
<condition attribute='statecode' operator='eq' value='0' />
<condition attribute='fullname' operator='not-null' />
</filter>
<link-entity name='systemuser' from='systemuserid' to='owninguser' visible='false' link-type='outer' alias=user'>
<attribute name='domainname' />
</link-entity>
</entity>
</fetch>";
_service = (IOrganizationService)_orgService;
RetrieveMultipleRequest req = new RetrieveMultipleRequest();
FetchExpression fetch = new FetchExpression(fetchquery);
req.Query = fetch;
RetrieveMultipleResponse resp = (RetrieveMultipleResponse)_service.Execute(req);
// Create a dataset and datatable
DataSet results = new DataSet("Results");
DataTable Table1 = new DataTable("SingleRowTable");
DataColumn column;
DataRow workRow;
foreach (Entity entity in resp.EntityCollection.Entities)
{
//create the columns in the data table
foreach (KeyValuePair<String, Object> attribute in entity.Attributes)
{
column = new DataColumn();
switch (attribute.Value.GetType().Name)
{
case "AliasedValue":
column.DataType = entity.GetAttributeValue<AliasedValue>(attribute.Key).Value.GetType();
break;
case "EntityReference":
column.DataType = entity.GetEntityReferenceValue<EntityReference>(attribute.Key).Name.GetType();
break;
case "OptionSetValue":
column.DataType = entity.GetAttributeValue<OptionSetValue>(attribute.Key).Value.GetType();
break;
default :
column.DataType = attribute.Value.GetType();
break;
}
column.ColumnName = attribute.Key;
Table1.Columns.Add(column);
}
// add the values to the row
workRow = Table1.NewRow();
foreach (KeyValuePair<String, Object> attribute in entity.Attributes)
{
switch (attribute.Value.GetType().Name)
{
case "AliasedValue":
workRow[attribute.Key] = entity.GetAttributeValue<AliasedValue>(attribute.Key).Value;
break;
case "EntityReference":
workRow[attribute.Key] = entity.GetEntityReferenceValue<EntityReference>(attribute.Key).Name;
break;
case "OptionSetValue":
workRow[attribute.Key] = entity.GetAttributeValue<OptionSetValue>(attribute.Key).Value;
break;
default:
workRow[attribute.Key] = attribute.Value;
break;
}
}
Table1.Rows.Add(workRow);
// only one row expected so exit
break;
}
results.Tables.Add(Table1);
return results.GetXml();
}
}
The results are in a very simple XML structure and already to use for the mail merge.
<Results>
<SingleRowTable>
<fullname>Adam Ant</fullname>
<new_referencenumber>PSN-00000286</new_referencenumber>
<new_locationid>Brighton</new_locationid>
<contactid>2d31f962-5f44-e211-ae45-00155d010a10</contactid>
<new_contactmechanism>100000009</new_contactmechanism>
<user.domainname>MSCRM2011\Administrator</user.domainname>
</SingleRowTable>
</Results>
Showing posts with label Dynamics CRM 2011. Show all posts
Showing posts with label Dynamics CRM 2011. Show all posts
Thursday, January 23, 2014
Saturday, November 19, 2011
Dynamics CRM 2011 Date Picker Displays US format
This post will only help if you are using the On Premise version - I don't have a solution for the Online version. BTW, I was using SQL Server 2008 R2.
But when you select a date, the picker puts the date in as US format. It will still filter the data correctly but I wanted UK format displayed.
The clue came from this blog. Now in fact when I ran my report in Report Server (i.e. outside CRM) the date format displays correctly.
So I finally found the solution. You need to add Culture="en-GB" to the reportviewer.aspx page located at C:\Program Files\Microsoft Dynamics CRM\CRMWeb\CRMReports\rsviewer. Note its the rsviewer directory not the viewer directory. I added it to the end of the first line - the <%@ Page declaration.
Then the date displayed in UK format in CRM.
If you add date parameters to a report that you want to run in Dynamics CRM 2011 then you get prompted to enter the dates. If you set the parameters as type DateTime then a date picker is also displayed.
But when you select a date, the picker puts the date in as US format. It will still filter the data correctly but I wanted UK format displayed.
The clue came from this blog. Now in fact when I ran my report in Report Server (i.e. outside CRM) the date format displays correctly.
Then the date displayed in UK format in CRM.
Labels:
Date Picker,
Dynamics CRM 2011,
SQL Server 2008 R2,
SSRS,
US Format
Wednesday, November 16, 2011
Filtered Fetch XML Reports for CRM 2011 On-line
I found very few blogs that explained how to create a Filtered Report for CRM 2011 using Fetch XML. My approach had been to use Advanced Find to create the Fetch XML and then copy and paste that into a Report in Business Intelligence Development Studio. Now that works fine but when you publish the report to CRM it will only run on all records of the entity, it does not work on selected rows or on a single entity record. The report just does not show up as selectable on the entity view of on the entity form.
The missing attributes are on the the entity element of the Fetch XML. Mine is a custom entity called new_identifiedneed and so I need to change
<entity name="new_identifiedneed">
to
<entity="new_identifiedneed" enableprefiltering="1" prefilterparametername="CRM_Filterednew_identifiedneed">
Now what is not made clear is that setting the correct prefilterparametername is crucial. It needs to be the name of the Filtered View for the entity prefixed by CRM_ (not CRMAF_ as for SQL based reports).
If you do this on the query you will find that it will automatically add a parameter to your dataset. When you run the Query you will get this prompt for the parameter.
Don't bother with entering a value, just click OK and you should get unfiltered results.
Check out the parameters on the DataSet which should look like this:
If that is present you're home free. Make the report look pretty, build it to create the rdl and then load it into CRM. When you add the report be sure to set the Related Records (Identified Needs in my case) and select the Display in properties to include both the Form and the List.
Now go to the entity view and select on or more records. Click on the Run Report icon on the ribbon and you should see your report. When you run it on Selected Records you should see what you wanted. The same applies when you open a form on a selected entity record.
Happy Reporting!
BTW I found this out by using the Report Wizard for ad hoc reports in CRM. It will not only create the appropriate Fetch XML but gives you an RDL file that you can open up and play with in Business Intelligence Development Studio.
The missing attributes are on the the entity element of the Fetch XML. Mine is a custom entity called new_identifiedneed and so I need to change
<entity name="new_identifiedneed">
to
<entity="new_identifiedneed" enableprefiltering="1" prefilterparametername="CRM_Filterednew_identifiedneed">
Now what is not made clear is that setting the correct prefilterparametername is crucial. It needs to be the name of the Filtered View for the entity prefixed by CRM_ (not CRMAF_ as for SQL based reports).
If you do this on the query you will find that it will automatically add a parameter to your dataset. When you run the Query you will get this prompt for the parameter.
Don't bother with entering a value, just click OK and you should get unfiltered results.
Check out the parameters on the DataSet which should look like this:
If that is present you're home free. Make the report look pretty, build it to create the rdl and then load it into CRM. When you add the report be sure to set the Related Records (Identified Needs in my case) and select the Display in properties to include both the Form and the List.
Now go to the entity view and select on or more records. Click on the Run Report icon on the ribbon and you should see your report. When you run it on Selected Records you should see what you wanted. The same applies when you open a form on a selected entity record.
Happy Reporting!
BTW I found this out by using the Report Wizard for ad hoc reports in CRM. It will not only create the appropriate Fetch XML but gives you an RDL file that you can open up and play with in Business Intelligence Development Studio.
Subscribe to:
Posts (Atom)




