Category Archives: Office

Completing the Microsoft Reporting Roadmap

In the recent announcement outlining the SharePoint integration strategy on the SQL Server Reporting Services Team’s blog, there was a statement that was almost hidden that I think deserves more attention. The statement was:

“….in time, we aim to support web-based viewing of Excel workbooks in Native mode…”

This may not sound like a big deal – after all, we’ve been able to serve up Excel workbooks in a browser since Excel Services was initially introduced with SharePoint 2007. However, as per Microsoft’s Reporting Roadmap from October 2015, Reporting Services is their on-premises solution for BI report delivery. If an Excel workbook is to be considered a report, the SSRS absolutely should be able to do it. The roadmap defined four types of reports:

  • Paginated
  • Interactive
  • Analytical
  • Mobile

I tend to see there being two types of reports, Structured and Analytical. In the list above, Structured corresponds with Paginated, and the other 3 types are different subtypes of Analytical. They four categories do, however line up well with the different reporting technologies available.

Report Type File Type
Paginated RDL (Classic SSRS)
Interactive PBIX (Power BI)
Analytical XLSX (Excel)
Mobile RSMOBILE (SSRS Mobile aka Datazen)

The roadmap was primarily concerned with the future of SSRS, but SSRS is Microsoft’s stated report delivery platform for on-premises reporting. The platform for cloud reporting is of course Power BI. There is a third platform for the delivery of “Analytical”, or Excel based reports, and that’s Excel Online. On premises, it’s called Office Online Server, but it is the same technology. The three platforms and their capabilities are shown below.


Excel Online/OOS

Power BI

















The technical preview of Power BI reports in Reporting Services is available for testing, which covers Interactive reports in SSRS, and the above statement indicates that there is a solution to support Analytic reports in SSRS as well. The Power BI platform does this already, and it is done by leveraging the capabilities of Excel Online. Given the fact that Office Online server provides the same capabilities on premises, it makes sense that it would be used by SSRS when the Excel workbook support is added.

It should also be noted that the above comparison shows Mobile reports being supported by Power BI. To be clear, Power BI does not support RSMOBILE files, but regular Power BI reports are inherently mobile and available through the Power BI mobile client. which is also how RSMOBILE reports are delivered to end users.

The Reporting Services team is clearly very close to completing the vision laid out over a year ago, in the Reporting Roadmap for on-premises users. If the goal is to have parity between on-premises and cloud platforms, the only thing remaining (apart from possible support of the RSMOBILE format) is support for Paginated reports in Power BI. There have been no statements made regarding this capability, but its absence is certainly notable.

Where Did Power View Go in Excel 2016?

If you’ve been using the Excel 2016 Preview or just Excel 2016 (depending on when you read this), you may have noticed that there is no longer an option to insert a Power View report into o workbook. The reason is that it has been removed from the default ribbon in Excel 2016. It used to be on the Insert tab in the Reports Section, right beside Power Map.

Power View in Excel 2013

However, opening the Insert tab in Excel 2016 reveals it to be missing.

Power View Missing in Excel 2016

Did Microsoft remove Power View from Excel? What’s going on? Power View is still very much a part of Excel; the only change is that now it is no longer a default ribbon option. The good news is that it’s simple enough to add it back in. To do so, we need to edit the ribbon. Click on File-Options, and then select Advanced Options. The ribbon editor will appear. We can add Power View to any tab that we would like, or even create a new one, but here we’re just going to add it back to the Insert menu. To do so, expand the Insert menu. Each command must be added to a group, so we need to click the “New Group” button. Next, because I don’t think anyone will want their group named “New Group”, we want to rename it. In this case, we’ll rename it to “Reports”, the way that it used to be.

Adding a new group to the Insert tab

Next, we need to add Power View into the group. The easiest way to do this is to select “Commands Not in the Ribbon” from the “Choose commands from” dropdown. It’s a long list of items to choose from, and you’ll be tempted to look under “P” for Power View. You will be disappointed. The correct command is actually to be found in the “I”s, and it is “Insert a Power View Report”. Select that option, and click the “Add” button.

Once this is complete, Power View should once again appear in the Insert tab, in the Reports section.

