Customer Effective Blog

Simplified Dynamic Drill-Through to Microsoft CRM Using Logical Entity Name

Previously on the CEI Blog, we have covered using the CRM_URL hidden parameter to dynamically build hyperlinks in SSRS reports.  For those of you not familiar with CRM_URL, a little background:

CRM_URL

One of the things that makes the combination of Microsoft CRM and SQL Server Reporting Services great is drill-through.  A report, like a sales pipeline report, becomes much more usable if you can click on the opportunity name and pull up the CRM record for that opportunity.  An easy novice report-writing mistake, when building reports with hyperlinks to CRM records, is to hard code the hyperlink URL using the URL of your CRM server.  This approach will work; however, the hyperlinks will only work in that one environment, and only for users who access CRM that way.  For example, if you want to take the report and upload it to another environment, the hyperlink expressions will need to be re-written, and if you have users who access CRM in other ways, such as offline, the report hyperlinks will not work.

That’s why Microsoft included the CRM_URL hidden parameter.  This allows you to build a hyperlink to CRM that dynamically changes based on the environment and how the user is accessing it.  If a user accesses a CRM report offline, if you use the CRM_URL parameter in your hyperlink, the links will point to offline environment while offline.  Bigger benefit is that the report can also be moved between environments without having to reconstruct the jump-to hyperlink expressions, which is essential to proper change management.

So instead of your hyperlink expression looking like this:

=”http://crmserver/orgname/sfa/conts/edit.aspx?id={“ & Fields!contactid.Value.ToString() & "}"

It should look like this

Parameters!CRM_URL.Value & "?OTC=2&ID={"& Fields!contactid.Value.ToString() &"}" 

For more information on how to set up the CRM_URL hidden parameter in your SSRS report, see the dynamic drill-through section of the Dynamics CRM SDK.

Dealing with Custom Entities

In the above example, we’re using the CRM_URL parameter to drill-through to a CRM Contact record.  The “OTC=” part of the expression defines what entity the hyperlink should link to, using the Entity Type Code.  Each entity in CRM has an Object Type Code.  For system entities, these codes are always the same.  See a list of system entity type codes in Dynamics CRM.  So if you are drilling through to a standard system entity, the above example will work fine.  The Contact Entity is always OTC 2.

But what about custom entities?  Custom entities also have an entity type code; however, the type code for the same custom entity may be different between two environments.  The reason is that the entity type code is set sequentially based on the order the entities in an environment are created.  If you are drilling through to a custom entity, the example above will still work for you if you specify the OTC of the custom entity in the environment to which you are publishing the report; however, when you move that report from your test environment to your production environment, there’s a good chance that the hyperlinks may have to be re-written.

Fortunately, there is an easier approach than the example specified in the SDK.  By replacing “OTC=” with “LogicalName=,” we can define the drill-through entity using the entity name rather than the entity type code.  For example, say we have a custom entity named “Truck,” we can drill through to it using the following expression:

=Parameters!CRM_URL.Value & "?ID="& Fields!new_truckid.Value.ToString & "&LogicalName=new_truck"

And we can now move the report between environments without having to re-write the report hyperlinks.

del.icio.us Tags: Microsoft CRM,reports,ssrs,drill-through

Truth In (SSRS) Labeling

If you’ve ever used a drop-down list in an SSRS report to pass parameters, you may have wondered how to reference the label of the parameter in the report.  Let’s set up an example to illustrate the problem.  Say I wanted a drop-down list to reference colors.  The parameter selected is already in my SQL WHERE clause (i.e. SELECT widgetname, widgetplant FROM widgets WHERE widgetcolornumber = @color).

So I’ve configured my parameter to look like the following (For illustrative purposes only, among other things it’s almost always a bad idea to hard code like I’m doing below: what if someone wants blue? And remember, someone will always want blue.):

I’m passing the number 1, 2, or 3 (see Value column). I could reference this in a text box or table header by using the following syntax:

=Parameters!color.Value

But how would I reference the label, which is the name of the color itself?  If you’re like me, you probably typed right past the answer when you were building that syntax, so let’s go to the instant replay slo-mo of what happened when you started typing =Parameters!color:

There are a couple other options there.  The one we’re interested in is the Label function.  If you used that, your syntax would be:

=Parameters!color.Label

