Microsoft Excel

New Posts From One of the Principal Developers Behind Excel Services

One of our most senior developers on Excel Services, Shahar Prish, is busy writing posts in his blog on the new programmability features in the 2010 version of Excel Services. The first set of posts talk about REST and moving forward he will expand to the new JavaScript OM we added this release. Here are the links, if you care at all about building custom solutions for Excel and Excel Services they are a great read:

General posts about REST:

Welcome to the new Excel Services

So what does REST on Excel Services look like???

Discovery via the Excel Services REST APIs

Getting ranges via Excel Services REST as well as Charts and Discovery

Excel Services REST APIs – the basics

Advanced Excel Services REST API capabilities – passing parameters to a spreadsheet

How to seamlessly embed data from Excel Services spreadsheets into Word

The Excel Services Windows Gadget:

Introducing the Excel Services Windows 7 Gadget

Showing Excel ranges in the Excel Services Gadget

Bringing it all back home – using advanced REST functionality with the Excel Services Gadget

And the blog is starting to show how to actually code the gadget:

Coding the Excel Services Windows 7 Gadget – Part 1 - Settings

Excel Services in SharePoint 2010 REST API Examples

Thanks to Christian Stich for putting together this series on the REST API.

In this final installment on the REST API, we’ll walk through a couple examples of using the REST API to embed spreadsheet data in new and interesting ways.

Embedding a ‘Live’ Chart on a SharePoint Wiki Page.

For this example we are embedding an Excel scatter chart on a SharePoint wiki page. The scatter chart works like an analog clock, so that you can see that the data returned via REST is dynamic, and the workbook containing the chart and the logic is attached to this blog post.

• First upload the Clock.xlsx workbook to a SharePoint document library. (the workbook is included in the archive folder that is attached to this post)
  
  Let’s assume that the workbook has been uploaded to the following location:
  
  http://contoso/Shared%20Documents/Clock.xlsx.
  
• Then craft the REST API URI to the scatter chart named Clock
  
  http://contoso/_vti_bin/ExcelRest.aspx/Shared%20Documents/Clock.xlsx/Model/Charts('Clock')
  
  You might want to post the URI into a browser’s address bar to verify that you can access the chart before proceeding with the next steps.
  
• Create a SharePoint Wiki page.
• Next, start editing the wiki page by clicking on ‘Edit Page’. Since we are inserting a chart which the REST API returns as a PNG image file click on the ‘Insert’ tab on the ribbon, then click on the ‘Image’ icon and then select ‘From Address’ from the dropdown box.
   
• Paste the URI that you have created earlier into ‘Address’ field.
  
• Finally, ‘Stop Editing’ the page

We see the clock when the wiki page reloads.

And when we reload the wiki page again, the scatter chart representing the analog clock will be retrieved again using the REST API – and will have been updated with the new time – showcasing the dynamic and live nature of Excel Services REST API calls.

A more business focused scenario could include a chart on a wiki page that shows the latest sales figures for a division of a company which are retrieved by Excel Services from a SQL Server database or Analysis Services cube when the wiki page is opened (or refreshed) by a user.

Inserting a Chart in a Word Document Using the Excel Services REST API.

Similarly, we can use the Excel Services REST API to insert live charts in Word and PowerPoint documents.

In Microsoft Word, select the ‘Insert’ tab on the ribbon, then click on the ‘Quick Parts’ icon, followed by selecting ‘Field’.

Then select ‘IncludePicture’ and paste the REST URI for displaying the RevenueChart from the Tax.xlsx workbook

http://contoso/Finance/_vti_bin/ExcelRest.aspx/Shared%20Documents/Tax.xlsx/Model/Charts(‘RevenueChart’)

into the ‘Filename or URL field’. Also, select ‘Data not stored with document’ in the ‘Field options’ column – this results in Word refreshing the chart (reflecting potentially updated numbers) each time the document is opened.

Thus, we have created a Word document that contains a live and up-to-date Excel Services chart. If the data in the workbook (which itself can come from an external source, such as a SQL data base or Analysis Services Cube) is updated, so will the chart in the Word document each time the document is re-opened or when the document is refreshed (by pressing the F9 function key).

Building a Mash Up with Bing Maps and the Excel Services REST API.

Bing Maps provides an SDK that enables developers to determine the location of a mouse click on the map and to get the address corresponding to this location. In this demo, the (U.S.) state corresponding to the click location is extracted from the address. Then an Excel Services REST URI is created that requests a chart with the census rankings of that state by setting the state’s name/abbreviation into the Excel Workbook.

Clicking on the city of Seattle brings up the Census Rankings for Washington State.

Similarly, clicking on the city of Houston brings up the Census Rankings for the State of Texas.

The entire code required to load the Excel Services Chart with the Census Rankings for each state is:

document.getElementById('resultDiv').innerHTML = "<img src='http://contoso/_vti_bin/ExcelRest.aspx/Shared%20Documents/StateRankings.xlsx/Model/Charts(%27Report%27)?Ranges(%27State%27)="+s+"'/>";

We assemble the REST API URI string by taking the URL to the workbook’s location and setting the range ‘State’ to the JavaScript variable ‘s’ which contains the state’s name/abbreviation.

The entire JavaScript code (including the code to get the location from Bing Maps) and the workbook for this demo are included in the archive folder that is attached to this article.

Summary

The Excel Services REST API is a powerful yet very easy to use means of embedding Excel Services content on web pages and for creating mash-up applications. It enables users to retrieve image, HTML and Atom feed representations of items in Excel workbooks and modifying those by setting values into the workbook. The REST API also leverages the other capabilities of Excel Services, such as external data connections and user defined functions which can be used to fetch up-to-date values from external sources, which can then be retrieved, displayed or otherwise consumed by simply specifying a REST URI. 

If you’d like to read more about REST, Shahar Prish, a developer on the Excel Services team who worked on the REST feature, has written a series of blog posts about REST on his blog.

I look forward to your feedback on this feature.

Excel Services in SharePoint 2010 REST API Syntax

