Creating a Dashboard using MOSS, Excel Web Services, Excel 2007 and Microsoft CRM

Posted Tuesday, June 24th, 2008 by David Pritchett

Creating a dashboard using SharePoint, Excel Pivot Charts and Microsoft CRM data is nothing new. Perform a Google search and you will receive thousands of results. However, finding information on how to create a live dashboard using the latest components of MOSS 2007, Excel Web Services, Excel 2007 and Microsoft CRM data is a bit more difficult.

1) Using the old method available in SharePoint 2003 of inserting the Office Pivot Chart web part and connecting to your CRM data source to retrieve the data does not work in MOSS 2007. The Office Pivot Chart web part is not available in this version using MOSS 2007 and Excel 2007.

2) Setting up Excel Web Services authentication methods (Windows Authentication, Single Sign On, or none) to allow the automatic refresh of the data in the spreadsheets on your dashboard page can be a bit confusing. I will discuss this in more detail later in this post.

3) Certain items used in Excel spreadsheets are not supported in Excel Web Services.

4) There is not one document that explains it all from start to finish, with all of the pitfalls highlighted. That is what I am hoping to accomplish with this post. My initial hurdles in getting this to work were all centered on getting the data to refresh with live data every time the page is opened. I kept receiving a data retrieval error saying the connection was unavailable. This was mainly the combination of the following:

a. Data Authentication

b. Creating the Excel Report based on a Query table, instead of a Pivot Table and Chart

c. Publishing the Excel Chart as a Report instead of a dashboard

This post is written based on the assumption that you have knowledge of MOSS 2007, creating a dashboard page in SharePoint and that the environment has been configured to use Excel Web Services. If not, below are some links that go into detail about how to do this and some other helpful links:

1) Plan external data connections for Excel Services – This article contain full instructions on how to configure MOSS to use Excel Web Services. Note: All of the steps in this article should be completed before moving on to building your reports and your dashboard.

http://technet.microsoft.com/en-us/library/cc262899.aspx#section7

2) Using Analysis Services data in Excel Services – This article goes into depth on configuring your servers to use Kerberos Authentication. This is required if you are using Windows authentication as your method of authenticating your spreadsheets to the data source.

http://www.tonstegeman.com/Blog/Lists/Posts/Post.aspx?List=70640fe5%2D28d9%2D464f%2Db1c9%2D91e07c8f7e47&ID=43

3) Excel Services part 12: Unsupported features

http://blogs.msdn.com/excel/archive/2005/12/01/499206.aspx

Before moving on with the rest of this post, a word on authentication; when creating your Excel spreadsheet and connecting to your CRM database to retrieve data, you have to select a method of authentication. There are three options:

1) Windows Authentication

2) Single Sign On

3) None

All three have their caveats, but the third option of “None” is the easiest to configure and is the one I have chosen to use in this example. The other two options require a much deeper understanding of Kerberos authentication and this will not be addressed in this post. Read the section entitled “Authentication to external data” in the “Plan external data connections for Excel Services” article listed above for a complete explanation on the configuration of each.

As I noted above, you need to configure your MOSS environment prior to actually creating your spreadsheet and building your page. A synopsis of the steps is listed below and is explained in detail in the “Plan external data connections for Excel Services” article:

1) Enable MOSS to use Excel Web Services

2) Add a trusted file location

3) Enable external data access for a trusted file location

4) Configure the unattended account settings – This is required if your authentication method is “None.” In this step, you will want the unattended account to be a domain user that is also a user in CRM. In my case, I just used the administrator login used when we installed CRM.

5) Create a data connection library

6) Add a data connection library to trusted list

7) Set a registry key to surface a data connection library in the client – This step is not required, but if you would like the data connection library to show up as a location to select data connections from when building your spreadsheet and creating a connection, you will have to do this. Otherwise, you can still access the location by typing in the path to the URL when browsing for your data connections.

Once you have completed these steps, you are ready to create your worksheet and integrate it into your website.

1) Create a new connection within a new Excel spreadsheet.

· Open Microsoft Excel 2007.

· Go to the Data tab.

· Select “From Other Sources.”

