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.
VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)
Tweet about this on TwitterShare on FacebookShare on Google+

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
VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)
Tweet about this on TwitterShare on FacebookShare on Google+

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.

VN:F [1.9.22_1171]
Rating: 5.0/5 (1 vote cast)
Tweet about this on TwitterShare on FacebookShare on Google+

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.

VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)
Tweet about this on TwitterShare on FacebookShare on Google+

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.


VN:F [1.9.22_1171]
Rating: 5.0/5 (1 vote cast)
Tweet about this on TwitterShare on FacebookShare on Google+

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”
VN:F [1.9.22_1171]
Rating: 5.0/5 (2 votes cast)
Tweet about this on TwitterShare on FacebookShare on Google+

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:


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

VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)
Tweet about this on TwitterShare on FacebookShare on Google+

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.

VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)
Tweet about this on TwitterShare on FacebookShare on Google+

Tip #329: Resolve Missing Record Dependencies

The other day after importing a solution into an environment, I started getting error messages when I tried to save a record. The log told me the error was because an entitlement template with ID “2b2ef0de-24b6-e411-80da-fc15b4286d18″ did not exist. It seems that somehow my solution had a dependency on this record, and that record didn’t exist, so it couldn’t save the record.

I resolved this issue using the Configuration Migration utility from the CRM 2015 SDK.

  1. In my environment, I created a new entitlement template and saved it.
  2. I then connected the configuration Migration utility and created the schema and exported data for the Entitlement Templates entity.
  3. Extracting the zip file, I edited the data.xml file to modify the ID of the record I created in step 1 to the ID of the missing record.
  4. In CRM I deleted the record I created in step 1.
  5. Finally, I re-zipped the configuration migration package and imported it into my environment.

After doing this, my records could be saved successfully.

Note: This tip will get you past the missing record error, but if the process depends on the specific record having specific properties, you will have to edit the record to set the correct field values. In my case, it was a fluke and the properties of the entitlement template were irrelevant to the process. Caveat emptor, petimus credimus, Lorem ipsum dolor sit amet.

VN:F [1.9.22_1171]
Rating: 3.5/5 (2 votes cast)
Tweet about this on TwitterShare on FacebookShare on Google+

Tip #328: How rollup fileds make charts better

Ever since charts were introduced in CRM 2011, there has been a limit of 50,000 records that could be aggregated via a chart.  This limitation is there for performance reasons, and has been a reason why some users with large data sets have stayed away from using charts.

CRM 2015 introduces rollup fields, which automatically roll up child record data to a field on the parent record. This presents a solution to get around the 50,000 record limit (and make charts with fewer than 50,000 record render more quickly): Create a rollup field on a parent record, then chart the rollup field values.

VN:F [1.9.22_1171]
Rating: 1.5/5 (2 votes cast)
Tweet about this on TwitterShare on FacebookShare on Google+