Excel services part 5: all about “interactivity”
Now that I have discussed publishing spreadsheets to Excel Services, let’s review how users can “interact” with spreadsheets that Excel Services has generated. To start, I want to explain what we mean by “interact” because it is anther development-team term that may not be clear to everyone reading this article.
When we set about building Excel Services, we wanted to provide a richer experience than simply displaying a static spreadsheet in a browser. For example, we wanted users to be able to be able to change sorts and filters to better understand data they were looking at without leaving the context of the browser. We wanted users to be able to adjust PivotTables to see exactly they data they wanted to see in order to make better business decisions, again without leaving the context of the browser. We wanted users to be able to set parameters to perform what-if analysis, again from within their browser. We call all these sorts of things “interactivity”, since they involve the user taking actions that change the spreadsheet the user is working with, but do so without leaving the context of the browser.
Specifically, our goals in this area are to allow users to:
view spreadsheets in a browser
navigate around those spreadsheets
perform further exploration of the data in those spreadsheets
change parameters to facilitate what-if analysis in said spreadsheets
We did not have a goal to enable full-blown authoring of spreadsheets in a browser – to author spreadsheets, users will need Excel 12. Also, we did not have time to enable every feature in Excel 12 in the browser, so we focused on features based on the four goals outlined above. Finally, it is worth noting that if the user has permission, they can always press one button to open the spreadsheet in Excel 12, and they can then do anything they want.
Generally, when we talk about interactivity, we generally group the features we enabled into three areas: worksheets, tables, and PivotTables. Let’s take a look at the details of what we did in each area.
Interactivity - Worksheets
As we have already seen, when a user is viewing an Excel-Services-generated spreadsheet in a browser, the visual fidelity between the Excel Services spreadsheet and Excel 12 is very good. Excel Services supports the same layout and formatting capabilities as Excel 12, so to users, a spreadsheet viewed in Excel 12 will look exactly the same as a spreadsheet in a browser (within the constraints of HTML). This includes basic formatting (e.g. row height and column width, font, colour, grid lines on or off, text rotation, etc.) as well as the new features I have already talked about like Data Bars, Colour Scales, Table Styles, and some others I have yet to discuss like charts.
There is one notable difference between Excel 12 and Excel Services, though – when a user is looking at a spreadsheet in their browser that was generated by Excel Services, only one “section” of the spreadsheet will be served up by Excel Services at a time (this is a performance optimization - less HTML needs to be served up to the client … the number of rows and columns can be configured with defaults of 75 rows by 20 columns). To allow the user to move between sections, we have added paging controls to spreadsheets viewed in the browser.
(Click to enlarge)
In addition to navigating “sections” within a single worksheet, users will be able to do the following when working with a worksheet in a browser.
- Page between sheets (using tabs just like in Excel)
- Expand and collapse outlining (using buttons on the side and top of the sheet, again like Excel)
- Set parameters (see previous post
)
Refresh external data and calculate the spreadsheet (to see the most recent data or calculations)
Find values within the worksheet (note this is a separate feature from browser Find – this feature will search for values in the entire worksheet, even the parts that are outside the current “section”).
You may have noticed the “toolbar” in the previous screenshots – this is where the controls to calculate, refresh external data, and find (as well as other things) are located.
(Click to enlarge)
Also, since I mentioned charts, here’s a shot of a chart in a page generated by Excel Services in a recent build.
(Click to enlarge)
Interactivity - Tables and AutoFilters
If a spreadsheet contains a table or autofilters, users will be able to set and update sorts and filters. This includes all of the improvements that I discussed in previous posts here and here with the exception of sorting and filtering by colour. Specifically, users will be able to:
- Sort ascending or descending
- Multi-select filter
- Top 10 filter
- Set quick filters (”Above Average,” “Below Average,” “Contains,” “Last Month,” etc.)
- Set custom filters (“Less Than”, “Contains”, etc.)
(Click to enlarge)
Interactivity - PivotTables
Similarly, if a spreadsheet contains a PivotTable, users will be able to interact with the PivotTable from within their browser. (Note, I haven’t covered work we have done in the area of PivotTables yet – and we have done a lot - suffice to say that all of the features we have added to PivotTables in Excel 12 have been implemented on the server.) This includes:
- Expand/collapse levels
- Sort ascending or descending
- Multi-select filter
- Top 10 filter
- Set quick filters (”Above Average,” “Below Average,” “Contains,” “Last Month,” etc.)
- Set custom filters (“Less Than”, “Contains”, etc.)
(Click to enlarge)
New for Excel 12, for example, is the fact that you can perform all of these operations on any level of a hierarchy that has been added to the PivotTable, and on member properties for those of you using SQL Server Analysis Services. Much more on that in a series of posts coming soon.
Controlling all this
I can hear the developers reading this asking “what if I don’t want users to be able to sort, filter, etc.?” … for example, if you want to display some data using a PivotTable, but you don’t want people to be able to change the view. We have made it possible to turn off interactivity. You can, for instance, allow users to only sort and not filter, to interact with Tables but not with PivotTables, and more.
Controlling Interactivity
That’s the summary of interactivity. Please let me know if you have questions. Next time, building applications with Excel web services.