· Select “From Data Connection Wizard.”

· Select Microsoft SQL Server. Click Next.

· Enter your server name. It is ok to use Windows Authentication here. Click Next.

· Select your CRM database and the view in which to use. In this example, I am going to create a simple graph using the Opportunity View. Click Next.

· Select a file name for your data source that will be saved. We will need to modify the location so that the data connection is stored to the Data Connection Library on the SharePoint site. To do this, click Browse. If you do not have a link to your SharePoint Data Connection Library, you can find this by browsing to your SharePoint site using Internet Explorer and selecting the Data Connection Library link. Copy the location from the Address Bar in IE (All the way through the DataConnections only. Do not include the .aspx ending. Ex. – http://intranet/Department%20Sites/Sales%20%20Marketing/sales_dash/Data%20Connections/) and paste this before the file name you have selected.

· Select the “Always attempt to use this file to refresh data check box.”

clip_image002[4]

· Click on the Authentication Settings button.

· Select your authentication method. In my case, I select “None.” This will trigger the Excel Web Services to use the Unattended Account we set up earlier.

· Select OK on this dialog and then Finish on the previous dialog.

· When you are prompted with the Web File Properties dialog, select SharePointLibrary as your Connection Type and ReadWrite as your UDC Purpose.

· On the Import Data dialog, select PivotChart and PivotTable Report. Note: Selecting Table will not work. It is called a Query Table and is not an option that is supported in Excel Web Services.

clip_image004[4]

· Build your Pivot Chart. Here I am performing a simple summation of the Estimated Value of my opportunities in a particular category.

clip_image006[4]

· Click on any area within the Pivot Table, click Data from the top menu bar, then Connection Properties.

clip_image008[4]

· Under the usage tab, select all three of the Data Refresh options.

· Under the definition tab, select the “Always use the connection file” checkbox.

· Since we have changed the connection information, we will have to re-save it to the Data Connection Library on the SharePoint site. To do this, click on the Export Connection File button. If your location did not default to the Data Connection Library, browse to it as described in step (h). Click Save to update the Data Connection file.

· Click OK to close the Connection Properties dialog.

· We are now ready to publish the spreadsheet to the Reports Library on the SharePoint site. Click on the Office Button in the top left corner, select Publish, then select Excel Services.

clip_image010[4]

· Before saving the file, make sure you are publishing it to the Reports Library on the SharePoint. As in step (h), if you do not have a link to the Reports Library, you can find it by navigating to the Reports Library on your SharePoint site using Internet Explorer. Copy the address and paste it before the file name. (Ex. http://intranet/Department%20Sites/Sales%20%20Marketing/sales_dash/ReportsLibrary/)

· The next dialog will prompt you with choices of the items you would like to publish. In my case, I only want to display the chart, so from the Show tab, I select Items in the Workbook and Chart 1. You can choose to show any item in the spreadsheet that is support by Excel Web Services. Select OK.

clip_image012[4]

· The next dialog is important as well. Be sure to select Dashboard Page. If you select Report, whatever you intend to publish is only published as a snapshot and the data will not refresh when revisiting the web page containing your data and charts.

clip_image014[4]

· Your published report will be rendered in Internet Explorer. The next step will be to add the report from your Reports Library to your SharePoint Dashboard page.

clip_image016[4]

Assuming that your base dashboard page has already been built in SharePoint, I am jumping ahead a few steps to actually adding the new Excel Chart to the page.

· From your dashboard page, select Add a Web Part.

· From the dialog box, select the Excel Web Access part.

clip_image018[4]

· After the part is added click on the “Click here to open the tool pane” link.

· In the “Workbook Display” section of the Properties, select the ellipsis to browse your SharePoint Report Library. Select the Excel file that you uploaded earlier.

· Go through all of the Properties areas for the web part, adjusting what is displayed and what is not. Once you are done with the Properties, select OK to add your web part to the dashboard page.

clip_image020[4]

There you have it! To build more reports and data connections, simply follow the steps listed above and then add them to your dashboard page. In this example I used the CRM database as my data source, but in reality this will work for any external data source.

Posted in Uncategorized | Leave a comment

SiteMap Privilege Tag

Dynamic Methods Microsoft CRM Blog


At times there are users who need to read data from entities but those same users should never really see the full list of items of that entity in a place where they could take action against any of the items.
For example, an entity called “Locations” exists in CRM. Users will need to have the ability to view these Locations in order to enter a Location on a related object form, perhaps an Account. As an administrator you would like to have the list available to you so that you can add to or modify the list for the users. It could be placed in the Settings area and sometimes that is enough to keep people away from the list. But just to be safe you want to guarantee that users cannot do anything to the list.
This is where SiteMap Privilege tags come in. Within each SubArea tag a Privilege tag can be added. When a Privilege tag is applied, CRM will check on the main page load what privileges the user has to see if that user should be able to see the item. If the user does not have rights, then the item is not shown, if the user does have rights then the item is shown.
So, following our example from above the following could be inside the SiteMap:

By setting the privilege to “Write” only those users that have the write privilege will be able to view the entity from the main CRM page. Multiple privileges may be used as well. Here are the possible values from the SDK:
All
AllowQuickCampaign
Append
AppendTo
Assign
Create
Delete
Read
Share
Write
Here’s an example with multiple privileges:

And finally, get creative. Just because the area is for an entity doesn’t mean that the privilege has to be for the same entity. Perhaps the Location entity should only show up to users who have rights to write to the Knowledge Base. The following would be completely legit as well:

Now your main CRM page can be much more dynamic depending on who you are.
David Fronk
Dynamic Methods Inc.

Posted in Uncategorized | Leave a comment

CRM Usage Reporting Unleashed

Microsoft Dynamics CRM Team Blog

frequent request we come across is from companies who want to know which users are using CRM and when. The CRM platform provides the facility to gather detailed usage information by writing plug-ins, but a simpler and more general mechanism is to use the Internet Information Services (IIS) logging mechanism.

Click here to read more

Posted in Reports | Leave a comment

SQL Server: The instance name must be the same as computer name

Saturday, 11 October 2008
Posted by David Jennaway at 10:11

This is something I’ve posted about on newsgroups, but one of my colleagues encountered it recently, and I think it deserves a blog entry.
The CRM Environment Diagnostics Wizard may throw the error ‘The instance name must be the same as computer name’. The most common cause of this is if the SQL Server has been renamed after SQL Server was installed. The reason is that, at installation time, SQL Server stores the computer name in a system table, sysservers. This information is not updated when the computer is renamed, and the error from the CRM Environment Diagnostics Wizard indicates that the entry in sysservers does not match the current computer name.
You can diagnose and resolve this by using some SQL system stored procedures. One of them lists the data in sysservers, the other 2 allow you to modify the data to reflect the current machine name.
To check if this is the issue, use SQL Management Studio (or Query Analyzer for SQL 2000) to execute the following query:
sp_helpserver
This will return output like the following:
Name,network_name,status,id,collation_name,connect_timeout,query_timeout
ORGNAME,ORIGNAME,rpc,rpc out,use remote collation,0,null,0,0
If the value in the name column does not match the current computer name, then you have to use the following SQL stored procedures to fix the problem. Note that sp_helpserver normally returns one record, but can return more records if you have configured linked servers. If this is the case, it is the row with id=0 that matters.
To change the information you have to first remove the incorrect record, then add the correct one, with the following queries:
sp_dropserver ‘ORIGNAME’ — where ORIGNAME is the name returned by sp_helpserver
sp_addserver ‘CURRENTNAME’, ‘LOCAL’ – where CURRENTNAME is the current computer name
If you use named instances, refer to them in the form SERVERNAME\INSTANCENAME. It may then be necessary to restart SQL Server after these changes, but I’m not sure of this. It can’t harm though if you can.
There is a KB article about this here. This descibes a similar solution, but be warned of a couple of minor issues with the solution – it fails to specify that quotes are required around the parameters to sp_dropserver and sp_addserver, and I have a feeling (though can’t provide concrete evidence) that running sp_helpserver is more reliable than select @@servername.

Posted in SQL 2005, SQL 2008 | Leave a comment

Renaming Active Directory and Redeploy CRM

20:26 3/31/2009, noreply@blogger.com (Darren Liu), Darren’s CRM Blog

I came across an issue today that the client would like to rename their AD because of some business decisions. They are wondering how to move CRM under the new domain.  Well to move CRM from old domain to the new domain, here are the steps that you can follow:

  1. Backup the [Organization]_MSCRM database.
  2. Backup the custom reports if you have any.
  3. Uninstall CRM 4.0 from the CRM server.
  4. Remove the MSCRM_Config database.
  5. Reinstall CRM 4.0 and choose to setup a new Organization option during the install.
  6. Restore the existing [Organization]_MSCRM database.
  7. Logon to the CRM server and launch CRM Deployment Manager.
  8. Import existing organization and then follow the wizard to remap the users in the new AD.
  9. Verify that you can logon back to the CRM environment.

That’s it! If you run into any this issue in the future, now you can follow the steps above to redeploy CRM.

Posted in Uncategorized | Leave a comment

Creating a Birthday Contact List

Microsoft Dynamics CRM Team Blog

Today we welcome our guest blogger CRM MVP Darren Liu from the Crowe Horwath company.

Have you ever been asked by someone to get a list of contacts having birthdays during a certain time period from CRM? If so what have you done to perform this task? Within the application, birthdays are tracked on Contact records as a single date (including year). This causes problems when searching for birthdays in a certain time period as the birth date is evaluated including the year. To illustrate, consider the following example:

· John Dole, 10/1/1980

· Adam Smith, 9/1/1970

· Mark Francis, 10/10/1960

Within CRM, searching for date is done by range. There is no easy way to identify from the above contacts all those having birthday in October as any range you choose will include the year. Wildcard functions on date fields are not a workable solution.

There are several solutions to this problem including JavaScript to parse birthday on the onChange event, a custom report or a plug-in. The desired functionality is to be able to search by birth month, birth day, and/or birth year, allowing the user to quickly identify all birthdays in a certain time period.

In this blog, I will show you how to use a pre plug-in to parse the birthday field into day, month and year. This way, the users will able to perform searches using Advanced Find. I have chosen the plug-in approach because it will help me parse the birthday field not only when the users update the birthday on the contact form but also when updating the birthday through the CRM web service for data imports and data integration.

Implement the pre plug-in

1. Create New Attributes

Create three new attribute on the Contact entity form in CRM. After creating the new attributes, publish the Contact customization.

Display Name

Schema Name

Type

Searchable

Values

Birth Month

new_birthmonth

Picklist

Yes

Jan = 1, Feb = 2, Mar = 3, Apr = 4, May = 5, Jun = 6, Jul = 7, Aug = 8, Sept = 9, Oct = 10, Nov = 11, Dec = 12

Birth Day

new_birthday

Int

Yes

Min Value = 1

Max Value = 31

Birth Year

new_birthyear

Int

Yes

Min Value = 1900

Max Value = 9999

clip_image002

2. Create pre plug-in using Visual Studio

Create a plug-in project name Crm.Plugin, copy and paste the following code to your Plug-in project.

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Crm.Sdk;
using Microsoft.Crm.SdkTypeProxy;
 
namespace Crm.Plugin
{
  public class MonthDayYearContactPlugin : IPlugin
  {
    public void Execute(IPluginExecutionContext context)
    {
    DynamicEntity entity = null;
 
    if (context.InputParameters.Properties.Contains(ParameterName.Target) &&
        context.InputParameters.Properties[ParameterName.Target] is DynamicEntity)
    {
        entity = (DynamicEntity)context.InputParameters[ParameterName.Target];
        if (entity.Name != EntityName.contact.ToString()) { return; }
    }
    else
    {
        return;
    }
 
    try
    {
        if (entity.Properties.Contains("birthdate"))
        {
            CrmDateTime _birthdate = (CrmDateTime)entity["birthdate"];
            if (_birthdate.IsNull)
            {
                entity["new_birthday"] = CrmNumber.Null;
                entity["new_birthmonth"] = Picklist.Null;
                entity["new_birthyear"] = CrmNumber.Null;
            }
            else
            {
                DateTime birthdayValue = _birthdate.UserTime; 
                entity["new_birthday"] = new CrmNumber(birthdayValue.Day);
                entity["new_birthmonth"] = new Picklist(birthdayValue.Month);
                entity["new_birthyear"] = new CrmNumber(birthdayValue.Year);
            }
        }
    }
    catch (Exception ex)
    {
        throw new InvalidPluginExecutionException("An error occurred in the Month, Day, Year Plug-in for Contact.", ex);
    }
    }
  }
}

3. Register the plug-in The last step is to register the plug-in. To register the plug-in, you may use the Plug-in Registration tool from the MSDN Code Gallery. After the assembly is uploaded, you need to associate the following steps to the plug-in:

Message: Create

Primary Entity: contact

Filtering Attributes: birthdate

Eventing Pipeline Stage of Execution: Pre Stage

Execution Mode: Synchronous

Triggering Pipeline: Parent Pipeline

Message: Update

Primary Entity: contact

Filtering Attribute: birthdate

Eventing Pipeline Stage of Execution: Pre Stage

Execution Mode: Synchronous

Triggering Pipeline: Parent Pipeline

Message: Create

Primary Entity: contact

Filtering Attributes: birthdate

Eventing Pipeline Stage of Execution: Pre Stage

Execution Mode: Synchronous

Triggering Pipeline: Child Pipeline

Message: Update

Primary Entity: contact

Filtering Attribute: birthdate

Eventing Pipeline Stage of Execution: Pre Stage

Execution Mode: Synchronous

Triggering Pipeline: Child Pipeline

Summary

That’s all there is to it! The users will now be able to use Advanced Find to quickly identify their contacts birthday in a certain time period from now on. For the existing contacts previously stored in CRM, you will need to write a one-time SQL script to update the birthday fields in the MSCRM database in order for CRM to return the correct data back to the users. Hopefully this will help you on your next CRM project.

clip_image004

clip_image006

Cheers,

Darren Liu

Posted in Uncategorized | Leave a comment

CRM 4.0 Build Versions

Build Number Released Details
04.00.7333.2138 10/22/2009 Update Rollup 7
4.0.7333.1750 8/26/2009 Rollup Update 6
4.0.7333.1676 7/8/2009 Mobile Express +Rollup 5
4.0.7333.1644 7/2/2009 Rollup Update 5
4.0.7333.1551 5/7/2009 Rollup Update 4
4.0.7333.1408 3/12/2009 Update Rollup 3
4.0.7333.1316 2/8/2009 Rollup Update #2 (Re-Release)
4.0.7333.1312 1/15/2009 Rollup Update #2 (Original Release)
4.0.7333.1113 11/24/2008 Rollup Update #1
4.0.7333.3 12/19/2007 RTM
4.0.7333.2 Dec-07 RC2 (Internal)
4.0.7309.0 Nov-07 RC0 (Limited Release)
4.0.7219.10 Aug-07 Beta – CTP3
4.0.7044.22 May-07 Beta – CTP2
4.0.6319.16 October 2006 (?) Beta – CTP1
Posted in Uncategorized | Leave a comment

Improving Microsoft Dynamics CRM Performance and Securing Data with Microsoft SQL Server 2008

Microsoft SQL Server® 2008 contains a variety of features that, when implemented properly, can improve the performance of a Microsoft Dynamics® CRM 4.0 implementation and secure the data within that deployment. These Microsoft SQL Server 2008 features include: – Compression – Sparse Columns – Transparent Data Encryption – Backup Compression The MS CRM E2 team, working in conjunction with the Microsoft SQL Server team, recently completed a project that was designed to: 1. Evaluate the new scenarios that these Microsoft SQL Server 2008 features expose 2. Measure the performance impact of implementing these features, both singly and in selected combinations This paper provides an overview of these Microsoft SQL Server 2008 features, together with benchmark results and recommendations for implementation.

 

Click here

Posted in performance | Leave a comment

Create Activities using the Dynamic Entity Way

Friday, December 19, 2008 Darren’s CRM Blog


I haven’t touch CRM SDK for a while since I am working on another project not directly related to CRM. One of my old client required us to populate CRM with some more training data so they can use it for their training. So I have to modify the training processor that my teammates wrote to add activities to CRM. It appears to me that everything in the training processor have used Dynamic Entity for create, update, and delete.

To create an activities using dynamic entity is pretty straight forward, however I got stuck when I have to deal with the sender and recipient fields on a phone call, email, appointment and fax activity. Sender and Recipient field are ActivityParty type, I have done some research online and I was not able to find a way to associate the two fields to an activity using the dynamic approach. After going through the SDK and I was able to figure it out. I would like to share it since you might have to do the same in your next project.

Method 1: Not using Dynamic Entity

activityparty actParty = new activityparty();
actParty.partyid = new Lookup();
actParty.partyid.Value = new Guid("ENTER CONTACT GUID");
actParty.partyid.type = "contact";

activityparty actParty2 = new activityparty();
actParty2.partyid = new Lookup();
actParty2.partyid.Value = new Guid("ENTER USER GUID");
actParty2.partyid.type = "systemuser";

phonecall call = new phonecall();
call.from = new activityparty[] { actParty };
call.to = new activityparty[] { actParty2 };
call.description = "Test";

crmService.Create(call);

Method 2: Using Dynamic Entity

Property subject = new StringProperty();
((StringProperty)subject).Name = "subject";
((StringProperty)subject).Value = "Test";

Property description = new StringProperty();
((StringProperty)description).Name = "description";
((StringProperty)description).Value = "Test";

// Create From:
Property party1 = new LookupProperty();
((LookupProperty)party1).Name = "partyid";
((LookupProperty)party1).Value = new Lookup();
((LookupProperty)party1).Value.type = "contact";
((LookupProperty)party1).Value.Value = new Guid("ENTER CONTACT GUID");

DynamicEntity actParty1 = new DynamicEntity();
actParty1.Name = "activityparty";
actParty1.Properties = new Property[] { party1 };

DynamicEntityArrayProperty from = new DynamicEntityArrayProperty();
((DynamicEntityArrayProperty)from).Name = "from";
((DynamicEntityArrayProperty)from).Value = new DynamicEntity[] { actParty1 };

// Create To:
Property party2 = new LookupProperty();
((LookupProperty)party2).Name = "partyid";
((LookupProperty)party2).Value = new Lookup();
((LookupProperty)party2).Value.type = "systemuser";
((LookupProperty)party2).Value.Value = new Guid("ENTER USER GUID");

DynamicEntity actParty2 = new DynamicEntity();
actParty2.Name = "activityparty";
actParty2.Properties = new Property[] { party2 };

DynamicEntityArrayProperty to = new DynamicEntityArrayProperty();
((DynamicEntityArrayProperty)to).Name = "to";
((DynamicEntityArrayProperty)to).Value = new DynamicEntity[] { actParty2 };

 

// Create Phone Call Activity:
DynamicEntity activity = new DynamicEntity();
activity.Name = EntityName.phonecall.ToString();
activity.Properties = new Property[]{subject, description, from, to};

TargetCreateDynamic target = new TargetCreateDynamic();
target.Entity = activity;

CreateRequest request = new CreateRequest();
request.Target = target;

CreateResponse response = (CreateResponse)crmService.Execute(request);

That’s it! Hope this will help you in your next project! 🙂

Posted in Uncategorized | Leave a comment

CRM 4.0 List Web Part Available!

CRM 4.0 List Web Part is available to download today. The list web part supports Multi-tenancy, IFD, 64bit and many more new enhancements comparing to the previous release.

System Requirement:

  • Windows Server 2003 or Windows Server 2008
  • Microsoft Dynamics® CRM 4.0
  • Microsoft Windows® SharePoint® Services 3.0 SP1
  • Microsoft® Office SharePoint® Server (MOSS) 2007 SP1
  • Microsoft® Internet Explorer 6 with SP1 or later versions

To download the list web part, click on the link below.

http://www.microsoft.com/downloads/details.aspx?FamilyID=3b6eb884-ec15-4288-a2a3-d0b47e057458&DisplayLang=en

Posted in Uncategorized | Leave a comment