Create a Microsoft Dynamics CRM 3.0 dashboard with SharePoint Portal Server
Published: December 6, 2005
You can use either Microsoft Office SharePoint Portal Server (MOSS) 2003 or 2007 or Microsoft Windows SharePoint Services 3.0 to create a dashboard to display essential Microsoft Dynamics CRM data for your users. In this example, you will learn how to use SharePoint Portal Server and Microsoft Office FrontPage to create a dashboard to display a chart of the top open opportunities and their projected revenue.
The first section explains how to create a SharePoint Portal Server page with an Office Web component (a PivotChart), connect it to the Microsoft Dynamics CRM database, and configure the PivotChart. The second section explains how to create a data view on the SharePoint site by using FrontPage. The final section explains how to replace the default Microsoft Dynamics CRM home page with your SharePoint Portal Server dashboard.
Note
This article provides the steps to create a dashboard by using SharePoint Portal Server. The steps to create a dashboard by using Windows SharePoint Services are similar, but the names and labels are different than those in SharePoint Portal Server.
Office Web components are installed on the SharePoint Portal Server page
•
The name of the server that is running Microsoft Dynamics CRM Server and Microsoft SQL Server, and the name of the Microsoft Dynamics CRM database from which the dashboard will get its data
•
Standard reports permissions (Live export to Excel) and Web content permissions in SharePoint Portal Server
Create a SharePoint Portal Server page with a PivotChart
In this section, you will learn how to create a SharePoint Portal Server page and add a dynamic PivotChart of Microsoft Dynamics CRM opportunities and their estimated revenue.
Create a SharePoint page
1.
Open your SharePoint Portal Server site.
2.
On the title bar of the SharePoint Portal Server site Home page, click Site Settings.
3.
On the Site Settings page, under Portal Site Content, click Manage top-level lists and document libraries.
4.
On the Documents and List page, on the top link bar, click Create.
5.
On the Create Page page, under Web Pages, click Web Part Page.
6.
On the New Web Part Page, in the Name box, type "Microsoft CRM Opportunity Dashboard."
7.
In the Choose a Layout Template list, select a layout, for example, Header, Left column, Body.
8.
In the Document Library list, select Document Library.
9.
Click Create. This may take a few moments.
10.
On your new page, in the Add Web Parts pane, under Browse, click Virtual Server Gallery.
11.
Under Web Part List, drag Office PivotChart into the body area on the page.
12.
In the Office PivotChart Web part, click Connect to an external data source.
Tip
For more information, click Help.
13.
On the Select Data Source page, point to the server that is running Microsoft Dynamics CRM Server and SQL Server, select the Microsoft Dynamics CRM database, and then click Open.
Note
If you do not have a data source, you can create one now. If you do have a data source, go to step 15.
Create a new data source
1.
On the Select Data Source page, click New Source.
2.
In the Data Connection Wizard, select Microsoft SQL Server, and then click Next.
3.
In the Server name box, type the name of the server that is running Microsoft Dynamics CRM Server and SQL Server.
4.
Leave the Use Windows Authentication option selected.
5.
Click Next.
6.
In the Select the database that contains the data you want list, select the Microsoft Dynamics CRM database. This name usually ends with "MSCRM."
7.
Clear the Connect to a specific table check box, and then click Next.
8.
You can type a description of the data source in the Description box.
9.
Click Finish.
14.
If you created a new data source, select it, and then click Open. Click OK on any confirmation pages.
15.
In the Select Table dialog box, select FilteredOpportunity, and then click OK.
16.
The PivotChart appears in your web part on your SharePoint Portal Server page, and a floating Chart Field List dialog box is displayed. If this dialog box disappears, click Show Field List.
17.
In the Office PivotChart Web part header, click the down arrow and then click Modify Shared Web part. Click OK on any confirmation pages.
18.
In the Office PivotChart Web pane, in the Title box, type “Open Opportunities,” and then click OK. Click OK on any confirmation pages.
Add the fields to display
1.
On the Open Opportunity Web part title bar, click Show Field List.
2.
On the Chart Field list, drag Owneridname into the Web part Drop Category Fields Here area on the bottom of the chart.
3.
Drag Estimatedvalue into the Drop Data fields here area on the middle of the chart.
4.
On the Open Opportunity Web part title bar, click Save.
Note
For more information about using PivotCharts, see the SharePoint Online Help.
.
Turn off extra UI
1.
On the Open Opportunity Web part header, click the down arrow and then click Modify Shared Web part. Click OK on any confirmation pages.
2.
On the Office PivotChart Web pane, under PivotView, clear the Show toolstrip check box, and then click OK.
3.
On the Open Opportunities Web part, right-click the chart, and click Commands and Options.
4.
On the Commands and Options page, on the General tab, select Chart Workspace.
5.
On the Show/Hide tab, clear Field buttons / drop zones.
6.
Close the dialog box.
Note
Save the Microsoft CRM Opportunities Dashboard page in your browser favorites or write down the URL. You will need the URL later.
In this section, you will open FrontPage to a data view of the Microsoft CRM Opportunity Dashboard page that we just created. The data view displays the details of the opportunities.
1.
Open your SharePoint site in FrontPage.
2.
In the Folder list, expand the document library that you saved the Microsoft CRM Opportunity Dashboard into, and double-click the file name to open the page.
3.
Make sure you are in Design view. If not, at the bottom of the document window, click Design.
4.
On the Microsoft CRM Opportunities Dashboard.aspx page, select the left column.
5.
On the Standard toolbar, on the Insert menu, point to Database, and click Data View.
6.
In the Data Source Catalog pane, expand Database Connections, and click Add to Catalog.
7.
On the Data Source Properties page, click Configure Database connection.
8.
On the Configure Database Connection page, enter the server name, select the Use Windows authentication option, and then click Next.
9.
Select the Microsoft Dynamics CRM database. This name usually ends with "MSCRM."
10.
Under Table, view or stored procedure, select FilteredOpportunity, and then click Finish.
11.
On the Data Source Properties page, on the Source tab, in the Query Area, click Fields
12.
Select all of the fields in the Displayed fields list, and then click Remove.
13.
In the Available fields list, select the following fields, and then click Add.
•
Accountidname
•
Actualvalue
•
Estimatedvalue
•
Owneridname
14.
Click OK to close the Displayed Fields page.
15.
Click OK to close the Data Source Properties page.
16.
In the Data Source Catalog pane, drag the new database connection into the left column Web part area. The data is displayed in the Web part.
17.
Save the page and close FrontPage.
To preview the page, open the page in Internet Explorer.
Replace the Microsoft Dynamics CRM home page with the SharePoint Portal Server dashboard
The site map is an XML file that defines the navigation and structure of your Microsoft Dynamics CRM Web site. These procedures explain how to replace the default site map in Microsoft Dynamics CRM with a customized site map that features your new SharePoint Portal Server dashboard, including the PivotChart.
Caution
When you import an updated site map, the prior customizations to the site map may be overwritten. For more information about editing the sitemap.xml file, see the topic “Navigation Configuration: SiteMap” in the Microsoft Dynamics CRM 3.0 Software Development Kit.
Replace the Microsoft CRM home page with your Microsoft CRM Opportunities Dashboard
1.
In Microsoft Dynamics CRM, on the Navigation Pane, click Settings, and then click Customization.
2.
In the Customizations area, click Export Customizations.
3.
In the Export Customizations list, select Site Map.
4.
On the More Actions menu, select Export Selected Customizations, and then click OK.
5.
In the File Download dialog box, click Save, select a location for the Customizations.xml file, for example your desktop, and then click Save again.
Note
Save a backup of the file, so that you can revert to the original site map, if you have to.
6.
Navigate to the Customizations.xml file location and open the file in a text editor, such as Notepad.
7.
Go to the SharePoint site in Internet Explorer and copy the URL of the Microsoft CRM Opportunities Dashboard page.
8.
To replace the home page in Microsoft Dynamics CRM 3.0 client for Microsoft Office Outlook, in the Customizations.xml file, replace the second occurrence of <SiteMap> with <SiteMap Url=”http://url to dashboard”> Replace “url to dashboard” with the URL of the Microsoft CRM Opportunities Dashboard page. Make sure to use the correct case, because this string is case sensitive.
9.
To display the dashboard as the default page when the Web client is opened, above this line:<SubArea Id="nav_activities" Entity="activitypointer" url="/Workplace/home_activities.aspx" /> Add the line: <SubArea Id="nav_home" ResourceId="Homepage_Home" Icon="/_imgs/area/18_home.gif" Url="url to dashboard" Client="Web"/> Replace “url to dashboard” with the URL of the Microsoft CRM Opportunities Dashboard page. Make sure to use the correct case, because this string is case sensitive.
10.
Save the Customizations.xml file on your desktop.
11.
In Microsoft Dynamics CRM, in the Navigation Pane, click Settings, and then click Customization.
12.
In the Customization area, click Import Customizations.
13.
In the Import Customizations area, browse to the updated site map file, and then click Upload.
To test the new home page, open Microsoft Dynamics CRM 3.0. Under My Workplace, a new area called My Home Page is displayed.
Note
If you have modified the site map and can no longer open the import customization list, you can access the URL in Microsoft Internet Explorer: http://<CRM_Servername>//tools/systemcustomization/ImportCustomizations/importCustomizations.aspx
Note
Tip
Caution