Microsoft Office 2010 Professional Excel Services

One thing that we hear from customers is that they would like to be able to re-use Excel spreadsheets in web portals and dashboards simply and without needing to write a bunch of custom code. For example, they have business people (financial analysts, business planners, engineers) that create content in Excel that they would like to re-use and share in a portal or dashboard,Microsoft Office 2010 Professional, but to do so is technically quite challenging. (For those that are not familiar with the term,Microsoft Office Professional 2010, “dashboard” is generally used to describe a page or screen that presents business information, often in highly graphical ways, that allows users to see trends, anticipate problems or opportunities, and make decisions. The term is used a lot in the context of business intelligence, operations management, etc.)
Today, I am going to discuss some of the ways in which Excel Services integrates into the upcoming release of the SharePoint products and technologies platform to allow all sorts of users to build web solutions that contain live, interactive, and actionable data simply and without code. Specifically, I will provide an example of how data in Excel spreadsheets can be integrated into portals or dashboards built using SharePoint,Microsoft Office Professional 2007, and talk a bit about how a user would go about doing this.
Creation of Dashboards
As I just touched on, creation and maintenance of a dashboard within an organization can be a time consuming process. It requires a developer to write custom code to access, visualize, and organize the data in such a way that users can use that data to monitor processes and make business decisions. Add the ability to interact with that data (drill down, filter, sort, etc.), and the process requires more custom code, becoming even more complex.
With the next version of the SharePoint products and technologies platform, we are delivering a set of building blocks (called “web parts”) that can be used to build a SharePoint dashboard entirely within a web browser and without writing any custom code. This includes Excel Services spreadsheets,Microsoft Office 2010 Professional, which allows technical and non-technical users to leverage their existing knowledge of Excel in order to create the interactive views of data and integrate that data into a dashboard.
Here is an example of a dashboard built in SharePoint with both SharePoint and Excel Services content. Note, I have highlighted the different sections to assist in my explanation below.
(Click to enlarge)
Let's look at the pieces that make up the dashboard, and how it would be created.
1 - Built on SharePoint
This dashboard has a consistent look and feel with the rest of the SharePoint site it belongs to … since the dashboard concept (a page containing a set of web parts) is native to the SharePoint platform in version 12, it is simple for users to create dashboards that look and feel like the rest of their intranet site, for example.
2 - Excel Services
Two of the web parts in the dashboard are provided by Excel Services - in this case a Table and a Chart contained within a spreadsheet, but it could have been anything - formulas,Microsoft Office 2007 Professional, PivotTables, etc. Previously, when I have shown a screenshot of a spreadsheet on the server, I’ve shown it in our 'full page view' that looks a lot like Excel. In the context of a dashboard however, users can configure Excel Services to show only a portion of the workbook (a ”summary” view.) In a dashboard, the author can also configure the level of interactivity available. Finally, since Excel Services is just showing a spreadsheet, the dashboard author is able to leverage all of their existing Excel knowledge in order to create that spreadsheet and format it in just the right way, without writing any custom code to access external data, perform calculations, format the results, or provide interactivity.
3 - Key Performance Indicators
One of the web parts in the dashboard is something that we call a “Key Performance Indicator (KPI) List” web part. This is a SharePoint part, not an Excel Services part. These are highly visual displays of data that allow users to quickly get a sense of the status of important factors in their work. The data can come from a wide variety of sources, from manually-entered data in Excel sheets all the way up through databases. Users can drill down on the items to see the detail behind the visualization. For instance, if the status of a KPI is red (bad), clicking on that KPI will automatically take the user to a page that shows how the number is trending, what the thresholds are for good/bad, and also the full spreadsheet (or other data source) that was used to determine the status of the indicator.
4 - SharePoint Filters
The final set of web parts in this dashboard are “Filter” parts (these are also SharePoint, not Excel Services parts). These can be hooked up (again, without code), to the other parts on the page, enabling users to filter everything they see on the page quickly and easily. For example, if a user named “Dan” wanted to see just his support calls for 'ProductA' during FY04, he would enter those criteria and click “Apply Filters”. All the content on the dashboard (chart, table, KPIs) would update accordingly. Filters can be set up to allow users to to pick from a predetermined set of values, enter a date/number/string, filter on the user viewing the dashboard, filter on values in an external database, and so on.
Creating a dashboard
How is this dashboard created? A user with the appropriate SharePoint permissions can create a dashboard with just a few clicks. Upon doing so, they can choose which web parts (there are many more than the few that I've mentioned above) they want to display on the dashboard, as well as where they want those parts displayed - much like creating a custom home page on MSN.com. After choosing the Web Parts to put on the dashboard, the dashboard author can configure the dashboard within their Web browser - setting up which spreadsheets are shown (and how), what KPI's are shown in the KPI list, and what Filters should be available, etc. All this is completely codeless.
We think this is pretty powerful functionality. Business users and IT can now build a custom web-based dashboard without writing any code, instead leveraging their existing Excel knowledge to access data, perform calculations, and finally format the spreadsheets that are shown in the dashboard, greatly reducing the cost of dashboard creation and maintenance.

<div