Using SharePoint Content Types as Report Templates

Last week a customer asked us if he could help users to start a PowerPivot file from an existing template.  For this specific environment some analysis was to be done on content stored in SharePoint Lists.  Using SharePoint 2010 and PowerPivot enables you to subscribe to these lists as Data Feeds, which is a nice way of getting this data automatically out of SharePoint.  Several lists had to be combined and obviously they didn’t want users to manually subscribe to these lists each time a new analysis had to be done.

To give the users a starting point where all necessary subscriptions were already defined, we used typical SharePoint functionality to attach “content types” to a document library.  Content types can be based on existing documents which makes it a nice way to define templates. 

Content types are described in the following way in the SharePoint documentation:
“A content type is a reusable collection of metadata (columns), workflow, behavior, and other settings for a category of items or documents in a Microsoft SharePoint 2010 list or document library. Content types enable you to manage the settings for a category of information in a centralized, reusable way.

For example, imagine a business situation in which you have three different types of documents: expense reports, purchase orders, and invoices. All three types of documents have some characteristics in common; for one thing, they are all financial documents and contain data with values in currency. Yet each type of document has its own data requirements, its own document template, and its own workflow. One solution to this business problem is to create four content types. The first content type, Financial Document, could encapsulate data requirements that are common to all financial documents in the organization. The remaining three, Expense Report, Purchase Order, and Invoice, could inherit common elements from Financial Document. In addition, they could define characteristics that are unique to each type, such as a particular set of metadata, a document template to be used in creating a new item, and a specific workflow for processing an item.”

So how exactly do you allow users to create a PowerPivot file with preconfigured content from SharePoint?
As a side note, this can be practically done for any file type and is not tied to the requirement to subscribe to SharePoint Lists.

There are a couple of easy steps to follow in order to get these content types linked to your document library (eg. PowerPivot Gallery).  The first step is obviously to create your required template.  If you want to learn more about subscribing to SharePoint lists please refer to this post by PowerPivotGeek.  I assume you know how Excel / PowerPivot works so I’ll jump to the SharePoint part immediately.

The first step in SharePoint is to define this template as a new content type (Site Settings – Site Content Types – Create)

After you created the content type (by selecting OK) you will be redirected to the settings of the newly created type.  To upload an existing Excel file go to Advanced Settings and browse for the file in the “Upload a new document template” option.

Now it is time to prepare the document library for additional content types.  First you will have to allow management of content types by going to the settings of the document library on which you want to use this content type (Library Tab – Library Settings – Advanced Settings):

A new section is now added to the settings screen with content type options:

Select “Add from existing site content types” to attach your new content type to the document list.  Also notice that you can configure this new content type to be the default and the original can be removed if necessary.  When you have completed this step the content type list should look like this:

By following these easy steps users now have the ability to create a new PowerPivot file based on an existing template.  All they have to do is select Documents – New Document and the templates are available from there.

More information on Content Types can be found at https://msdn.microsoft.com/en-us/library/ms472236.aspx.

A special thanks to Jan Tielens (blog | twitter) for the review.