I have no idea why Power View has been removed from the ribbon by default. It may just be temporary given that we’re not yet at release, but it could signal some other change. In any event, if you work with both Power View and Excel 2016, you can continue to do so.

How to Use a Wildcard Certificate With Office Web Apps 2013


There is a lot of guidance out there that indicates that it isn’t possible to use wildcard certificates with Office Web Apps 2013. Much of this stems from one of the certificate requirements listed in this Technet document:

“The FQDN in the SAN field can’t begin with an asterisk (*).”

On first blush, this would in fact rule out wildcard certificated since their SAN (Subject Alt Name) or friendly name is the FQDN of the wildcard (* Indeed, there is no way in the IIS manager to alter the name of the certificate . Issuing a PowerShell command the create a new Office Web Apps server farm with the wildcard name results in the certificate being invisible to the command, and you receive the following error:

Office Web Apps was unable to find the specified certificate

However, in my experience, wildcard certificates work just fine with Office web apps. There is just a trick to getting them to work.

The problem isn’t the wildcard certificate per se, it’s the fact that the friendly name can’t contain a wildcard. All that we really need to do is to change it.  It’s not immediately obvious how that’s done, but it can be done through the MMC snap in.

If the certificate is already on the server, then great, but if not, you’ll need to import it. In this example, we’re importing a certificate that’s been exported from another server, a common enough scenario for wildcard certificates. However, the origin doesn’t matter.

The first thing you’ll need to do is to run MMC (Microsoft Management Console). To do this, from a command prompt, type MMC and hit enter. Then load the Certificates snap in, selecting the Local Computer store when prompted.

The location of the certificate is important. If it’s not in the right place, the new farm command won’t see it, and you’ll receive exactly the same error as above. The certificate needs to be imported into the Personal folder of Local Computer. Right click on the Certificates folder, hover over All Tasks, and select Import. Then, go through the prompts and select the certificate to import.


Once imported, we want to change its Friendly Name. We can do that by selecting the certificate, right clicking it, and selecting Properties.


From here, it’s a simple matter of changing the friendly name from the wildcard address, to something with significantly fewer asterisks, in our case xxxxxxwildcard


Once done, click OK, and close the MMC. Your certificate now has a friendly name that you can use to create your Office Web Apps farm.

New-OfficeWebAppsFarm -InternalUrl "" -ExternalUrl "" -CertificateName "xxxxxxWildcard" –EditingEnabled

Once created, you can continue to configure your Office Web Apps farm, and then bind SharePoint to it.

While I’m on the topic, and because it comes up frequently, once you have bound your SharePoint farm to the Office Web Apps server, it’s important to turn off view rendering for Excel files if your farm uses Excel Services. This is because the Office web apps don’t support data connections in Excel files, or PowerPivot models, and data interactivity won’t work. By issuing the following command:

New-SPWOPISuppressionSetting -extension xlsx -action view

we tell the SharePoint farm to use Excel Services (which supports data connections) when viewing xlsx files, not Office Web Apps.

Finally, if this is being added to an existing farm, you’ll want to run a full crawl to repopulate your search index with the new rendering mechanism for Office documents.

Using Document Templates with Lists and Content Types in SharePoint

While the concept of document templates has been in SharePoint for a very long time, and content types since SharePoint 2007, I often find that many organizations don’t take complete advantage of their power, or don’t use them at all. In many cases, this is because they are unaware of how to properly do so.

This post is an attempt at demonstrating how templates can work with both libraries and document templates, taking full advantage of the metadata contained within SharePoint in the document. It will also describe an approach that we use to help with their maintenance.

I have described 3 walkthrough scenarios, each complete in its own right, but when taken together form a comprehensive template management strategy. These 3 scenarios are:

    1. Using a Library Template
    2. Using a Content Type Template
    3. Use A Library to Manage Your Document Templates

All of the examples here are shown using the 2010 suite of tools (Office 2010, SharePoint 2010). However, almost all of this capability is available in the 2007 family of products. Some of the menu locations are different with 2007 (Particularly with Word) , but the capabilities are there.

A – Using a Library Template

The simplest way of working with templates is to modify the default template for a document library. In the example below, we’ll create a new library, create metadata, display and use the metadata, and finally, create a new document based on this template. Several of the steps used in this example will be used in the other examples.

1. Create a new Document library

From your destination site, click either “Libraries” or “All site content” from the Quick Action pane on the left (note – the menu options that appear will vary according to the site template used, and the options that have been chosen by the site administrator). If neither option is available, select “Site Actions” then View All Site Content”.

Finally, from the resultant dialog, select “Document Library”, give the new library a name, and click the “Create” button. In this case, we’ll use “Letters”.


2. Add metadata to the library

Now we need to add some metadata to this library. When the Library is first created, the Library ribbon tab will be open. From there click the “Library Settings” button.


On the following screen, in the columns section, select “Create Column” (or add from existing site columns, and add the metadata fields that you want to use with this library. In this case, we’ll add a few (pretty boring) fields that you might want to use when tracking letters.

Side note – as a best practice, I try to never use spaces in the names when creating fields, lists, sites, etc. When spaces are used, SharePoint converts these spaces to _x0020_ in the internal field names. Once the field is created, the name can be changed, and that will only affect the display name, not the internal field name.


3. Edit the default template

Once we have our metadata fields in place, it is time to edit the default template. This example is using a word template, but the same approach works for other Office content (Excel, Powerpoint, etc).

Without leaving the list settings screen, select Advanced Settings (in the General Settings section).


On the next screen, there is a section for the library’s document template.  in that section, select the “(Edit Template)” link. Word should open (you may be prompted for credentials) and then you will be editing the library template.

4. Show the Document Information Panel

Since we want to incorporate the document metadata into the body of the document, we will want to use the document information panel. The document information panel will show the user editable metadata for a document. When the document is stored in a SharePoint library, or is a template for one, you can also expose the metadata from the library itself (or from a content type….more on that later). The nice part is that it can be displayed right at the top of the document, so if the properties are used throughout the document, you only need to edit them in one place. Unfortunately, it is turned off by default, and it really has to be one of the best hidden features in Word 2010. It was hard enough to find in 2007 – you had to add the developer tab to your ribbon, and then select “show information panel” there. In 2010, it’s hidden in the backstage.

Click File, and then click on the info tab. To the right of the screen you’ll see the section for properties. You might think that “Show All Properties” at the bottom would do it, but no. You need to hover your mouse over the “Properties” title, and select the dropdown arrow. You will be rewarded with two options, and  we want the first one, “Show Document Panel.


The good part is, you only need to set it once, and it sticks. If you’re setting it for a template, it’ll stick there too. Once selected, you will be returned to the document and the document information panel will be displayed.


You should see all of the metadata fields that you created for the list, in their proper column order, using the appropriate controls for their data type. Any metadata changes made in SharePoint will be reflected here, and conversely, SharePoint will be updated with any of the changes entered here. This is helpful, but it’s really powerful when you use the metadata to help form the document.

5. Quick Parts – Put the Metadata to work

To use a metadata field in the document, position your cursor where you would like the field to be displayed (or edited), click the “Insert” ribbon tab, select the “Quick Parts” button, select “Document Property” from the dropdown, and finally select the field that you want to use.


Repeat the procedure for each field that you want to use. When finished, your document will look something like the following:


Finally, Save your template, and exit Word. You’re pretty much done.

6. Test the Template

After you close Word, you should be returned to your library. To test your template, you’ll want to try creating a new document. Go ahead and do so – click on the Documents tab in the toolbar, and the click on the new document button.


Go ahead and enter in the metadata values. Notice how the document updates as you do so. When complete, your document should look something like the image below:


Finally, save the document, and exit Word (or just exit). You’ll likely be prompted for a name – give it one. After Word closes refresh the library view, and you should see your document, with all of the relevant metadata in the library view.


B – Using A Content Type Template

Modifying a library template is relatively straightforward, but it does entail several limitations. Firstly, by using library defined metadata (and template) you are limiting yourself to only that library. Neither these fields nor this template can be used elsewhere in the site or site collection. In addition, all of the documents in the library must use this metadata definition.

In order to move beyond this limitation, SharePoint 2007 introduced the concept of Content Types. A detailed discussion of content types is beyond the scope of this article, but simply put, a content type is a collection of metadata fields that can be applied to a document. A content type is loosely analogous to a class in object oriented programming.

Luckily, content types contain not only metadata, but also documents. That means that we can define a content type in a similar fashion to the way we modified a list in the example above, but we can then take that content type and apply it to any library in the site collection. With the content type syndication features available in SharePoint 2010, we can apply it to any library in our organization (it’s no longer limited to even the farm). In addition, by using content types, any given library is no longer limited to a single set of metadata fields.

The process for creating a content type template is similar to creating a library template, but there are a few important differences. Where identical, I will reference the library template procedure.

1. Create The Content Type

Content types can be created at the site level or the site collection level. They will be available to their containing site and all sites below. If the content type is created at the root of the site collection, it will be available to the entire site collection.

To do this, navigate to the root site and select “Site Actions”  and then Site Settings


You will then be presented with the Site Settings page. The Content types are stored in the Content Type Gallery, so next we will select “Site Content Types”.


We are going to create the “Letter” Content Type, so go ahead and click the “Create” button. You will then be taken to the Content type creation screen. This is similar to creating the new list as we did in the example above, but this time, there are additional options that control how the content type works.


We’ll be calling this content Type Letter, and it will inherit all of the properties (things like Name, and title) from the “Document” content type, which is in the “Document Content Types” category. Finally, we’ll want to group all of our content types in the same group, and since we don’t already have one, we’ll create the “My Custom Types” group.

Once created, we can add all of our custom fields to the content type. We’ll use the same fields that we did in the example above.

2. Add Metadata to the Content Type

Upon creation of the content type, you should be taken to the Content Type Information page for it. From here, go ahead and add the same columns that added to the list above.If you bump into a reserved or in use column name (i.e. First Name) either use a different name (ie  Recipient First) or use the existing site column. Also, don’t forget to put all of the new columns into a group for later maintenance). When complete, the information page should look similar to the screen shown below.


Content types have some additional controls that allow you to control how the Document Information Panel appears within Word. In the settings section, Click on the “Document information Panel settings” link. On the next screen, ensure that the “Show Always” option is selected.


You should also note that this is where you can control precisely what appears in the Document Information Panel. Should you choose to do so, you can replace the default panel with an InfoPath form, but I won’t be covering that in this post.

Once you are ready, click OK.

3. Create an Word File and Upload it

When a document library is created, a document template is normally created along with it, unless specified otherwise. This is not true for content types. Therefore, We need to create a document to use as the template for this content type. Initially the process is very straightforward. Start Word, create a new document. If you want to add content to the document at this point, that will work, but it’s not necessary. When ready, save the document to your file system and remember its name/location.

Once done, go back to the content type definition window, and click the “Advanced Settings” link. in the resulting window, click the browse button, and navigate to the file that you just created.


Click OK, and then navigate back to the site. The content type is complete – for the moment.

4. Add a Library for Template Customization

Although we can edit the template once it’s been added to the content type, we can’t do anything with the metadata fields. That’s because Word can only reach back into the library that contains a document to see the metadata fields (the columns), and the content type gallery does not contain the columns that the content type itself does. We therefore need to create a document library that will use our content type. We will then create a new document that uses this content type, configure it the way we wish, and then replace the content type template with this document.

Firstly, add a new document library to a site (ideally the root site). We will be using it again below, and ultimately to house other document templates, so give it a logical name, something like “Document Templates”. Once created, we need to enable content types, and choose the our content type.

To enable content types, first navigate to the new library, and from the Ribbon, click the Library Tab, and then click the Library Settings button. From the Library Settings page, click the “Advanced Options” link. Once on the Advanced settings page, set “Allow management of content types?” to “Yes”.


Notice that the document template setting is greyed out. That’s because the content type will control the template. Click OK to close the advanced options screen.

You will now notice that we have a content types section, and the Document content type is being used. We want to add our content type, and then remove the generic Document content type. Since a library must have at least one content type, we must first add ours, and then remove the Document type.

First, click the “Add from existing site content types” link.


The “Add Content Types” dialog is loaded. Select your group from the dropdown, and add your content type to the Add list.


Click OK when ready. You will be returned to Library Settings, and you should now see your content type as number 2 in the list. We now want to remove the Document type, but there’s a slight catch. It is the first item in the content type list. The first item is the default content type, and the default content type cannot be deleted. We must first therefore make our content type the default, and then we can delete the Document content type. To do this, we click the “Change new button order and default content type” link.


From the next page, set the order of the Document content type to anything other than 1.


Click OK, and then click on the “Document” link in the Content Types list.  You are then taken to the List Content Type settings screen. From here, click the “Delete this content type” link, and confirm. Note that you are NOT deleting the Document content type itself, but only its association with this document library.


You should be returned to the Library Settings page, and see only one content type. Return to the library to perform the next step.

5. Create a New Document in the Library

From the library, click the Documents tab on the ribbon, and the the New Document button.


Accept the confirmation, and Word will open.  You will not need to turn on the Information Panel, as that is controlled by the content type itself. Recreate the letter following the procedure laid out in step 5 above.

Once you finish using Quick Parts, save the letter back into the library (this isn’t absolutely necessary for what we’re doing right now, but will help us in the last example). Once saved to the library, save it once again, but this time to your file system, and take note of the location/name.

6. Replace the Existing Content Type Template

The content type template that you had originally created was blank, but was necessary for the content type to work at all. We now need to replace it with our formatted letter template. To do so, we need to navigate back to our site content gallery (Site Actions – Site Settings – Site Content Types). Locate and click on the site content type created above (Letter in this case), and the content type information screen appears. Click on advanced settings, and replace the existing template with the new one by uploading the new one.


Close this window and return to your site. We’re ready to test this out.

7. Consume the New Content Type

Create a new library to test this. Configure it to use content types, add the new content type and remove the default Document content type. I’ve already outlined the procedure for doing this above with the template library, so I won’t repeat it here. Create a new document, change some of the metadata values and save it.

If you will not be using the procedure outlined below for template maintenance, you can delete the template library at this point.

In order to make changes to the template at this point, you will need to repeat the procedures in steps 5 and 6, immediately above, but you will be able to do this from any library configured to use this content type. There are a few down sides to maintaining the template in this way. For one, it is cumbersome, and for another, the ability to modify site content types, particularly at the root level is very high (or at least it should be….). This is a problem because you don’t necessarily want to charge farm administrators with the task of keeping document templates current.

Below, I will outline the last example which allows you to edit the template simply by editing a document in a library.

C – Using A Library to Manage Your Document Templates

So far, we have set up our template using our library metadata and stored it within our content type definition. We can use that content type throughout our site collection, but what happens when the requirements change for the site collection? In that case, someone with authority to do so must locate the content type definition, and repeat some of the steps above to update it.

If we centralize our document templates into a single, or even multiple document libraries, it makes for easier maintenance. It will be necessary for the template library to use all of the content types that it also contains, in order to do metadata maintenance.

The good news it that if you have been following along, you have already completed steps 1 and 2 below when you created the template library. Given that we’ve already been through many of the key concepts, I’ll keep the below examples brief.

1. Create a Template Library

If you have not already done so, create a new library for containing document templates. Go into advanced settings and enable content type. Add the content type of the template that you wish to manage. If necessary, delete the default “Document” content type.

The detailed procedure for doing this can be found above.

2. Add the Template to the Library

Either upload or create the document to be used as a template. Edit the document to suit your purposes.

3. Use the Document Directly as the Content Type Template

Here is where we deviate from where we have been before. First, you’ll want to take note of the URL of the file that you used in step 2. I make it a practice to always use relative URLS, which is everything to the right of the web application in the URL of the document. If your site collection is not at the root of the application, you will need to include the site collection path (i.e. /sites/mysitecoll).

The easiest way to get the document URL is by opening it in Word and getting it from the document information panel (File-Info).


Navigate to the site content type gallery (Site Actions, Site Settings, Site Content Types), and open your custom content type. When the information screen opens, click the Advanced Options link. The document Template URL will be set to the file name of the current template (if one has been created). Replace that with the URL of your document in the library.


Click OK, and return to your site.

4. Test it Out

If you don’t already have one, go ahead and create a new document library and have it use this content type. Create a few documents. As an added bonus, go back to the content type and add a mew metadata field. Edit the template in the template library to use this new field somewhere in the body of the document. Finally, edit one of the documents that use this template, and you should see the new metadata field appear in the Document Information Panel. If you create a new document, you should see the changes that were made to the template itself.

If you’ve made it this far, congratulations! Hopefully this demonstrates the power of content types combined with document templates, and helps provide some guidance on their use.

Connecting to Cubes and External Data with Excel in SharePoint

While many people are still unaware of it, Excel 2010 (and even previous versions) is a very powerful business intelligence client. That’s right, I said Excel. And I don’t mean the classic grab some data, do some charts and email it around sort of Excel, I mean connecting it to Analysis Services cubes and performing fast, useful data analytics on known sets of data. It can also go off and do some pretty amazing things with the Data Mining add-in, or PowerPivot, but for now I’m going to restrict myself to using core capabilities, and getting them published to SharePoint.

To start with, it’s important to understand how SharePoint interacts with Excel. Of course, at it’s core, you can store Excel files in a SharePoint document library,and open them in Excel. However,starting with SharePoint 2007,SharePoint included Excel Services, which allowed you to open a spreadsheet directly in the browser (without having Excel installed or using any ActiveX trickery). It also provided an Excel calculation engine, that was programmatically callable. Why would that matter? A power user could develop a complex model with Excel, store it in SharePoint, which could then be used as a calculation “black box” for other things. The model could also be tweaked by the power user as necessary with them needing  to worry about coding. Finally, you could expose part of the spreadsheet (and named range or object) through the Excel Services web part, which would truly allow you to incorporate Excel content into a dashboard.

SharePoint 2010 brings more to the table, including not only the browser consumption of spreadsheet content, but editing through the Excel Web Application.

However, all of this power does not come without its risks, and when interacting with external data, risks abound. Microsoft has done an excellent job of providing us with a highly secure infrastructure that allows us to do what we want here, but the only trouble is that it’s not always clear which approach to take. My aim in this article is to navigate through the process of connecting to an external data source (an analysis services cube, but the principle applies to others) in a particular scenario in a simple fashion. Our scenario is a fairly common one:

  • One Front End SharePoint Server
  • One SQL back end Server hosting the SharePoint databases
  • One SQL server hosting the Data Warehouse and the Analysis Services cubes
  • Kerberos not installed in the environment

In this environment, we have an authentication problem, the famous double hop problem. If I as a user ask the server to process a spreadsheet that connects to external data, it can’t just take my authentication token and pass it along to the external data source. What we need to do is to set up a proxy account (this has its own set of implications that I won’t get into here) and we’ll do that via the secure store service. If you’re using Kerberos, then you don’t have the double hop problem and the Secure Store part of this won’t apply, but then you’re probably busy enough dealing with Kerberos issues anyway….

If you’ve ever connected to external data, and then sent the file to someone else, you’ll know that they get prompted to verify that they want to connect to the data source and that they trust the connection. In this case, the author has created an embedded data connection. Embedded data connections are not allowed (or at least are strongly discouraged) on the server, because it has no way of verifying the authenticity of the connection.

The way that we deal with this in SharePoint is by creating a connection file, and then publishing it to a trusted location in SharePoint. Administrators need to take heed, in that by default, Excel Services trusts all SharePoint locations, which makes life easy, but not necessarily secure. The library that the connections are stored in should also utilize approval features, but this is not required. This walkthrough isn’t concerning itself with the security aspects, but they need to be considered in any real world situation.

Most of the steps below only need to be performed once, and connection documents can be reused, etc. However, this walkthrough is aimed at getting everything all set up from scratch.

We also don’t want to store our credentials in our connection string, so we will utilize the Secure Store service in SharePoint which will allow us to use, without necessarily knowing, a set of credentials. This will allows us to work around the double hop problem in a secure way. We will start with the setup of the Secure Store Service.

1. Set up the Secure Store Service ID

From Central Administration, navigate to Manage Service Applications, and click on your Secure Store application. If you don’t have one already, you’ll need to create one. You should know that SharePoint Foundation does NOT come with the Secure Store service. However, Search Server Express 2010 does come with it (and a few other things). Given that it’s free, it provides a nice option.

The Secure Store Service Application relies on both the Secure Store Service (duh) and the Claims to Windows Token service. You’ll need to make sure that they’re both started in the “Services on Server” Section in Central Administration System Settings.

The secure Store application requires an encryption key. If  one has not already been created, you’ll see a message indicating that you need to do so. The process is simple, just click the “Generate New Key” button in the ribbon.


Once a key has been created, we need to create a new target application, which is essentially a set of credentials. This is the application that our connection strings will reference when they need to connect to a back end data source. You create a new application by clicking the “New” button in the ribbon. The New application screen then appears.


There are a couple of things to note on this screen. Firstly, the Target Application ID is the ID that you will be using when you set up your connection below. You can name it what you like, but you’ll need to remember what it is. The Display Name and the Contact E-Mail need to be filled in, but the important thing to note is the Target Application Type field. If this is to be used by more than one person, you need to make sure that it is set to Group. By default, it is set to Individual, which doesn’t work so well in a shared environment. Take it from me – I found out the hard way. When this is filled in, click Next, and you’re presented with the following screen.


The Administrators are just that – the people who will set the properties of this set of credentials. The Members are the people that will be allowed to use this credential set in connections, External Lists etc. In the example above it’s set to anyone authenticated, which again, I wouldn’t recommend in production…..

When done, click OK, and we’re done right? Not so fast. We never actually set the credentials for this application, just everything around it. To do that, we select the application, and click the “Set (credentials)” button in the ribbon, or hover over the ID in the list and select “Set Credentials” from the dropdown.


In the subsequent screen you enter the account and the password (twice) of the credentials that are to be used as a proxy. Click OK and you’re done. A couple of things to note. If using a Windows account the name should be in DOMAINACCOUNT format. The proxy account is NOT a managed account, and if the password changes, you’ll need to come back here to update it. The proxy account should be a least privileges account, with access only to the things that are absolutely necessary to retrieve the appropriate data, or whatever its purpose is. Don’t add it to the Domain Administrators group.

OK, now step 1 is done. Whew.

2. Set up a Data Connection Library

The next thing we need to do is to set up a library to store our data connections. If you’ve set up a BI Site already (to use PerformancePoint), you can use the libraries there. Depending on your requirements, I find that it’s often a good idea to centralize this library and use it from different locations, but your requirements may vary. What we want to create is a Data Connection Library. This library will be used to store connections for both the Office Applications (ODC) and InfoPath UDC).

