PerformancePoint and ADOMD.NET

I was recently configuring PerformancePoint in SharePoint 2013 and had deployed the SQL Server 2012 AdventureWorksDW Multidimensional cube for testing purposes. It had been a while since I configured PerformancePoint so thankfully I had written this post previously. As luck would have it I ran into pretty much every roadblock mentioned in that post……and one additional “detour” that I had almost forgot about (that also had a new twist).

If you’re in PerformancePoint Dashboard Designer and trying to create a data source that connects to SSAS, but seeing nothing show up in your Database dropdown (as seen below…..sorta) you might need to install ADOMD.NET 11.

clip_image001

Not only install it, but install it on all servers that run PerformancePoint AND you’ll need to adjust the Web.config file to tell PerformancePoint to use it.  This is of course assuming you’ve set everything else up properly. I’m taking for granted that you’ve already done stuff like

  • Associate an Unattended Service Account to the PerformancePoint Service Application and granted the appropriate users the right to use the unattended account in the Secure Store Service Application
  • Double checked that the service account you’re using for your Unattended Service Account actually has access to at least one db on the SSAS instance you’re trying to connect to

If none of that sounds familiar, check out my post on secure store for Visio Services or my posts on using data refresh with Excel Services…configuring PerformancePoint should be pretty similar to those.

Note that I wasn’t getting any error messages, but instead the database dropdown was just empty. If you’re getting any type of error on the screen shown above, your problem is most likely something else and not ADOMD.NET. Still with me? Is your database dropdown still mocking you at the very moment with its emptiness? Hopefully this will cure your PerformancePoint woes.

So….let’s do this

  1. Go download ADOMD.NET 11 already will ya?….at the time of this writing you can find it here: http://www.microsoft.com/en-us/download/details.aspx?id=29065
  2. Install it on all the first farm devices that’s running PerformancePoint. (something like next, next, finish)
  3. Once ADOMD.NET 11 is installed, navigate to \Program Files\Microsoft Office Servers\15.0\WebServices\PpsMonitoringServer
  4. Locate and make a backup copy of the web.config file found there. (Just in case you mess this all up…..I certainly don’t trust you.)
  5. Open the web.config file and scroll on down to the very bottom. Once you get there you should see something like this:
    clip_image003
  6. Change the oldVersion from 9.0.0.0 to 10.0.0.0
  7. Change the newVersion from 10.0.0.0 to 11.0.0.0
  8. Save that bad boy
  9. Repeat for every server running PerformancePoint

Completing the above had my database dropdown populating with an abundance of easily selectable db’s for as far as the eye could see. Some say it’s still populating new and wonderful connection options to this very day (results may vary).

I don’t recall having to do an iisreset or anything…although I think I might have closed Dashboard Designer and reopened it after making the changes. Hopefully this helps!

PerformancePoint Troubleshooting

I recently had to do some troubleshooting for PerformancePoint in SharePoint 2013. There were several different things that ended up being wrong with this particular farm, so I figured I’d do a write up on the problems I encountered and how they were resolved. The primary problems in the order I encountered them were:

  1. The user was completely unable to open Dashboard Designer.
  2. The user was unable to successfully connect to SharePoint with dashboard designer, or when they did none of the data source or content libraries would be available
  3. The user was unable to create or save data sources in dashboard designer once connected to SharePoint
  4. The PerformancePoint service application was throwing errors any time I tried to access the “manage” or “property” settings in Central Administration

I think the best way to approach these problems would be in the order that they were encountered, so let’s get to it.

1. The user was completely unable to open Dashboard Designer

When the user tried to open the Dashboard Designer from their PerformancePoint content library they were greeted with the following error message:

Cannot retrieve application. Authentication error

In this scenario, the user was logged into their computer locally (ie not using a domain account), however they were logging into SharePoint using a domain account. To get around this issue we used the “Windows Credential Manager” to tell SharePoint what account it should be using for authentication. To setup the Windows Credential Manager, follow these steps:

  1. Navigate to the Control Panel in your Windows OS
  2. Locate and click on the “Credential Manager” icon in the control panel
  3. Next to the “Add a Windows credential” link
  4. In the textbox labeled “Internet or network address” type in the address of the SharePoint site you’re logging into. You’ll want to exclude the http:// though, and you do not need to include any sub-site directory. For example, if I was opening Dashboard Designer from a sub-site with an address of http://portal.contoso.com/BITestSite all I would need to enter is portal.contoso.com
  5. Type in the appropriate credentials in the “User name” and “Password” textboxes. Don’t forget to include the domain in your username (domain\username)

    clip_image001
  6. Click OK

The above seemed to resolve the problem of not being able to open Dashboard Designer. SharePoint must check the login of the Windows machine somewhere behind the scenes.

2 - The user was unable to successfully connect to SharePoint with dashboard designer, or when they did none of the data source or content libraries would be available