If you were to create two text boxes in this example, and set the drop-down to “Red” the text boxes would read 1 (Value) and Red (Label).

The other thing to keep in mind here is that you can pass this Parameter label as a parameter value to another report.  This is the parameters dialog box from the Jump To Report selection:

This can come in handy in drilling through to other reports.

The alternative to using the Parameter Label construct is to set up a set of cascading parameters, which if you’ve had some practice at doing it’s not that big of a deal.  But those machinations just aren’t worth it when you use this.   IDs, like CRM GUID’s or picklist values, are almost always (and almost always should be) passed as parameters, and the need to know the text associated with that ID and display it in a report comes up constantly.

Finding Your Inner Report Server

Ever wonder where your reports go when you use the Publish Reports for External Use menu item?  Ever need to know what the URL for the report server for the CRM reports is but your friend in IT isn’t available?  Fear not, SSRS has a handy little function designed just for these situations.  It’s a function called ReportServerURL and if you use Visual Studio to develop your reports it has been right under your nose.

To show how to use this, let’s take an existing CRM report (or create a brand new one) and add a text box to it. Add a border to it as well, as that will be important later. Right click and from that menu click Expression.  

The Expression Editor will appear next. Click to Globals à ReportServerURL. Notice the red arrow that points to some nice descriptive text. Don’t look for the red arrow in Visual Studio though, that’s just me being extra helpful.

Click OK and your text box should look something like this:

Now run the report locally.  Your textbox, provided you added that border, will look like this:

 
Nice function, huh?  The reason is blank is because you’re not running it from the Report Server yet.  So let’s remedy that by posting the report to CRM and the running it. Your textbox should look something like this, with names changed to protect the innocent:

 

Copy and paste that address into IE and you should see into the report server itself. 

So if you’re ever in a pickle (how did that expression ever come about and under what scenario?), and you need to know the report server URL you are in luck.  Look for a future blog on other reasons why you would want to know it and take a look at the sister functions here called ReportFolder and ReportName to get a better idea of some not so popular but useful SSRS functions.

 

 

 

 

 

 

Turbo-Charged Microsoft CRM Organization Import

In the past we have blogged about the Dynamics CRM organization import--this is where you take a copy of an existing Microsoft Dynamics CRM environment and move it to a new server--this is frequently used when refreshing a dev or test environment with a copy of a production CRM environment.

Probably the biggest complaint about this process has traditionally been how long the process takes--on average, it took 4-5 minutes per user in your database, and required a lot of free sql server disk space.  As a result, a typical dev refresh could take 10 or more hours, even when importing the organization on the same domain as the production environment.

If you haven't tried this recently, I would recommend that you try it again using one of the most recent updates.  Update Rollup 8 or later includes some major improvements to this process.

Last year I had a client that wanted to refresh their development environment with a copy of production data.  They had 175 users, and were running update rollup 6.  The organization import took over 15 hours to complete.

Six months later, they wanted to again refresh the dev environment so the data and configuration more closeley matched production.  Now they are running Update Rollup 10.

I had heard that the organization import process had been improved, but I hadn't tried it out yet.  I was prepared for the process to run for multiple hours since it had taken so long the last time; however, this time it only took 28 minutes to complete successfully!

This is a great improvement, and makes refreshing a staging environment much easier.  This also shows a great committment by Microsoft for continuous improvements to the application.  Thanks Microsoft Dynamics CRM team.

Dynamics CRM 4.0 and Exchange OnLine

Several people have asked me recently if Dynamics CRM 4.0 will work with Exchange Online, Microsoft's new hosted version of Exchange 2010.  It seems like many companies are considering outsourcing Exchange to Microsoft, which to me sounds like a great idea.

The answer to the question of compatibility with Microsoft Dynamics CRM is "yes!"

As of the Nov. 2009 R4/Update Rollup 7 Email Router, Exchange Online is now an option.  The CRM team achieved this by changing the e-mail router to use exchange web services for sending and receiving emails--so in effect, they have totally changed what is happening under the hood while preserving the functionality and the user experience.

This means that you can have full email sending and tracking capabilities using CRM with Exchange Online, including forwarding in-box or workflow emails sent using the email router.

Some small considerations:

1.  You can't have CRM E-mail routher configured to use both on premises Exchange and Exchange OnLine.  You have to use one or the other
2. Exchange Online doesn't send emails AS another user, it sends them ON BEHALF OF another user--what this means is that emails like workflow generated emails where an administrator sends an email as a different user would appear differently than they do with on premised Exchange--they would say "Mary Jones on behalf of John Smith."  Microsoft says that if you want to enable sending as other users with Exchange Online, you can, but it requires you to log a support call.

See Configure Microsoft Dynamics CRM Online E-mail Router with Exchange Online for details on setting up the E-mail router with hosted Exchange.  While this refers to Crm Online Email Router, just about everything is the same for on premises deployments of CRM connecting to Exchange Online.

Beware Of The "Others"

If you ever watched the television series Lost, you know about the “Others.”  These were mysterious inhabitants of the island who were often at odds with the show’s main characters.

In Dynamics CRM, there are also “others.”  These “Others” frequently show up in picklists, such as account industry, customer type, address type.  When you are configuring one of these picklists, sometimes the thought goes through your mind “maybe I should add a placeholder called “other” just in case I didn’t think of every possible choice.”

Before you let an “other” into your list, think twice.  Consider this cautionary tale.

One of our clients is an accounting firm that serves multiple industries.  One of their goals was to track business by industry, so they required the Industry plicklist on the account form; however, they also included an option for “other” in the picklist.

After they had used CRM for a couple of years, we helped them develop some dashboards and reports for their account data.  What we found when we graphed their accounts by industry was that the “other” bar was significantly longer than any other industry.  This was not because these companies were in unusual industries, they were all in one of the existing industry codes, such as manufacturing.

The lesson learned was that users tend to take the easy way out—it was easier to select “other” than to determine and record the actual industry in the field.

Here are my recommendation on how to fight the “others” in CRM:

1. If possible, don’t include “other” in your picklist.
2. Instead, have an easy process for your users to follow to request new picklist values, in case a legitimate option is missing.
3. If you must include an “other” option, monitor this data closely to insure that records don’t get mis-categorized as “other” if they actually have a more legitimate categorization.

del.icio.us Tags: Lost,Dynamics CRM,others,picklists,best practices

Scribe Insight Installation, Updates, and Windows User Account Control

--This is a post that I recently wrote for the Scribe Integrator blog, which is a great blog for all things Scribe Insight. 

When installing software on Windows 2008, you typically want to install it as an administrator.  I’m not just talking about the user account having local administrator rights—with Windows User Account Control on Windows Server 2008 (and also on desktop versions of Windows Vista and Windows 7), you have to right click on an .exe file and select “Run as Administrator” if you want the installation to run successfully.  This applies to almost any application, including Microsoft Dynamics CRM, Scribe, SQL Server, and others.

Of course, you can disable User Account Control; however, frequently this is not an option, especially if you aren’t the owner of the server on which the software is being installed.

WithScribe Insight installs, I have found that you always want to run the installation as an administrator; however, there are a few more considerations.

The first time you run Scribe Workbench

