(Simplified) Pivot Table Integration

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 clip_image014 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: <QuerySource>
   2:     <Connection>    DRIVER=SQL Server;SERVER=server;APP=Microsoft Office 2003;
   3:                     DATABASE=Database Name;Network=DBMSSOCN;Trusted_Connection=Yes
   4:     </Connection>
   5:     <CommandText> T-SQL Select Statement </CommandText>
   6: </QuerySource>

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: <QuerySource>
   2:     <Connection>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:     </Connection>
   8:     <CommandText> 
   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:     </CommandText>
  11: </QuerySource>

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).


Ed Martinez, Tao Yue, Ramanathan Pallassana

Comments (10)

  1. 196 Microsoft Team blogs searched, 97 blogs have new articles in the past 7 days. 218 new articles found…

  2. Thank you for this introduction to using Pivot tables with CRM; this is a very powerful reporting option which end users appreciate a lot, since Excel is a product most of the CRM users are very familiar with, and often preferred over SRS reports because of their flexibility and familiar UI.

    There is one major drawback though: it is not possible to pull data into Excel using MS Query directly from CRM in case of a hosted (IFD) CRM deployment because of obvious security issues.

    What are your thoughts on this? In the next years most deployments will move from on-premise to hosted and a lot of focus will be on cloud based computing. Rumours tell that MS is working on a SaaS version of SQL server, so maybe one of your colleagues is already working on a solution to get data from the cloud into Excel?



  3. Josephine Fleschute says:

    This was excellent.  We need more blogs like this.

  4. Ed (Author) says:

    There needs to be a standard "cloud" query mechanism like FetchXML.  We opted to create FilteredViews because of the strength of T-SQL, and were able to leverage Export to XL as a result as well as SQL Reporting Services.

    We have an export to excel in IFD as well as CRM Online, but it is limited to the view you have at hand (which is where most people usually stop). Additionally, you can refernce related entities in views as well.  Your question was specifically for MS Query though and a query builder over the cloud does not yet exist (that I know of).

  5. Hi Ed,

    Thank you for your answer. I hereby assume you will have a "cloud" query engine ready with the release of CRM 5.0? 😉 I have no preference over any query technology , just as long it gives me the data in Excel.

    I see your argument with "that is where most people stop", but that is exactly the point where we (consultants) come in because the query gets too complex for ordinary users.

    You wouldn’t believe it, but there are still so many users out there who need (calculated) data from CRM and still do a lot of manual work where everything could be automated.

    Again, thanks a lot for your answer; also a big thank you for a brilliant product and a very informative and lively blog!



  6. DAgrahl says:

    The arrogance of the observation in this article, and I quote…"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." … is totally untenable and elitist in nature in that the author would propose to know what i should and should not be permitted to read/understand/use. Who do yout think you are?!

  7. Marc says:

    Hi again Ed,

    Looks like the folks at SQL server data mining team are solving our challenge at this moment:


    Check out the "table analysis tool for the cloud".



  8. Sqiar says:

    SQIAR (http://www.sqiar.com) is a leading global consultancy which provides innovative business intelligence services to small and medium size (SMEs) businesses. Our agile approach provides organizations with breakthrough insights and powerful data visualizations to rapidly analyse multiple aspects of their business in perspectives that matter most.

Skip to main content