Tip #219: You want the top one

One of the challenges of creating reports is the lack of real data in development environment. There is not enough Nancies Davolios to simulate real volumes. For that reason alone production organization is often replicated “back” to test and development environments. Reports suddenly come to life and designers can see 7-figures opportunities and otherwise empty categories.
There is a price to pay, however. If you ever designed report with explicit pre-filtering then at some point you have a data source with dynamic SQL that looks like this:

SET @SQL = 'SELECT Name, DueDate, TotalAmount
FROM ('+@CRM_FilteredInvoice+') AS FA 
where ShipTo_StateOrProvince = ''FL''

UNION

SELECT Name, DueDate, TotalAmount
FROM ('+@CRM_FilteredInvoice+') as CA 
where ShipTo_StateOrProvince  = ''CA'' '

EXEC (@SQL)

All is well until you press OK button at which point you are presented with this:
Parameter prompt

It’s understandable, report designer is very determined to parse the statement to figure out the columns. There is a temptation to type select * from filteredinvoice and be done. If you are developing in a replica of production environment, chances are that will be your opportunity to have a very long lunch. There is not much fun watching parser trying to digest 2 million records for the sake of figuring data type of TotalAmount.

The solution? Use top 1 clause to limit resultset to one record only as parser does not need any more than that:
One invoice only

Tip #218: Sales Literature vs. SharePoint

So you have implemented Dynamics CRM with SharePoint document integration. The mindset of many companies with SharePoint is that SharePoint is where all documents should live, and that is a good idea, as SharePoint provides document collaboration features not found in CRM.

However, Microsoft Dynamics CRM includes Sales Literature functionality. The value of sales literature is that it makes product brochures available to sales reps from where they work—sending emails in Outlook. It also allows related documents to be grouped together, so if a product has 5 documents that have to be sent when someone emails a customer with a quote for that product, sales literature can be a big time saver. I refer to sales literature as “email attachment templates” because it pre-groups related attachments that are frequently sent and makes life much easier. By inserting one sales literature record into an email, the sales rep gets all of the related attachments that are part of the sales literature record. It is much more accurate than sales reps sending a copy of the literature from their hard drive that may be outdated or incorrect. Documents only stored in SharePoint are not going to be as easily accessible to salespeople as they send emails to clients, nor are they going to be connected to products and campaigns like they are with Sales Literature.

It doesn’t have to be either/or. Many companies use SharePoint for marketing to collaborate on the documents as they design them and store the master copies in SP, but also use sales literature to group and expose these documents to sales.

It can be a win-win—marketing collaborating on the document in SharePoint, exposing it to sales via sales literature, and sales representatives saving time when sending product brochures and other marketing collateral to customers..

Tip #217: Bad Id, bad

Following my anal retentive attention to the tightness of LINQ code and SQL it generates, I decided to refactor an old piece of code that extracted identifiers of the accounts matching certain criteria. Being very smug with all the newly acquired knowledge, I quickly produced an equivalent of:

var activeAccounts = crm.AccountSet
   .Where(a => a.Name.StartsWith("Bar"))
   .Select (a => a.Id);
           
foreach (var id in activeAccounts)
{
   Console.WriteLine("{0}", id);
}

To my horror, this code generated an equivalent of “select *” SQL statement (ok, it listed the columns explicitly but it listed all columns), neutralizing all my efforts. Explicit column name, on the other hand, generated mean and lean SQL:

var activeAccounts = crm.AccountSet
   .Where(a => a.Name.StartsWith("Bar"))
   .Select (a => a.AccountId);

object.Id is a very handy abstraction, but when it comes to LINQ, it’s a leaky one.

Tip #216: In a parallel dimension

I’ve had the following discussion with two different CRM administrators in the past week. We’ll call them “Larry.”

Larry: “My CRM organization is very slow. I try to search for records and it takes 25 seconds. I tried to import an organization, and it took 25 hours to complete.”

Me: “What is your Max Degrees of Parallelism setting in SQL?”

