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:
- Excel Services â Key Scenarios
- Excel services Part 5: All About âInteractivityâ
- Excel Services Part 11: Excel Server, SharePoint, and Dashboards
- Using Parameters In Dashboards
We have made many improvements to the EWA in SharePoint 2010, and they can be lumped into three major buckets:
- Support for more existing, as well as new Excel 2010 Features
- Making the user experience even more familiar to users of Excel
- 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:
- External data periodic refresh
- New for 2010, the ability to type into the grid (when the Office Web Apps are installed)
- Miscellaneous other improvements
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:
- You could increase the number of rows and columns per âpageâ using the web part properties
- Your users could use the âpaging buttonsâ located at the top right of the EWA
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:
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.
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.
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:
- The ability to move around the grid using the keyboard
- The ability to select multiple cells, rows or columns
- The ability to copy from the EWA and paste into another application
- The ability to resize rows and columns
- Support for many Excel keyboard shortcuts
- 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:
- 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
- Open the web part page in SharePoint Designer
- Right-click on the EWA and select âAdd Connectionâ
- In the Web Part Connections Wizard that will appear, select âGet Values for Multiple Parameters Fromâ
- Select the source web part
- Map the values from the source web part to your defined parameters in the EWA
- 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.
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.