Replacing OWC Reporting with Excel Services
Today's author: John Campbell, a program manager who works on the Excel Services teams.
Replacing OWC Reporting with Excel Services
As a Program Manager on the Excel Services team, I often field emails from people who have a thin reporting solution that is based on Office Web Components (OWC). They would like to migrate that solution to Excel Services, but aren't sure how or what the pitfalls are. In this entry I will talk about what OWC functionality Excel Services can be used to replace, some common pitfalls, and step by step instructions for putting together a sample report that overcomes the most common OWC conversion pitfall.
Why would you want to replace OWC with an Excel Services based solution in the first place?
The strongest reason is that OWC is a deprecated technology. It is being phased out, and new features will not be added moving forward - OWC is in a security fix and maintenance mode. See this blog post for more information.
Excel Services has been blogged about before so I won't spend a lot of time going into great detail on all the scenarios it enables. I will only list the high level advantages it has as compared to OWC:
- There is no client side install. Users just need a browser and that's it - no Active X and nothing to worry about rolling out, securing, and deploying to lots of client machines.
- No programming or special knowledge required - users create good looking reports in Excel, and then just save them to SharePoint. So mere mortals who do not have development backgrounds and don't understand web pages or web technology can actually do it from start to finish in the simplest cases.
- There is no special conversion process a spreadsheet has to undergo - it is just an Excel file. Spreadsheets can be edited by the spreadsheet owner, and when they resave into SharePoint, the latest results are automatically available via Excel Services - there is no special conversion step required. So users who may not be tech savvy (see #2) can make the updates without the need for intervention from an IT developer.
- One version of the truth - spreadsheets can be tightly controlled and managed in SharePoint, but still rendered thin so the information can be shared broadly. They can even be locked down so that users can ONLY get the thin view - i.e. the custom business logic in the spreadsheet can be hidden and protected, while enabling users to still interact with the spreadsheet, recalc it, refresh it, etc.
- It has 100% calc fidelity with Excel, including new formulas that were added in Excel 2007.
- It supports the new Excel 2007 BI features (conditional formatting, styles, etc), and as new features are added in later versions of Excel there is a good chance they will be supported on the server as well.
- It supports programmatic access via web services to enable "real" server based calc. So if you have some spreadsheets that you want to offload calc on the server, you can do that with Excel Services.
- It is extensible via User Defined Functions. Using managed code, there are many ways to extend the calc of a workbook and light up other custom programmatic scenarios as workbooks can call your custom managed code on the server using simple function syntax.
What types of OWC solutions can be migrated?
Most OWC solutions can be boiled down to the following two scenarios:
- OWC is used as part of a custom application to provide general spreadsheet\pivot functionality, or to provide thin charting on the fly in the context of a custom application.
- OWC is used to provide thin, and often interactive, reporting. This can be reports both based on static spreadsheet data, or data consumed from a database and it often includes charts and pivots.
Excel Services was not designed to target #1. Excel Services can be used to replace OWC in many scenarios like #2. The rest of this article is going to focus on replacing OWC in reporting scenarios.
What are some of the common pitfalls to replacing OWC reporting with Excel Services?
Excel Services was a V1 release, and so does not currently support the full array of OWC functionality. Here is the short list of the most common complaints that I hear when people evaluate replacing their OWC reporting solutions with Excel Services. Note - not all of these are blockers! In many cases they can be overcome by really understanding the report consumer's needs and by performing some simple workarounds and\or better scoping the required reporting functionality.
Pitfall: Users can't re-pivot pivottables in Excel Services.
This is the most common complaint that I hear from people who want to replace OWC with an Excel Services based reporting solution. There is no pivot field well on the server so users cannot do things like repivot the table, add new fields, or remove fields. I often hear the developer who created the OWC solution for some group tell me that their users are very sophisticated and absolutely must have the flexibility to be able to repivot any which way they want. Sound familiar? Is this claim really true? Most users don't have the savvy to be able to create completely new reports on the fly - they aren't familiar enough with the data or don't understand pivottables well enough to be able to do it. Of course there are always the small handful of folks who design the data or reports that can absolutely do this. I'm not talking about them, I'm talking about the other 100 people that need to view the report, perhaps drill into the data a bit, and just want their questions answered in a clear and quick way.
A pivot report with many, many, possible combinations can, once the requirements and business cases are clearly scoped and understood, usually be boiled down into a small handful of views that are the most clear and usable. My advice here is to talk with the business users and work to see if the data can be scoped and defined using a handful of different pivottable views. I think with enough discussion you just may find that they can, and given all the benefits that Excel Services has, a strong case can usually be made here. Note that it is possible that there are a few users who do require more functionality. In cases where the defined views aren't enough, those advanced users can always open the report directly in Excel client and can do whatever they need there. When opening in Excel, their state isn't lost - i.e. any drilling they did on the pivottable will still be there, so they can start working in Excel from exactly the same place where they left off in the browser.
Pitfall: Users can't type in the grid using Excel Services - i.e. Excel Services doesn't support editing.
A lot of reporting solutions don't typically have this as a hard requirement - this is really for folks who are trying to just host a spreadsheet in the browser. Excel Services does support setting parameters - single cell named ranges that can have their values set on the fly to drive calculations. So simple what if analysis can be accomplished. Excel Services also has the advantage of providing greater control over who can edit and view the files, with can actually be seen as an advantage in today's compliance aware enterprises.
How to setup an Excel Services report by providing different views on the data without requiring the use of a pivot field well
Since lack of pivot field well support is the #1 potential blocker, I will walk through a way to provide a report that works around this. The end result will be a report that allows users to get the views on the data they need on a nice looking page with simple to use controls for choosing the pivot on the data.
For this example, I will just be using an Excel spreadsheet connected to the sample Adventure Works cube. The report is created in Excel. The filter controls that I will eventually add to the page can be added via out of the box SharePoint UI. So this entire example requires absolutely no programming!
Step 1: Create the report
For this step I fired up Excel and created a connection to the data source. I identified the 4 views of the data that were most understandable and relevant for my imaginary group of users. In the real world, this is something that will need to be thought about and iterated over with the owners of the data or business users who generate and\or consume the reports.
The goal is to get a very clear understanding of the needs, and then create a single workbook with multiple pivottables where each pivottable has one of the required views of the data. In this example, I created each pivottable on a separate sheet.
Give each pivottable an understandable name that corresponds to the view of the data. To rename a pivottable, select the table, and then choose the Options tab in the ribbon. Under the Pivottable Name chunk, type the new name of the pivottable and press enter.
Step 2: Publish the report to SharePoint
From the file menu, choose PublishàExcel Services
A vanilla Save As dialog will pop up. Choose the Excel Services Options button.
This will launch an options dialog that will let you control what is displayed from this workbook when it is viewed on the server.
In the options dialog, expand the dropdown on the Show tab and choose Items in the workbook. Here is where you can specify specific objects that should be displayed when the workbook is rendered on Excel Services. If a user opens the workbook in Excel client, they will see the entire spreadsheet, but on Excel Services they will only see the objects that you specify. Check the checkbox next to All PivotTables so that all of the pivots will be shown.
Click Ok to close that dialog. Then, finish the publish operation by specifying the URL of a SharePoint document library to save to, and click Save.
If you left the Open in Excel Services checkbox checked in the Save dialog, a browser will open automatically and show you the report rendered on Excel Services.
It should look something like this:
Notice that a single pivot is shown, and is refreshable/interactive. On the right hand side of the toolbar, there is a View dropdown. Expanding that gives the user a list of all the different pivots on the data (really just letting them choose different pivottables).
If your goal was just to make the pivot data available, then you are done. Users can see the report you published by choosing to "View in Web Browser" from the document library (note that you can also configure the doc lib so that the default click takes them to the thin view). If you want to create a more advanced report using SharePoint filters, then read on.
Step 3: Create (or Edit an existing) SharePoint page to show the report
For this example, I will keep it simple and just create a new web part page in the same document library where you stored the Excel file. Expand the Site Actions on the upper right hand of the document library page. Choose Create.
On the next page, under the Web Pages category, click Web Part Page.
Give the page a name your report page and choose a layout (I chose Header, Left, Column, Body for my layout), and click create.
Step 4: Add and configure web parts on the page
Now, we need to add the right web parts to show the report. Choose the large zone in the middle of the page to add a web part that will be used to display the Excel report, and click the big orange Add a Web Part button.
On the resulting dialog, scroll down, and under Defaults, check Excel Web Access and click Add.
The dialog will close and the web part should be on the page. Next, click the link that says Click here to open the tool pane.
A tool pane will open. Many, many settings can be tweaked that will affect how this web part will render workbooks. For this example, we will keep it pretty simple and do two things: 1 - enter the URL of the workbook you published in Step 2 in the text box labeled Workbook, and 2 - set the dropdown to None under the Type of Toolbar. Next, click OK to close the task pane.
In the left column of the page, click the add web part button. This time add both the Choice filter and Filter actions - you will need to scroll down as they are under the filter section.
The dialog will close and you should have some filters on the page. For the choice filter, click the link to open the tool pane to configure this filter.
In the tool pane, give the filter a name. Then, in the text box for the choices, enter the name of each pivottable followed by a semi colon followed by the user friendly name. Each of these must be entered on a separate line in this dialog. You can also optionally give the filter a friendly title, and under the Advanced Filter Options, specify a default choice. After you set your options, click Ok to close the toolpane.
Step 5: connect the web parts
The only thing left to do is to connect the web parts such that the user's selection in the choice filter controls what view of the pivottable is shown on the page. To do this, we need to connect the web parts so that the named item to display is taken from the filter.
On the Choice filter tool bar, choose EditàConnectionsàSend Filter Values toàExcel Web Access - <workbook name>
Now you should get a dialog asking you to configure the connection. Choose Get Named Item From in the dropdown and click Finish.
The page is now configured. You can then click Exit Edit Mode on the top right of the page. This will give you the same view of the page as the users will have.
On the finished page, click the button next to the choices filter. It will pop a dialog that shows the friendly names of views of the data. When you select one and click Ok, the dialog will close. You will need to click the Apply Filters button on the page to have the corresponding pivottable be shown. Apply Filters is there because you can have multiple filters on the page (typical in pages that have more filters, web parts, etc on the page) and it allows you to set the filter values once for all filters before applying the values across the entire page's view.
This provides very similar functionality to what I have seen in many OWC report pages. You can also add other web parts to this page, with other views of data - i.e. a web part showing corresponding charts for instance. You can even add other filters to the page to allow users to set parameters to drive calc or change views of data via filtering the pivottables. For instance, I could have a filter that sets the Country parameter on a pivottable and chart, and when the filter is changed all the web parts on the page will get updated with the new information.
By using filters in SharePoint and reports created in Excel, Excel Services can be used to display compelling, interactive, and refreshable reports as a replacement for an OWC reporting solution.