From the Site Actions menu on the site where it is to be located, select More Options, the, in the subsequent dialog box, Filter by Library, and select Data Connection Library. Give it a name (Don’t use spaces  here, you can always come back and add them later, but we don’t want spaces in the internal name), and click Create


What makes this library special is that it uses the “Office Data Connection File” and the “Universal Data Connection File” content types. You could always add these content types to any old document library and achieve the same effect. You could also include Reporting Services connections if you’re using Reporting Services in integrated mode.

This library also needs to be registered as trusted in the Excel Services Application. This is done through the Manage Service Applications section of Central Administration. Simply click on your Excel Services application, click Trusted Data Connection Libraries, and add the URL of your library, if not already there.


3. Set up a Library to house the Excel Reports

You can store the Excel Report that we’ll be creating below into any document library in the Site Collection. If you have set up a BI Center, then you already have a “Reports” library, whose purpose is to do just that. Oddly, by default, this library isn’t set up to contain Reporting Services reports (.rdl files), but that isn’t relevant to my task here.

You simply need to create a new document library, and for the purposes of this example, I’ll call mine Excel Reports (original, huh?)

You’re now ready to create our connection. We’ll use Excel to do that.

4. Create And Store The Connection File

Open a new Spreadsheet in Excel. Click on the Data tab, then click the “From Other Sources” button and choose “From Analysis Services” (obviously, if you’re using something else, choose that).


