Extracting Data from SharePoint

When I work with people about using SharePoint "as a management weapon" a frequently asked question is about how to get information out of SharePoint, in particular how to print. When using Sharepoint as a "management weapon" this is a natural need since getting the data into a form for presentation or further munging into insightful representations for other mangers and executives.

Doing this is often a lot easier (and less expensive) than people expect and anticipate. Since many perceive SharePoint as an "application", they sometimes expect SharePoint to provide some sort of bespoke functionality for printing and extract. Or they engage the services of SharePoint application developers (at great initial cost plus follow-on support costs) to do bespoke things.

One of the great things about SharePoint is that is is fairly "restrained" about doing bespoke and special things like this. It's inherent value is that it basically does this in conventional, standard, and recognisable ways.

The purpose of this short article is to outline a few approaches. I do not try here to give a step by step explanation--that can be obtained elsewhere if needed. See the reference list below for some books that are already written and can provide extensive instructions. I see no need to replicate all that at great expense and labour. However, some pointers are often useful. These are listed here in order of simplicity (High to Low) and sophistication and power (Low to High).

Print Using the Browser's Capability

SharePoint is a web-based application. You use a web browser, probably and most usefully Microsoft Internet Explorer, to display the information returned from the SharePoint server. Accordingly, the first option is to simply print the page using the browser. This is quick, fast, and easy. Sometimes people are repelled from making such print-outs for managers and executives--but most managers and executives surely will appreciate how inexpensive in time such a technique is.

• Setup the view you wish to display. You can use a view provided by your server, or you can construct your own. Remember that a SharePoint View defines what fields are displayed, in what order, how the rows are grouped, and applies any filtering.
• Once you get the information on the screen as you want, in the browser use Menu: File/Page Setup ... to define the paper size and orientation. Often it is most useful to use landscape orientation.
• Current versions of IE have an option to "Enable Shrink-to-Fit" to get all the information displayed on the screen onto the paper.
• Sometimes you get pleasing results if you print to A3 paper (real or virtual as a PDF) adn then shrink the page down to A4 for presentation
• Sometimes you can simply do a partial screen shot of the portion of the screen you wish to show and paste that into your PowerPoint. Hint: avoid the temptation to show the entire screen shote page in PowerPoint. Take only portions of the page. To do this, use the standard Windows techniques of Alt-PrtScn or specialty software like SnagIt! or others.

Copy Data into the Document, Excel, or Other

Sometimes SharePoint's simple standard views (or views you make yourself) are not good enough. After all, one of the grand features of SharePoint is that it only does so much and does it well. So, don't fight SharePoint--use it.

• Display the View that shows the information you want.
• Press the Actions button to display the View in a Datasheet (this won't work with antique version of IE and Excel, nor will work with browsers other than IE).
• If you don't have all the fields you want, then you have to get them on the SharePoint View. If you have too many fields, simply "hide" them. To hide columns (and do other things with columns), as in Excel, right mouse click on the column header and tell SharePoint to "hide" the column.
• In the header rows, take note of the little black downward-pointing arrows. Click on that with your mouse pointer and notice that you can sort and filter the rows. Again, this is just like in Excel.
• Once you have the information you want displayed, then select all (or some) of the rows and columns. The easiest way to select all is to click the far left column in the header row (again, just like in Excel). Then copy this data into the Windows Clipboard (Menu: Edit/Copy, or press CTRL-C).
• Move to where you want to put the data--which could be Excel, Word, PowerPoint, or even email. Click the cursor into the location where the data should go and Paste (via the application's menu or CTRL-V). Depending on how the target application can respond to pasting structured information, the information will go in.
• From within the target application, do what you want with the information. With something like Excel or Access, you can do further analysis (summing, statistics, etc.), or in PowerPoint just format to make it as "pretty" as you want.

Get Scalable and Automated Data Reporting

SharePoint does not do "report writing". Some people expect it. I think that one of the grand features of SharePoint is that it does not have built-in reporting writing because most people have (and have had for many years) that capability on their desktop computers in the form of other Microsoft tools: Word, Excel, and Access. There are scores of other commonly-available tools and applications available to do this--some free or low-cost and others not so low-cost.

To do this requires, of course, knowledge and perhaps experience of how these tools work. Surprisingly there are many people who really do not know how to use Word, Excel, and Access in ways other than they have become accustomed to over the years. However, these tools are provided at great expense by the same management and executives who are seeking information/reporting so I see few reasons why not to use these tools.

• Under the "Actions" button in Spreadsheet, execute the "Export to Spreadsheet" button. Follow the instructions presented by SharePoint and Excel. In Excel, it is probably best to "unlink" the spreadsheet to SharePoint--else changes you make in Excel will ripple back to SharePoint. Do this vial Menu: Data/List/Unlink List.
• Or, under the "Actions" button, executive the "Open with Access" button. Follow the instructions presented by SharePoint and Excel.

More guidance about how Excel and Access work with SharePoint work together is found in the documentation for these products (Help, books, etc.). This guidance is teachable and learnable. Seek out training and/or coaching if you wish to pursue this important topic.

Really Cool and Powerful SharePoint Functionality

Once you do that described above, take a look at the following:

• Display the list in a Datasheet (under the Actions button)
• When in Datasheet view, see the Actions button again to see a new option to "open the task pane". When you do this it will appear at the right.
• From the SharePoint Task Pane, you can find powerful (and easy to use) features to use Excel (querying, printing, charting, and pivot-table reports) and reporting via Access.

Excel or Access

I close this article to briefly comment on the always-debated question of is it better to use Excel or Access for this sort of thing.

SharePoint, after all, is really a database. Access is a tool for handling databases. Excel is a tool for number-crunching. Yes there are many overlaps and cross functionality (hence causing the debates).

My bottom line is that Access should be the target tool for report writing, analysis, querying, etc. I accept that many people have not yet had the opportunity to learn anything about Access. It is easier to deal with data in Access than with Excel because with Access, if built with that goal in mind, has all the fit-for-purpose tools and capability. Excel is a very complex tool--despite the fact that it is in ubiquitous use.

Using Access properly (which can be taught and learned), you can create automated and repeatable reporting and analysis of data held in SharePoint. The outputs can be as sophisticated algorithmically and artistically as you have ability and budget to create. When you automate this capability it will be (if done properly) supportable, maintainable, and scalable. These last features are where companies make their money by keeping capability high and cost low.

Managers and Executives like that.

For More Reading and Learning

Here are a few:

The SharePoint Shepherd's Guide for End Users. Shows you want you can do with SharePoint and exactly how to do it in step-by-step approach. Great book for those who need to be told exactly how to do it.

Seamless Teamwork. This book is for leaders, managers, and executives who have been told by their IT department to "Here is SharePoint. Use it. It's Great." However, the IT department (rarely) explains how to use SharePoint as a business tool to help the business. This books provides that guidance. The book outlines a way of making the best of SharePoint for individuals, teams, and the organisation.

SharePoint for Project Management. Most companies do not understand SharePoint and use it simply to share documents and spreadsheets. This hands-on book demonstrates how SharePoint can also help you organise and manage complex projects. It's the book I wish I had written. It completely matches my experience and understanding of the possibilities.