Filtering in Crm 2011

In Crm 2011 new functionality provided that allows us to filter our views same as Microsoft Excel filtering functionality.

We can filter the columns from within the Grid as shown in below screen shot.


Click on Filter button that enables filtering for each column as shown in below screen shot.


The drop down shows the following options for filtering.


Click on Custom filter option and specify the conditions based on which records will be filtered.


Now records will be shown only for those potential customer whose name begins with “b” as shown in below screen shot.


We can save our views as shown in below screen shot.


Specify the name of your view and it will be shown to your views list as shown in below screen shot.



Hope this helps!!!

Group by using FetchXML

In Microsoft Dynamics CRM 2011 FetchXML includes grouping and aggregation features which let us to use aggregate functions like sum, count etc.

Using Group by we can calculate sum, avg, min, max, count. But, Group by clause is not supported through LINQ in CRM.

You can only specify one aggregate attribute in a query and you cannot use the distinct keyword. To create an aggregate attribute, set the keyword aggregate to true, then specify valid entity name, attribute name and alias(variable name). You must also specify the type of aggregation you want to perform.

<fetch distinct='false' mapping='logical' aggregate='true'>
<entity name='entity name'>
<attribute name='attribute name' aggregate='count' alias='alias name'/>
</entity>
</fetch>"

Below is the example to get sum of total amount of all won quotes:
string quotes = @"
<fetch distinct='false' mapping='logical' aggregate='true'>
<entity name='quote'>
<attribute name='totalamount' alias='totalamount_sum' aggregate='sum'/>
<attribute name='statecode' groupby='true' alias='state' />
<filter type='and'>
<condition attribute=' statecode ' operator='eq' value='won' />"+
"</filter> "+
"</entity> "+
"</fetch>";

EntityCollection quotes_result = _service.RetrieveMultiple(new FetchExpression(quotes));
foreach (var q in quotes_result.Entities)
{
Decimal wonQuoteAmount = ((Money)((AliasedValue)q["totalamount_sum"]).Value).Value;
}

MessageSecurityException when connecting to the Web Services

When trying to establish connection with the organization service or discovery service in CRM 2011 you may receive the following exception

Unhandled Exception: System.ServiceModel.Security.MessageSecurityException: An unsecured or incorrectly secured fault was received from the other party. See the inner FaultException for the fault code and detail.

Solution: You need to Delete the device registration folder named LiveDeviceID in your profile folder C:\Users\<username>.

Alternatively you can navigate to “C:\Users\<username>\LiveDeviceID\” and rename the LiveDevice.xml file to LiveDeviceOld.xml.

Differences in Custom Workflow Assembly in CRM 4.0 and CRM 2011

We have listed the list of changes noticed for designing a custom workflow assembly in CRM 2011 and in CRM 4.0.

1. References

CRM 4.0
using System.Workflow.Activities;
using System.Workflow.ComponentModel;
using System.Workflow.ComponentModel.Compiler;
using Microsoft.Crm.Sdk;
using Microsoft.Crm.Sdk.Query;
using Microsoft.Crm.SdkTypeProxy;
using Microsoft.Crm.Workflow;

CRM 2011
using System.Activities;
using Microsoft.Crm.Sdk.Messages;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Workflow;

2. Base Class

Base class definition has been changed from SequenceActivity to CodeActivity.

CRM 4.0: In CRM 4.0 we have to specify both Workflow name and Workflowactivitygroupname in the code as written in the following code.

[CrmWorkflowActivity("My Custom Workflow", "CRM Workflow")]
public class MyCustomWF: SequenceActivity

CRM 2011: In CRM 2011 that is done in different way.

public class MyCustomWF: CodeActivity

Both Workflow name and Workflowactivitygroupname can be specified at the time of registering the assembly as shown in below screen shot.




3. Execute Method

The overridden Execute method remains the same except parameter and return type.

CRM 4.0
protected override ActivityExecutionStatus Execute(ActivityExecutionContext executionContext)

CRM 2011
protected override void Execute(CodeActivityContext executionContext)

4. Create service

CRM 4.0
IContextService contextService = (IContextService)executionContext.GetService(typeof(IContextService));
IWorkflowContext context = contextService.Context;
ICrmService crmService = context.CreateCrmService();

