Tip #339: N:N relationships and Advanced Find

So you create a many-to-many relationship and add data to it, then you want to run an Advanced Find filtered on that relationship. But when you run the advanced find, you don’t get any results. What’s up?

The reason you don’t get anything back is because you have the relationship set to not display in the navigation area. Update your relationship to display on both sides of the relationship, and you will be able to filter advanced find off of the relationship and return results.

Tip #338: Rename that organization

So, you followed our earlier tip and ended up with
https://ihateyoumarcus.crm.dynamics.com.

Two days later, he finally agreed that, yes dear, we do need monogrammed napkins for each one of our 276 guests, but on the condition that this CRM name goes away.

CRM Online

  • Login into https://portal.office.com
  • Click Admin > CRM
  • If your company is rich enough, select organization, otherwise it’s preselected. Click Edit
  • Change URL to your liking. Find that someone has taken “grumpycat”, opt for “cardpinis2590”

CRM On-premises

On premises you can change the name using CRM Deployment Manager:

  • Disable organization
  • Edit? No, editing will only allow you to change the display name
  • Delete organization (Yes, delete. Chicken?)
  • Import organization back
  • At that point you’ll be given an opportunity to change the name
  • Consult Banned Words They Don’t Want You To Use. Based on your chequered history, stay clear of “marcus” anywhere in the organization name
  • Inhale/exhale – that’s how long it usually takes

Partner-hosted CRM

Call your partner’s support line and ask them to change it for you. After all, it’s them who provisioned this name in the first place.

Now, what did he say about the plastic glasses being just fine?

Tip #337: Using stored procedures with CRM reports

One trick veteran report writers like to use when writing SSRS reports for Dynamics CRM is to use a stored procedure for the report query. There are several appealing reasons to do this:

  • Performance: Stored procedures are cached, which can give them a performance advantage when compared to a query in the report. This advantage is diminished in modern versions of SQL, but I have seen complex, long running reports where the query timed out when embedded in the report, but completed successfully when using a stored procedure.
  • Separation of code: by separating the query in a stored procedure, it can sometimes be easier to troubleshoot/manage the query when it is separated from the RDL. We’ve all seen weird behavior when editing an existing CRM report–you upload it and the old version is cached, so you have to remove and republish the entire report. This can get very complicated if you have multiple subreports. By separating the query into a stored procedure, I can update the query and filters without having to republish the report.
  • Reuse of code: Sometimes you need to use the same query for multiple reports. A stored procedure lets me have a single copy of the query used by multiple reports.

There are also some potential downsides to using stored procedures for CRM reports:

  • It requires the report writer to have direct access to the CRM SQL database. When using embedded queries in reports, the report writer can author reports from SQL data tools without having direct access to the SQL database. Creating and editing of stored procedures requires a higher level of SQL database privileges than embedded queries.
  • It is borderline unsupported. This falls into the same gray area that creating custom indexes does. It is not documented in the implementation guide or the SDK, so it is not officially supported. It also requires elevation of the default database permissions. However, it can be a safe thing to do, as long as your stored procedures work in a supported way: Don’t write directly to the database, don’t query base tables, use filtered views. If you do this, you can remain close to supported with minimal risk.

Enabling stored procedures for reports

So you weigh the pros and cons and decide that you are comfortable with using stored procedures in CRM reports. How do you make it work?

To enable stored procedures for reports, you need to understand how reporting in CRM works. This applies to CRM 2011-2015. When I run a report in CRM, the report is not actually being run by me. Via the SRS data connector, CRM runs the report under the context of the service account that SSRS is running as on the CRM SSRS server.

  1. Log on to the SSRS server used by your CRM deployment. Pull up services and find the SQL Server Reporting Services windows service. Go to the properties of the service and look at the “Log on” tab. Look for the service account that the SSRS service runs as.
  2. Log on to the SQL server used by your CRM deployment. Go to SQL Management Studio. Add the service account found in step 1 to SQL as a user.
  3. Grant the service account “execute” permission to each stored procedure used by your SSRS report.

Tip #336: Choose your name wisely

A common mistake when setting up your on-premises CRM organization is choosing the wrong name. Name not display name.
Organization name vs display name
Frequently when seeing the field to enter the organization name, the novice CRM implementer will think they should enter the name of their company/organization. This is generally a bad idea.

  • The organization name will form part of your URL if you use IFD. This will result in some very long/hard to remember URL’s. https://acmeindustriesofnorthamericalimited.acme.com anyone?
  • The URL may also become redundant. Acme.acme.com is bad, and may be already reserved for other purposes.

So what should you use? Something easy to remember and descriptive of the purpose of the organization. Typically something with “CRM” in it, or whatever you refer to CRM internally as. I like to include “dev,” “test,” “UAT,” etc in non production organizations, then use a combination of the company abbreviation and CRM for production.

