Customer Effective Blog

Microsoft CRM Reporting by Fiscal Year

Many companies track their sales by quarter. When you want to report on historical sales or on future pipeline, it can be helpful to view this information by quarter; however, Microsoft CRM does not record the quarter that the sale happened. CRM does include Fiscal year settings, but this is only really used for the CRM quota functionality.

So what do you do if you have a fiscal year that doesn't correspond to calendar year? For example, if your fiscal year runs June – May?

Here is a SQL query that will do the job:

select name, accountidname, estimatedclosedate,case

when datepart(m,estimatedclosedate) >=6 then (datepart(year,estimatedclosedate))+1

Else

datepart(year,estimatedclosedate)

end as 'Fiscal Year',case

when datepart(m,estimatedclosedate) between 6 and 8 then 'Q1'

when datepart(m,estimatedclosedate) between 9 and 11 then 'Q2'

when datepart(m,estimatedclosedate) =12 then 'Q3'

when datepart(m,estimatedclosedate) between 1 and 2 then 'Q3'

when datepart(m,estimatedclosedate) between 3 and 5 then 'Q4'

Else

null

end as 'Fiscal Quarter'

from FilteredOpportunity

This query is a simple pipeline type query that returns the Opportunity Title, Account Name, Fiscal Year, and Fiscal Quarter. If the month is June-December, it puts the opportunity in the next fiscal year.

Update Microsoft CRM 4.0 from Excel

I have been testing EBAX for Microsoft CRM from Prosoft Systems. The original trial version didn't work for me, but the latest release fixes the problem that I was having. It's also much better than the first preview release, because it is a full release—it works with every entity in CRM, including custom entities.

After you install the add-on and launch Excel, you will notice that you now have a MS CRM tab on the Excel ribbon. Click on the "Set CRM Server" to configure it to work with your CRM environment. The latest version works whether your CRM environment is on-premises, IFD, or CRM Online.

Once you have configured EBAX to work with your server, you will now see drop-downs menus to select your entities and views. You will see all entities available. Once you select your entity and view, click the "Populate Data" button.

EBAX will then bring the data from the view into Exce.. If there are any required fields on the form that are not part of the view, these fields will also be retrieved. You can then update any fields that you want to change and hit the Update Data button to send your changes back to CRM.

Inside the CRM Task Pane you will see some additional drop down selectors—these correspond to any picklist, date, or lookup fields in the view. If you need to update any of these, for example sales stage, select your record, and select the new sales stage value in the dropdown that corresponds to column J.

 

What I find especially exciting about this release is that you can not only edit existing records, you can also create new records—just add a row to the bottom of the spreadsheet. It also is very logical for tracking what records are being changed, because any new/modified records are changed to a green font.

Also, it is smart enough so that you can sort the rows however you want in Excel, without messing up your CRM data when you synch your spreadsheet with CRM.

Great job—can't wait until the final release.

CRM Email Router Required to Send Email from Microsoft CRM Web Client

Sometimes you learn that everything you know is wrong. I recently had that experience in a discussion about the Microsoft CRM email router.

In Microsoft CRM 3.0, the E-Mail router (formerly called the Exchange Router) had the job of routing in-coming Exchange emails with a tracking token to the CRM mailbox, where they were tracked in CRM.

With 4.0, the E-Mail router works quite differently. It now is called the E-mail router because it not only works with Exchange, it also works with POP3, but also SMTP for outbound e-mail. That last part is the part I had to unlearn.

Microsoft CRM 3.0 web client would send outgoing emails directly via SMTP. With 4.0, outgoing E-mail also utilizes SMTP; however, the E-mail Router is now required to send email from the Web client. If you don't have the E-mail Router installed and you try to send e-mails from the web client, they won't go anywhere.

The flip side is if you are using the Outlook client, you do not have to use the E-mail Router, as CRM 4.0 for Outlook can now monitor and track emails without forwarding rules or the router. There are reasons why you would still want to use the E-mail Router if your users are using the Outlook client, if you want to centrally manage it, if you have multiple organizations, or if you use email enabled queues.

A great in-depth explanation of the pros and cons of each approach can be found on the Microsoft Dynamics CRM Team Blog.

 

Dynamic Worksheets from Microsoft CRM IFD Deployment

Problem: CRM is accessed via Internet Facing Deployment. When a user hits the Excel button on a list view, and selects either Dynamic Worksheet or Dynamic Pivot table, the resulting spreadsheet just shows the CRM login screen.

Solution: To use dynamic spreadsheets from an IFD deployment, the person opening the spreadsheet needs to have the Outlook client installed and configured on the workstation that he is using to open the spreadsheet. Since CRM is not on the same domain as the workstation, the Outlook client facilitates the login credentials for the CRM datasource used in the spreadsheet.

Solution for Scribe Error 111

I recently had an issue connecting to an ODBC data source in Scribe Insight Workbench. I could connect to the data source, view the database objects, but when I attempted to query, either a single object or custom query, it returned an error box that looked like this:

Scribe support was very helpful. They suggested that we use the Scribe OLE DB adapter to connect to the data source. We configured the OLE Adapter to connect to the ODBC DSN that we had previously set up and entered the name and password, and it worked great.

Thanks Scribe.

Hiding Navigation Items within a form

