Microsoft Dynamics CRM Team Blog: (Simplified) Pivot Table Integration

[Blog bot]

Источник: http://blogs.msdn.com/crm/archive/20...tegration.aspx
==============

A business user wishing to integrate Excel pivot tables with an existing database implementation, such as Microsoft CRM, will face a number of challenges that may frustrate and intimidate our end users:

• Connection points to the data sources are mysterious and so numerous in choice, that a user often times abandons trying to discover the proper combinations necessary to connect an application to a data source. SQL machine names and network paths are not (and should not be) in the forefront of our users knowledge.
• Discoverability of database table connection points are obfuscated and documentation is not intended for an end user audience. The intent of database design is for data flow optimization not for end user understanding.
• SQL, for all it’s strength and power, is a mystery to our end users and requires a very technical orientation to exploit all of it’s potential. Even those limited users in an organization well versed in SQL will find the mystery of joins in a sophisticated database daunting and unique from one database schema to another.
• A strong business orientation and understanding the significance of underlying data should be the main emphasis required to evaluate and derive good business decisions. Bipolar understanding of business models and database schemas should never be a requirement of our end users.
• Database field names are in English and their meanings hidden behind a short terse word. Our international base and often “single” language set of users will be lost in a database if not insulted by the rather brash assumption that everyone in the world needs to understand English to navigate their data.

(S)PTI bridges the gap between obfuscated databases/servers and Excel pivot tables. (S)PTI can also be used as a model to integrate other application features (such as Word Mail Merge or PowerPoint Tables) with database based applications. The technical connection points and database schemas are automatically created by an (S)PTI compatible application, the only thing our users should be asking is: “What If…”. As an extension, (S)PTI can be used as a dynamic UDM (Unified Dimension Model) generator; dynamic because the user poses business question ahead of generating the views and different in that (S)PTI attempts to reduce the number of available data points to those selected or currently viewed by the user.

Executive Summary

It’s really quite simple, the business user would like to go from a point of visual perception where she has gathered all her data in a grid, applying an understandable business postulate and driving that data forward to an Excel (live) pivot table for further slicing, dicing and coalesced measurements. In other words, from grid to pivot in two clicks or less. Visually, in Microsoft CRM Version 3, it would look something like this; the user would like to go from a filtered data grid (which she has created using CRM’s Advanced Find):



To an excel pivot table, carrying over the same information that she sees in the original grid (by clicking on the Excel icon on the grid toolbar):



An important note now follows, the number of items on “field list” is reduced to (but not limited to) the field of view the user originally had. By design, this allows our users to focus and concentrate on the items she feels important, without being inundated with too many fields to sort through.

What may follow is an interesting pivot table and chart, which speaks for itself as far as rolling up and properly viewing the original data:



The user may now save this spreadsheet to CRM as a report and then load it later with updated values or may use the contents in WebParts to display in a corporate Sharepoint site, with the added assurance that individual security is properly applied to data being presented.

Extended Summary

Let’s delve a little deeper. Let’s gain a greater understanding of the advantages of (S)PTI with a typical business question:

“How effective have my recent marketing campaigns been? Additionally, how well balanced is the response team distributed among my sales force?”

CRM Version 3 has a powerful cross entity advanced find engine which allows our business user the ability to pose these types of questions in a structured query environment.



Here we posed our question using CRM Advanced Find to our “Campaign Reponses”: give me all the response activities for my most recent (6 months) marketing campaigns. (Technical Note: “Parent Campaign” is a cross entity relationship, which Advanced Find resolves and discovers for the user in a friendly way.)

Clicking on “Run Search” displays the following table:



Now that we have the data that we want we’d like to be able to see this in a pivot table and chart since it’s difficult to quickly see the counts and assignments in this view. Additionally, we discover that that we should also take into account the “Response Code” of the campaign, so we’ll make a mental note and include this in the header.

Clicking on the Excel button I get the following dialog:



I need to include the owners of the responses to fulfill the sales force balance question, so I click on “Select Columns” and choose owner as an added field:



