[Client-side Workaround] SharePoint 2010 Excel Services cannot Automatically Refresh Data when using SharePoint Lists as a Data Source
So far as I can tell, Excel Services is not capable of automatic data refreshes when using SharePoint Lists as a data source. If you're looking for this functionality, you have two choices: PowerPivot (best) or scheduled client-side data refreshing (ghetto).
In order for Excel Services to communicate with other data sources to do data refreshes without your intervention, it relies on SharePoint's SSO Service, the Secure Store. There are three different methods of connecting - Unattended Service Account, Embedded Connections, & External Data Connections - and I tried all three of them in various configurations but kept running into Access Denied errors. I even tried some of that voodoo magic we were taught growing up and it didn't even help.
I finally stopped trying after reaching a dead end on the Excel Services data refresh flowchart then further confirming said dead end within the ULS which informed me that there was an "Unauthorized attempt to access session by user <username>. Session belonged to user <username of user that started the session> (Event ID: 2011)." Seems that SharePoint does not like Excel Services asking to see its lists.
To confirm, I looked up the error in the SharePoint Technical Reference and it said
Excel Services Application maintains individual user sessions. Sessions maintain state related to workbook calculations, parameters that a user sets, interactions that a user has with a live workbook, and data returned from a data source as a result of a data refresh operation. Sessions are maintained per user per workbook, and can contain private data and information. Sessions are available only to the user that started the session. The issue in this case is that a user who did not start a session attempted to access data from that session.
(Emphasis mine)
FFFFFINNNNEEE, Microsoft. Until we get PowerPivot implemented, I'll just make PowerShell do my dirty work. The script below, which you can schedule, assumes the following:
- your workstation will be on most of the time
- you have Excel 2010 Professional installed
- you can use Windows Explorer view of libraries
- versioning is turned on and limited to a certain number of versions
(this makes 24 versions a day) - that you want EVERY Excel file in a library to be refreshed
- you have write permissions to all excel files
- you have access to do so and the files are checked in
So, it goes to the document library, searches recursively for all Excel files, opens them up, checks them out, opens them, refreshes the data connections, saves the file then checks them in with a new version. K, here goes:
For Document Libraries that require checking out files prior to editing them:
# This is the location of the document library that has the Excel files
# You must have WebDAV enabled on the server (which is default, I think)
# and the webclient service enabled and running on your workstation.
$library = "\\sharepoint.ad.local@SSL\DavWWWRoot\Shared Documents"
# Start Excel (it will be invisible unless you do $excel.visible = $true)
$excel = new-object -comobject Excel.Application
# Give Excel time to open or it errors inconsistently
Start-Sleep -s 3
$excelfiles = get-childitem $library -recurse -include "*.xls*"
foreach ($file in $excelfiles)
{
$workbookpath = $file.fullname
if ($excel.workbooks.canCheckOut($workbookpath)) {
# open the worksheet and check it out
$excelworkbook = $excel.workbooks.Open($workbookpath)
$excelworkbook = $excel.workbooks.CheckOut($workbookpath)
# Don't ask cuz I don't know (yet). You have to open it again.
$excelworkbook = $excel.workbooks.Open($workbookpath)
# Refresh all the pivot tables with the new data.
$excelworkbook.RefreshAll()
# Save and Check it in
$excelworkbook.Save()
$excelworkbook.CheckInWithVersion()
}
}
$excel.quit()
And this code is for libraries that do not require check-out:
# This is the location of the document library that has the Excel files
# You must have WebDAV enabled on the server (which is default, I think)
# and the webclient service enabled and running on your workstation.
$library = "\\sharepoint.ad.local@SSL\DavWWWRoot\Shared Documents"
# Start Excel (it will be invisible unless you do $excel.visible = $true)
$excel = new-object -comobject Excel.Application
# Give Excel time to open or it errors inconsistently
Start-Sleep -s 3
$excelfiles = get-childitem $library -recurse -include "*.xls*"
foreach ($file in $excelfiles)
{
$workbookpath = $file.fullname
# open the worksheet
$excelworkbook = $excel.workbooks.Open($workbookpath)
# Refresh all the pivot tables with the new data.
$excelworkbook.RefreshAll()
# Save and Close
$excelworkbook.Save()
$excelworkbook.Close()
}
$excel.quit()Wanna schedule this script hourly? schtasks /create /tn RefreshData /tr "powershell -noninteractive -nologo -command C:\scripts\refresh.ps1" /sc HOURLY. Don't forget to limit Versions in your library as this script creates 24 versions of each workbook per day.
Also, this script is 10x's faster on Windows 7 if you disable the use of the Web Proxy Autodiscovery Protocol (WPAD). IE -> Tools -> Internet Options -> Connections -> LAN Settings -> Uncheck Automatically Detect Settings.
And finally, if you find that your files are opening Read-only, make sure all instances of Excel are closed, then delete your Microsoft Office cache in %userprofile%\local settings\temporary internet files\content.mso\.
SharePoint 2010: Creating Dashboards and Charts for SharePoint Lists using PerformancePoint and Excel Services from Start to Finish
I really thought making SharePoint Lists into pretty graphs and charts in SharePoint would be far more straightforward than it's turned out to be. Recently, I was given a project where I had to do just that and while I read through about 30 SharePoint BI books, none really gave a straight answer on which of the various SharePoint reporting technologies I should use. It seems I had a choice between:
- Reporting Services
- SharePoint's Reporting Services add-on
- A near-useless SharePoint Chart Web Part
- Excel worksheets
- Excel Web Services
- PerformancePoint Dashboards w/ a SharePoint list data source, SQL Server Analysis Server data source, Excel Workbooks data source, and/or Excel Services as a data source
So overwhelming.
Initially, I decided PerformancePoint Dashboards and PowerPivot would best suit my needs. PowerPivot is an Excel Add-on + a SharePoint Add-on that basically surfaces Analysis Services for reporting and as awesome as it is, the farm I'm using doesn't currently support it (setting up PowerPivot is a bear, I'll admit.) The good news is that it will be, and when it is, it'll be SQL Server 2012's version that comes with Power View and PowerPivot. Whattt!
Ultimately, I decided that I'm going to accomplish my task using Excel Services and PerformancePoint Dashboard Designer. This solution addresses automatic refreshing so your reports will stay (mostly) up to date. For complete beginners, this is basically how it works: You use export a SharePoint list query as a data source in an Excel spreadsheet, use Insert->PivotChart to work with the data, publish those specific charts and tables back to SharePoint using Excel Services, access those published items using PerformancePoint Dashboard Designer (which you can't download from the Internet -- you download the ClickOnce file from your SharePoint page), then publish a dashboard and tada!
(End User) Pre-Configuration
- Obtain access to a SharePoint 2010 server running Excel Services and PerformancePoint Services
- Update: Automatic data refreshing doesn't work with Excel Services when using SharePoint lists as a data source. For that, you'll need a client side script or PowerPivot.
- Enable PerformancePoint Services Site Features and SharePoint Server Enterprise Site features on your site (Site Actions -> Site Settings -> Manage Site Features) if they aren't already.
- Add some items to the Tasks list that comes built-in to Team Sites. In the example below, I have 108 items, but you won't need that much; I'm just working with live data.
- Create a new Business Intelligence Site. Site Actions -> New Site -> Business Intelligence Center
Working in Excel
- In the SharePoint Task List you just populated, click List in the ribbon bar, and Export to Excel. You'll be prompted to download a lil .iqy file to your desktop.
- Open a new Excel Worksheet
- Insert -> PivotTable
- When prompted, click Use an External Data Source
- Choose Connection -> Browse for more... -> Select the .idq file you just downloaded
- This is important: Even though this connection is to a SharePoint list and you'd think the updates would be instantaneous, you have to explicitly set the connection to refresh in order to keep your chart up-to-date.
Let's do this by clicking "Properties" -> Refresh Control -> Enable background refresh, Refresh Every 60 minutes, Refresh data when opening file.Don't click any of those. If you need data refreshes, use PowerPivot or this client-side script. - Click all those boxes, then OK. If you ever need to get to this screen again, you can find it at Data -> Existing Connections -> Right Click -> Edit Connection Properties.
- A new toolbox will appear on the right. Drag "Status" to "Row Labels" and the "E" looking thing. This will setup the data to make a chart that counts the number of Completed tasks, New Tasks, Tasks Assigned to Someone Else.. and so on.
- Yay, now click PivotChart (under PivotTools -> Options)
- Choose a chart. I'm just gonna go with the default by clicking OK. Now, your spreadsheet should look something like this, with a PivotTable and PivotChart:
- See how it says "Chart 1" in the upper left hand side? That's the default Chart name. Let' change that by clicking on the PivotChart then clicking PivotChart Tools -> Layout -> PivotChart Name:
- I'm going to name mine Task Status Chart
- K, now click in the PivotTable area, and under "PivotTable" at the top, click Options. On the left, you'll see where you can change the PivotTable Name. Do that if you want.
Publish Your New Chart (Don't just Save)
- Save the Excel file to SharePoint
- File -> Save and Send -> Save to SharePoint -> Set Publish Options
- Publish Options -> <b>Select the individual items</b> by clicking on "Entire Workbook" then selecting All Charts -> OK
- Go ahead and check the PivotTable (notice we didn't change that PivotTable's name so it's still the default PivotTable1) options, too.
- Save your file to SharePoint by browsing for a location or saving it to a library that's already been saved.
Show Published Excel Chart in a PerformancePoint Dashboard
- Browse to the Business Intelligence site you created in the Pre-reqs.
- Hover over Create Dashboards then click Start using PerformancePoint Services.
- You'll then see a page that says "Run Dashboard Designer". Click that.
- Your computer will install about a 16mb ClickOnce application and this is where you'll always come to launch it.
- This interface is a little confusing. All you have to know right now is that you'll be using two item things -- Other Report and Dashboard.
- Let's first get the Report item: Click on PerformancePoint Content on the left hand side to make the "Create" menu available. Then Create -> Other Reports -> Excel Services
- Use the selection tools to navigate down to your spreadsheet, and ultimately, your published items. Below, I'll select Task Status Bar Chart.
- Name your Report in the Properties Tab. You can now click Save or move onto the next step.
- Now we're going to create a dashboard. Click the Dashboard Icon in the upper left hand corner.
- Select your layout. In the example below, I went with two panes, just because.
- When the dashboard appears, you'll see "Details" on the right hand side.
- Expand Reports -> PerformancePoint Content -> Drag the report to any pane.
- While we could just click the Office ball in the upper left hand corner and "Deploy", we need to adjust the size of the chart real quick
- Click the down arrow on the report within the pane, and select Edit Item -> Size -> Set to Auto-size Width & Auto-size Height.
- Now it's time to Deploy your Dashboard! Click the Office ball thing -> Deploy
- Drill down to select your Dashboards library within your BI site.
- The new dashboard will load automatically.
- Congrats, you've done it! Time to bask in the glory of your work.
You may run into administrative errors along the way (I know I did), but that is out of the scope of this blog post. Next up? Accomplishing the awesomer version of this using PowerPivot. Speaking of, Microsoft could really do a better job of marketing PowerPivot as an all-around great reporting tool and not just something amazing for large data sets. This tool is super useful even for smaller data sets. I'll probably be blogging about that sometime soon. More people should know!
[Solved] Annoying Scrollbars in Excel Web Access Web Parts and PerformancePoint w/ Excel Services
Recently, while using Excel Services to access PivotCharts and PivotTables within Excel Web Viewer Web Parts and PerformancePart Dashboards, I encountered vertical and horizontal scrollbars that would not go away, no matter how much I tried resizing.
god don't like ugly
To accomplish the above, you have to do two things.
- In Dashboard Designer, set the sizing of your Excel report to auto.
- This is what got me: published PivotCharts and PivotTables should not be on a dedicated sheet in Excel. They must be embedded as an object within a spreadsheet.
everything is right in the world
If you continue to experience scroll bars, resize your chart within Excel and republish. From there, you may need to modify the size of your web part in its properties.
[SOLVED] PowerPivot for SharePoint 2010: An error occurred connecting to this data source.
I'm currently working on a project where I need to quickly make pretty graphs and charts for data in small but important SharePoint lists. After looking through a number of books and consulting with my SharePoint buddies, PowerPivot for SharePoint 2010 seems to be the best tool for the job.
Getting it to work hasn't been easy, I believe because there's so many components that come into play. You've got the PowerPivot add-on for Excel 2010, the PowerPivot service for SharePoint, Excel Services for SharePoint, SQL Server Analysis Services and PerformancePoint Dashboards. Here's how it all started...
- I was assigned to make pretty charts for a site
- I installed the Excel + the PowerPivot add-on
- Exported the SharePoint list
- Opened the exported list, enabled Data Connections, and made a PowerPivot chart thing
- Checked to make sure PerformancePoint & Excel Services features were enabled
- Created a BI site
- Saved the Excel file to SharePoint
- Launched Dashboard Designer using the BI page
- From there, I attempted to access the cube in a PerformancePoint dashboard, per Microsoft's instructions
- Run Dashboard Designer
- Create
- Data Source
- Analysis Services
- Use the following connection: http://sharepoint/Documents/localpivot.xlsx
I left the authentication as default and received the following errors:
- You do not have permissions to see this data or the server is unavailable. Additional details have been logged for your administrator. Contact the administrator for more details.
- An error occurred connecting to this data source. Please check the data source for any unsaved changes and click on Test Data Source button to confirm connection to the data source.
Supposedly, installing ASADOMD10 from the SQL Server 2008 R2 Feature Pack would fix the issue, but it didn't. This farm is running on my local workstation, so I check Event Viewer and see: The Unattended Service Account "AD\yo.momma" does not have access to the server specified by the data source connection string. But.. but.. I'm the administrator of this whole thing. I 0wn this farm, what gives? So I check the IIS log:
2012-03-06 18:52:33 127.0.0.1 POST /_vti_bin/client.svc/ProcessQuery - 80 - 127.0.0.1 - 401 2 5 0
2012-03-06 18:52:33 127.0.0.1 POST /_vti_bin/client.svc/ProcessQuery - 80 - 127.0.0.1 - 401 1 2148074254 0
2012-03-06 18:52:33 127.0.0.1 POST /_vti_bin/client.svc/ntlm/ProcessQuery - 80 AD\yo.momma 127.0.0.1 - 200 0 0 343
2012-03-06 18:52:33 127.0.0.1 POST /_vti_bin/PPS/PPSAuthoringService.asmx - 80 - 127.0.0.1 Mozilla/4.0+(compatible;+MSIE+6.0;+MS+Web+Services+Client+Protocol+2.0.50727.5448) 401 1 2148074254 0
2012-03-06 18:52:33 127.0.0.1 POST /_vti_bin/PPS/PPSAuthoringService.asmx - 80 AD\yo.momma 127.0.0.1 Mozilla/4.0+(compatible;+MSIE+6.0;+MS+Web+Services+Client+Protocol+2.0.50727.5448) 200 0 0 15
2012-03-06 18:52:33 127.0.0.1 POST /_vti_bin/PPS/PPSAuthoringService.asmx - 80 - 127.0.0.1 Mozilla/4.0+(compatible;+MSIE+6.0;+MS+Web+Services+Client+Protocol+2.0.50727.5448) 401 2 5 0
2012-03-06 18:52:41 172.16.0.125 POST /_vti_bin/PowerPivot/redirector.svc/ DataSource=%2FDocuments%2Flocalpivot.xlsx 80 - 172.16.0.125 ADOMD.NET 401 2 5 0
2012-03-06 18:52:41 172.16.0.125 POST /_vti_bin/PowerPivot/redirector.svc/ DataSource=%2FDocuments%2Flocalpivot.xlsx 80 - 172.16.0.125 ADOMD.NET 401 1 2148074254 0
You can see the myriad 403 Forbidden -- maybe it's some messed up NTLM permissions? So I check /_vti_bin/PowerPivot/ and, voila! The files don't exist.
That's not a Forbidden, that's really a 404 Not Found. I never even installed PowerPivot for SharePoint in the first place. You can enable references to it around SharePoint if I recall, but the service is part of the SQL Server install. Well, damn.
I decided to start entirely from scratch by removing my workstation from the farm, and uninstalling the two versions of SQL Server, Express and Denali CTP, on my workstation. Then I setup a new farm using SQL Server's Installation interface (I didn't even realize I could do that, bad admin!) I won't lie..the install was so clean -- it created the local Central Admin instance as well as a new Site Collection with all the PowerPivot features already enabled. Delicious.
If the above was not your issue, you may need to:
- Make sure SQL Server PowerPivot is setup as a Service Application: Central Admin -> Manage Service Applications
- Make sure it's associated with the web app: Centeral Admin -> Application Management -> Configure Service Application Associations -> Application Proxies
- Make sure the PowerPivot Service has started: Central Admin -> Manage services on server
Also, check your Event Viewer logs, that's been helpful for me.
About
-
We are IT consultants
who grew up living and loving life
in Cajun Country.
Authors
Categories
- Oracle
- OS X & iDevices
- PowerShell
- Security
- SharePoint
- SQL Server
- VBScript
- Virtualization
- Windows
- Wordpress
