Disclaimer: This post is based on notes taken while watching a conference session. For that reason, it may contain incorrect information or data that I might have misunderstood. Also, the product it refers to was not available yet at the time of the writing, thus, not allowing me to validate the present information.
This was the third session I attended on the second day of the SharePoint Conference 2009, and it was given by Pej Javaheri and Steve Tullis, both from Microsoft.
The first cool thing about this session was that the slide deck was presented using PowerPoint Web App. Talk about eating your own dog food 🙂
Most of the session was one big demo showing off an overview of some of the top new features on Excel 2010 and Excel Services 2010, which I will describe below.
Power Pivot
Power Pivot is the new name of what was previously known as project “Gemini”. It’s a new capability that the SQL Server team has worked on and brought to Excel, which allows deeper analytics on very large data sets. One other nice thing is that it can be made available through SharePoint 2010.
You can find more information about this on: http://www.powerpivot.com.
Business Intelligence Center
SharePoint 2010 includes an new site template called Business Intelligence Center which is an improvement over the previous Reports Center site template. This new template contains extensive guidance to help you start using the new BI components offered by SharePoint 2010.
Excel 2010: Pivot Charts and Pivot Tables
Excel 2010 introduces Pivot Charts. Pivot Charts are like normal charts connected to the same source data as the Pivot Table, however now you can drill down on hierarchies and select which members you want to see, right on the chart object, instead of having to do that on the associated Pivot Table, and your selections will affect both the Pivot Chart and the Pivot Table.
Both on Pivot Charts and Pivot tables, on the field selection popup menu, when drilling down a hierarchy to select a member, you will now find a search box to help you search an item instead of having to browse through all the hierarchy to find it.
Another new thing is the ability to define named field sets and use them in Pivot Tables and Pivot Charts. These named sets are stored inside the workbook and can be reused by other users.
In a Pivot Table, you can perform a series of calculations by using a new feature called Show Value As. This new feature allows you change the way you view a field by selecting from a list of predefined calculations, such as:
- % of Grand Total
- % of Row Total
- % of Column Total
- % of Parent Row Total
Before you either had to perform the calculations outside the Pivot Table or put these as cube formulas.
Excel 2010: Conditional Formatting
A few improvements were made to the conditional formatting capabilities of Excel, and now Data Bars support positive and negative values. Also, new Icon Sets have been introduced.
Excel 2010: Slicers
Visual Slicers are a new Excel object that allows users to filter the data on Pivot Charts and Pivot Tables with a single click. Slicers show you a set of members of a hierarchy as buttons which you can press to filter data. Additionally, members that have no available data will be grayed out, giving you a quick visual understanding of which members have data and which have not.
Excel 2010: Sparkline
Also an addition to Excel are the Sparklines. A Sparkline is like a mini chart that illustrates a trend in a set of data. Excel comes with three types of Sparklines:
- Line
- Columns
- Win/Loss
The Sparkline object is treated like any other cell content (such as a formula) which means it can be copied, pasted and expanded.
Excel 2010: What-If Analysis
When accessing external data (a SSAS cube) to build a Pivot Table, you can now use What-If Analysis to write back to the data source.
Excel Services 2010: More Workbooks and Client Fidelity
Excel Services (Excel Web App) will now try to open all workbooks in View Mode even if they contain unsupported features. In the previous version, Excel Services wouldn’t even open the workbook showing an error message which wasn’t much help to solve the problem.
In Edit Mode, however, it’s a different story but the list of unsupported features was not shown. I can tell you what I saw from the demos, but this is not an extensive list:
- Images are supported
- Charts, Sparklines and Slicers are supported
- Pivot tables are supported
- Grouping is supported
Don’t misunderstand me. I’m not saying you can add sparklines and slicers while editing the workbook. I’m saying you can edit a workbook that contains these objects and even interact with them, but that’s it.
As a final remark, all of this works very well on Safari and FireFox (besides Internet Explorer, of course).
Excel Services 2010: Edit and Collaboration
The big news here is that you can edit a workbook using the Excel Web App. You have a limited set of operations you can perform but I can tell you it’s still quite impressive. The Edit Mode support co-editing, which means more than one user can edit the document simultaneously and Excel Web App will warn you of who is also collaborating with you.
Edit Mode has a reduced version of the Excel Ribbon but has a fairly impressive set of features (for a web app):
- Supports undo and redo
- Supports live preview (when selecting colors or formats)
- Supports auto-complete when entering a formula
- Supports grouping of rows and columns
It probably supports a lot more cool features, but this is what I saw in the session.
The new Excel Web Access Web Part allows you to turn interactivity on and off, which means the users can now type in the cells. In the previous version, if you wanted the users to interact with your Excel workbook through the Excel Web Access Web Part, you had to define parameters which would be shown on the side of the web part.
Just one note: interactivity is not the same as editing. You can interact, and see changes in charts and calculations, but the original workbook remains unchanged.
Excel Services 2010: JSOM (JavaScript Object Model)
Another addition to Excel Services 2010, is the JavaScript Object Model (JSOM) which allows developers to handle events in Excel Web Access Web Parts (like mouse clicks) and react to them.
Steve demonstrated the JSOM in a SharePoint Dashboard with two different Excel Web Access Web Parts. The first one had an Excel grid and the second one had two charts. Whenever he clicked a row in the first one, the charts showed data that was related to the selected row.
Excel Services 2010: REST API
One of the coolest new features (in my perspective) is the REST API exposed by Excel Services 2010 which can be used to retrieve data on:
- Ranges
- Charts
- Tables
- PivotTables
The data is retrieved as an Atom feed (which you can syndicate to) or in HTML format allowing it to be embedded wherever you wish. When retrieving a Chart, Excel Services will return a PNG image of the chart.
One of the demos regarding the REST API was creating a quick part in Word, of type field, and setting the source of that field to be the REST URL for a Chart. By doing this, Word inserts the chart image in a document, but allows you to update it like any other field.