Larry: “0”

Me: “Change it to 1.”

Larry: “That’s much better!”

This is an old tip (I think I blogged about it in a “optimal settings for CRM 3” post, but I can’t find it), but it is one of the most frequent causes of less than optimal CRM performance, because this property is set to 0 by default when SQL Server is installed. The default setting means that SQL server gets to choose how many processors are used in the execution of a query. Sometimes you can be ok with the default setting, but it is especially a problem when you have a SQL server that CRM shares with other applications. SQL may decide to use all of the processors for another application’s queries, and CRM requests may be delayed. In both of the cases I saw it in the last week, CPU resources did not appear to be overloaded at the time the slow performance took place, but changing the Max Degrees of Parallelism to 1 made a huge difference.

Common objections to changing this setting

  • If it should be set to 1, Microsoft would have it default to that setting.

Microsoft sets it to 0 as that is the most common setting, but strongly recommends that you change it based on application query patterns. CRM documentation like the implementation guide and the  Server Optimization Whitepaper specifically recommend that Max Degrees of Parallelism be set to 1 for CRM.

  • I have another database, like a reporting data warehouse, that needs to have a different setting

If you want to sacrifice CRM performance to make your reporting data warehouse work better, that is your choice, but in trying to save a little bit of money on SQL licensing, you will probably wind up with frustrated users and lost time waiting for CRM forms to load. If you want to save money and cut corners on your deployment infrastructure, SQL is not the place to do it.

Tip #215: Computer says ‘no’

Well, this is embarrassing. Remember that flogging I unleashed on unscrupulous developers being disrespectful to LINQ, performance and humanity in general? I was basking in my own cleverness of possessing the knowledge of LINQ operators including magic Any:

if(crm.OpportunitySet
    .Any(opp => opp.EstimatedValue >= 1000000m))
{
   // that would have been a winner if it worked
}

Yesterday I had a chance to use what I preached and this is what I got in return:

System.NotSupportedException was unhandled
HResult=-2146233067
Message=The method ‘Any’ is not supported.
Source=Microsoft.Xrm.Sdk

That proves two things:

  1. Never assume anything when it comes to complex products like Dynamics CRM. Have a theory? Run a test – usually it does not take long.
  2. Support for LINQ syntax is a responsibility of LINQ-to-CRM provider that translates LINQ expressions into QueryExpression. LINQ providers are notoriously difficult to write and they are created by humans who pick their battles and choose spend time elsewhere but sweating on support for obscure operators that may end up never being used.

So what about that “yes/no” test? This is the best result so far:

if(null != crm.OpportunitySet
  .Where (opp => opp.EstimatedValue > 1000000m)
  .Select(opp => new { opp.OpportunityId })
  .FirstOrDefault())
{
   Console.WriteLine("Computer says 'yes'!");
}

That LINQ results in not the best but still paltry SQL clause “select top 2 opportunityid from …”.

Tip #214: What to do if your option set won’t drop down

In Dynamics CRM 2013 or 2013 SP1, sometimes you will find that an option set won’t drop-down when you click on it. It doesn’t happen every time, but occasionally, when you click on the option set with your mouse, the option set will not drop down, but you can toggle through the options with your keyboard arrow keys.

This is a known issue. If you find that this is happening with one of your option sets, one workaround is to position the label for the option set to the left of the field. After changing the label position, the option set should drop down as desired.

Thanks to Scott Jung for this tip!

Tip #213: Add a subgrid of related security roles to the user or team form

In a comment on Tip 211: When upgrading, don’t forget the team form, Pebo asked “what about the possibility for showing roles in subgrids of teams and user forms?”

This is a great question, and we have an answer.

First, you need to understand security roles in Dynamics CRM. There is not just one record for each role. There is a copy of each security role for each business unit. If you add a subgrid of related security roles to the team or user record, it will not show related security roles on the form. This is because the only view that the subgrid definition allows you to pick is the “All Roles” view.

userroles