Now that we can actually get the PerformancePoint Dashboard Designer to open, we quickly ran into our 2nd issue. In this particular case every time the user opened PerformancePoint Dashboard Designer, the designer would open but would not be connected to SharePoint. First we tried manually entering in the SharePoint URL into dashboard designer and connecting. This can be done by doing the following:

  1. Click on the “Jewel” button at the top left of dashboard designer
  2. Click the “Designer Options” buttons from the resulting menu
  3. In the resulting options menu, click the “Server” tab
  4. Type the URL of the top level SharePoint site

    (This URL should be the to the top level site, not a sub-site or a specific library. For example if I’m opening Dashboard Designer from the PerformancePoint content library located at http://portal.contoso.com/SSRSClass/Lists/PerformancePoint%20Content/By%20Content%20Type.aspx all I want to enter as the SharePoint URL is “http://portal.contoso.com” entering an incorrect URL here will most likely result in the following error message: “Cannot contact site at the specified URL xxxxx There is no Web Named xxxxx”)

    clip_image003
  5. Click connect and enter in your log on credentials when prompted

At this point we started getting the following error: “The URL must be in one of the following zones: Local, Intranet, or Trusted”

Well, that seems simple enough to fix. Just add the site to your trusted sites. That can be done by following these steps:

  1. Navigate to the Windows Control panel
  2. Click on the “Internet Options” icon
  3. Click on the “Security” tab
  4. Click on “Trusted Sites” (looks like a green checkmark)
  5. With Trusted Sites highlighted, click on the “Sites” button
  6. Enter the top level URL into the first textbox show in the image below (in my case this would be http://portal.contoso.com) and click the “Add” button

    If you get an error stating “Sites added to this zone must use the https:// prefix. This Prefix assures a secure connection” simply uncheck the box labeled “Require server verification (https:) for all sites in this zone” on the Trusted sites screen before clicking the Add button.

    clip_image004
  7. Click the “Close” button on the Trusted sites window
  8. Click “OK” on the Internet Properties window
  9. Try to connect Dashboard Designer manually by entering the URL (as described earlier)

This time we were able to successfully connect, and the lower left hand corner of Dashboard Designer was displaying our user name and site. However, Dashboard Designer may still not work properly. If you continue to experience problems, read the next section.

3 - The user was unable to create or save data sources in Dashboard Designer once connected to SharePoint

Even though Dashboard Designer is now working, you might still run into problems. I’ve seen different error messages in different environments when users are trying to perform different actions. A few examples of error encountered (typically when trying to create or save a data source) are as follows:

“An unexpected system error has occurred. Additional details have been logged for your administrator.”

“Access denied. You do not have permission to connect to the server. Please contact your system administrator.”

“The data source no longer exists or you do not have permissions to view it. Additional details have been logged for your administrator.”

I’ve even seen some cases where users can create content (kpi’s, dashboards, etc) but can’t create or save data sources at all. These errors seem to come about when the account that is running the application pool for PerformancePoint doesn’t have the correct permissions. So there are a few things that we need to figure out here. First we need to figure out what application pool PerformancePoint is running under. Secondly we need to figure out what account is running that application pool. Lastly, we need to give that account the appropriate permissions. To do all of this we’re going to use some PowerShell cmdlets.

  1. Log into the SharePoint server that has Central Administration installed on it
  2. Open “SharePoint 2013 Management Shell”
  3. If this is your first time using the management shell you’ll want to copy and paste in the following command

    Add-PSSNapin microsoft.sharepoint.powershell
  4. Run the following command to return a list of all the PerformancePoint properties

    Get-SPPerformancePointServiceApplication
  5. Scroll back up to the top, and locate the name of the application pool being used. In my case below, it’s “Default SharePoint Service App Pool” shown highlighted in yellow in the image below

    clip_image006
  6. Next we’ll want to figure out what account is being used to run the application pool shown above. To do that run the following command without quotes:

    Get-SPServiceApplicationPool

    clip_image008 
  7. As you can see in the image above, in my case the account “CONTOSO\TestNoAccess2” is being used

  8. Now that we’ve got our account, all we need to do is give it the appropriate permissions to our web application. Run the following commands replacing the web application and app pool account with your own information:

    $w = Get-SPWebApplication -Identity http://portal.contoso.com
    $w.GrantAccessToProcessIdentity("Contoso\TestNoAccess2")

If all has gone well, you should now be able to create data sources etc inside of SharePoint designer.

4 - The PerformancePoint service application was throwing errors any time I tried to access the “manage” or “property” settings in Central Administration

This last error I encountered came when I was trying to use Central Administration to access any of the settings for the PerformancePoint Service Application. The error would occur any time I tried to click on the “Manage” or “Properties” buttons for PerformancePoint. PerformancePoint would also throw an error if I tried to access those screens by clicking on the Service Application itself and then clicking “PerformancePoint Service Application Settings”. The error I kept getting was:

“Sorry, something went wrong

An entry with the same key already exists.”

It should be noted, that other than not being able to access these screens, PerformancePoint was working just fine. I tried restarting services, deleting and recreating both the PerformancePoint and Secure Store service applications in every order imaginable(a solution I had read on a few other blogs) but still couldn’t get rid of the error. Ultimately I ended up calling in some reinforcements in the form of SharePoint admin extraordinaire Stephen Wilson. He noticed that there were actually 2 “Access Services” service applications installed with the exact same name. One was for 2010, and one was for 2013, but both were just named “Access”. We were able to re-create the error in my dev environment with ease by simply recreating the duplicated Service Application name scenario with Access in my environment.

So…what to do about it? If you know only one of the Access Service Applications are being used, delete the one that isn’t being used, that immediately corrected the problem in my development environment.  However if you don’t know, you might want to tread lightly. The only reason I needed to get to those screens anyway was set the unattended service account, an act that can be achieved via PowerShell pretty easily. For example, you can use the script below to both create an account in the Secure Store and associate it with PerformancePoint:

Set-SPPerformancePointSecureDataValues -ServiceApplication "PerformancePoint Services" -DataSourceUnattendedServiceAccount (New-Object System.Management.Automation.PSCredential "CONTOSO\YourUserName", (ConvertTo-SecureString "YourPassword" -AsPlainText -Force))

Just add your specific PerformancePoint Service App name, and the user id and password.

You can actually get and set just about any value you want using PowerShell. Here’s a list of all the PowerShell commands for PerformancePoint:

http://technet.microsoft.com/en-us/library/ee906547(v=office.15).aspx

As I like to say ….it’s just that easy! Well I think that just about wraps up this post, hope you found it helpful.

The Dark Art of the Excel Services Workbook Data Refresh

In my last blog post we talked about how to create an Excel Services dashboard that pulls in information from SharePoint lists using OData feeds. This post is a continuation of that, but can also be applied to other data sources your Excel workbook might be connected to. In this walkthrough we’ll be taking a look at how you can get your dashboard to refresh in the following ways:

  1. User manually refreshes the workbook
  2. Workbook automatically refreshes every time it’s opened
  3. Workbook automatically refreshes every x minutes (even while it’s open, or in a web part)

In this walkthrough I’ll be using SharePoint 2013 on prem. If you’re going to follow along you’ll need to have access to (and some familiarity with) SharePoint Central Administration….or you’ll need to become good friends with your SharePoint administrator (They’re probably a really nice person, you should take them out to lunch). While this walkthrough is meant to be read in order, I’ve decided to break it up into section as follows for readability (hopefully)

  1. Configuring the unattended service account for Excel Services
  2. Configuring authentication options in your Excel workbook for manual refresh
  3. Setting the cache duration - (aka why you’re not seeing your data change immediately)
  4. Make your workbook refresh as soon as it’s opened (and kill that refresh warning)
  5. Make your workbook refresh right before your eyes while it’s open
  6. Make Excel web parts refresh right before your eyes

1 - Configuring the unattended service account for Excel Services

One of the first things we need to consider when we talk about an Excel Services workbook refreshing is:

  • What account will be used to access the data that Excel is going to refresh/go get?

After all, security is kind of a big deal, particularly when it comes to data. There are several ways to approach this, but we’re going to use what is probably the most common method and set up a service account, i.e. an account that is used specifically for getting data from our data source (in this case a SharePoint list). When we want to create a service account for these purposes in SharePoint, we call it an “Unattended Service Account”. Several service applications inside of SharePoint (including Excel) support the assignment of an unattended service account to them. Once an unattended service account has been set for a service application, it will use that account every time the service application needs to go get data.

OK great, so how do we set that up? I’m glad you asked! First we need to create what’s called a “Target Application ID”.  Target Application ID’s are basically a set of credentials that we want to use for something (in our case accessing data) and they live in their own SharePoint service application called the “Secure Store”.  Once you have a Target Application ID created, you can assign it to another service application (like Excel Services) as the “Unattended Service Account”.  To configure a Target Application ID in the Secure Store, we’re going to need access to SharePoint Central Administration. If you have no idea what Central Administration is or how to get to it, now would be a good time to go get your SharePoint administrator to help you along. During the steps to follow, we will be changing settings that have to potential to break existing workbooks that may already be using these services if someone has already configured them for other purposes.

The first thing we’re going to do is manually configure our Target Application ID in the Secure Store service application. Follow these steps:

  1. Open Central Administration
  2. Click on “Manage service applications” under the “Application Management” heading
  3. Locate the Secure Store service application and click on it (not the proxy).

    If you’re having problems locating your Secure Store, it’s possible your administrator named it something different. Look in the “Type” column for “Secure Store Service Application” to help you find it. If you still can’t find it, it’s possible that the service application hasn’t been created yet. If this is the case you’ll need to create it. I suggest going here and watching the video to help you along: http://technet.microsoft.com/en-us/library/ee806866(v=office.15).aspx
  4. Click the “New” button at the top left hand corner
  5. Type “BlogTestAccount” for the “Target Application ID” and then make the necessary adjustments to make your screen match the screenshot below

    clip_image001
  6. Click “Next”
  7. Click “Next” again to accept the default “Field Name”, “Field Type” etc.
  8. In the box labeled “Target Application Administrators” enter an account that you want to be able to make changes to this Target Application ID(probably your own account at very minimum).
  9. In the box labeled “Members” you want to enter the users and groups who will be allowed to use this this service account. If you have specific users that are having problems refreshing your workbook later, there’s a good chance it’s because you haven’t included them here.

    clip_image003
  10. Click “OK”

You should now see “BlogTestAccount” in your list of Target Application ID’s.  The next thing we need to do is actually assign an account to “BlogTestAccount” for it to use when it tries to get new data. To do that, follow these steps:

  1. Place a check in the Target Application ID box next to “BlogTest Account”
  2. In the ribbon at the top of the screen, click on the “Set” button in the “Credentials” group (not the set button in the permissions group)
  3. Type the user name (including domain) and password for an account that has access to the data you’re going to be refreshing (aka your SharePoint list). Be sure to type this correctly, as this screen will not validate your account other than confirming that the passwords match each other

     clip_image004
  4. Click “OK”

Alright now that we’ve got our new Target Application ID all ready to go, we need to decide if we want Excel Services to use it as the default account every time it tries to go get data. Alternatively you can manually configure each of your workbooks individually to use a Target Application ID (we’ll cover how to do that a little later). If you want to have Excel use your “BlogTestAccount” Target Application ID as its unattended service account, follow these steps:

  1. Open Central Administration
  2. Click on “Manage service applications” under the “Application Management” heading
  3. Locate the Excel service application and click on it (not the proxy).

    If you’re having problems locating Excel, it’s possible your administrator named it something different. Look in the “Type” column for “Excel Services Application Web Service Application” to help you find it. If you still can’t find it, it’s possible that the service application hasn’t been created yet. If this is the case you’ll need to create it. I suggest going here and watching the video to help you along: http://technet.microsoft.com/en-us/library/jj219698(v=office.15).aspx
  4. Click on “Global Settings”
  5. Scroll down to the very bottom of the “External Data” section and select “Use an existing Unattended Service Account”
  6. In the box labeled “Target Application ID” type the name of the Target Application ID we just created “BlogTestAccount”

    clip_image005
  7. Click ”OK”

Congrats! We’ve finished configuring our unattended service account. Now it’s time to put it to some good use.

2 - Configuring authentication options in your Excel workbook

We’re not going to cover how you create an Excel workbook that connects to a SharePoint list using OData in this walkthrough, because I’ve already covered that here: http://tavislovell.com/create-a-dashboard-from-your-sharepoint-list-data-using-excel-services-and-the-excel-web-access-web-part/

So if you haven’t already read that post, go do so now and create yourself an Excel workbook. If you’ve already read that post, let’s get down to business and talk a little bit about authentication. With your Excel workbook open, follow these steps to get to the authentication options:

  1. In the ribbon at the top, click on the “Data” tab
  2. In the “Connections” group of the data tab, click the “Connections” button

    clip_image006
  3. Highlight the connection in your workbook, and then click the “Properties” button

    clip_image007
  4. Click on the “Definition” tab of the resulting Connection properties window
  5. Click on the “Authentication Settings” button at the bottom of the window

    clip_image008

At this point you should be staring directly into the eye of the beast (aka looking at the “Excel Services Authentication Settings” screen as shown below).

clip_image009

Let’s have a little talk about this screen, for it is here where I’ve seen many a man stumble and fall on their quest for Excel workbooks that refresh. There are only 3 options, but we must choose wisely. Let’s cover them all though.

OPTION 1 - “Use the authenticated user’s account”: In this scenario when the Excel workbook tries to refresh itself, it’s going to try to get the new data by pretending it’s you and using your account. HOWEVER, there’s a good chance it is going to fail at that task unless you have Kerberos setup in your environment. With that said, we’re going assume that you do not have Kerberos setup, so this option is of no use to us.

OPTION 2 - “Use a stored account”: This is a viable way for us to get the job done. Remember when we created our Target Application ID (BlogTestAccount) in the first section of this walkthrough? Well if we wanted to use that specific account to go get our data when the workbook refreshes, we would simply select this options and then type in “BlogTestAccount”. Feel free to use this option.

OPTION 3 - “None”: With this option, when the workbook refreshes it’s going to use the Target Application ID that was set as the unattended service account for the Excel Services service application (we also did this in the first section of this walkthrough). So this is also a perfectly good option to use assuming you were following along.

To finish this section simply choose option 2 or 3 as described above and then click “ok” back to the workbook and then save your workbook.

**NOTE** for whatever reason I’ve seen the changes above sometimes revert back to option 1 when using OData as a data source. For some reason this seems to happen almost without fail when I set these options as I’m creating a new data connection. After you save your workbook, I highly suggest going back into these settings and double checking that they’re still set appropriately.

At this point you should be able to upload your workbook to SharePoint, open it and manually refresh it using the data tab at the top. Don’t be surprised if you update the SharePoint list with a few new values, then manually refresh your Excel workbook only to see it’s not getting the new data. That’s because your data is being cached, and we’ll talk about that in the next section. The main goal here is that you’re not getting any errors when you try to refresh the workbook, and if that’s the case you’re in good shape. If you get a warning about the refresh, that’s also ok, we’ll deal with that in a bit as well. Now let’s take a look at that caching problem of ours.

3 - Setting the cache duration - (aka why you’re not seeing your data changes)

So you want to see the data in your workbook refresh on the spot, and always get the most up to date data whenever you manually refresh. Well, we could do that, but the cache is really there for a reason. It’s protecting your resources so they don’t grind to a halt if a bunch of people are constantly hitting refresh. The default cache for the refresh is 5 minutes, and personally I don’t think that’s all that long to wait. However if you wanted to adjust the amount of time that data is cached, here’s how you would do it:

  1. Open Central Administration
  2. Click on “Manage service applications” under the “Application Management” heading
  3. Locate the Excel service application and click on it (not the proxy)
  4. Click on “Trusted File Locations”
  5. You should have one entry with an address of “http://”. Click on it
  6. Scroll down to the bottom until you get to the “External Data” group
  7. Look for the heading “External Data Cache Lifetime” as shown below

clip_image010

As you can see there are two different types of refresh you can configure the cache for, and they’re each measured in seconds (300 seconds works out to 5 minutes). Thus far we’ve only talked about manual refreshing, so if you’d like to change that value simply change it in the “Manual refresh” textbox and then click OK at the bottom of the screen. In a bit we’ll be taking a look at how you can have your workbooks automatically refresh every time they’re opened, or even while someone has it open and is currently looking at it. To change that cache value, simply change the value listed in the “Automatic refresh (periodic / on-open” textbox. If you’re on a dev farm with not many users, feel free to set the cache values to zero if you want to test stuff out, but in a production environment remember that the cache is your friend.

4 - Make your workbook refresh as soon as it’s opened (and kill that refresh warning)

At some point you might grow tired of having to manually refresh your workbook every time you open it. Can’t we just make the refresh happen auto-magically? Yes we can, and here’s how:

  1. Open your workbook in Excel
  2. In the ribbon at the top, click on the “Data” tab
  3. In the “Connections” group of the data tab click the “Connections” button

    clip_image006[1]
  4. Highlight the connection in your workbook, and then click the “Properties” button

    clip_image007[1]
  5. Place a checkmark in the box labeled “Refresh data when opening the file”

    clip_image011
  6. Click OK
  7. Close the “Workbook Connections” window
  8. Save your workbook
  9. Re-upload your workbook to SharePoint

Now every time someone opens the workbook it will refresh (although the caching described in section 3 still applies). Note that having your workbook manually refresh every time it’s opened isn’t always a great idea, particularly for workbooks with long refresh times.  

By default Excel services will prompt the user with a warning any time it’s about to run a query.  If you’ve set up your workbook to refresh on open, that warning will be the first thing you see every time you open the workbook, and it can get mighty old dismissing it over and over again.  If you’d like to fashion a pair of cement shoes for that warning here’s how you might go about it:

  1. Open Central Administration
  2. Click on “Manage service applications” under the “Application Management” heading
  3. Locate the Excel service application and click on it (not the proxy)
  4. Click on “Trusted File Locations”
  5. You should have one entry with an address of “http://”. Click on it
  6. Scroll down to the bottom until you get to the “External Data” group
  7. Look for the heading “Warn on Refresh” and uncheck “Refresh warning enabled” as shown below

    clip_image012
  8. Click “OK” at the bottom of the screen

Laugh maniacally as that warning sleeps with the fishes.

5 - Make your workbook refresh right before your eyes while it’s open

Occasionally  I have someone who wants to create a dashboard in Excel, and they want to put it up on a big screen TV somewhere and have it live update in “real time”. Well, I dunno about “real time” but when most people say “real time” what they REALLY mean is “near real time”…and that we can do. But before we do, we should probably have a little discussion about what is and isn’t practical. The shortest refresh period you can set for an Excel workbook out of the box is 1 minute. So once every minute Excel is going to go out and re-query data (the caching from section 3 still applies here…go read that if you skipped it). Now this can create quite a workload for SharePoint, and whatever data source it’s pulling from. Some workbooks might not even finish refreshing in a minute. So….don’t set all your workbooks to refresh every minute. Find out how long the refresh even takes by doing some manual refreshes, and remember the amount of data is probably going to grow over time, so refreshing might start taking longer in the future. Then find out how “real time” the dashboard REALLY needs to be from a practical perspective. The less real time, the less workload it is on all the systems involved. That said, let’s make some magic happen:

  1. Open your workbook in Excel
  2. In the ribbon at the top, click on the “Data” tab
  3. In the “Connections” group of the data tab click the “Connections” button

    clip_image006[2]
  4. Highlight the connection in your workbook, and then click the “Properties” button

    clip_image007[2]
  5. Place a checkmark in the box labeled “Refresh every”
  6. Set the number of minutes Excel will wait between each refresh

    clip_image013
  7. Click OK
  8. Close the “Workbook Connections” window
  9. Save your workbook
  10. Re-upload your workbook to SharePoint

That should do it, you’re dashboarding like a pro now! You should be able to have your Excel dashboard open, then open a new tab and change/add/delete a value or two in the SharePoint list it’s pulling from, then watch in awe as your dashboard updates whenever the next refresh hits (remember cache settings still apply, see section 3 for details)

6 - Make Excel web parts refresh right before your eyes

Welcome to final section on refreshing in Excel. You’re probably just about ready for this walkthrough to be over so I’ll make this fast. I covered how to display your Excel workbook items in web parts in my last blog post. There’s really not much involved in getting any of the refreshing techniques we’ve covered so far to work inside of the Excel web part other than making sure a few checkboxes are selected (and I believe they are by default). I’m not going to go into details about how to edit your web part etc, you can check my last blog post for those instructions, but here’s what you’ll want to look for if refreshing is not working in your web part for some reason:

  1. Edit your web part so that the Excel Web Access menu is open
  2. If you want manual refresh to be available, locate the “Toolbar Menu Commands” heading and make sure the “Refresh Selected Connection, Refresh All Connections” option is checked. As a side note, even if the “Type of Toolbar” is set to none, users will still be able to right click the web part and then select refresh
  3. If you want to make the automatic timed refresh work, located the “Navigation and Interactivity” heading and make sure the “Periodically Refresh if Enabled in Workbook” option is checked

BOOM! That’s it! Hopefully at this point you’re feeling confident enough to start practicing the dark art of the Excel Services workbook data refresh on your own. My fingers are starting to feel pretty tired from all of this typing (pretty sure this is my longest post yet). Let me know how things work out for you in the comments and thanks for reading.

Create a dashboard from your SharePoint list data using Excel Services and the Excel Web Access web part

If you use SharePoint in any capacity, chances are you’re storing data in SharePoint lists somewhere. Of course once you’ve got data inside lists, someone is going to want to report off of it. In this post we’re going to take a look at how you can create dashboards inside of SharePoint that pull in data from SharePoint lists using Excel Services, web part pages, and the Excel Web Access web part. Now there are several other technologies we could use like Reporting Services, PerformancePoint, PowerPivot, or Power View to create dashboards, but some of those technologies require enterprise licenses for SQL or the PowerBI functionality in Office 365. In this example I wanted to keep it simple, and use technologies that people are more likely to have. (Although you do still need to have a SharePoint enterprise license in order to use Excel Services.) Our basic approach for building this dashboard will be:

  1. Review our sample SharePoint list and test data
  2. Connect Excel to our SharePoint list and build reports
  3. Display our Excel reports via a web part page and the Excel Web Access web part

1 - Review our sample SharePoint list and test data

Let’s start off by taking a look at our SharePoint list that we’ll be pulling data from.

clip_image001

Our SalesTestList is pretty simple. It tracks the sales amount for a given client. It also tracks when the sale happened and what category the sale belongs to. The month and year columns are derived from the SalesDate column using the calculated field functionality, so users only have to enter date information once (when they pick the SalesDate). The month and year column make it easy to roll up the sales amounts for different time periods in our dashboard. The formula we used to create the calculated field columns for Month and Year are as follows:

MONTH: =TEXT(SalesDate,"mmmm")

YEAR: =TEXT(SalesDate,"yyyy")

Several other formulas for calculated columns can be found here: http://yalla.itgroove.net/2012/09/sharepoint-calculated-column-formulas/

Now that we’re familiar with our SharePoint list and test data, let’s take a look at how we can pull this data into Excel.

2 - Connect Excel to our SharePoint list and build reports

The charts we’ll be building for our dashboard will be pulling in data from our SharePoint list via the “From OData Data Feed” option in Excel. To create a single chart, that shows the sales amount by category and month, follow these steps:

  1. Open Excel 2013 and choose “Blank Workbook” as your template
  2. Click on the “Data” tab on the ribbon at the top
  3. In the “Get external data” grouping, click on the “From other Sources” button
  4. Select “From OData Data Feed” from the list of options

    clip_image002
  5. In the textbox labeled “Link or File:” you’ll need to put the URL to the listdata.svc for your site. This is done by simply taking the url to your site and appending /_vti_bin/listdata.svc to it. So for example if the SharePoint site that my list is in has a URL of https://TestSite.SharePoint.com the URL I’ll need to enter is https://TestSite.sharepoint.com/_vti_bin/listdata.svc

    clip_image003
  6. Click the “Next” button on the data connection wizard
  7. If prompted to sign in, use credentials that have access to your list
  8. Choose the list you want to connect to from the available options on the data connection wizard

    clip_image004
  9. Click the “Next” button on the data connection wizard
  10. Click the “Finish” button on the data connection wizard
  11. Choose “PivotChart” on the import data screen then click the “OK” button

    clip_image005
  12. If all has gone well, you should now be looking at a blank pivot chart in Excel. On the far right hand side you should see a window labeled “PivotChart Fields” that has a list of all the columns in the SharePoint list that you’ve connected to. To begin creating our chart, we simply need to start selecting the values that we want to display in our chart. In our example we want to show the user the sales amount by category and month, so we’ll want to select the following fields:

    SalesAmount
    CategoryValue
    Month
  13. Finally we’ll want to move (drag with mouse) the “CategoryValue” Field to the “Legend (Series)” box so that our PivotChart window in the bottom right looks as follows:

    clip_image006

Ultimately our chart should now look similar to the following:

clip_image007

Finally we’ll want to give our chart a name so we can easily differentiate it from any other charts we create. To name your chart complete the following steps:

  1. Select the chart by clicking on it with your mouse
  2. Click on the “Analyze” tab on the ribbon at the top of the screen
  3. On the far left, type your chart name into the textbox labeled “Chart Name” (in my case the name will be “SalesByCategoryAndMonth”. It’s important that you give your chart a name, as we will be referencing this name later when we display our chart in a web part)

    clip_image009

From here all we need to do is save our workbook and upload it to a SharePoint document library for users to view. It is important to keep in mind that the users viewing the workbook will need to have access to view the list that the workbook is pulling the data from if they want to refresh the chart. (You can check out my full post on configuring the data refresh here) With a little more work we could throw together additional charts in Excel and have our dashboard look something like this:

clip_image011

When/if you create additional charts against the same data source, don’t create a new data source within excel for each one. All you need to do to create additional charts using the same data source is:

  1. Highlight a blank cell in Excel
  2. Click on the “Insert” tab in the top ribbon
  3. Click the “Pivot Chart” icon
  4. Select “Use an external data source” in the pivot chart wizard
  5. Click the “Choose Connection” button in the wizard
  6. Select your existing connection from the “connections in this workbook” section

    clip_image013

Once you’ve got all the charts you need for your dashboard, simply upload your workbook to a SharePoint document library.

3 - Display our Excel reports via a web part page and the Excel Web Access web part

Now that we’ve got our Excel workbook pulling in data from our SharePoint list, and the appropriate charts built, the next thing we’re going to do is build a web part page that we can use to show the charts. Some might argue that this is somewhat unnecessary, and that the user could just open the Excel workbook to view the dashboard. That solution would definitely work, but there are a few reasons why you might want to display the charts in a web part page. For starters, when you open a full workbook in Excel services, the entire browser pretty much turns into Excel, and you lose all the SharePoint navigation links. Another reason might be that you want to show other SharePoint objects on the page in addition to the charts. Whatever your reasons are, here’s how we can go about displaying our charts on their own web part page:

First things first, let’s create a new web part page by following these steps:

  1. Navigate to the /pages directory of our SharePoint site (for instance if your site is https://TestSite.sharepoint.com your pages directory should be at https://TestSite.sharepoint.com/Pages)
  2. Click on the “Files” tab on the top ribbon
  3. Click the “New Document” dropdown
  4. Click “Page”

    clip_image014
  5. Give your page a Title, Description, and URL Name in the available text boxes
  6. Select “(Welcome Page) Blank Web Part page” as the “Page Layout”

    clip_image016
  7. Click the “Create” button

Now that we’ve create our web part page, we need to navigate to it (https://TestSite.sharepoint.com/Pages, then click on the link for the page you just created) and edit the page to add the Excel web part. To do all of that, follow these steps:

  1. With your new web part page open, click the “Gear” icon at the top right hand corner of the page
  2. Select “Edit Page” from the dropdown

    clip_image017
  3. On the new ribbon at the top of the screen click the “Insert” tab
  4. Click the “Web Part” button
  5. Select “Business Data” from the Categories window
  6. Select “Excel Web Access” from the Parts window
  7. Click the “Add” button on the far right of the window

    clip_image019

Once we click the Add button, we should see our new “Excel Web Access” web part on the screen as shown below.

clip_image021

To finish configuring the web part to show the Excel chart we created earlier, complete the following steps:

  1. In the Excel web part, click the link labeled “Click here to open the tool pane”
  2. In the new menu on the right hand side of the screen, locate the textbox labeled “Workbook” at the top of the screen. Use the adjacent browse button to select your uploaded Excel workbook
  3. In the “Named Item” textbox, type the name for your specific chart (naming your chart in Excel was covered in section 2 of this walkthrough). It’s my experience that using the browse button for this textbox usually doesn’t work. You just have to type the name of your chart in manually, so be careful and make sure you type it correctly.

    clip_image022
  4. Scroll to the bottom and click the “Apply” and then the “OK” Button

    clip_image024

With any luck, you should now be seeing your chart displayed in the web part. Keep in mind that there are a ton of options in the window we used to configure the web part to point to our chart. You can control what controls show up for the user, the size, the title etc. I’ll leave it to you to play around with all of those options. To get the options window back, simply click on the web part and look in the far right hand corner for a dropdown triangle, click it, then select “Edit web part”.

Next we’ll want to save and publish our web part so everyone can see it. To do that, follow these steps:

  1. In the upper right hand corner of the screen, click the “Save” button
  2. Once the page is saved, there will most likely be a yellow alert bar near the top of your screen, click the “Check it in” link
  3. Add any comments you want to on the “check in” window then click the “Continue” button
  4. Now you’ll most likely have a yellow alert bar near the top of your screen telling you the page is not published. Click the “Publish it” link in the yellow bar

That’s it! You’re showing SharePoint list data on your web part page. Now if you wanted to get a little more elaborate you could add the rest of your charts and mess with some of the size controls to get something like this:

clip_image026

Or even crazier still you could start to add filters/parameters to the screen, but that’s another post entirely. At any rate, we can now share our SharePoint list data in chart form. Hope this was helpful, and happy reporting/dashboarding!

How to create filters for external data columns in SharePoint lists

If you have a custom SharePoint list that uses columns that are of the type “External Data”, you may have noticed that when you’re adding a new item to the list (and more specifically when you’re picking the external data to associate with your new item), that there’s a filter dropdown when you’re browsing your external data. By default the dropdown only contains <Select Filter> and if we click on the dropdown button, we’ll see that no filters are actually available.

clip_image001

If you only have a few values available in your external content type (as seen in the image above) not having a filter isn’t really that big of a deal. However if your external content type contains a large amount of data, not having filters available can really be an inconvenience. In this walkthrough, we’ll take a look at how you can create filters for your external content types so your users are able to quickly locate the data that they need. In this particular example we’ll be using SharePoint 2013 and SharePoint Designer 2013.

This walkthrough assumes you’re already somewhat familiar with how to create an external content types using SharePoint Designer. If you’re not all that familiar with external content types you might want to check out my previous walkthrough covering the basics. You can find that walkthrough here: http://tavislovell.com/using-bcs-and-external-content-types-in-sharepoint-to-update-sql-tables/

The first thing that we’ll want to do is open the site that contains our external content type in SharePoint designer. To do that follow these steps:

  1. With SharePoint Designer open, click the “Open Site” button

    clip_image002
  2. In the “Site Name” textbox, type the full URL to your site and click “Open”. In my case I’m going to create the external list on a sub-site named “TavisBIDemo”

    clip_image003

Once connected you should see a screen similar to the following:

clip_image005

Now that we’ve got our site open in SharePoint designer, the next thing we want to do is edit the “Read List” operation for our external content type. The read list operation is where we’ll actually need to add our filter. To edit the read list operation and add a filter complete the following steps:

  1. In SharePoint Designer, click on “External Content Types” under the “Site Objects” window
  2. Double click the existing External Content Type that you want to add the filter to
  3. Open the existing “Read List” operation by double clicking on it under the “External Content Type Operations” window

    clip_image006
  4. On the “Operations Properties” screen of the wizard that comes up, click next
  5. You should now be on the “Filter Parameters” screen. This is where we’re going to be configuring our filters. In our case, we’re going to make a filter for the “First Name” column that exists in our ECT dataset. Click the “Add Filter Parameters” button

    clip_image008
  6. In the “Properties” section on the right hand side of the screen. Use the “Data Source Element” dropdown to choose the column you want to create the filter for (in my case, “FirstName”)

    clip_image010
  7. In the same properties window, click the “(Click to Add”) link next to the “Filter:” label
  8. In the “New Filter” textbox, type a name for you new filter (This will be the text that the users see when choosing the filter, so you’ll want to name it something meaningful
  9. You can adjust the additional “Filter type” and “Operator” dropdowns to suit your particular need. In my case I will also be checking the “Ignore filter if Value is:” option and selecting “NULL”

     clip_image011
  10. Click “OK” on the “Filter Configuration” window
  11. Since I chose the “Ignore filter if Value is:” option and “NULL” for my filter, I’m also going to set the default value for my filter to NULL. This will cause all possible values to show up if the filter has not been applied

    clip_image013
  12. Click the “Next” Button on the “Filter Parameters” window
  13. Click the “Finish” Button on the “Return Parameters” window
  14. Save the save button at the top left hand corner of SharePoint Designer to save your changes

That should do the trick. If we go back to our list with the ECT (refresh the browser) and bring up the picker for our ECT data we should see our “First Name” filter available. Not only that but all values should be listed since we said to ignore the filter if the value was null, and the default value is null. If we type a value into the filter and click the search button, we should see our results filtered as seen below.

clip_image014

That should do it. Congratulations you’ve created a filter for your users to quickly locate the data they need when picking from an ECT! Hope this helps.

Why can’t I sort or filter the external columns in my list?

The scenario I’ve seen a few times is that you have a list that you’ve added a few “external data” columns to. Everything is looking great until you click on the column header of one of the external columns to sort or filter it, and……nothing. The menu doesn’t come up when you click on the external column, and the header doesn’t even highlight when you scroll over it.  (click picture for detail)

clip_image002

The good news is that this can be fixed. The problem seems to be that the “Type” property of the external column is set to “Note” instead of “Text”. The bad news is that the only way I know of changing the type property for the external columns involves downloading and running some software on your SharePoint server.

The program you’ll need to download is called “SharePoint Manager” and can be downloaded for free from the codeplex website at http://spm.codeplex.com/

Once you run the program you should be presented with a screen that looks like this:

clip_image004

I must caution you to be very careful in SharePoint manager and not start randomly changing things as you will more than likely break something.

What we need to do is navigate to the particular list and columns that are not filtering and sorting. In my case the list exists on a sub-site and drill down path looks something similar to this:

[Farm] » [Services] » [SPWebServices] » [Web Applications] » [your web application] » [Sites] » [your site address] » [AllWebs] » [Your subsite)] » [Lists] » [Your List] » [Fields] » [the field not filtering/sorting]

With the problematic field highlighted, locate the “Type” property in the Properties window. It will most likely be set to “Note”. Change it to “Text”.

clip_image006

Repeat that process for any additional ECT columns that aren’t sorting, then click the save icon up at the top left hand corner.

*Note, do not change the primary ECT column (usually has a “Type” of “Invalid”). If you change that value to text, it will break your list…or at least it did for me when testing.

After making the changes, you should now be able to filter on the ECT columns that were previously not working.

2014-07-17 14_59_36-Windows 7 x64 - VMware Workstation

That’s all for this post, hope this saves someone some time.

Using BCS and External Content Types in SharePoint to Update SQL Tables

Occasionally I get a request to setup an external list in SharePoint that links back to a SQL Server table. Depending on the scenario, the goal might be to just display data from a SQL table in a SharePoint list so (so users can only view the data) or they might want the ability to edit the data in the list and have those edits pushed back to the SQL table. In either case, setting up an external list that pulls/edits data from a single table isn’t all that difficult, and in this walkthrough we’ll take a look at how it’s done.

This walkthrough uses SQL Server 2012, SharePoint 2013, and SharePoint Designer 2013. If you don’t have SharePoint Designer, you can download it here: http://www.microsoft.com/en-us/download/details.aspx?id=35491

Almost all of the work we’ll be doing will be inside SharePoint Designer. With that in mind let’s open up the designer and connect to the site we want to add our external list to. To open your site in SharePoint Designer, follow these steps:

  1. With SharePoint Designer open, click the “Open Site” button

    clip_image001
  2. In the “Site Name” textbox, type the full URL to your site and click “Open”. In my case I’m going to create the external list on a sub-site named “TavisBIDemo”

    clip_image002

Once connected you should see a screen similar to the following:

clip_image004

Now that we’ve got our site open in SharePoint Designer, we’re ready to start creating our external list. Creating an external list boils down into 4 main steps as follows:

  1. Create an external Content type
  2. Create a data source for the external content type
  3. Create operations against the data source (read/edit etc)
  4. Create the list and forms

STEP 1 - Create an external Content type

The first thing we’ll need to do is to create a new external content type in our site. To do that, follow these steps:

  1. In the Site Objects pane on the far left, click on “External Content Types”

    clip_image005
  2. On the ribbon at the top of the screen, click the “External Content Type” button

    clip_image006
  3. You should now have multiple new sections in the center of your screen. Locate the section titled “External Content Type Information” and click on the “New external content type” link next to “Name” and enter a new name for your external content type. (I’ve named mine “TavisTestBCS” in the image below)

    clip_image007

Our external content type has now been created, although SharePoint designer won’t allow us to save it until we define operations in step 3. That’s ok though, just don’t close down SharePoint designer

STEP 2 - Create a data source for the external content type

The next thing we need to do is create a data source that points to the SQL Server table that we’re wanting to surface via our external list. To create the data source follow these steps:

  1. Click the link titled “Click here to discover external data sources and define operations” (shown at the very bottom of the previous image)

    Note, after you click the link in step 4 your screen will change. This is the “Operations Design View” screen. To toggle between this screen and the previous screen we were on, you can use the “Summary View” and “Operations Design view” buttons located at the top left corner of SharePoint Designer.
  2. Click the “Add Connection” button
  3. In the External Data Source Type Selection window choose “SQL Server” as the Data Source Type
  4. Enter the Database Server name for the SQL Instance you want to connect to
  5. Enter the Database Name for the SQL database that contains the table you want to connect to
  6. In the “Name (optional)” textbox, enter a name for this datasource
  7. Choose “Connect with Impersonated Windows Identity” from the available authentication options
  8. Enter the Secure Store Application ID of an unattended account that has access to the database and table you want to connect to.

    To complete step 8, you need to make sure that your Secure Store Service Application in SharePoint has been configured, and has an unattended service account that has the appropriate permissions to access the database and table you’re trying to connect to. The secure store is accessed through the SharePoint Central Administration tool, so you may need to contact your SharePoint administrator. Once that account is in place, you just need to grab the application ID for that account inside of the Secure Store.

    clip_image008
  9. Click “OK”

Once the data source has been created, you should see it in the “Data Source Explorer” window as shown in the image below.

clip_image009

STEP 3 - Create operations against the data source (read/edit etc)

Now that we’ve got our data source create, it’s time to create some operations for our external content type. There are several operations available, however in the interest of simplicity for this example we’re going to create them all at once accepting most of the default options. To create the necessary operations follow these steps:

  1. In the “Data Source Explorer” window, click the “+” to see available folders
  2. Click the “+” on the “Tables” folder to see the available tables
  3. Right click on the table you want to show/edit in your list and then click “Create All Operations” to start the operations wizard

    clip_image011
  4. Click “Next” on the initial screen

     clip_image013
  5. Click next on the “Parameters” window

     clip_image015

    At least one of the columns in your table should be a marked as “Map to Identifier” as shown in the image above. If the table you’re creating the operations on already has a primary key set in SQL Server, then that primary key column will most likely already be listed as the identifier. If not, you will need to determine which column in your data should be the unique identifier/primary key and mark it as the identifier by placing a check in the “Map to identifier” field.
  6. Click the finish button on the “Filter Parameters” window

     clip_image017

The filter parameters window can be used to allow users to filter the data shown in the list. In our simple example we’re not using any filters/parameters, but it is highly recommended if your table contains a large amount of data.

Once you click finish, you should be able to see all the operations that were created under the “External Content Type Operations” section of the screen. (Shown highlighted in yellow below)

clip_image019

STEP 4 - Create the list and forms

Now we’re in the home stretch! All that’s left to do is create the actual list and list forms. To finish creating your external list, follow these steps:

  1. In the ribbon at the top of your screen, click the “Create Lists & Form” button

     clip_image021
  2. If asked to save your external content type, click yes
  3. In the “List Name” textbox type in whatever you want your list to be called
  4. Place a checkmark in the box labeled “Create InfoPath Form”

    clip_image022
  5. Click the “OK” button

That should do it! If you browse out to your SharePoint Site and look under the “Site Contents” you should now see your new external list.

clip_image023

For the sake of brevity we skipped over some of the more advanced stuff like filters, or showing data elements from multiple tables, or even calling SQL Stored procedures to update, or insert rows into your table(s). So there’s still more to learn, but this walkthrough get you up and running with a simple external content type connected to a single SQL Server table. Hope you found it helpful. Let me know in the comments.