Here are some suggested organization names (case is irrelevant, of course, but may help readability especially if you’re trying to convey a secret message):

  • Awesomecrm
  • Doubinski (See if anyone can spell it correctly)
  • Salesforcestinks
  • acme-1969 (numbers and hyphens are allowed)
  • 1 (or just numbers, including those that can be mistaken for ‘l’)
  • 1l1l1O0l1lOO001l1 (with the names like this who needs security)
  • j––ll–––––––m (It’s amazing to see what a user’s mind can substitute mentally for a hyphen)
  • TheWorkingVersionofCRM
  • OurCompanysHighlyConfidentialInformation
  • EdwardSnowdenUsedtoWorkHere
  • Password
  • KatieFromReceptionCallMeOn800-555-0199

Tip #335: CRM upgrades and dead reports

Evan “Doc” Watson sends a tip to remember to clear out old/unused reports prior to upgrading to CRM 2013/2015. The dependency check in the upgrade process will kill your upgrade if any entities referenced in a report no longer exist in the environment.

If you have any reports that reference an entity that has been removed, you will see an error in the upgrade log that looks something like this:


Exception occured during Microsoft.Crm.Tools.Admin.OrganizationUpgrader: Error.ActionFailed Microsoft.Crm.Tools.Admin.PopulateDependenciesAction

InnerException: Microsoft.Crm.Metadata.EntityMetadataNotFoundException: The entity with a name = 'new_shipmentsummary' was not found in the MetadataCache.

Should this happen to you, you will need to delete the report (if you can), or if it won’t let you delete the report, recreate the missing entity.

Tip #334: Organization refresh and upgrade date

So your CRM Online test environment is scheduled to be upgraded to the Fall 2014 (CRM 2015) release next week. To get ready for it, you log in to the Office 365 console and refresh the organization to ensure that a current copy of your production environment gets upgraded to get a realistic test of your production upgrade. Great! Smart move.

One thing to look out for is that if you have already confirmed your upgrade date for this environment, refreshing the organization will cause your upgrade date to get changed to the next default date (which may be 3 months from now).

After the organization refresh is complete, check the scheduled upgrade date. if it is moved, you can move it back.

Tip #333: Secret reporting parameters

In yesterday’s tip we mentioned the CRM_URL report parameter.

But that’s not all folks.

One of the best things the aspiring report writer can do is to download the RDL for one of the system reports, such as the Account Summary, and look at the RDL in Visual Studio (after adding to a report project). You will see a trove of additional helpful parameters that you can use to make your reports better.

Screenshot 2015-02-25 11.27.03

Parameters like CRM_UserTimeZoneName, CRM_CalendarType, and CRM_FormatDate are very useful to get user time, date, and language settings for your report. While these are not in the SDK (as far as I have been able to tell), they have been documented in sources like this blog post and since they are used in the standard CRM reports, should be considered supportable for your custom reports.

 

Tip #332: Report hyperlinks the logical way

When building hyperlinks to CRM in SSRS reports, there is a right way and a wrong way:

  • Wrong way: hard coding your CRM URL. Do not set your URL to “https://crmserver/….” If you do this use the hyperlink will only work in one environment and will have to be rewritten to work in another environment.
  • Right way: Use the CRM_URL parameter in your report. This makes the report hyperlinks environment agnostic, and your links will work in all environments, even when offline.
  • Wrong way: Using OTC in your hyperlink expression. “=Parameters!CRM_URL.Value & “?OTC=1&ID={“& Fields!accountid.Value.ToString() &”}”” will work, as it calls the Object TypeCode (OTC) of the entity. This never changes for System entities, but custom entity OTC will change when customization is imported into a new environment.
  • Right way: Use the logical entity name in your hyperlink expression instead of OTC. “=Parameters!CRM_URL.Value & “?ID=”& Fields!accountid.Value.ToString & “&LogicalName=account”

Tip #331: What kind of Bing Map license do I need?

Donna “Not Congresswoman Donna F” Edwards sent a helpful tip about how to determine what type of Bing Maps license you need for an on premises deployment of Dynamics CRM.

If you go to http://www.microsoft.com/maps/Licensing/licensing.aspx, it will ask you questions to choose which type of license you need:

binglicense

This page also discusses the scenarios for each type of key: http://www.microsoft.com/maps/create-a-bing-maps-key.aspx

Tip #330: When should you use CRM as a development platform?

Dynamics CRM TipperTime for another mini truck stop, folks. In a recent discussion, Donna “I’m on a boat” Edwards asked

When does it make sense to use Dynamics CRM as a development platform vs. building your own application from scratch?

Shan “Grillmaster” McArthur had a great response:

The best way to estimate the strategic value and/or ROI of using Dynamics CRM as a development platform for applications instead of building your own application from scratch is to assess how much of the application depends on a security model, configurable forms and schema, workflow, and mobile features, and if the data model would naturally fit into the forms/view model of CRM.

An application that has a higher percentage of these features and a close fit to the CRM UI model will have a very high productivity ratio (from building with Dynamics CRM), but if these things are not important and the application does not fit the CRM UI then the ratio would be lower.