In the past two weeks I detailed both the new REST functionality in Excel Services as well as the Excel Services Gadget (which will hopefully be available for download soon). In the previous post I showed how you can use URL parameters to REST URLs to modify the result you get back. In this post, I will show how you can create an incredibly compelling story for users by using both that and the gadget that I introduced earlier this week.

To start, I have a workbook that contains information about my team bug counts. On top of everything else inside that workbook, it also contains a cell where, when an alias (email) is entered, pars of the workbook recalculate to show the bug trend/counts for that specific person:

If you click on the image, you will see the cell called “Alias” (the one that currently has “shaharp” inside it). the green table you see contains information specific to the Alias cell (so, if you change the cell to another value, the data will update accordingly). On top of that we also have a table of links in the workbook:

For each person there are two links – one under the alias of the person and the other called “Home Site”. Here are the formulas for both:

Under the alias, the formula is: =HYPERLINK("http://tsdlt2/_vti_bin/ExcelRest.aspx/Docs/TeamBugs.xlsx/Model/Charts('PersonalBugs')?Ranges('Alias')=" & A1, A1)

Under the Home Site the formula is:=HYPERLINK("http://tsdlt2/PersonalPages/" & A1 & ".aspx", "Home Site")

As you can see, the first formula resolves to a hyperlink to a REST URL where the object shown is the same “PersonalBugs” chart but where for each person, a value is set into the “Alias” cell. In this way, a single workbook/chart can serve multiple purposes. Here, we pass in the alias to a cell and then get the chart that corresponds to that person.

Here’s how this works. First, you can see that the link show up in the gadget itself:

Now, remember, there are two links here. The Home Site one is just a run of the mill link and when clicked, will open up in your default browser:

As you can see, the link corresponds to an alias which then opens up in the browser according to the formula.

The interesting part is what we can do with the other link though. The gadget recognizes that the link is actually a REST link – and so it decides that it can actually display it as part of the gadget in a flyout:

Once the gadget loses it’s focus, or you click inside it, the flyout will collapse back into the gadget.

And that’s it for this week. This week I tried to show you some interesting ways of using the new Excel Services REST APIs and some of the new and exciting opportunities it gives developers and users. There was a post about the “essentials” of REST in Excel Services – a kind of “cheat sheet” for all the things REST can do. There were two posts about the Excel Services Gadget which showed the concept and then a post that explained the more advanced stuff REST can do by passing parameters into the workbook via the URL.

Next week will show the actual code running behind the gadget. Have a good weekend!

The last post I wrote, I showed the Excel Services gadget and how it can be a great tool for bringing your Excel data right to the desktop. In this short post, I will show some of the other capabilities it has – namely, the ability to show ranges on top of charts.

In the settings page of the gadget, you have the ability to choose not only charts, but also ranges, tables and PivotTables. The workbook I have contains a simple color coded “score card” which contains the number of bugs I have and the average number – I can choose that score-card to show when the gadget is minimized – in some cases, it can be a better indication than the chart shown in the last post. The actual workbook that contains the range is relatively simple – here’s what that specific range looks like:

Here’s what the settings page looks like – notice that I still want to see the chart showing my stats when the gadget is maximized:

Once you hit OK and the gadget refreshes, you will see the score card showing in the gadget itself:

And just like in the previous post – clicking on the “Maximize” button will show the chart.

Here’s a short video that shows exactly how this works and what it looks like:

Enjoy!!

Last week I posted a number of pieces about the various ways the new Excel Services REST APIs allows developers to leverage data and elements from within workbooks by giving direct URLs to them. This post contains details about the available capabilities of the REST APIs – essentially a list of all the entities that are available for deep-linking.

All the relative URLs presented are based off the base REST URL which I explained in the very first post – the base URL essentially follows the following template:

http://[server]/_vti_bin/ExcelRest.aspx/[DocLib]/[File.Xlsx]

The following table shows all the accessible resources off this base URL:

As you can see, there are a number of elements inside workbooks that are addressable via a URL. For example, the table I pasted in here actually resides within Excel Services. This means that it is addressable via the /Model/Tables(‘[Name]’) rows. The following example shows this very same table accessed in IE by a direct link:

The name of the table is “Table1” – because the HTML format is the default one when accessing tables, that means that one does not need to specify it to get HTML back.

That’s it for now. The next post will show you a solution written using REST which will allow end users to put refreshable data from Excel Services on their desktop via a relatively simple Windows 7 Gadget.

Margins. In script.

To set? Use element.style.marginTop.

To get in runtime? Use element.topMargin.

Arghhhhhhh! It's like DHTML is forcefuly trying to make me hate it.

In my previous post, I showed what embedding a chart from Excel Services inside a blog looks like. In this post, we will take a step back and explore the discovery mechanisms built into the Excel Services REST APIs.

Discovery allows developers and users to explore the content of the workbook either manually or programmatically by supplying ATOM feeds that contain information the elements that reside a specific workbook. Starting out with discovery is pretty easy. If you will recall, the link we used to access the chart in the previous post looked a like this:

http://tssrva/_vti_bin/ExcelRest.aspx/sites/sp2009/shared documents/Bugs.xlsx/Model/Charts(‘PersonalBugs’)

To start seeing how discovery works, simply chop off all that Charts business and you are left with:

http://tssrva/_vti_bin/ExcelRest.aspx/sites/sp2009/shared documents/Bugs.xlsx/Model

This is your anchor for starting discovery. Pointing your browser to this URL will show up the four resource collections we currently support:

You will notice that you get access to four resource collections: Ranges, Charts, Tables and PivotTables.

Clicking on the Charts link will bring to yet another ATOM feed – this one listing all the charts available in the workbook:

As you can see, there are two charts on the workbook – one called “PersonalBugs” (which was used in the previous post) and one called “TeamBugs”. Clicking on “TeamBugs” will navigate to the actual chart URL:

Using the ATOM feed provided by the REST APIs allows for an incredibly easy way of getting to the data you care about. The flip side of this is that since these are just ATOM feeds, they are readable by machine as well! So, in the previous screenshot, if you ask FireFox to give you the source of the document, you get a bunch of XML:

As you can see, the feed contains easily traversable elements that will allow any piece of code to go through and discover what elements exist in the workbook – each ATOM entry corresponds to a Chart that can be accessed.

This same mechanism applies to discovering PivotTables, Tables and Ranges. In the next post, I will show some of the capabilities revolving around those.

Now that Microsoft SharePoint Conference 2009 is done, I have some more time to start posting about all the nice things we have done with SharePoint 2010 – especially with Excel Services. In the coming months, I will post about Excel Services, concentrating on some of the new capabilities we enabled via our new extensibility mechanisms.

Since the new REST capabilities we built into Excel Services are close to my heart, I will start discussing them first.

Now, if you know what REST is, you will probably aching to comment that what I am going to discuss is not really REST. There are quite a few parts missing and some things that may not be exactly up to the “standard” (as much as it is one, which is not much). If it’s easier for you, you can also call it “URL facilitated access to spreadsheet component elements”. I find REST quicker to say.

The new API allows you to access parts of the workbook directly through a URL. The first part of the URL is a representation of the workbook location. The second part is the path to the requested element inside the workbook. In practice, there’s also a “marker” which is our entrypoint (an aspx page).

To start off with a simple example, say there’s a workbook called bugs.xlsx which lives in a document library called “Shared Documents”. Say that workbook contains a chart called “PersonalBugChart”. You can get direct access to the chart via a simple URL:

http://server/_vti_bin/ExcelRest.aspx/Shared Documents/Bugs.xlsx/Model/Charts(‘PersonalBugChart’)

Each Excel Services REST URL is built out of three parts:

Marker path: That’s the “http://server/_vti_bin/ExcelRest.aspx”. To access REST you always need to preface it with this.

File path: That’s the “/Shared Documents/Bugs.xlsx” part. This is the file that contains the element you are interested in.

Element path: That’s “/Model/Charts(‘PersonalBugChart’)” part. This is the path inside the workbook to the element you request.

That’s it. The result of this URL is a PNG stream which would be the chart that is named PersonalBugChart. When the workbook updates, the REST path will return the new chart – this is a completely live URL. Placing this URL inside an img tag on a page will place the image on the page:

<img src=”http://server/_vti_bin/ExcelRest.aspx/Shared Documents/Bugs.xlsx/Model/Charts(‘PersonalBugChart’)”>

If you have SharePoint 2010 Enterprise, try it with your own Excel workbooks! It’s as easy as that! Change the chart type, the contents, anything, and the next time the chart is requested, you will see the new content.

In the next post, we will discuss how you can access ranges both as data (via XML) and as HTML fragments.

I was going to get coffee today at work, minding my own business, when someone from the test-team asked me if the order I was doing things when preparing coffee was intentional. Absentmindedly, I explained the reasoning behind the order of things and how I reached this way of doing things and belatedly realized that I may have gone too far in my explanation – I think it was the look of pity in his eyes that clued me in.

To explain, first you need to understand what our kitchenette looks like and the various steps in making coffee:

Here are the various things we are seeing here:

1) Access – that’s where I typically come to the kitchenette from.
2) The coffee machine
3) Counter
4) Cups
5) Lids
6) Fridge (Where milk is).

Our coffee machines basically give you an Americano – no milk. The way I take my cup’o’joe is to do 12 oz of machine generated coffee (into a 16 oz cup) and fill another 2-3 oz of milk – and then the  cup needs to be lidded as well. Of course, there are a number of variations on how to make such a coffee with this layout.

Making Coffee – the naive way:

Step 1: Go to position (4) – grab a cup.
Step 2: Go to position (1) – place cup at machine.
Step 3: Press buttons - Wait for coffee to be made.
Step 4: Take milk from fridge (6)
Step 5: Fill cup with milk (possibly at position (3))
Step 6: Discard milk carton or, if still full, place back in fridge.
Step 7: Go to (5) and grab a lid.
Step 8: Place lid on cup.

And we are done. To understand how this can be optimized we first need to figure out how much each part of the coffee making process takes:

Making coffee (the machine phase): ~2 minutes (constant).
Getting milk (from position (1)): On average, takes about 20 seconds. Depending on traffic, can easily take as long as 1:00 minutes if there is a lot of traffic and even longer if you consider the possibility of people talking to you while you are making the coffee.
Getting a cup: Usually takes about 5 seconds from position 1 – can take longer due to traffic and chat. Rarely takes more than 30 seconds and requires little concentration.
Filling cup with milk: Takes about 5 secodns.
Discarding/Returning milk: Takes about 10 seconds. Worst case (traffic etc) can take up to a minute.
Grabbing a lid: 5 seconds from position (1).
Lidding the cup: 5 seconds at position (3).

So.. Translating this into pseudo code, this is what we get:

function NaiveCoffeeMaking()
{
WalkTo(4);
GetCup(); // 10s
WalkTo(2);
PlaceCupInMachine(); // 1s
MakeCoffee(); // 120s
GrabCupFromMachine(); // 1s
WalkTo(6);
GetMilk(); // 20s
PourMilk(); // 5s
DiscardOrReturnMilk(); // 10s
WalkTo(5);
GrabLid(); // 5s (probably closer to 3s in this case)
WalkTo(3);
LidCup(); // 5s
}

Phew.. Okay.. So we have that. Now lets calculate how much time this takes (best case):177 seconds!

