Tip #233: Including external data in internal reports

You want to have a report in Dynamics CRM reference a database other than the Dynamics CRM database. Maybe you want to join external data, like data from your Dynamics AX ERP system, to account data in Dynamics CRM.

In many cases, the best answer is to use a data warehouse, Write a report that points to the data warehouse from an external reporting server, then display the report in Dynamics CRM using the link to URL report feature to point to another report server.

However, sometimes this is not practical. Maybe you don’t have a data warehouse or separate reporting server set up, and you simply want to include data from another database in your Dynamics CRM reports. This can be done.

First thing you need to know is that when you upload a SQL based SSRS report to your on premises CRM environment, the application overwrites the data source to use the shared MSCRM data source. This means that you cannot just add a data source pointing to another system in your SSRS report. It will fail to upload.

  1. Create your SSRS report in SQL data tools/BIDS and create an embedded data source pointing to your Dynamics CRM database.
  2. Make sure that the service account that your MSCRM services runs as has permission to read the other database. This database should either be on the same server as the CRM database, or a linked server should be created so both databases can be queried from the same SQL server.
  3. Write your data set query, be sure to specify the fully qualified table name of the external system database. You can join this table to the CRM database in your query to include data from both CRM and another system in the same report.

Using this approach you can also use CRM specific report functionality like prefiltering against other data sources. If you query the MSCRM database filtered views using the CRMAF alias prefix and join to another database using the fully qualified table name, prefiltering will still work in the report.

Tip #232: Vanity monitoring

Woo-hoo! Our CRM uptime has been 99.999% for the past 6 months! One awesome internet facing deployment we cooked up!

What do you use to measure that?

One of these wonderful and free services

Uhm, you do realize that all you have been measuring is the uptime of your ADFS login page?

These are vanity metrics – they paint the pretty picture but are otherwise useless.

To record the fact that your CRM is up and running, you need to perform a login and get at least first authenticated page. CRM login is a complex process, depending on the needs it’s either federation or OAuth and neither is for the faint-hearted. None of the reasonably priced third-party monitoring tools I had a look at were capable to perform authentication magic out of the box.

Couple vendors, however, do provide enhanced page monitoring using webkit based browsers at their end that can replay user action recorded on the client side. http://site24x7.com/ offers standalone tools while http://alertsite.com/ uses browser plugin approach; either one works fine and does allow replaying of the login process:

alertsite

Note that this test falls through to the mobile site because browser is not identified correctly – something that probably is not difficult to do.

Before you drop everything and start recording massive scripts consider the following:

  1. Talk to your users. What matters to them is important. If they say it’s slow, there is no point in bombarding management with the figures proving otherwise.
  2. Don’t measure just “no error” response from the server. While you are at it, measure response times for specific pages. CRM that never sleeps but where every page takes 15 seconds to load is unlikely to be loved by the users.
  3. Most of the monitoring services offer world-wide coverage, some of them do allow selection of the monitoring servers. Use that. Page load times of 100ms from Greenland or Mongolia mean very little. Unless you have users there, in which case drop us a line – we’d love to hear from you!
  4. For on premises installation consider [temporarily] injecting monitoring scripts into generated HTML (some of the packages do allow that) – that will give you a much better approximation of the user-experience and much clearer picture of the bottlenecks.
  5. Consider monitoring not only CRM application but also CRM web services, especially if your CRM integrates with third-party packages that utilize them. Authentication will be a challenge, some vendors do support crafting SOAP requests explicitly, look into that.
  6. Understand the anatomy of the page load in CRM 2013. A lot of functionality is now delivered asynchronously and even though full page load time could span into seconds, page can be responsive and useful long before that.
  7. Add monitoring of your SQL Server – if you have one – the primary suspect on any given day when performance metrics decide to take the southbound route.
  8. On premises compliment monitoring with Dynamics CRM performance counters.

Tip #231: Increase the limits of CRM for Tablets

The tablet app for Dynamics CRM includes limits on what can be included on a form in the app:

  • 75 fields
  • 10 Grids
  • 5 Tabs

These limitations are there for a reason: mobile devices have lower RAM than a PC browser, so the default limits are there to ensure good form performance. However, sometimes you may have a situation where you need to show more on the form.

CRM includes organization settings that can increase these limits. You can change these settings using the OrgDBOrgSettings tool, available with any update rollup download. This post will give you instructions on how to connect the tool to your environment, on premise or online.