The first time you run the workbench and register the application, it is also a good idea to run the application as an administrator (right click on the icon for Scribe on your desktop and select “run as administrator.”  If you don’t I have seen error messages, or have seen the registration not “take” and require the registration wizard to be re-run next time you open the application.  After the application runs for the first time and the product is registered, running as administrator is typically not required.

Installing updates

The first thing I always do after installing Scribe Insight and registering the product is to install the 6.5.2 update.  This update is the most recent update to Scribe Insight, and it has a number of very important updates and fixes.  Like with the application installation, you want to run the update install as an administrator.  Otherwise, you may see errors like this:

But how do you run the update as administrator?  Since the update is an MSI file, when you right click, there is no “Run As Administrator” option.  There are several ways to do it, this is my favorite and probably the least painful way to do it:

1. Download the 6.5.2 update.
2. Navigate to the downloaded file ScribeInsight.msi.  Hold the shift key and right click on the file and select “Copy as path.”
3. Open Notepad and paste the copied file path.  Save the file, making the file extension “.bat.”
4. Right click the .bat file and select “Run as Administrator.

The update will now install successfully.

Separate Server Roles and Microsoft Dynamics CRM 4.0

JUNE 2011 Author update:  When this post was written, there was a limitation in CRM 4.0 that prevented the asynchronous service from being run on multiple servers in a Dynamics CRM 4.0 implementation.  While the approach recommended in this post was a good recommendation at the time, the reader should be aware that subsequent Update Rollups have resolved conflicts preventing the asynchronous service from running on multiple servers.  I would recommend that if you want to scale your deployment to multiple servers, consider using a network load balanced cluster with the asynchronous service running on both nodes.  This will provide improved performance for users as well as high availability for failover.

 

ORIGINAL POST--MAY 2010  

One of the choices  when deploying Microsoft Dynamics 4.0 is whether or not to separate server roles.

 

When you install Dynamics CRM on a server, the application layer (web server, CRM application) and the platform layer (asynchronous service, discovery service, sdk) run on the same server. 

 

There are several reasons that you would want to separate server roles.  For example, if you have a heavy quantity of asynchronous activity, such as imports, workflows or plugins, you don't want to affect availability of the application, and you don't want asynchronous operations to have a long backlog and slow down the amount of time it takes for new asynchronous operations to be processed.

 

Traditional Approach

 

The traditional way to separating server roles with Dynamics CRM 4.0 Enterprise was to select the separate server roles option when installing.  This gives you the option to install the roles that you want on a server, so you can have one server be the application role, and have another be the platform role, for example.

 

Complexity

 

Before you do this, you should be aware that going with the separate server roles may introduce some complexity into your environment.  When everything runs on one server, everything uses the same server URL; however, if the platform role is separated,  things that hit the discovery service, such as the outlook client, email router, and custom code will need to hit the URL of the platform server, not the application server URL, and having two different addresses may add some confusion for certain users.  If you elect to deploy your environment Internet facing, or IFD, so you have CRM available to people not on your network, there is a lot of complexity and frankly this option is not very well documented in the IFD scenarios documentation.

 

IFD and Separate Server Roles

 

if you have separate server roles, you will need to expose both the app server website and the platform server website externally.  You will need to run the ifd tool on both the application and the platform server--this is not documented in the ifd scenarios document, but is required if you want external users to be able to configure Outlook clients in ifd mode, You will need to have ssl certificates on both the application and platform server, which will cost you extra.  The IFD scenarios document doesn't say this, but you will have to have your platform server URL be organization specific--if your CRM URL is myorg, for example, you will need the URL to be something like  myorg.platform.company.com.  again, not documented, but CRM will expect the org name to be in the Url when you configure the Outlook client.  Users will have to configure their outlook clients using the platform server URL, not the CRM application URL.  The reason for that is the outlook client connects to the discovery service during configuration, and the discovery service is part of the platform role, not the application.  having a different CRM URL for outlook client and web client can be very confusing to some users.

 

An Easier Approach

 

As we have seen, the traditional approach to separating server roles may add some complexity to your CRM deployment; however, that doesn't men you shouldn't do it.  Rather, there is another approach that achieves the same goals.

 

This approach is to install the full CRM application on both servers, both pointed at the same CRM databases.  On server A, stop the asynchronous service, and on server B, start the asynchronous service.  Drive all of your users to the URL of server A.

 

The end result is that, like with separate server roles, you have all application traffic handled by server A, and all asynchronous load handled by server B, but you avoid all of the complexity of traditional separate server roles.  Users will be able to use the same URL for the web and outlook interfaces, and Internet facing deployment will be much more straightforward, requiring only on URL or ssl cert.

 

Thanks To Alex Fagundes at Power Objects for providing input for this post.

Workflow Deadlock Issue Solved

I am really impressed with Microsoft Dynamics CRM Update Rollup 10.  First, it solved an issue we saw with synchronization errors when using shared calendars.  It also fixes a workflow issue.

This issue can happen when you have a workflow with a send workflow step, such as a notification workflow.  Sometimes when you generate multiple instances of the workflow simultaneously, some of them can fail due to deadlocking.

You can read more about this fix in Microsoft KB article 981053.

del.icio.us Tags: Microsoft CRM,Dynamics CRM,workflow

AgChoice CRM Success Story – New Video

Customer Effective is proud to announce a new Microsoft developed video on a CRM success story with their client, AgChoice. 

This is the first of two videos highlighting AgChoice Farm Credit, MidAtlantic Farm Credit, and AgFirst Farm Credit Bank. Please review the full case study via our website

Video Link - http://www.youtube.com/user/CustomerEffectiveCRM#p/a/u/0/80Pn0nRvxfQ

AgChoice Farm Credit (AgChoice) is a federally chartered Agricultural Credit Association (ACA) with nearly $1.3 billion in gross loan assets. Through 10 branch offices, it provides a broad range of financial services to farmers and forest products business owners. Its chartered territory covers 52 counties in central, western, and northern Pennsylvania as well as 4 counties in West Virginia.

AgChoice Farm Credit conducts annual customer satisfaction surveys to guide its philosophy of continuous business improvement. Recent results showed that 28 percent of customers prefer to conduct business at their home/farm. Another 34 percent prefer to do business with the Association by phone.

These findings highlighted a potential weakness in AgChoice's operations. Customer data resided in silos—employees’ heads, hard drives, and paper files. So, only AgChoice salespeople could comprehensively answer customers' inquiries about the status of their loans. This tethered them to their desks instead of spending more time in the field with prospects and customers.

A second challenge was that AgChoice salespeople had large geographic territories to cover. However, existing CRM systems were not scalable to close the gap. Spreadsheets, paper files, and e-mail messages were all labor-intensive, and to cover more sales territory with these tools, the Association would have to hire new staff.

In light of these issues, the AgChoice management team established two goals:

· To liberate salespeople from their offices and markedly increase their on-farm presence with customers and prospects.

· To empower back-office loan processing and customer service employees to take salespeople's places to satisfactorily answer customer inquiries.

What AgChoice needed to achieve these goals was an enterprise wide CRM solution. Unfortunately, the Association lacked the resources and budget to field an agricultural-specific solution on its own. This led executives to engage AgFirst's IT team to address their challenges.

AgFirst enlisted the help of Microsoft Gold Certified Partner Customer Effective to help with the implementation effort.

Read the rest of the story via our website - http://www.customereffective.com/Customer_Success

Inserting HTML in CRM 4.0 workflow generated email with dynamic fields

I ran into an interesting problem when trying to create an email generated through workflow that contained HTML and CRM dynamic fields.  The main problem was the pictures included in the html message.  Well actually, the main problem was the lack of an html editor in the CRM email editor.
To get some html in the email body, I copied and pasted from Microsoft Word.  The standard html, such as links and text areas, seemed to work fine but the pictures would not show up.  The issue was that I was using Microsoft Word.  I used Internet Explorer (IE) and the correct content displayed.  I am sure you can probably use some other html editors but I only used IE.
Here are the steps I followed.
1.  The first thing that you need to do is to have an html page.  If it contains images, those images need to be on an external server and the urls in the html need to point to the location on the external server.  The simple way to test this is to save your html page to a folder and ensure there is not a folder named images in that folder.  Now open the html page in Internet Explorer (IE) and make sure you can see all of the content.
On a side note, it is possible to have the images show up if the image path points to a location on your network that you have access to so don't get fooled by that.
2.  Once you can successfully view the email in IE, select all the content on the page and copy it to the clipboard (Ctrl + C).
3.  Now you want to go into your workflow and click the "Set Properties" button to the right of you "Send e-mail" action.
4.  Paste (Ctrl + V) the html you copied from your browser into the body of the email you are creating.  Please note that using the paste button in the toolbar above the body probably won't work.
5.  Now that your email has the html content you want, click your cursor where the CRM dynamic field to be placed and insert the field.  Sometimes, the dynamic field does not show up in the exact spot you want it to.  In this case, I will get the field in place by deleting the carriage return before the field and then adding another one after the field.  If my dynamic field ends up being placed at the bottom of the message, I delete it and add it back to the body in the correct location.
I have not been able to determine why the dynamic field gets missplaced.

Good Luck!

SSRS List of Month Numbers Using Common Table Expressions

One common task in report writing is to give users a list of months to select from and use it as a parameter.  There are a couple of ways to approach this from using a cursor/loop to even doing a series of UNION statements.  However, one tool that came around with SQL Server 2005 is the Common Table Expression (CTE).  CTE's are great for a lot of reasons including their usefulness in tidying up long SQL statements.  (I say that, but I won't be talking about how here). Notwithstanding, this quick CTE can provide you with the numbers one through twelve:

You probably recognize this screen shot from Visual Studio where I'm using it as a dataset. I in turn use this dataset in the Available Values section for a parameter and set the default value of the parameter using =Month(Now()).

 
I could also do something similar to get the past 5 years:

 
These just small examples of what CTE's can do.  If you're not using them, definitely check them out. I hope you find this useful.