Sunday, June 28, 2009

Create browser based Dashboards using Excel Services

In an earlier post, I discussed how you can create free form Excel reports using the cube functions in Excel 2007. This is a great way to create reports, charts, KPIs (Key Performance Indicators) and dashboards using the tool you are already familiar with.

However, you may want to make these reports and dashboards available to a wider audience via a Web browser. You want to enable users to filter the worksheets and charts to suit their needs. You also want to prevent your master Excel workbook from being edited by certain users while allowing only specific users to edit the master workbook. What is the solution?

Welcome to Excel Services! Excel Services is an interactive Web view for spreadsheets - a way for people to see data in their Web browser instead of using Excel. In fact, to look at the report or dashboard in Excel Services, users do not even need Excel installed. However, if they do have Excel installed, they can use the “Open Snapshot in Excel” feature of Excel Services to open the limited version of the workbook data in Excel and use the standard Excel features such as auto sum, formulas and charts for further analysis. However, this does not change the master version of the workbook saved in Excel Services.

In order to make your Excel workbook available in Excel Services, you publish your Excel workbook to Excel Services. To publish workbooks to Excel Services, you need:
  • Excel Services set up with SharePoint Server 2007 Enterprise.
  • A SharePoint site based on SharePoint Server 2007 Enterprise. This is where you can save your workbooks, and where others can see your workbook data by using their Web browsers.
  • Excel 2007 installed on computers through one of the following: Office Professional Plus 2007, Office Enterprise 2007, or Office Ultimate 2007.
Again, to look at a worksheet in Excel Services, users don't need Excel installed at all. All they need is a Web browser.

If you would like to learn how to set up Excel Services and publish an Excel workbook to Excel Services, there is a good tutorial available here.