Thanks to Christian Stich for putting together this series on the REST API. 

As we saw in the previous post, using the Excel Services REST API is as simple as specifying a URI (Uniform Resource Identifier) in your web browser.  In this post we’ll walk through the details of the URI syntax.

We start out with a simple Excel workbook stored on a SharePoint 2010 server at the following URL:

http://contoso/Finance/Shared%20Documents/Tax.xlsx

This example workbook contains two named ranges, a chart, a table and a PivotTable.

In order to create a URI that can be used to access the REST API we insert _vti_bin/ExcelRest.aspx/ in the above URL behind the site part (in this case /Finance).

http://contoso/Finance/_vti_bin/ExcelRest.aspx/Shared%20Documents/Tax.xlsx

Discovering Items in a Workbook

Next we add /Model behind the filename (in this case /Tax.xlsx)

http://contoso/Finance/_vti_bin/ExcelRest.aspx/Shared%20Documents/Tax.xlsx/Model

We now have a URI that can be used to discover the items in the workbook – the REST API returns an ATOM feed that contains the types of items that are accessible – currently ranges, charts, tables and PivotTables are supported.

Note: In the case where the workbook resides in the root site of the SharePoint server then you would insert the _vti_bin/ExcelRest.aspx/ behind the server name.

http://contoso/Shared%20Documents/Tax.xlsx

becomes…

http://contoso/_vti_bin/ExcelRest.aspx/Shared%20Documents/Tax.xlsx/Model

Retrieving Items in a Workbook

We can get a list of the named ranges in the workbook by simply appending /Ranges to the previous URI or you can click the link for Ranges that was returned in the previous example.

http://contoso/Finance/_vti_bin/ExcelRest.aspx/Shared%20Documents/Tax.xlsx/Model/Ranges

The REST API returns an ATOM feed that contains the named ranges in the workbook – this workbook contains two named ranges: TaxRate and Revenue.

We can also get also get an HTML representation of a range by specifying the range using /Ranges(‘range name’) and by adding ?$format=HTML.

To get the HTML representation of the name range Revenue we specify the following URI:

http://contoso/Finance/_vti_bin/ExcelRest.aspx/Shared%20Documents/Tax.xlsx/Model/Ranges(‘Revenue’)?$format=HTML

Discovering and accessing tables, PivotTables and charts is similar to discovering and accessing ranges as shown above.

Tables:

http://contoso/Finance/_vti_bin/ExcelRest.aspx/Shared%20Documents/Tax.xlsx/Model/Tables

http://contoso/Finance/_vti_bin/ExcelRest.aspx/Shared%20Documents/Tax.xlsx/Model/Tables(‘RevenueTable’)?$format=html

PivotTables:

http://contoso/Finance/_vti_bin/ExcelRest.aspx/Shared%20Documents/Tax.xlsx/Model/PivotTables

http://contoso/Finance/_vti_bin/ExcelRest.aspx/Shared%20Documents/Tax.xlsx/Model/PivotTables(‘PivotTable’)?$format=html

Charts:

http://contoso/Finance/_vti_bin/ExcelRest.aspx/Shared%20Documents/Tax.xlsx/Model/Charts

http://contoso/Finance/_vti_bin/ExcelRest.aspx/Shared%20Documents/Tax.xlsx/Model/Charts(‘RevenueChart’)?$format=image

Supported REST API Return Formats

Notice in the previous paragraph that we added a query string parameter to the URI in the previous examples where we were accessing the actual items. This query string parameter is used to specify the format that the REST API should return. The currently supported formats are:

• html
• atom
• image
• workbook

When ?$format=html is specified the REST API returns an HTML representation of the item that can be displayed by the web browser.

When ?$format=atom is specified the REST API returns an ATOM feed representation of the item.

When ?$format=image is specified the REST API returns an image file (at this time the format is always PNG).

I’ll cover the ?$format=atom return format in a future post on the REST API. At that time we will also address the differences between the ATOM feeds for /Ranges, /Tables and /PivotTables.

When ?$format=workbook is specified the REST API returns the workbook which can then be opened in Excel or saved. The format of the workbook being returned matches the format of the original file – if the original file is in .xlsx format, then the workbook being returned will be in .xlsx format; if the original file is in .xlsb format, then the workbook being returned will be in .xlsb format. Same for .xlsm format.

Which Requests Support Which Formats?

Not all formats are supported with all items. Moreover, different types of REST API requests have different default values. For example /Charts(‘chart name’) default return format is image - this means that you don’t have to specify ?$format=image as part of the URI. Similarly, with /Ranges(‘range name’) you can omit ?$format=html if that is the format you would like to be returned.

http://contoso/Finance/_vti_bin/ExcelRest.aspx/Shared%20Documents/Tax.xlsx/Model/Charts(‘RevenueChart’)

is equivalent to

http://contoso/Finance/_vti_bin/ExcelRest.aspx/Shared%20Documents/Tax.xlsx/Model/Charts(‘RevenueChart’)?$format=image

Both URIs return an image in PNG format.

Below is a matrix that shows the supported formats for different types of REST API requests. Specifying ?$format=… allow you to override the default return format with another supported format.

What About Normal Ranges?

The example above showed how to access a named range. It is also possible to access arbitrary, non-named, ranges using A1-style notation. The following URIs show how it’s done for accessing single cell ranges and multiple cell ranges.

To get an HTML representation of the content of cell A1 on Sheet1:

http://contoso/Finance/_vti_bin/ExcelRest.aspx/Shared%20Documents/Tax.xlsx/Model/Ranges(‘Sheet1!A1’)?$format=HTML

To get an HTML representation of the content of the range A1:D4 on Sheet2:

http://contoso/Finance/_vti_bin/ExcelRest.aspx/Shared%20Documents/Tax.xlsx/Model/Ranges(‘Sheet2!A1|D4’)?$format=HTML

NOTE: you need to replace the colon in the A1 style notation for ranges with a pipe character when used with the REST API -> the range A1:D4 is entered as A1|D4