The ability to hide items on the main Navigation menu is a function of modifying the site map.  However, hiding Navigation items from within a form requires some basic jscript.  For example, a customer is not using the invoices or quotes functionality of the CRM system.  Therefore, when the Account or Contact form is opened these items need to be hidden.  The following jscript is entered on the on load event of the entity in question.  In this case, it would be the Account and Contact entity.

Of course other navigation items can be hidden to by simply replacing the item in the ("navXXXX") section of the code.

var oCloseMenuItem = document.all("navQuotes");
if (oCloseMenuItem != null) {
    oCloseMenuItem.parentElement.removeChild(oCloseMenuItem);
    }

var oCloseMenuItem = document.all("navInvoices");
if (oCloseMenuItem != null) {
    oCloseMenuItem.parentElement.removeChild(oCloseMenuItem);
    }

More IFD Lessons Learned: IFD in a DMZ

In a previous post we talked about lessons learned from using the Microsoft CRM IFD tool, or important details that aren't explicitly detailed in the documentation. 

In a recent implementation we came across a unique wrinkle.  The deployment needs to be internet facing; however, corporate policy at the client dictates that anything that touches the internet needs to be isolated from the rest of the network via a DMZ (Demilitarized Zone).

The challenge presented was that this option is officially supported at Microsoft, but it is not really documented, other than a diagram on p. 4-63 in the CRM 4.0 Implementation Guide (Planning section).

Prior to deployment, I discussed it with MSFT support.  Based on the newness of the IFD Tool, they did not have many recommendations on how to do the DMZ deployment.

So we went ahead and did the implementation, and it went very smoothly.  These are the steps and the order that we did them:

1.  Install CRM, just like any other deployment

2.  Enable DMZ

3.  Open the required ports back to the LAN: 

SQL 1433
SRS 80
Exchange 25
Domain Controllers (whatever your Domain Controllers are)

(NOTE:  Each of these pieces has an option to specify a different port upon install, so if you did a custom install, open whatever ports you selected during install)

4.  Test from CRM server to make sure nothing is broken

5.  Procure and install SSL certificate.  Important note:  for IFD deployment, a Wildcard SSL certificate is required.

6.  Set up the CRM URL (see point #3 on the previous post for details about what your IFD CRM URL needs to be)

7.  Run the IFD tool, testing to see if you can resolve the DNS.

Biggest lesson learned is that yes, it is possible to install CRM behind a DMZ and make it internet available; however, I would recommend against it if possible.  Given the number of ports that need to be opened up back to the LAN, it somewhat defeats the purpose of having a DMZ, and the IFD deployment is pretty secure without a DMZ, especially if you use SSL.

Creating A New Report System View in Microsoft Dynamics CRM 4.0

Microsoft CRM includes four categories of reports by default:

• Administrative Reports
• Marketing Reports
• Sales Reports
• Service Reports

These are good categories, but say you want to create a new category for your custom reports. Say you work for Acme co, and you want to categorize your company specific reports "Acme Reports." You can add this category, but it is a several step process.

1. Create the category

   In Microsoft CRM 4.0, go to Settings on the Wunderbar, and select Administration from the left side navigation bar. Click "System Settings." In the System Settings form, go to the Reporting tab. From here, you can create new categories, delete or rename existing categories.

   

2. Now you will have your new category defined, and you can classify reports as "ACME Reports." However, you will notice that when you view CRM reports, ACME Reports is not listed as one of the available views.

3.  To create an "ACME Reports" view, you will need to create a view, just like you would for any other entity. In CRM 4.0, click Settings, customization, Customize Entities. Select the Reports Entity and create your view. The filter logic should look like this:

You may notice that the report category is not stored in the report entity—you will need to select it from the related category entity.

4.  After you publish your customizations, you should now see "ACME Reports" listed as one of the report views; however, when you select it, you might see an error box that looks like this:

If you get this error, the reason probably is that you neglected to add the "Report Type" column to your view. Once you add the column to your view, it should work.

Installing the CRM SRS Data Connector With a Named SQL instance

When you deploy Microsoft CRM 4.0 internet facing, you also need to install the SRS Data Connector on your SQL Reporting Services server to enable reports to be generated from CRM while in IFD mode.

If your database is installed in a named SQL instance, the Environmental Diagnostic at the beginning of the installation process will error out with the error ""Unable to validate SQL Server Reporting Services Report Server installation."

The problem is that the installation looks for the SRS database in the default instance of SQL.  If it cannot find it, it doesn't think that SRS is installed.

There is a knowledge base article with a solution to this problem.  It is a pretty simple solution, which involves adding a line to the install configuration XML file and launching the install from a command prompt.

It solved the problem for me recently.

4.0 upgrade incompatibility with the CRM 3.0 VPC

We use the Microsoft CRM VPC images extensively for demo’s, and have configured them for a variety of different business scenarios. Recently I needed to upgrade the August 07 CRM 3.0 VPC image and ran into a lot of errors attempting to do so.  When I opened a ticket with Microsoft, they indicated that the VPC databases were modified in such a way that they are not compatible with the 4.0 upgrade, so if you have VPC images that you’ve done a lot of configuration with, according to Microsoft these images cannot be upgraded.  The solution in my case was to export the customizations from the 3.0 VPC environment, import the customizations into a non-VPC 3.0 image, then upgrade this 3.0 image to 4.0.  Of course in this scenario all user data from the VPC will be lost, but in most instances where a VPC is being used, the customizations are of more importance than the user data itself since these are obviously not production systems.