Microsoft Office Tutorials and References
In Depth Information
Five Key Ways to Use Excel Services
Building Business Intelligence Portals and Dashboards
In order for Excel to be considered a complete BI solution, there must be a way to incorporate workbooks
that contain data analysis inside of portals and dashboards.
Excel Services and the BI features of Office SharePoint Server provide an answer to this need. With these
features, you can use Excel to analyze your business data, and then distribute your analysis through
Office SharePoint Server. You can use the BI functionality to build Report Centers and Report Libraries
that become the hub for BI and reporting needs.
The Report Libraries can contain Excel workbooks that have been published by the various analysts.
Users can then view these workbooks in the browser, ensuring that they remain managed and
controlled. They can also incorporate the workbooks (or parts of the workbooks) as part of dashboards built
using Office SharePoint Server. The dashboards can contain live workbooks, as well as KPI lists created
Leveraging Workbook Calculation in Custom Solutions
As mentioned, Excel Services includes a web services interface. The interface is basic and focused on a
couple of core scenarios.
One of these scenarios is building applications that directly leverage Excel workbooks as their business
logic. The key capabilities of the Application Programming Interface (API) are loading a workbook,
calculating it, setting values into cells, and retrieving values from individual cells or the entire updated
With these core interfaces, you could conceivably use Excel workbooks as functions in custom-developed
applications. The workbooks become an integral part of the application, and are called programmatically
to calculate results for specific inputs.
The Excel workbook is calculated by Excel Services, which provides a robust and scalable platform for
calculating workbooks. This is very different from calling a client productivity tool, and can be leveraged
as part of large enterprise applications.
To better understand how Excel Services can help solve your needs, consider this use case. Assume a
pricing analyst in the Marketing Department is responsible for developing pricing and discounts for a specific
range of products. The analysts must develop the discount schedule based on various factors (such as the
amount being purchased and the type of reseller). The discount model is first developed in the Excel client.
The same discount model must be called by the company’s web-based order quotation system. Without
Excel Services, a developer would be tasked with recoding the logic represented in the Excel workbook
in code so that it can be leveraged as part of the application.
With Excel Services, the workbook author can continue to maintain the workbook, and the developer can
leverage the business logic stored in it programmatically. After the details for a new order are entered
in the online quotation tool, the program calls on Excel Services to load the pricing workbook, plug the
values from the input form, calculate the workbook, and retrieve the resulting values to present the user