CRM 2011
IWorkflowContext context = executionContext.GetExtension();
IOrganizationServiceFactory serviceFactory = executionContext.GetExtension();
IOrganizationService service = serviceFactory.CreateOrganizationService(context.UserId);

5. INPUT Parameters

CRM 4.0
Declaration: how to initialize the input parameter

// specified dependency property
public static DependencyProperty CaseIDProperty = DependencyProperty.Register("CaseID", typeof(Lookup), typeof(TotalTaskRetrieval));

// Specified Input property
[CrmInput("Enter Case ")]

// Set the reference Target for Property created
[CrmReferenceTarget("incident")]

// Property Defined for caseId
public Lookup CaseID
{
get
{
return (Lookup)base.GetValue(CaseIDProperty);
}
set
{
base.SetValue(CaseIDProperty, value);
}
}

Use: Access the input parameter declared above as,
Guid caseid=CaseID.Value

CRM 2011

Declaration: how to initialize the input parameter

[Input("Enter Case ")]
[ReferenceTarget("incident ")]
[Default("3B036E3E-94F9-DE11-B508-00155DBA2902", " incident ")]
public InArgument CaseID { get; set; }
Use: Access the input parameter declared above as,
Guid caseid = CaseID.Get(executionContext).Id

6. OUTPUT Parameters

CRM 4.0
[CrmOutput("outputSum")]
public CrmMoney Sum
{
get
{
return (CrmMoney)base.GetValue(SumProperty);
}
set
{
base.SetValue(SumProperty , value);
}
}

CRM 2011

[Output("outputSum")]
[Default("23.3")]
public OutArgument Sum { get; set; }

Hope this helps!!!

Reporting through BIDS in CRM Online 2011

In the CRM 2011 online if you want to create your custom SSRS report then here is the trouble, you cannot create the report directly using the SQL Queries as you do not have access to SQL Server. For this reason MS has provided BIDS tool for Dynamics CRM 2011. You can download this tool from http://www.microsoft.com/downloads/en/details.aspx?FamilyID=64a09b75-8376-4b9d-aea3-8a83a1837c4d

Using the BIDS tool, you can connect to Dynamics CRM Online service and use the CRM FetchXML queries to generate the queries required for the report.

To create the SSRS report for the CRM 2011 Online first you need to run the tool “CRM2011-Bids-ENU-i386” on the machine where you have installed the SQL Server Business Intelligence Development Studio.

After installing this, a new Data Source type gets added in the “Embedded connection type” option named “Microsoft Dynamics CRM Fetch”. To create the new report with fetch XML for the CRM 2011 you need to select this data type as shown in the below screenshot.

In the Connection string you need to provide your CRM 2011 online url and the org unique name, where the org unique name is optional and in the permissions section you need to provide the credentials of the CRM user.

In the SSRS report the main things are the Parameters, Query/Dataset and Design. Below is the explanation.

Parameters: You can simply defined the parameter as it is, but if you want to use the CRM filtered parameter and show this reports on the CRM grid or on the selected record, then you need to use the enableprefiltering in the fetchXML of this parameter. You can write the default fetchXML for this as follows.

Query/Dataset: Main thing in the Query is to pass the condition from the parameters. Generally in report the structure is that you have main-report and a sub-report, where you pass the parameter values of the main-report to the parameter of the sub-report.

Here we take an example to understand how it works in this scenario. For example: suppose we want to see all the contacts that is modified by a certain user, we want to run this report on the CRM user form and see the list of contacts.

For this purpose, we have created a Main report where we have a parameter named “@CRM_FilteredSystemuser” which is passed from CRM and it creates a dataset from this Parameter with three columns.
Fullname
Internamemailaddress
Systemuserid

The query for this dataset is shown in the above screenshot.
Note: If you have used the CRM parameter then it should be hidden.

Now we need to create the sub-report which contains the list of the contacts, in the sub-report we have a parameter named @Userid, and the value for this parameter is passed from the Main Report. Now the Query for sub-report with condition is as shown below.


Once you execute the Query, the dataset will be generated. From there on you can go ahead and design the report like any other normal SSRS report.

Once designed, you can upload this report to CRM and run it through the CRM User entity form.

Through BIDS it is now really easy to design reports for CRM Online 2011. Good knowledge of FetchXML is required. However there are tools available that would let you convert/design FetchXML queries through Query designer interface.