Inserting Values

The Excel Services REST API provides a means to change values in the workbook. These changes only affect the values, HTML rendering info or charts returned by the REST API for that particular request. These changes are NOT saved back to the workbook, nor do they affect other REST API or Excel Web Access browser sessions.

Setting values is simple and can be done by specifying single cells using A1 style notation, or by specifying a single cell named range or a parameter.

In the following example, we have a workbook with a named range called “Tax Rate”, which is currently set to 10%.

http://contoso/Finance/_vti_bin/ExcelRest.aspx/Shared%20Documents/Tax.xlsx/Model/Charts(‘RevenueChart’)

results in the original chart being returned

Changing the Tax Rate to 50% in the workbook by adding ?Ranges(‘TaxRate’)=0.50

http://contoso/Finance/_vti_bin/ExcelRest.aspx/Shared%20Documents/Tax.xlsx/Model/Charts(‘RevenueChart’)?Ranges(‘TaxRate’)=0.50

results in an updated chart being returned:

We could also have used A1 style notation to specify the cell to be changed (‘TaxRate’ has the A1 style address ‘Sheet1!B1’)

http://contoso/Finance/_vti_bin/ExcelRest.aspx/Shared%20Documents/Tax.xlsx/Model/Charts(‘RevenueChart’)?Ranges(‘Sheet1!B1’)=0.50

It is also possible to set multiple values into different cells. Additional query string parameters are separated by the ampersand & character.

http://contoso/Finance/_vti_bin/ExcelRest.aspx/Shared%20Documents/Tax.xlsx/Model/Charts(‘RevenueChart’)?Ranges(‘TaxRate’)=0.50&Ranges(‘FiscalYear’)=2008

And you can combine this with specifying the return format – the order of the query string parameters does not matter. For example:

http://contoso/Finance/_vti_bin/ExcelRest.aspx/Shared%20Documents/Tax.xlsx/Model/Charts(‘RevenueChart’)?Ranges(‘TaxRate’)=0.50&Ranges(‘FiscalYear’)=2008&$format=html

and

http://contoso/Finance/_vti_bin/ExcelRest.aspx/Shared%20Documents/Tax.xlsx/Model/Charts(‘RevenueChart’)?Ranges(‘FiscalYear’)=2008&$format=html&Ranges(‘TaxRate’)=0.50

and

http://contoso/Finance/_vti_bin/ExcelRest.aspx/Shared%20Documents/Tax.xlsx/Model/Charts(‘RevenueChart’)?$format=html&Ranges(‘FiscalYear’)=2008&Ranges(‘TaxRate’)=0.50

are all equivalent and return the same result.

More To Come

This concludes our walk through the REST API syntax.  You should now have enough knowledge to start building REST-based solutions with Excel Services.  In my next post I’ll showcase a few simple scenarios that you can quickly build yourself.

Simple Access to Spreadsheet Data Using the Excel Services 2010 REST API

Thanks to Christian Stich for putting together this series on the REST API.

Beginning our journey on all things Excel Services 2010, I thought I’d start off with a feature that’s been getting a lot of buzz lately since its introduction at the SharePoint Conference.

What is it?

The Excel Services 2010 REST API is a new programmability framework that allows for easy discovery of and access to data and objects within a spreadsheet. “Programmability” is almost a bit of a misnomer because in its simplest incarnation it doesn’t require any knowledge of programming to use. If you can craft a URL, you can use the REST API. This makes the REST API accessible to non-developers, yet it offers capabilities that make it a very powerful tool for professional developers as well.

For example, say you have a spreadsheet saved to a SharePoint document library that looks like the one below:

Now, let’s say you are writing an internal blog post about the effect of taxes on revenue, and you’d like to embed this Excel chart into your blog post. Simple, just craft a REST URL (technically, it’s a URI, but let’s ignore that for now) that looks like the following:

http://myServer/Finance/_vti_bin/ExcelRest.aspx/Shared%20Documents/Tax.xlsx/Model/Charts(‘RevenueChart‘)

and paste this into the Insert Image function in your blog software, and out comes a chart that looks like this:

Congratulations!  You’ve just used the REST API in Excel Services 2010.  Pretty easy, right?  But wait, there’s more!

The REST API in a Nutshell

By crafting the proper URI, the REST API allows you to:

1. Discover the items that exist in a workbook, such as tables, charts and named ranges
2. Retrieve the actual items in the workbook in one of the following formats:
1. Image
2. HTML
3. ATOM feed
4. Excel workbook
3. Set values in the workbook and recalculate the workbook before retrieving items

Is the Data Static?

The data, including charts, that is returned by the REST API is not static – it’s live and up-to-date.

With the REST API, any changes in the workbook are reflected in the data that is returned. This includes the latest edits made to the workbook, functions that have recalculated (including User Defined Functions), and external data that is refreshed.

The REST API can also push values into the workbook, recalculate based on those changes, and return the range or chart you requested after the effects of the change have been calculated.  For example, in the sample spreadsheet above, the chart is displaying the effects of a 10% tax rate.  With REST, you can request a chart displaying the effects of a 5% tax rate by adding a parameter to the URI that changes the value of cell B1 to 5%.

Examples

The opportunities for using REST are virtually endless. To spur your imagination, here’s a sampling of some ways to use the REST API:

• Embed a chart or range in a web page, blog post, etc.
• Embed a chart as a “linked” image into a Word document, PowerPoint presentation, or Outlook mail, and have always up-to-date information.
• Build a “mash-up” that uses Excel Services for calculation, charting, and/or as a data source.
• Build a Windows 7 gadget that displays information from an Excel workbook
• …and many more ideas I am sure our customers will think up.

Coming Up

I hope this has given you a sense of the capabilities and power of the REST API. In the next post I’ll dive into the details of the URI syntax. Stay tuned.

Excel Services 2010 Overview

Thanks to Steve Tullis for putting together this post.

Before delving into Excel Services 2010, I want to recap a point that some readers may not be aware of.  The Excel team is delivering two browser-based solutions as part of the Office 2010 wave of products.  The quick synopsis - here are the two solutions:

1. Excel Services: Version two of our real-time, interactive, Excel-based reporting and dashboard capabilities which ship as part of SharePoint Server 2010. Also included are APIs which enable rich business application development.
2. Excel Web App: The companion to the Excel client which extends the ability to create, view, edit, and collaborate on Excel workbooks using only a browser.

In the enterprise, these two offerings can be installed together – at which point, all the benefits of both are realized:  A lightweight spreadsheet authoring and collaboration tool AND an enterprise BI and application development tool.

Today’s post is the first post of a series which introduces, then explains in detail, Excel Services 2010.  Later on we’ll have a series of posts on Excel Web App.

A Bit of Background

If you remember, Excel Services 2007 shipped in Microsoft Office SharePoint Server 2007 as part of the Enterprise CAL. The focus of the 2007 release was twofold: leverage SharePoint to provide control of Excel workbooks . . . to ensure there existed only one version of the truth; and provide Business Intelligence (BI) capabilities which any SharePoint user could leverage to share data and analysis which they had done in Excel by publishing it to SharePoint and allowing users to consume it via the browser.

Excel Services 2010

Excel Services 2010 is all about continuing the promise we made in 2007, by which I mean:

1. Better symmetry across Excel and Excel Services
2. Continued integration with SharePoint
3. Improved user experience
4. Tools for application development

Let me touch on each of these briefly . . . as a teaser for the blog postings which will be made over the next few weeks.

Better symmetry across Excel and Excel Services:

We heard your feedback regarding file support . . . and have changed our paradigm from refusing to open files which contain unsupported features to making our best effort to open any workbook. For features we partially support, we either show cached values (e.g. query tables) or notify the user and remove the feature prior to displaying the workbook (e.g. Office Art shapes).

Not only have we invested to support common features - such as embedded images – but we also ensured new Excel features are available in Excel Services. Examples of new features include Sparklines, Slicers, PowerPivot, improved conditional formatting, improved functions, and many more.

Continued integration with SharePoint:

We continue our tight integration with SharePoint for security, content management, version control, document-level compliance, data connection management, service administration . . . essentially, all those features required to run and manage a service, and to establish and maintain a single version of the truth.

Additionally, we work closely with the SharePoint BI team to ensure tight integration between Excel Services, PerformancePoint Services, and other BI related capabilities shipped in SharePoint. When you get SharePoint Server 2010, create a new site based on the ‘BI Center’ template to see this in action.

Improved user experience:

We’ve made numerous investments to improve user experience . . . the top investments: we’ve Ajax-ified our service; which means you can refresh elements of a page instead of having every change require a page refresh; and, we’ve introduced scrolling, which means you can easily and smoothly navigate through your Excel content. While these are the most visible investments, there are many, many more.

Tools for application development:

Our application development story expanded significantly – improvements to the Web Services, and introduction of a JavaScript Object Model and a REST API, open the doors for both professional developers and end users to build business applications, mash-ups, or just provide an easy way to share Excel content beyond the workbook.

Wrap-up

I know I have introduced more questions than answers . . . by doing so, I hope I have piqued your curiosity about Excel Services. Our plan is to address those questions via posts on this blog over the next few weeks.

As you can tell, this release introduces many new capabilities. I encourage all of you to come back and read these posts. If your questions aren’t answered, let us know. If you have feedback or suggestions, let us know those as well. We are very excited about the changes and improvements we’ve made – I am certain that what you see over the next few weeks will provide great insights into how you and your organization can benefit from Excel Services 2010.

Excel Add-In for Manipulating Points on Charts (MPOC)

Overview

In Excel 2007, the ability to directly resize or reposition points on the chart was deprecated. This feature was sometimes referred to as "Graphical Goal Seek." For example, in Excel 2003 a user could click on a data point in a column chart twice which would surface handles that could be used to resize the columns. Over the last couple of years we have received a lot of feedback from customers indicating that this was a valuable feature for some scenarios. However, we were not able to react in time to roll this feature back into Excel 2010 but we are evaluating how to bring this back as a native feature in a future release. In an effort to restore this lost functionality, we have developed a sample Add-In that can be used in both Excel 2007 and Excel 2010.

In this blog post, I will provide the Sample Add-In for download and illustrate how to use this Add-In for manipulating points on your chart.

The sample add-in with RibbonX and VBA code is here.

Using the MPOC Add-In

Before trying out the following scenario, please install the Add-In as described in the provided documentation (attached at the bottom of this post).

In this section we will run through a fictional scenario about how you can use the Add-In to manipulate points on your chart. Tailspin Toys is in the process of developing a new toy airplane and are conducting various experiments before they settle on the final design. We will be looking at one such experiment where the company is studying the impact of different materials and wingspans on the maximum altitude that can be attained by this toy air plane.

The experiment has been largely successful and it has been observed that for all materials there is a general trend:

• Initially as the wingspan increases, the altitude begins to increase.
• However, larger wingspan results in a heavier plane. Hence, as we keep increasing the wingspan the heavier plane fails to reach the previous attained heights resulting in the benefits tapering off.

The scientists are elated at their find but these are some erroneous values in the data (show with red circles on the chart). The field scientist tells us that the erroneous readings were caused by some loose wiring to the altimeter used in the experiment. Since the trends are pretty obvious, the research department wants to clean up the charts before presenting their findings to the management.

In the next steps, we will see how MPOC Add-In can be used for this purpose:

IMPORTANT: Please back-up your file before using the MPOC Add-In. The undo functionality in Excel will NOT work since this Add-In is written entirely using VBA. Also, this is a sample Add-In, so please exercise caution before using it on important data.

1. We will start by selecting the point on the chart that we want to manipulate
    
2. A “Manipulate Points“ button will now appear whenever a chart object is selected under the “Chart Tools -> Layout” Tab of the Ribbon.
    
