Excel Services Cheat Sheet

You want (or maybe you need) to learn Excel Services in 3 minutes?

Ok, ok, I know it’s impossible, but anyway, if you have to cheat, here’s some help.

Ready? Go!

· Their name is Excel Calculation Services

· They are exposed by a Service Application, that I strongly suggest you create J

· You need the Enterprise Edition of SPS2010

· The ECS perform computations and rendering on the serve side, starting from an Excel 2007/2010 file that should be “compatible” and uploadedl into a SharePoint library

· What compatible means?

o Long story: http://office.microsoft.com/en-us/excel-help/differences-between-using-a-workbook-in-excel-and-excel-services-HA010021716.aspx?pid=CH101024611033. It’s for Office2007/SP2007, most of these limitations still apply

o Short Story: no VBA macros, no data validation, no external worksheet references, etc…

· You access computation results from a client application using SOAP Web Services or the brand new Rest Services API

· You access the sheet (or part of it) rendered by the server just by clicking the “View in browser” link on the ECB, or maybe the document link itself if the default library settings state that the default open behavior is “Open with Server Side Handler”

· When the document is viewed in the browser, all logics behind it (i.e. all formulas) are flattened and you only get the results of the computation

· If a user has just the ViewOnly permissions, he can just view the document in the browser or download a snapshot of it. Once again, the snapshot contains just data, without formulas

· If a user has, at least, the Read permissions, he can also download a copy of the document, which contains also formulas and logics

· From an administrative perspective, you – the Farm Admin guy – should probably:

o Define “trusted locations” (i.e. libraries where the ECS consider documents trusted and, as such, can manage them as discussed above)

o Define external data connections (upload Data Connection Files, specify how connections are performed, take care about authentication, etc…)

· If you are guessing what a data connection is… well… it’s a connection to some external data repository! More seriously speaking, y can define such connections with the Excel Client (or retrieve them from a central location that is, of course, your SharePoint environment). Data that flow through data connections can be displayed in a sheet, with al sort of cool features that Excel provide (pivot tables and reports, slices, etc…)

Mmmmmhhh… it took more that 3 minutes, probably J

Italian Microsoft SharePoint and Office Conference 2011

Microsoft Technical Conferences 2011 – Microsoft SharePoint and Office Conference 2011 – Milan, April 19-21, 2011

Microsoft SharePoint & Office Conference si presenta come un appuntamento imperdibile, ricco di contenuti di elevato livello tecnico, rivolti a IT Professional, Developer, Site Builder e Decision Maker.

I’ll be talking about:

All around Metadata

Da sempre SharePoint ha, tra le proprie caratteristiche peculiari, un supporto ampio ed esteso ai “metadati”, che arricchiscono item e documenti rendendoli informazioni ricche strutturate. Tale supporto è stato migliorato nel tempo fino ad arrivare all’attuale versione di prodotto, in cui alla gestione tradizionale si aggiungono funzionalità “enterprise”, che consentono di centralizzare la definizione di tassonomie e folskonomie. Si parla, non a caso, di “Managed Metadata”, con un chiaro riferimento ad un sistema in cui le modalità di gestione assumono un ruolo centrale. In questa sessione affronteremo le principali tematiche relative alla definizione e, appunto, alla gestione di un set di metadati condiviso e fruibile, focalizzando l’attenzione sull’architettura delle informazioni, sul loro utilizzo e sull’opportunità di estendere le soluzioni basate su metadati per adattarle alle esigenze di progetto. Una sessione di approfondimento, in cui gli aspetti legati all’infrastruttura e allo sviluppo sono descritti con l’occhio “funzionale” di un Information Architect.
On stage with me: Elisabetta Sasselli.

Troubleshooting e Debugging di Applicazioni SharePoint

SharePoint è una piattaforma applicativa complessa, non solamente per via delle numerose funzionalità native del prodotto, ma anche – e forse ancor di più – per via di un’architettura estesa, che richiede buone competenze a diversi livelli per essere padroneggiata in maniera ottimale. Per queste ragioni lo sviluppo di applicazioni SharePoint può essere denso di insidie, e la risoluzione di errori è un’attività all’ordine del giorno per qualsiasi SharePoint Specialist. Nonostante i miglioramenti degni di nota introdotti con SharePoint 2010, troubleshooting e debugging rimangono attività estremamente delicate e, a volte, coperte da un alone di magia :-). La brutta notizia? Non esistono bacchette magiche. Quella bella? Esistono strumenti, tecniche e processi che consentono di strutturare troubleshooting e debugging in maniera più razionale, circoscrivendo l’area di diagnosi e riducendo i tempi di intervento. Questa sessione illustra alcune di queste tecniche, con l’obiettivo di aiutare lo sviluppatore per il bene del progetto… e del suo stomaco 🙂

SharePoint Performance Tuning for Public web sites