Enter in the name of your server, and click Next, Select the Database and Cube that you want to connect to, and then click Next again. We’ll need to do a couple of things in the last screen that appears.


First, select “Always attempt to use this file to refresh data”. Then, click the “Authentication Settings” button. This is where it gets interesting.


s Authentication is what you would use if the data resides on the same machine as the SharePoint front end, or your organization is using Kerberos. With this setting, the credentials of the user are used to connect to the data source.If you select None, then the credentials identified as the “Unattended Service Account” (if configured) in the Excel Services configuration will be used. You can only use one of these accounts, and when configuring it, it too will use the Secure Storage Service. We want to select our credential set and we do so by selecting SSS, and entering the ID of the credential set that we created in step 1 above.

Next, we need to save this connection into the SharePoint data connection library that we created in step 2. Click the Browse button, enter the URL of the library in the Address bar, choose a name and click save.


Finally, When you’re done, click finish, and fill out the metadata form that pops up with any comments or keywords that you might want to use later to fine the connection document. Now we have a connection document in the connection library.

5. Create The Excel Content

When you’re done creating the connection document, Excel will prompt you to insert a pivot table or chart. Choose the location, and Excel will insert it for you, and put you in pivot edit mode. You can select your dimensions and measures, and build your chart accordingly. When you’re ready, your worksheet should look something like this.