3. Click this Button to Launch the MPOC Dialog. The dialog will be automatically populated with information about the selected point.

   In this case, we have selected the 8th point in the “Aluminum” series. Since this is an X-Y scatter chart, we can change both the X and Y value associated with this point. Next, we will see how we can change the Y- value associated with this point to smoothen our curve.
   

   NOTE: Value controls are used to directly manipulate the value of the cell corresponding to the selected point on the chart. We dynamically add additional value controls depending on the type of the chart. For example: column or bar charts have one value controls, x-y scatter charts have two value controls and bubble charts have three value controls.

4. In this step, we will decrease the Y-Value associated with the point to smoothen the curve (as we have determined that the altitude is too high due to an error).

   NOTE: The MPOC Add-In provides 3 different ways to change the value of the point with varying level of precision:
   Scroll Bar – When you are far away from the value you want, use scroll bars quickly change values.
   Spin Button – When you are close to the value you want, use spin button for greater precision to zone in on the value you want.
   Text Box – if you know the value you want, why waste time zoning in on the value. Use the textbox to directly input it in the text box

   1. Drag the scroll bar associated with the Value Y to the left to quickly preview where we want the point on the chart. We let go when we are in the vicinity of where we want to be.
       
   2. Next, we will use the spin button to refine the position of the point (or else if we know the precise value, simply type it in the text box).
      
5. Next, we will change the next erroneous value. Since we have a modeless dialog, all we have to do is simply click to select the point on the chart (NO need to close the dialog). The dialog will automatically update with information about the selected point.
   

   MPOC provides a truly modeless experience - you can even click on a different chart or a different worksheet and the dialog will automatically update itself.

   
   Repeat step 4 to change the value associated with this point.
6. Next, let us explore alternate ways of selecting a different point on the chart by using the Series dropdown and Point dropdown on the dialog (or you may choose to select your point directly on the chart by using a mouse pointer as described in step 5). This method is quite useful when there are many points or series on the chart and selecting points can be difficult. Also, this opens up the possibility of manipulating points of charts where point selection is not supported – such as area charts.
   1. With just the chart selected (no points or series selected), launch the MPOC dialog as described in step 2 (if it is not up already).
   2. The MPOC dialog is automatically populated with the series information for the selected chart.
      
   3. Choose the series in the Series dropdown whose point you want to manipulate. Let us choose “Aluminum”. The entire series will be automatically selected on the chart and the point information will be populated in the Points dropdown.
      
   4. Next, let us choose the point that we want manipulate using the Points dropdown. We happen to know that we want to select the 28th point in the series. The point is automatically selected on the chart and the information about value is populated in the dialog.
      
   5. The next step is to change the value to smoothen the curve. We do this the same way as shown in step 4.
      

Thus using the MPOC Add-In, we can easily manipulate points of the chart to smoothen out the erroneous values.

MPOC Add-In Documentation

The section above covers the basics of using the Add-In. There are many more features that are beyond the scope of this blog article but these are covered in great detail in the provided documentation (attached at the bottom of this post). Please read the documentation before using this Add-In. Some of the topics covered in the documentation are:

1. Installation Instructions
2. Formula Support – The MPOC Add-In will not overwrite formulae in cells. We will instead use the Goal Seek feature in Excel to manipulate points that are based on formulas.
3. Dialog Forms – The MPOC Add-In supports two forms of dialogs controlled by registry keys
   1. Compact Form
       
   2. Expanded Form
      
4. Known Limitations – Unsupported scenarios include Cross Workbook charts and Pivot Charts.

Conclusion

The Manipulate Points on Charts (MPOC) Add-In is a sample solution that will help you manipulate points on charts. This Add-In can be used to visually smooth curves in Excel. If this Add-In is useful to you or if there are any chart interactivity features you would like to see, we would like to hear from you.

PowerPivot for SharePoint 2010 – The Business User’s Perspective

This article continues the series on PowerPivot that started with an overview as well as an introduction to PowerPivot for Excel 2010. We’ll now focus on the support PowerPivot provides for collaboration.

Frequently, business users need to share applications they create on their desktops with a workgroup. As it turns out, SharePoint provides a great platform that is critical for realizing the overarching goal of extending business intelligence technology to an organization:

• Great feature set for collaboration: Given that PowerPivot data is stored within an Excel workbook, whatever support SharePoint provides for sharing documents – versioning, workflow, easier security management, the flexibility of creating sites, sub sites or document libraries, customization of portals, etc. – is automatically available to PowerPivot users.
• Increasing pervasiveness: As SharePoint is deployed in more organizations, business users and IT are becoming more familiar with the tool as well as best practices on how to use it. PowerPivot will extend those tools, processes and learnings instead of replacing them.
• Centralized resources: Business users can depend on an infrastructure that’s around, where they can schedule heavy lifting to happen, where they don’t have to worry about backups or management of servers. The separation of duties that SharePoint enables – IT focuses on infrastructure & compliance, while business users focus on the content – is perfect for enabling managed Self Service Business Intelligence.

Let’s take a quick stroll through a few of the features that PowerPivot for SharePoint enables for business users.

PowerPivot Gallery

SharePoint users are very familiar with a flat list of documents such as this:

For our target audience, the library contents are frequently workbooks that users can better associate with visually. In addition, we want to make publishing PowerPivot workbooks inviting, fun, and representative of the care our users put into their spreadsheets. As such, PowerPivot provides several skins that can be applied on a document library. Here’s one where each container is a workbook and each thumbnail is a snapshot of the actual worksheet contained within the workbook:

Here’s another one that is more optimized for using larger real estate:

Both of these views use Silverlight to provide smooth scrolling across snapshots.

Irrespective of the skin, the bitmap shown is a real snapshot captured whenever the document is changed in SharePoint.

Integration with Excel Services

Selecting any of the snapshots launches the appropriate application. Selecting the snapshot of a worksheet, for example, will lead users directly into Excel Services with the right worksheet selected by default:

Remember that the workbook contains PowerPivot data within it. Performing any action that requires access to that snapshot – explicitly forcing a refresh, clicking on a slicer, retrieving a list of possible values to filter on, etc. – will transparently cause PowerPivot for SharePoint to extract this embedded data, load it into SQL Server Analysis Services running on the SharePoint farm, and service the query request from Excel Services. Thereafter all queries for that workbook, whether from the first user or any others, are satisfied using a high performance database engine. PowerPivot for SharePoint continues to manages the lifetime of this database and will automatically unload it if it hasn’t been used for a while.

There are, of course, many more details about this implementation but for this post focused on business users, it is important to note that the only difference end users see is that the first connection to the database may take slightly longer than once the database has been loaded, and of course, that they don’t need to download the complete document on their machine, or even have Excel installed on the machine, to benefit from what someone else in their workgroup produced.

Scheduled Data Refresh

Frequently workbooks contain PowerPivot snapshots that need to be refreshed periodically. Instead of requiring users to manually download the workbook to their desktop, refresh the data set and upload an updated copy of the workbook, PowerPivot for SharePoint allows business users to setup a data refresh schedule per workbook:

This mechanism:

• reduces the need for users to use their local machine resources for performing data refresh
• removes the burden of the mechanics of downloading, refreshing and uploading the file, as well as of remembering to do one more task
• can work well in organizations where there is lower bandwidth between a client desktop and the data sources, but there is higher bandwidth available between SharePoint and database systems, both managed by IT.

If PowerPivot contains data from different data sources, users can specify a schedule for each of those data sources independently as well. This is useful in the case where operations data needs to be refreshed more frequently than reference data, for example. 

Using PowerPivot Workbooks as Data Sources

Some PowerPivot users will spend significant time in importing, cleaning and shaping the data for their use. Others in their workgroup may want to reuse this effort. Today, one option available is to save a copy of the original workbook to retain the data and logic and delete what’s unnecessary. This unfortunately duplicates the data and breaks a link from automatically receiving future updates to the original spreadsheet. If that is too painful, users can choose another approach, of putting everything in one spreadsheet, with different sheets reserved for different users. Both of these options are less than ideal.

The mechanism that PowerPivot for SharePoint uses for extracting embedded data from workbooks and making it available for queries from Excel services is also available for any other application that connects to Analysis Services. i.e. as a user, I can create two different scenarios:

1. PowerPivot in my workbook can import PowerPivot data stored in another workbook. In this approach I get the benefit of a refreshable snapshot without needing to duplicate effort, and/or 
2. My workbook can use PowerPivot data stored in another workbook as a data source and send queries to it directly. This option is not limited to just Excel. In fact, any application that understands how to query a SQL Server Analysis Services database, such as Reporting Services, can query PowerPivot data stored within a workbook. The only change users needs to do is use the SharePoint URL to the workbook that contains PowerPivot data as the server name and PowerPivot for SharePoint takes care of the rest!

Summary

This was a quick overview of the features PowerPivot for SharePoint 2010 introduced in the August CTP. Hopefully you got a taste of how the PowerPivot team looked at this space holistically and the usefulness of our first release. As usual we welcome your comments either on this blog or on the PowerPivot blog itself, a site that contains many deeper articles on this topic.

Using PowerPivot with Excel 2010

In this blog article, we’ll step through using PowerPivot for Excel 2010 for building a rich application in Excel.

Note: following screenshots describe the SQL Server 2008 R2 August Community Technology Preview (CTP) functionality for a feature codenamed Gemini. As recently announced, Gemini will be released under the PowerPivot brand.

After installation, Gemini appears on the Excel 2010 ribbon:

Importing Data

Selecting the ‘Load & Prepare Data’ button launches the PowerPivot client window:

As you would expect from any modern tool, the ‘From Database’ button launches a wizard to step you through getting data from a database. More information about data import process and functionality is available on the PowerPivot blog here. For this article, we’ll bring in following tables:

• Media – all movies our hypothetical company rents out,
• Purchases – all purchases our customers have had in the last few years,
• Date – details per date of purchases such as whether it was a weekend, holiday, etc.,
• Time – time of day of purchases, and
• BoxOffice – table of box office sales for some of the movies in the last few years

During the import process, a snapshot of all these tables is imported into PowerPivot and stored in memory using a highly scalable engine. The table Purchase, for example, contains 100 million rows. Saving the file will not only save any contents on the workbook but also all PowerPivot data. i.e. the PowerPivot data is stored as a blob within the Excel workbook to simplify transport as well as remove the management overhead of managing separate database services.

After the import, each table shows up as a tab in the PowerPivot client window. In this case, our database had relationships already defined between these tables and they were automatically recognized up by the PowerPivot Data Import Wizard. Users can, of course, define their own relationships as well.

The PowerPivot client window allows you to operate with this large data set very quickly: common operations such as sorting and filtering typically complete in under a second on common hardware available today (< $1000).

PowerPivot also allows you to extend imported data using calculations which are maintained through data refresh. Various types of calculations and mechanisms for creating them are described on the PowerPivot blog here.

So far, we’ve imported data only from a single source. You can, of course, combine data from a variety of data sources – databases, text files, ATOM data feeds, as well as just Copying/Pasting data directly – and create calculations and/or relationships as if they were a table imported from a single database.

Visualizing Data

Once you have the data, PowerPivot enhances the Excel experience by providing quick templates for frequently used layouts:

Selecting ‘Four Charts’, for example, creates a worksheet with four Pivot Charts and one worksheet each for the PivotTable source of the data, speeding up the view creation process:

PowerPivot overrides the default PivotTable Field List by providing its own Task Pane:

For those users familiar with OLAP Pivot Tables, there are several interesting features.

Instead of seeing dimensions and measures within measure groups, PowerPivot shows a tabular view of the data – just tables and columns. In addition, based on whether a column is dropped in the Values area or on Axis or Legend, the PowerPivot Task pane creates a measure or uses the field as an attribute. For example, if Distributor is dropped  on the Values area, a measure (Count since this is a string column) is created automatically. If, however, Distributor is dropped on AxisFields, it is used to group the data. This highlights a few points:

1. PowerPivot leverages the richness of SQL Services Analysis Services mode internally without imposing dimensional modeling concepts on users, thereby enabling the functionality of OLAP PivotTables with a friendlier, tabular, model of the data, and without requiring IT to create and deploy Analysis Services cubes.
2. All PivotTable and PivotChart queries are answered by the PowerPivot engine running on the desktop using data that’s stored within the Excel workbook. A connection to a server running SQL Server Analysis Services is not necessary.
3. PowerPivot complements the richness of Excel’s visualization facilities – including tables and charts – with a rich, scalable, embeddable, data engine.

Within a matter of seconds a quick view is created…

… which can be easily made more appealing using standard Excel formatting features:

As mentioned earlier, saving the workbook at this point will save both the view above as well as the PowerPivot data and any enhancements such as calculations.

PowerPivot also provides helpers for adding slicers to the workbook:

The ‘Slicers Vertical’ and ‘Slicers Horizontal’ areas create zones on the left and top of the Pivot controls that help layout, align and resize slicers easily:

Summary

The combination of usability of Slicers and the performance of PowerPivot engine provides a very interactive set of views that one could easily mistake for a rich custom application built by IT after weeks of effort. This – Self Service Business Intelligence – is the key value that Gemini provides end users.

This completes a quick run through of PivotTable for Excel 2010. In the next article, we’ll dig in to PowerPivot for SharePoint to see how it enhances the SharePoint collaboration experience.

New PowerPivot Site

Introducing PowerPivot

Today we have a guest author from the SQL Server Analysis Services team, Ashvini Sharma, to tell us about the PowerPivot (née Gemini) feature that you may have heard about recently.

PowerPivot is the recently announced name of technologies this blog previously referred to by its codename, Gemini. This article describes why there is a need for such a tool, and briefly what PowerPivot provides. More information is available on the PowerPivot blog.

The Need for PowerPivot

PivotTables continue to be indispensible for allowing users to analyze their data flexibly and interactively. If you’re a subscriber of this blog, you’ve already read some of the recent articles on investments the Excel team continues to make around PivotTables for Excel 2010.

However, using a PivotTable that connects to an OLAP data source of course requires such a data source to exist. While a corporation may have many OLAP data sources where a single version of the truth and a unified model for looking at the business is necessary, this is not always the requirement.

For personal or workgroup-oriented solutions, our customers tell us there’re shortcomings in technology available: 

1. Requires advanced technical knowledge: Creating OLAP cubes is a non-trivial effort which requires highly technical understanding of concepts such as dimensions, measures, MDX, etc. As such, IT staff is frequently called upon to create such models on behalf of business users.
2. Incurs higher cost to solution: Since IT groups have limited bandwidth, only a few of an organization’s analysis projects get the necessary attention and resources. In order to increase efficiency, IT may also attempt to consolidate similar solutions, which incur higher coordination cost and increased time to delivery.
3. Produces solutions that are hard to customize: Business users frequently ask for data sets or analysis paths that they could not have predicted earlier. This is typical of ad hoc analysis that PivotTables support – an answer frequently leads to the next question and it is very hard to predict all possible questions, and time consuming to bake them in the model a priori. In addition, some data, may be so specific to a business problem that one user of the model may have it on their desktop, and it is not appropriate to share it across all users of a cube.
4. Increases cost of ownership and friction: Some business teams hire technical consultants or volunteer one of their own to take on this “burden”. Unfortunately, this responsibility goes beyond learning new technology into also developing skills and devoting time for managing and maintaining any delivered solutions. In addition, IT stays unaware of such underground applications and get rightly concerned about business decisions being made on solutions not supported by them.

Lets take a step back to make a few key observations:

• A significant gap exists between an organization’s need for deriving insights from their data and the organization’s capacity to satisfy that need.
• IT and business user resources are being stretched beyond their natural competencies: IT has to become more familiar with business users’ domain, and business users need to become more technical so that they can “speak” IT.
• In our view, what’s missing is simply technology that allows business users to help themselves while providing visibility to IT, a scenario we call “Managed Self Service Business Intelligence”.

PowerPivot

The PowerPivot functionality is delivered by SQL Server’s Analysis Services team in collaboration with the Excel team and is based on our experience delivering the Microsoft Business Intelligence platform over the last decade.

There’re two components of PowerPivot: PowerPivot for Excel 2010 and PowerPivot for SharePoint 2010.

Designed for business users, PowerPivot for Excel 2010 is a data analysis tool that delivers unmatched computational power directly within the application users already know and love — Excel. Leveraging familiar Excel features, users can transform enormous quantities of data from virtually any source with incredible speed into meaningful information to get the answers they need in seconds. PowerPivot for Excel consists of the following components:

• The Excel 2010 addin that delivers the seamless PowerPivot user experience integrated within Excel.
• The VertiPaq engine that compresses and manages millions of rows of data in memory with blazing fast performance.

PowerPivot for SharePoint 2010 enables end users to effortlessly and securely share their PowerPivot applications with others and work seamlessly in the browser using Excel Services. PowerPivot for SharePoint also helps IT improve their operational efficiencies by tracking PowerPivot usage patterns over time, discovering mission-critical applications, and improving system performance by adding resources. PowerPivot for SharePoint consists of the following components:

• PowerPivot Gallery – a Silverlight based gallery where users can share PowerPivot applications with others and visualize and interact with applications produced by others using Excel Services and Reporting Services.
• PowerPivot Management Dashboard – a dashboard that enables IT to monitor and manage the PowerPivot for SharePoint environment.
• PowerPivot Web Service – the “front-end” service that exposes PowerPivot data via XML/A to external applications such as Report Builder.
• PowerPivot System Service – the “back-end” service that manages the PowerPivot application database, load balancing, usage data collection, automatic data refresh, etc.
• Analysis Services – the Analysis Services server running the VertiPaq in-memory engine and integrated with SharePoint to load and manage the data within PowerPivot workbooks.

We’ll drill into these features in the next few blogs. Stay tuned!