Pagine veloci? Utenti contenti? Clienti soddisfatti? Istruzioni per l’uso.
On stage with me: Elisabetta Sasselli and Igor Macori.

European Best Practices SharePoint Conference 2011

European Best Practices SharePoint Conference 2011 – London – April 11-13, 2011

Best Practices is about doing things the right way: the most efficient, effective ways to achieve goals, distilled into adaptable, repeatable procedures you can use

  • sort through the best solutions to any task
  • reach consistent, confident decisions at every level
  • break the cycle of avoidance, disagreement and subpar results
  • eliminate design, deployment, organizational and administrative confusion
  • enhance communication, collaboration and efficiency while lowering costs
  • avoid technology errors, misconceptions and pitfalls
  • leverage the hard-won experience of industry leaders
  • gain early competitive advantages
  • replace disorder with clarity, direction and confidence

This Conference gathers the leading SharePoint Experts to define, describe and set methods that will become industry standard – insights you can gain now to avoid pitfalls, cut costs and gain a competitive edge. Speakers include:

  • Microsoft MVPs
  • Microsoft Product Team Members
  • Top industry executives and authors
  • Leading trainers, consultants and topic experts
  • Industry colleagues

I’ll be talking about:

Debugging and troubleshooting SharePoint 2010 Applications

SharePoint is definitely a complex application platform. This is due not only to the huge number of features that it provides, but also to its internal architecture, which is based on several base technologies and is, therefore, quite hard to be understood and mastered. This is one of the reasons why the development of SharePoint Applications is traditionally considered tricky and intricate, and… yes, you probably have to spend some time troubleshooting and debugging what seems not to be working as expected! But wait! You have tools you can use and techniques you can learn! And you can try to make your troubleshooting experience a little less painful, leveraging the logging improvements that SharePoint brings to the table and identifying issues much more quickly and consistently. We’ll try to explain some of these techniques and these tools, hopefully providing some good tips that will help you reduce the time spent in front of a debugger or a long, long log file.

Improving the CQWP Markup with SuppressWebPartChrome

Several SharePoint web parts, the ContentByQueryWebPart being one of those, expose a property named SuppressWebPartChrome.

When set to true, the web part code strips out the surrounding layout that constitutes the “chrome” of the web part itself.

An example will explain this much better.

Consider the following HTML markup, generated with the SuppressWebPartChrome property set to false (its default):

<table class=”s4-wpTopTable” border=”0″ cellpadding=”0″ cellspacing=”0″ width=”100%”>


          <td valign=”top”><div WebPartID=”00000000-0000-0000-0000-000000000000″ HasPers=”true” id=”WebPartWPQ3″ width=”100%” class=”ms-WPBody noindex” OnlyForMePart=”true” allowDelete=”false” style=”” ><div id=”cbqwpctl00_News_g_ded0dcdb_9aaf_44c3_98b5_6af1963c4f3b” class=”cbq-layout-main”><ul class=”dfwp-column dfwp-list”><li class=”dfwp-item”><div class=”item link-item”><a href=”http://erickson.sharepointjukebox.vmw/News/Pages/Test.aspx” title=””>Test</a></div></li></ul></div></div></td>



And compare it with the following markup, where the SuppressWebPartChrome property has been set to true:

<div id=”cbqwpctl00_News_g_ded0dcdb_9aaf_44c3_98b5_6af1963c4f3b” class=”cbq-layout-main”><ul class=”dfwp-column dfwp-list”><li class=”dfwp-item”><div class=”item link-item”><a href=”http://erickson.sharepointjukebox.vmw/News/Pages/Test.aspx” title=””>Test</a></div></li></ul></div

In the first snippet I have higlighted the HTML portion that gets stripped out.

This is a huge improvement, especialloy because:

  • The resulting markup is smaller, with a lower network bandwidth consumption as the first consequence
  • You get rid of a surrounding table, which makes your markup more compliant to accessibility requirements (and, more generally, it just makes your markup better from a semantic perspective)

Needless to say, I suggest you to “think in advance” and apply this very early during the development lifecycle, since you may need to adjust your CSS or JS libraries accordingly!

FileRef vs EncodedAbsUrl

During the last days I found myself applying a lot of small post-migration patches, some of which can be accomplished with some (PowerShell) script, some of which I keep doing everything by hand.

The last one, and the one I’m talking about here, is related to a couple of custom DataFormWebParts that used the FileRef attribute of the SPListDataSource to render hyperlinks.

Now, it seems that in SharePoint 2007 these attributes return a site relative url with a leading slash (i.e.: /sites/somethingelse/etc), whereas I inspected the same attributes in a SharePoint 2010 environment and I got the site relative path without the leading slash. This, of course, causes the links to be considered relative to the current path, and the final effect is a lot of 404 all around.

The fix is super easy: I substituted the FileRef attribute with the EncodedAbsUrl attribute, which return exactly what I was expecting.

Sgunf 🙁