The thing to consider though is that the machine phase can be asynchronous – that is to say – while the machine is making coffee, the CPU (me) is waiting Idle. The question is, how can we use that to optimize the time? For our pseudo code then, we will now separate MakeCoffee into two functions: BeginMakeCoffee() which essentially means pressing the buttons on the machine and immediately return to the caller and EndMakeCoffee() which completes the operation (and if not done, blocks until done). And so, the most optimized version of how to make coffee is:

function FullyOptimizedCoffeeMaking()
{
WalkTo(2);
BeginMakeCoffee(); // 120s – but returns immediately
WalkTo(4);
GrabCup(); // 5s
WalkTo(6);
GetMilk(); // 20s
PourMilk(); // 5s
DiscardOrReturnMilk(); // 10s
WalkTo(5);
GrabLid(); // 5s (probably closer to 3s in this case)
WalkTo(2);
PlaceCupInMachine();
EndMakeCoffee();
LidCup(); // 5s
}

Notice that when we call BeginMakeCoffee(), we immediately return and continue processing – going through the phases of getting the milk, preparing the cup and only then, when done, placing it in the machine. So, in this case, best case time would be: 125 seconds. That’s 120 seconds that it takes to make the coffee plus the 5 that it takes to lid the cup. The rest of the time is taken up in parallel to making the coffee.

But there’s a problem! BeginMakeCoffee() and EndMakeCoffee() do not work exactly like a machine async operation. Generally speaking, when you issue an async operation, the sub-system will take care to store the result for you when it’s done and wait patiently for you to call the second part of the operation (EndMakeCoffee) before it returns the result. A coffee machine does not work that way. When the coffee is ready, it’s coming out – whether there’s something to store it or not. That presents some problems for the FullyOptimizedCoffeeMaking() function! What happens if instead of talking about Best case, we were to talk about worst case. The GetCup() can sometimes take 30 seconds. GetMilk can sometimes take 120 seconds. As well as some of the other things that make take longer under high-stress cases. What happens then? Well, what happens is that the coffee starts pouring and there’s nowhere for it to go, so it gets wasted. Sacrilege!!

In essence, we need something that’s both optimized AND safe. Since life does not have convenient temporary memory (where the coffee would have been stored) or sync objects (where the act of pouring the coffee could have been locked and deferred until a cup is to be placed underneath the nozzle), it means we need to work around those limitations:

function OptimizedAndSafeCoffeeMaking()
{
WalkTo(2);
BeginMakeCoffee(); // 120s – but returns immediately
WalkTo(4);
GrabCup(); // 5s
WalkTo(2);
PlaceCupInMachine();
WalkTo(6);
GetMilk(); // 20s
WalkTo(2);
PourMilk(); // While cup is in the machine – 10s
WalkTo(6)
DiscardOrReturnMilk(); // 15s
WalkTo(5);
GrabLid(); // 5s (probably closer to 3s in this case)
WalkTo(2);
EndMakeCoffee();
LidCup(); // 5s
}

The CPU (me) now needs to work more – you will notice that there is more walking around to accommodate the new placement of the cup (it can no longer be carried around – one needs to walk to it to pour the milk and then back to the fridge to discard or put back). The time this now takes is still 125 seconds - 120s to make the coffee and prepare the cup and another 5s at the end when the cup is lidded. Switching the lines around and doing more leg work though has bought us a lot more safeness.

Note that this is still not perfect – if the 5s it takes to get a cup and place it in the machine would balloon from 5s to 120s, we would be right back in our original position of losing our coffee. However, that is far less likely to happen in this case. If you want to eliminate risk completely, simply switch BeginMakeCoffee() with GrabCup() and PlaceCupInMachine(). You will lose 5 seconds (making the total time 130 seconds), but you are then completely safe.

So that’s what I was trying to explain to the tester when that look came to his face. The thing is, I am sure everyone does this. But people probably do this unconsciously or at least in a less obsessive compulsive analytic manner than a software developer would.

Image Resizer has moved to another blog – this way it does not interfere with this blog which is all about Excel Services – my actual daytime job.

Find it here!