The following are the settings that you can modify to increase the tablet client limitations. Be sure that after you make this change that you test the app in the device type that your users will be using to verify that performance is acceptable in your more complex forms.

Setting Max Value
TabletClientMaxFields 500
TabletClientMaxLists 50
TabletClientMaxTabs 50

Tip #230: Ban the browse button

Corporal punishment by bayonetsI like German words. Take, for example, Spießrutenlaufen. If you like me, and don’t speak a word of German, you know, just by looking, that the word means business, the word means corporal punishment.

I often wonder if ignorance of the developers can be cured by introduction of Spießrutenlaufen for coders. Function is longer than the screen – slap on a wrist! Still using Polish notation – 3 cuffs! Broke the build – 7 kicks! Adding assembly references by hand – go stand in the corner for 10 minutes! On one hand!

What do you do if your project requires either 2011 or 2013 SDK assembly? Do you click Add > Reference for the project and then click Browse button to get to this?
Adding assembly references

If yes, then look at this word again: Spießrutenlaufen. Why is adding assemblies bad? Where do I begin? Versions, for one. Broken path to assembly, another reason. My SDK assemblies are in q:\__(@#$%^&)__\randomstringtomakeaverylongfoldername\schadenfreude folder, that’s why you’ll be seeing yellow exclamation marks when loading one of my projects.

Since all 2011 and 2013 SDK assemblies have been published on NuGet, there is absolutely no reason to use evil Browse button. Need to work with 2013? Simple steps:

  1. Click Tools > NuGet Package Manager > Manage NuGet Packages for Solution
  2. Click Online > nuget.org
  3. Search for CRM 2013
  4. Depending on your project needs select one of the packages (hint: when selecting a package, read the description on the right hand side to see what assemblies are on offer), click Install

That’s it in the nutshell. Use your search-fu to find more information about precise version management, how to make nuget and source repository play well together, etc.

And, yes, NuGet packages already contain 6.1.1 assemblies and references in your project will be automatically refreshed, no need to do anything.

Tip #229: Reminder overload

You are moving to Dynamics CRM from a lesser CRM application (let’s call it Salesforce.com, but could be any other CRM). As part of the migration, you are importing legacy appointment records into Dynamics CRM and assigning them to their respective users in Dynamics CRM.

When you go live, if you use the standard Appointment synchronization rule with either Outlook Sync or Server Sync, these appointments will synchronize to the user’s Exchange calendar, and the user may be surprised to find that she has hundreds of reminders pop up when she logs in to Outlook.

If you are importing historical activities, one way to prevent this reminder overload is to modify the user’s synchronization filter for appointments to only synchronize appointments that occur near or after the go-live date. Pick a date shortly before the go-live date, such as the day before the users will start using CRM. Change the first line in the filter from Start Date Contains Data to Start Date on or after [desired start date].

filter

You can do this in bulk for all users using the xrm toolbox. See instructions here.

 

Tip #228: He knows when you are sleeping

All sort of interesting things happen when we are asleep: Santa Clause, tooth fairies, and, lately, CRM errors. If you are running production on premises system, enabling blanket tracing needs to be done with a great care as server disks will overflow very fast. It’s OK during the day to enable trace, reproduce the error and disable trace, but if your errors happen in the middle of the night, who’s there to flip the lights on and off?

Immense power of Windows scheduling to the rescue – schedule your traces to sleep well at night.

Couple points from Tipp Jarr:

  • Multiple servers in deployment are like a box of chocolate, so if you have more than one of those, run the job on each one of them.
  • Enabling script in the article misses TraceRefresh flag. Without it, your script will work only once.
  • KB writers seems to have a dislike for carriage returns. Here is the content lined up and ready to be pasted
    To enable:

    Windows Registry Editor Version 5.00 
    
    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSCRM] 
    "TraceEnabled"=dword:00000001 
    "TraceRefresh"=dword:00000002
    

    To disable:

    Windows Registry Editor Version 5.00 
    
    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSCRM] 
    "TraceEnabled"=dword:00000000 
    "TraceRefresh"=dword:00000003
    
  • When scheduling the job, make sure it runs under account with sufficient privileges. Muggles do not have permissions to modify HKLM keys.

Tip #227: View desktop version of CRM in IOS 8