Note that this geeky view gives me deeper information about my data (such as Schema Name and Type), but I can gather what information I need from the “Field Name”, which is localized and familiar. After hitting “OK”, Excel launches and is ready for pivot table creation.



After I drag and drop the fields that I am interested in, I get the following table:





The conclusion is clear: campaign CMP-01001 and CMP-01003 had the most responses and the distribution of the campaigns is a skewed towards Keith. Additionally, one discovers that John Paul has 60% of his campaign responses end up either “Not Interested” or “Do not send” and may be having some issues selling the product associated with CMP-01001 once he is given a lead or the campaign is sending out the wrong message; I’ll keep an eye on it. As an action item, I’ll have Paul pick up the pace and start responding to more leads as they come in.

CRM Version 3 allows me to save this “live data” spreadsheet alongside other reports and access them straight from my campaign area in CRM. I’ve named my Excel spreadsheet “Campaign Distribution and Effectiveness” and published it in CRM associating it to the Campaigns area:



In a week I’ll run this spreadsheet and check to see how well things are moving along; at that time all the totals will be updated reflecting the most recent data using the same criteria I posed earlier (which includes a 6 month expiry date I have set on my campaign creation age).

Technical Implementation: Overview

The magic to all this stuff is in creating a SQL command given a particular user understood criteria and injecting the SQL into an Office XML template. The server connection information is also injected into the XML template, further relieving the user from manually creating a complicated and obscure connection point. In CRM Version 3, the SQL generated uses FilteredViews technology which implements Windows integrated security; data requested by an Office application from CRM will allow only licensed users to access data straight from the SQL server. Additionally, the CRM security model is also respected by way of FilteredViews, which further filters the accessible data per row based on the users’ rights.

1: 2: DRIVER=SQL Server;SERVER=server;APP=Microsoft Office 2003; 3: DATABASE=Database Name;Network=DBMSSOCN;Trusted_Connection=Yes 4: 5: T-SQL Select Statement 6:

The “Connection” information and “CommandText” are filled in by CRM and sent to the client for further processing by the Office application (all of the italicized items are filled in by CRM). Let’s take, for example, the following criteria:

Give me all the “Campaign Responses” whose parent “Campaign” was created within the last 6 months.

Using CRM Version 3, Advanced Find will inject the following T-SQL statement and connection information:

1: 2: DRIVER=SQL Server; 3: SERVER=CRMServer-02;UID=edmar; 4: APP=Microsoft Office 2003;WSID=CRMRept-01; 5: DATABASE=Aventure_Works_MSCRM;Network=DBMSLPCN; 6: Trusted_Connection=Yes 7: 8: 9: select campaignresponse.subject as 'Subject', campaignresponse.activityid as ' (activityid)', campaignresponse.regardingobjectidname as 'Parent Campaign', campaignresponse.responsecodename as 'Response Code', campaignresponse.companyname as 'Company Name', campaignresponse.statecodename as 'Status', campaignresponse.owneridname as 'Owner' from FilteredCampaignResponse as campaignresponse inner join FilteredCampaign as aacampaignresponseregardingobjectid on (campaignresponse.regardingobjectid = aacampaignresponseregardingobjectid.campaignid) and (aacampaignresponseregardingobjectid.createdonutc >= GetUTCDate() and aacampaignresponseregardingobjectid.createdonutc < dbo.fn_EndOfNextXDay(GetUTCDate(), 180) ) 10: 11:

Excel or Word will launch and use this information to connect to the server. And in addition, the XML template contains defaults that are not necessarily obvious (such as refresh on connect and do not keep data on save) which helps the user be more secure. Additionally, since CRM only touches this block, static text and formatting may be applied to the original template. (Warning text such as “CONFIDENTIAL” can be applied to the root template and be seen by everyone in the organization.)

(S)PTI Extensions and Potential Implementations

(S)PTI can be expanded to do mail merge with Word or the select statement can include an HTTP address to link the data back to the edit form in CRM (this is the primary reason why each generated SQL has the object id as a default output).

Cheers,

Ed Martinez, Tao Yue, Ramanathan Pallassana



Источник: http://blogs.msdn.com/crm/archive/20...tegration.aspx