Later, we’ll want to show just the chart on a dashboard page, and to do that, we need to make sure that our chart has a logical name, so we need to do that, as highlighted above. Any named range can be published out to an Excel Services web part. When ready, it’s time to publish to SharePoint, but we also want to set some file properties first. These properties are very well hidden….

Click on the “File” tab to go to the backstage. Once in the backstage, click on the “Save and Send” tab on the left hand side. Then click on “Save to SharePoint”. Your screen will appear as follows:


Finally, we need to click on the Publish Options button. This allows you to control what gets used by Excel Services, and what appears when the spreadsheet is opened in the browser. It also lets us define parameters which can be used by the Excel Services web part, but  I won’t be using parameters here. I will however choose to publish only my chart and my pivot table to Excel Services, so I click on the dropdown option in the Show tab, change it to “items in the workbook”, and check my two items.


Finally I can click OK, then click on “Save to SharePoint” and save the spreadsheet in the library created in step 3 above.

6. Test the File and Publish the Chart in a Dashboard

Navigate to the library that you stored the report and click on it. The file should open in a browser, and you should see the first item (alphabetically) that you set to publish above.


You can switch between published items, using the view dropdown highlighted above. Once you’ve gotten this far, everything is working, and we’re ready to put our chart into a dashboard page.

All that we’ll need to know is the name of the item, and the address of the spreadsheet. In our case, our item is “Chart 2” and the URL of the spreadsheet is http://uvspdev2010/ExcelReports/MySpreadsheet.xlsx. Navigate to anywhere in the site collection that you’d like to show the chart. I’m going to use a team site. From the destination, select Site Actions – Edit Page (or select Edit from the ribbon). Place your cursor where you want the chart to appear, click the Insert tab on the ribbon and click the Web Part button. Select the Business Data category, Choose the “Excel Web Access” part and click insert.


Once added, click on the “Click here to open the tool pane” link, and enter all desired parameters. The two crucial parameters are Workbook and Named Item. When adding content to a dashboard, I find that it is better to remove all of the buttons and web part chrome, but that’s totally dependent on the use case. When ready, my web part parameters look something like this:


At this point, you can click OK, and you should see your item on the page in context.

image t=”773″>

Congratulations – you’re done!!

This was meant to be a “Hello World” walkthrough, and thus, I’ve left out quite a bit. You can add slicers and all kind of cool BI functions to your sheet, and they’re work in the browser. This is showing data in a cube, and therefore the chart will be updated when the cube is. You can also use parameters in Excel and drive them through filter web parts and connections on the display pages.

There ARE a lot of moving parts here, but once the infrastructure is set up, power users can do some pretty spectacular things using just Excel and SharePoint, and they can do so in a fully managed environment. Oddly enough, chances are that your power users probably already know both of these tools.