Dynamics CRM hides some links when the application is opened in a mobile browser, like IOS Safari. This includes admin functions that are not designed for mobile browsers, like run report, Advanced Find, and the Settings area.

If you still would like to have these buttons appear on your tablet, third party browsers like Google Chrome have traditionally offered the ability to make the browser request the desktop version of the website, and with CRM, the missing links will then appear.

IOS 8 also ads this feature to mobile Safari:
1. Tap the URL bar to reveal the favorites/recently viewed records pane.
2. Pull the pane down.
3. Select “Request Desktop Site.”

 

IMG_0085-0.PNGYou will now see the full website. Note that this doesn’t mean that the buttons will all work. Some, like Advanced Find, will not work correctly. Others, like running reports, publishing workflows, or adding security roles to users, can be done from a tablet browser when desktop version is requested.

Tip #226: It’s a privilege

If you wish to have a link in the Dynamics CRM sitemap menu only appear for certain people, this can be accomplished by adding the privilege tag to the subarea in the sitemap XML. This allows you to specify that users must have the permission specified to see the link. For example, in the standard Activity Feed personal wall link in CRM 2013, users must have read permission for the Post entity to see the “What’s New” link.

Privilege

If you don’t like performing XML surgery, an easier way to do it is with the sitemap editor in the XRM Toolbox. By right clicking on a sitemap link, you can select “Add Privilege,” then specify the entity and the permission required.

 

 

Add Privilege

So what if you want to hide an entire area for a group of users? If all links in an area are hidden for a user, the entire area will go away. So, for example, say you want to hide the Settings area for users who are not System Administrators? In that case, you could add a privilege tag to all of the subareas in the settings area and specify read permission for the Solution entity. Any user who is does not have read permission for the Solution entity will not see the Settings area button.

But what if you want to show it to a specific group and hide from all of the rest of the groups, and there is no specific permission that group has that is not shared by the other groups? For example, say that you add an “Inside Sales” sitemap area to group links needed by the inside sales group. This includes links that other groups have access to, but you want to hide this area from other groups to simplify their user experience.

In this example, you could create a new custom entity called “Inside sales view.” Do not add the custom entity to the sitemap, but only give read permission to the entity to the inside sales group. Then add a privilege tag to each of the subareas in the “Inside Sales” sitemap area based on read permission for the “Inside Sales View” entity.

The result will be that only the inside sales group (and system administrators) will see the new sitemap area.

Tip #225: When CRM makes ye say “Arrrrrrgh!”

Friday, September 19
International Talk Like a Pirate Day 2014

If ye be usin’ Dynamics C Arrrrrrrrrrrr M fer several years, ye may spy wit’ ye eye that CRM starts runnin’ like a scallywag. Here be several thin’s ye can do to swab th’ deck ‘o ye CRM system, hoist performance, ‘n spit shine th’ cobwebs out ‘o th’ cornerrrs. Trrrrranslate, arrrrrrgh! Trrrrranslate, arrrrrrgh!

