Back
Excel

Excel Services in SharePoint 2010 Dashboard Improvements

Thanks to Dan Parish for putting together this post.

One of the great things about Excel Services is the Excel Web Access (EWA) web part. This web part allows you to render entire workbooks, or just portions of a workbook (for example a chart or a Table) in a SharePoint dashboard page. For a high level overview of what you can do with the EWA in SharePoint 2007, take a look at some of our existing blog entries:

We have made many improvements to the EWA in SharePoint 2010, and they can be lumped into three major buckets:

  1. Support for more existing, as well as new Excel 2010 Features
  2. Making the user experience even more familiar to users of Excel
  3. Support for additional part-to-part scenarios

#1 is very broad and so has been and will continue to be covered by other blog entries. This entry will specifically deal with improvements to the dashboarding experience: numbers 2 and 3.

Making the user experience even more familiar to users of Excel

In SharePoint 2007, the EWA’s rendering of workbooks was virtually identical to that of the Excel client’s. However, actually using the EWA wasn’t always the same. While all the interactive functionality (sorting, filtering, drilling up/down in a PivotTable) worked the same as Excel, some fundamental things like scrolling and periodic data refresh worked quite differently. In SharePoint 2010, we’ve addressed all of these issues by making the EWA AJAX based, which allows for more user interaction to happen within the browser itself, thereby allowing for a more fluid, Excel-like experience. Specifically, the following areas have been revamped:

  1. Scrolling
  2. External data periodic refresh
  3. New for 2010, the ability to type into the grid (when the Office Web Apps are installed)
  4. Miscellaneous other improvements

Scrolling

In SharePoint 2007, the EWA rendered the grid in ‘pages’, which were by default 20 columns by 75 rows. If you had more data than that you had two options:

  1. You could increase the number of rows and columns per ‘page’ using the web part properties
  2. Your users could use the ‘paging buttons’ located at the top right of the EWA

image

In SharePoint 2010, we got rid of this model and made our scrolling work just like you’d expect: like Excel’s. The paging buttons are no more, and since the grid isn’t rendered in ‘pages’ anymore, the associated web part properties have also been removed.

In the EWA in SharePoint 2010, you can scroll around your entire used range (and beyond) quickly and easily, whether your range is ten rows or a million. Additionally, due to how scrolling has been implemented, the size of the range of data you want to display in the EWA has no impact on the amount of time the page takes to load. The EWA always just renders what it is you are actually looking at in order to keep loading and rendering times to a minimum.

Periodic external data refresh

The EWA in SharePoint 2007 supported periodic external data refresh, however it didn’t work the same as it does in Excel. Because in the EWA in SharePoint 2007 every action (including refreshing external data) caused a postback within the EWA, when external data was refreshing users were unable to continue to view their data or continue their work. This meant that web part page authors had two choices: they could set the EWA to refresh automatically, interrupting users every time it did so, or they could have the EWA prompt the user via a special notification bar for when they wanted to refresh like so:

image

In SharePoint 2010, because of the new AJAX grid, we’ve removed the need to even have to make this decision. Web part page authors can still decide if they want the EWA to periodically refresh data at all, but if they choose to allow it they’ll find that there is no more web part property to choose whether or not to notify the user. This is because in SharePoint 2010, the EWA can refresh external data in the background just like the Excel client. So, whenever it’s time for new data to be fetched, it will just appear on screen without affecting the user’s workflow or asking them to make a choice. It’s all automatic and seamless, just like it was intended to be.

New for 2010, the ability to type into the grid (when the Office Web Apps are installed)

One of the most requested features for the EWA in SharePoint 2007 was to let users type into the grid. Parameters, while good for some things, aren’t ideal when users need to enter many values or when the spreadsheet is authored in a way that encourages users to enter data into specific regions.

So, for 2010, we’ve added this capability as well. If the Office Web Apps are installed, you’ll see a new web part property in the Excel Web Access web part tool pane called ‘Typing and formula entry’. When selected, users will be able to type and enter formulas directly in the EWA.

image

This functionality allows for the creation of rich, interactive, what-if models using the EWA, and just like before, these changes are per-user, and will never affect the underlying workbook.

You may be wondering if there is a way to have the EWA save changes to the underlying workbook, but the answer is no. The EWA is all about allowing users to consume and explore models created in workbooks, while preserving the underlying workbook itself. Stay tuned for future posts however on ways to accomplish something similar once you have the Excel Web App installed using our new JavaScript Object Model.

Miscellaneous other improvements

There are a whole host of other improvements to the user experience within the EWA. Some of the most requested features that have been added include:

  1. The ability to move around the grid using the keyboard
  2. The ability to select multiple cells, rows or columns
  3. The ability to copy from the EWA and paste into another application
  4. The ability to resize rows and columns
  5. Support for many Excel keyboard shortcuts
  6. And more!

While we’ve kept the rendering just as good (and in many cases made it better) as in SharePoint 2007, we’ve done a lot of work this release to really make the overall experience of using the EWA more familiar, while at the same time opening up new scenarios that simply weren’t possible before.

Support for additional part-to-part scenarios

In SharePoint 2007, the EWA could connect with virtually all SharePoint web parts (as well as custom built web parts) using the IFilterValues web part interface. This worked great in many scenarios, as this interface allows a web part to send a value (or set of values) into a single parameter in the EWA. However, we frequently got asked if there was any way to send multiple values from a single web part into multiple parameters in the EWA, and the answer was unfortunately no.

In SharePoint 2010, we’ve removed this limitation by supporting the IWebPartParameters interface. This interface is also supported by virtually all SharePoint web parts, and is also documented on MSDN to allow for customers to build their own web parts using it as well. This interface allows one web part to send multiple values into multiple parameters in the EWA.

To setup a connection using the multiple-to-multiple capabilities of this interface, you will need to use SharePoint Designer, and then you can follow these simple steps:

  1. Create your web part page either on the server using SharePoint, or directly in SharePoint Designer, including both the EWA web part and the web part you would like to have send values into the EWA
  2. Open the web part page in SharePoint Designer
  3. Right-click on the EWA and select ‘Add Connection’
    image
  4. In the Web Part Connections Wizard that will appear, select ‘Get Values for Multiple Parameters From’
     image
  5. Select the source web part
  6. Map the values from the source web part to your defined parameters in the EWA
    image
  7. You’re done!

Remember that in order for the EWA to accept values, you must first define parameters. See the Using Parameters In Dashboards blog entry for details on how to do that.

Conclusion

As you can see, we’ve greatly expanded the capabilities of the Excel Web Access web part in SharePoint 2010. It’s now easier to use, more Excel-like in how it functions, and it opens up new dashboarding possibilities with its support for typing and its support for the IWebPartParameters interface.

Please take a look at some of the previous blog entries related to sparklines and slicers for some of the new functionality offered in the Excel client that is also supported in the EWA, and stay tuned for more blog entries detailing the improved support for existing Excel functionality as well.

If you have any specific scenarios you are wondering if we have added support for, please ask your question in the Comments section and I’ll be sure to respond.