The problem is this view filters out the business unit specific roles, so when you view your user or team form, the subgrid is blank, even though the user or team has security roles.

userform

Security Role is also one of the few entities where the views cannot be customized. You cannot create new system views or edit existing views. The subgrid works, but the view filters out the records that we want to show.

Thanks in part to this post, I was able to make the related roles subgrid work correctly.

  1. Insert a subgrid on the user or team form. In this case I called it “User_Roles” and selected to show only related Security Role records, then save and publish the entity.
  2. Go settings–>Customization–>Customize the System. Expand the Security Role entity customization and go to “Views.” Open the definition of the “Role Associated” view. Click “Actions” and “Email a link.”
  3. From the link, we can get what the GUID of the Role Associated View. viewID
  4. Export a solution containing the team and/or user entities, where your subgrid lives. Extract the .zip file and open the customization.xml file in the XML editor of your choice.
  5. Search for your subgrid name. In this case “User_Roles.” Replace the ID in the <ViewId> line with the ID copied in step 3. GUID
  6. Save the xml file, zip it up, import, and publish.

Now when you open your user record, you can see the security roles associated with the user.

roles2

But is it supported?

So the question that you may be asking is, this sounds great, but is it supported? Given that Microsoft does not allow changing of the view associated with the subgrid via the user interface, does that mean that doing so via XML surgery is unsupported?

As illustrated in the blog post linked above, Microsoft has built in capabilities like subgrid deep linking and fetch left outer joins that are not exposed through the user interface. Sometimes they actually recommend it. So editing the XML in itself does not make something dangerous or unsupported, as long as  you know what you are doing. Microsoft has not tested this exact scenario, but it is safe and only will cause a problem if the view referenced by that GUID does not exist.

 

 

 

 

Tip #212: Getting a yes or no answer in LINQ

Say, you need to find out if your organization has any opportunities with the estimated value of more than a million dollars (that’s right, that’s how developers usually roll). No need to retrieve anything, just simple yes or no. There are many ways to accomplish the task, most of them, sadly, are as efficient as shaving yak with a disposable razor.

Chances are some people will write this:

var opportunities = crm.OpportunitySet
    .Where(opp => opp.EstimatedValue >= 1000000m);
if(opportunities.FirstOrDefault() == null) 
{
    // we have a winner
}

or even this

var opportunities = crm.OpportunitySet
    .Where(opp => opp.EstimatedValue >= 1000000m)
    .ToList();                
if(opportunities.Count > 0) 
{
    // we have a winner
}

LINQ parser quite often is smart and forgiving and you may get away with the first one but why take any chances and drag records across the wire? (second snippet is really bad, in case you missed that)

This code will get you home:

if(crm.OpportunitySet
    .Any(opp => opp.EstimatedValue >= 1000000m))
{
    // we have a winner
}

Even without looking at the final results I would bet that the last snippet would use EXISTS clause in T-SQL which is the most efficient way to check for record existence.

Tip #211: When upgrading, don’t forget the team form

When upgrading a deployment of Microsoft Dynamics CRM to version 2013, it is easy to overlook some of the back-end forms. These don’t get the attention of entities like Accounts and Cases, but they are very important to the people who use them.

One of my favorite new forms in 2013 is the Team form.

team form

This form is significantly easier to use than it was in 2011, because it adds a grid of team members to the form. You can easily see who is on the team, and add new members to the team. It also adds the new “Team Type” field, so you can see if the team is an owner team or an access team.

 

Tip #210: See when CRM for Tablets last synced metadata

If you have a user who does not see the latest customization changes in the CRM tablet app, check the last time that the user’s tablet app synced customization changes.

Click the options (…) button in the lower right corner, click “Settings,” then “About.”

about2

The “About” screen gives some useful information, such as the app version and the server CRM build number. It also tells us when the last time the user synced. If the sync date is prior to the last time you published customizations, hard close the app and restart it, downloading customization when prompted.

If the user is not prompted, publish customizations in CRM, then have the user close the app and restart it.