If you have been using Dynamics CRM for years, you may have noticed that the system has started to run a bit slower. Here are a few things that you can do to clean up your CRM system, improve performance, and cut some of the clutter that may have started to build up.

  1. Careen asynchronous table, ‘n if ye don’t already, schedule bulk scuttle jobs t’ do it magically. Trrrrranslate, arrrrrrgh!

    Clean up asynchronous table, and if you don’t already, schedule bulk delete jobs to do it automatically

  2. Hoist th’ newest update rollup. Trrrrranslate, arrrrrrgh!

    Deploy latest update rollup

  3. Swab th’ POA table. Use Buccaneer Sewell’€™s secret decoder rin’. Trrrrranslate, arrrrrrgh!

    Clean up the POA table. Use Scott Sewell’s secret decoder ring.

  4. Make sure ya ‘ave a good stash strategy. Trrrrranslate, arrrrrrgh!

    Review backup strategy.

  5. Refresh dev/QA orgs. Make th’ existin’ orgs walk the plank, import updated copy o’ production MSCRM database. Trrrrranslate, arrrrrrgh!

    Refresh dev/QA orgs. Drop the existing organizations, import updated copy of production MSCRM organization.

  6. Use th’ CRM data detective t’ review how frequently fields be populated. Delete/hide unused fields ‘n entities. All mateys have those “’twas a good ideas but avast, didn’t pan out”€ type entities in their crew. If ye ‘ave any o’ these or any fields that were added ‘n then nah used, scuttlin’ these can ‘ave a big bounty. Trrrrranslate, arrrrrrgh!

    Use the CRM data detective to review how frequently fields are populated. Delete/hide unused fields and entities. Everybody has those “good ideas that didn’t pan out” type entities. If you have any of these or any fields that were added and then not used, deleting these can have a big benefit.

  7. Look at rigs to stop plundering. If ye ‘ave added entities o’er time, chances are that th’ roles don’t reflect yer original security strategy. Maybe good time t’ switch t’ a base security role stratarrrrgy. Trrrrranslate, arrrrrrgh!

    Review security roles. If you have added entities over time, chances are that the roles don’t reflect your original security strategy. Maybe good time to switch to a base security role strategy.

  8. Review audit settin’s/duplicate detection settin’s to verify they be set correctly. These thin’s get turned off when solutions be imported. Chances be that some ‘o them be disabled. Trrrrranslate, arrrrrrgh!

    Review audit settings/duplicate detection settings to verify they are set correctly. These things get turned off when solutions are imported. Chances are that some of them are disabled.

  9. Review searchable field settin’s usin’ th’ xrm toolbox. Set fields not used on forms (other than system required fields) to be non searchable ‘n simplify Advanced Find. Trrrrranslate, arrrrrrgh!

    Review searchable field settings using the xrm toolbox. Set fields not used or on forms (other than system required fields) to be non searchable and simplify Advanced Find.

  10. Pillage any junk views ‘n reports. I define “junk” as views ‘n reports that be not actively used by anyone. Trrrrranslate, arrrrrrgh!

    Remove any junk views and reports. I define “junk” as views and reports that are not actively used by anyone.

  11. Organize ye olde trreasurremap. Frequently a crew that be in place fer several years gets a lot ‘o junk in th’ sitemap, like a settin’s area wit’ 75 links. I like to cut out anythin’ that does not have to be in th’ settin’s area. Fer thin’s like lookup entities fer country that never change, I like to remove from settin’s area fer simplification. If someone needs to modify ’tis list, they can do it via advanced find. Trrrrranslate, arrrrrrgh!

    Organize your sitemap. Frequently a deployment that has been in place for several years gets a lot of junk in the sitemap, like a settings area with 75 links. I like to cut out anything that does not have to be in the settings area. For things like lookup entities for country that never change, I like to remove from settings area for simplification. If someone needs to modify this list, they can do it via advanced find.

  12. Remove any third parrrty add-ons that be no longer used. Again, that fax integration was a jolly idea at th’ the hour, but do we really still need it? Trrrrranslate, arrrrrrgh!

    Remove any third party add-ons that are no longer used. Again, that fax integration was a good idea at the time, but do we really still need it?

  13. Upgrade to CRM 2013 SP1. Or if ye are not prepared to release the kraken, at least upgrade components like SQL Serrrrver ‘n Internet Pirate to newer versions to make yon future upgrades easier. Trrrrranslate, arrrrrrgh!

    Upgrade to CRM 2013 SP1. Or if you aren’t ready to upgrade, at least upgrade components like SQL Server, Internet Explorer to newer versions to make future upgrades easier.

Tip #224: Email signatures

In Tuesday’s tip, George Doubinski threw me under the bus gave a great alternative use for translation files in Dynamics CRM. The eight year old boy that I pay to write tips for me thought that this was a great tip, and made me think of some other alternative uses for Dynamics CRM features. By “alternate use” I mean a use for a feature that is not its primary purpose.

Here is another of my favorite alternate uses.

Sometimes I send an email from the Dynamics CRM web client. If I’m on a contact record, it can be quicker to just send the email from within the Dynamics CRM web client. However, when I do this, my email signature from Outlook doesn’t appear on the email. There is no “insert signature” button. But you still can use email signatures from the web client. Just use an email template for your signature.

  1. Create a new email template. Add you relevant contact information. If desired, you can also paste in a photo of your face.signature 1
  2. When you write your email in the web client, after you compose your email, click the “insert template” button. signature 2
  3. Select the template for your email signature. CRM will ask you if you want to replace the email subject with the template subject. Click “Cancel.” signature 3
  4. Your email will now include your signature.

signature 4