SQL vs. FetchXML reports in CRM 2011

When it comes to building reports for CRM 2011, there are two options available: SQL reports or FetchXML reports. Both make use of SQL Reporting Services and require the SSRS Data Connector to be installed to interface with CRM. In the following table I am attempting to summarize the differences between these two reporting options:

	SQL Reports	FetxhXML Reports
Building experience	Requires a separate program for designing the report such as SQL Business Intelligence Development Studio (BIDS) or Report Builder.	CRM comes with a Report Wizard which can be used for building these reports. The report wizard is a web report designer integrated with CRM. These reports can also be designed using BIDS (must download the Report Authoring Extension).
Skill set	Building SQL reports requires SQL Server skills and development experience.	When built using the Report Wizard, advanced CRM users can have the skills to build reports (or super-users) without requiring a developer.
Flexibility	These reports can take data from CRM and present it in multiple ways. Reports can achieve complex requirements as you can use any feature from SQL Reporting Services.	Functionality is restricted to what the Report Wizard can support which can be quite limiting at times.
Queries	Data is queried using SQL statements that read the filtered views in the organization database.	FetchXML queries are used for retrieving data for these reports (Advanced Find can be used to generate FetchXML queries).
Reporting mechanism	These reports can be scheduled, delivered by email and other mechanisms.	Must be executed on-demand.
CRM Online Support	Not supported	Supported

Plugin Registration Rules in CRM 2011

I wrote this post a while back but never published it! With the introduction of Sandbox in CRM 2011, plugins can be registered in CRM Online in isolation, which introduces a whole new set of validation and rules for registering plugins. I have created the following decision graph which can be helpful in understanding the plugin registration process and limitations on the CRM server:


Additionally, there are these general rules that apply to all plugins:

• Plugin assemblies must have a strong name (either fully signed or delay signed).
• The strong name of a plugin assembly (and its types) excluding the version build and revision numbers must be unique in the CRM organization.
• Workflow custom activities are not supported in sandbox mode (isolation).
• Sandboxed assemblies can only be registered in Database.
• Only sandboxed assemblies can be registered in CRM Online.

CRM with “no software” ?

I was inspired to write this post when I saw at the grocery store a can of lemon juice that said “contains no chemicals”, then I wondered: Isn’t lemon juice just citric acid with other bunch of chemicals? Even a freshly squeezed lemon is just a mix of chemicals that make what it is. Now, what does this have to do with CRM? Well, it immediately reminded me of a company marketing CRM as “no software, no hardware”! They certainly do not sell a bunch of notebooks, telephone books and pens that they call CRM, it is just a tricky marketing strategy for their product (which I fully admire and respect). But of course CRM is software and it should be sold as such. Whether the software is on the cloud or in your premises is a different story.

Perhaps I am being too up-tight, but sometimes I feel marketing blurs the line between advertising a product for what it is and tweaking words to give a deceptive impression, I imagine someone might buy that lemon juice thinking that it is healthier than the competitors.

How to update the full-name format in CRM

There are three special entities (contact, lead, user) which auto-generate the full name attribute based on a combination of first, middle and last name attributes. Other entities can also have a full-name attribute as explained in this post.  The format for the full-name is configurable under Settings –> Administration –> System Settings:

However, updating the format will not retro-actively update the full-name field for existing records so if your organization has the need to update the full-time format, you will need some manual intervention to make sure existing records abide to the new format. There are some posts suggesting to execute SQL scripts to update the existing records; however, SQL scripts are unsupported and also not possible in CRM Online. You just need to execute the following code which will take care of updating existing records to conform to the current full-name format in your organization:

private static void UpdateFullNameFormat(IOrganizationService service)

1. {
2.     UpdateFullName(Contact.EntityLogicalName, service);
3.     UpdateFullName(SystemUser.EntityLogicalName, service, true);
4.     UpdateFullName(Lead.EntityLogicalName, service);
5. }
6.  
7. private static void UpdateFullName(string entityName, IOrganizationService service, bool checkSystemUsers = false)
8. {
9.     QueryExpression query = new QueryExpression(entityName);
10.     EntityCollection coll;
11.     query.PageInfo.PageNumber = 1;
12.     if (checkSystemUsers)
13.     {
14.         // Skip the SYSTEM and INTEGRATION built-in users which cannot be modified
15.         query.Criteria.AddCondition(new ConditionExpression("calendarid", ConditionOperator.NotNull));
16.     }
17.     query.ColumnSet = new ColumnSet("lastname", "middlename", "firstname");
18.     do
19.     {
20.         coll = service.RetrieveMultiple(query);
21.         foreach (Entity e in coll.Entities)
22.         {
23.             service.Update(e);
24.         }
25.         query.PageInfo.PageNumber++;
26.     }
27.     while (coll.MoreRecords);
28. }

Note that the only thing that I am doing is executing an Update request for each record, without modifying any attribute but it will force the full-name field to be re-calculated based on the new format. When it is so simple to correct it in a supported manner I wonder why complicate life with SQL scripts!

Note that I am using PageInfo because there might be more that 5000 records of each entity so the query results are paged. Thanks Andriy for pointing it out!

Personal vs. System Views, Dashboards and Charts

There are two types of views/dashboards and charts in CRM 2011: Personal and System. The fundamental difference is that the first one is owned by a user/team while the second one is owned by the organization. This has a set of implications that might affect your design, that is why I have provided this comparison below which can be useful when selecting one or the other (there are pros and cons for each):

	Personal	System
Ownership	Can be owned by a user or team	Owned by the organization
Visibility	By default it is only visible to the user who creates it.	By default it is visible to all users.
Privileges	Can be protected using the standard privilege depths for the entity (none, user, BU, BU and child BU, Organization). This can allow you to make a chart/dashboard accessible to some users but not all and be able to select which users can see which charts/dashboards.	User access can only be configured to all or none (if a user has access to a system chart/view then the user will have access to ALL system charts/views.
Sharing	Can be shared with a specific user or team. For example the CEO might want to share a chart only with a VP.	Cannot share system views/dashboards/charts since they are at the organization level.
Solutions	Cannot be included in a solution. This is a show stopper if you need to move personal views/dashboards/charts across deployments and organizations. You would need to copy them manually. For charts, you can export the XML and import as a system chart.	System views/dashboards are solution aware and are fully supported to be transported in solutions.
CUD operations (Create, update, delete)	Most users will have access to create their own personal views, dashboards and charts.	Only high privileged users and system administrators should have access to CUD operations on system views, charts and dashboards.