Are you an Office Developer? You'll Want to Read This:

Today at the SharePoint conference the Office Developer Marketing Team announced the Office 2010 Application Compatibility Program. Details were posted today at Gray Knowlton's blog.  Here's an excerpt from his post:

With the great value Office 2010 brings for end users, IT Professionals and Developers, we are also investing heavily in making deployment of the new version of Office easier. As part of our focus on deployment, we have renewed priority on helping ensure applications and Add-ins for existing installations of Office continue to work without hangs, crashed or performance degradation when interfacing with Office 2010.

IT departments charged with upgrading Office take special care to find the add-ins, macros and other 3d party applications users have installed to ensure they will not cause problems after the upgrade is complete. Developers (professional and non-professional dealing with macros and scripts in Office applications), on the other hand, spend time testing and migrating their code to work seamlessly in Office 2010. And then, there is a task of migrating Pre Office 2007 binary documents to the latest Open XML format based files.

Today we are announcing the Office 2010 Compatibility Program to help address these areas. The compatibility program will provide tools for environment assessment, code scanning and remediation assistance, and an update to the document conversion tools introduced with Office 2007. The tools, guidance and services we are delivering will be the most comprehensive we have provided to date for a new release of Office.

The post goes on to describe how to get involved with a beta of the tools.  Check it out.

Learn More About Access 2010

I know many Excel users also tend to be Access users, so I thought it would be a good idea to share this bit of news.  The Access folks recently recorded a great video showcasing the major work that has gone into Access 2010.  In short: Access solutions, built in Access and published to the web where users interact with forms and reports via the browser.  Pretty exciting stuff.  Check it out here:

http://blogs.msdn.com/access/archive/2009/10/19/access-web-databases-and-the-access-show.aspx

More updates from SPC

Steve Tullis & Pej Javaheri presented “Intro to Excel and Excel Services 2010” to a standing room only crowd at SharePoint Conference 2010. Features demo’d include:

• Excel:
• Pivot Chart improvements
• Filter Search
• Named sets
• Improvements to conditional formatting
• Slicers
• Sparklines
• Excel Services:
• Client fidelity improvements & support of new Excel 2010 features
• (View) Support of workbooks containing images, VBA, etc.
• Edit & Collaboration in the Enterprise (SharePoint) and in the Cloud (SkyDrive)
• Leveraging JSOM to create highly interactive dashboards
• Interactivity, What-if, and modeling inside the Excel Web Access web part
• Embedding Excel-based content in Office Client applications using REST

Using the Q&A session  – which continued for ~ 15 minutes outside the room during the next presentation – as an indicator, people immediately saw the potential for how their business could be impacted by these products,  and are excited about getting their hands on the beta to try it out.

Office Web Apps Technical Preview Expanding

From the Office Web Apps Blog:

About a month ago I wrote about the Technical Preview on Windows Live. Since then we’ve seen a lot of interest in Office Web Apps and received numerous requests from people wanting to try them out. To make this possible, we are opening up the Technical Preview and inviting more people to try out the Office Web Apps.

For a limited time, you can sign-up for a Technical Preview account here (if you are already a part of the technical preview, this link will generate an error): http://skydrive.live.com/acceptpreview.aspx/.documents?aobrp=browse.

The features in this expanded Technical Preview are the same as the initial preview; however, during the preview (and beyond) you can expect to see new functionality added over time. Thanks for helping us make Office Web Apps even better!

All About SharePoint 2010

The SharePoint blog has a pretty extensive write up detailing the innovations in SharePoint 2010.  Here are some Excel relevant excerpts:

On Business Intelligence:

Historically, business intelligence has been a specialized toolset used by a small set of users with little ad-hoc interactivity. Our approach is to unlock data and enable collaboration on the analysis to help everyone in the organization get richer insights. Excel Services is one of the popular features of SharePoint 2007 as people like the ease of creating models in Excel and publishing them to server for broad access while maintaining central control and one version of the truth. We are expanding on this SharePoint 2010 with new visualization, navigation and BI features. The top five investment areas:

1. Excel Services – Excel rendering and interactivity in SharePoint gets better with richer pivoting, slicing and visualizations like heatmaps and sparklines. New REST support makes it easier to add server-based calculations and charts to web pages and mash-ups.

2. Performance Point Services – We enhanced scorecards, dashboard, key performance indicator and navigation features such as decomposition trees in SharePoint Server 2010 for the most sophisticated BI portals.

3. SQL Server – The SharePoint and SQL Server teams have worked together so SQL Server capabilities like Analysis Services and Reporting Services are easier to access from within SharePoint and Excel. We are exposing these interfaces and working with other BI vendors so they can plug in their solutions as well.

4. “Gemini” – “Gemini” is the name for a powerful new in memory database technology that lets Excel and Excel Services users navigate massive amounts of information without having to create or edit an OLAP cube. Imagine an Excel spreadsheet rendered (in the client or browser) with 100 million rows and you get the idea. Today at the SharePoint Conference, we announced the official name for “Gemini” is SQL Server PowerPivot for Excel and SharePoint.

5. Visio Services – As with Excel, users love the flexibility of creating rich diagrams in Visio. In 2010, we have added web rendering with interactivity and data binding including mashups from SharePoint with support for rendering Visio diagrams in a browser. We also added SharePoint workflow design support in Visio.

On the Office Web Apps:

We made SharePoint 2010 a great place to host the new Office Web Apps so you can view and update content from within a browser and include Office content as part of your web site (e.g. an Excel spreadsheet as part of “Sales Metrics Portal"). The Office Web Apps provide a familiar user experience, high fidelity viewing and essential editing without loss of data or formatting. They include Word, Excel, PowerPoint and OneNote. The OneNote client and Web App support is one of the coolest features of the release to enable multiple people to collaborate on a rich canvas online or offline. In addition to the Office Web Apps, we updated InfoPath Forms Services and Excel Services and added, new for 2010, Visio and Access Services.

For more SharePoint 2010 resources, check out this post.