THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

Do more with less: the missing feature in Excel Services

A little background: in these days I’m preparing sessions for some upcoming conferences in Italy (despite the English titles, most of the sessions will be in Italian: Microsoft SharePoint & Office Conference 2010 next week and Microsoft SQL Server & Business Intelligence Conference 2010 on April 20-22) and of course PowerPivot will be an hot topic. However, an important part of the puzzle is Excel Services, which makes the rendering on the web for this new self-service reporting environment. You know, a good picture worth a thousand words and for this reason I tried to push the rendering engine. And from here we start the discussion of this post…

Well, what is the state-of-the art of a dashboard? Is it a page full of gauges? Is it a thermometer? Is it a mix of pie charts, 3-d histograms and colored arts? Absolutely not. They don’t have value. They are good just for a demo, but in everyday work you want to examine correlations between data and having control of a large number of parameters and indicators.

Thus, a good collection of dashboards is available on the Excel Competition Dashboards page on BonaVista website (now acquired by XlCubed). Yes, they use a little component called Microcharts to do that, and in fact Excel 2010 has its own Sparklines but even with older versions of Excel you can get Sparklines for Excel for free using a free library written by Fabrice Rimlinger.

Well, it seems easy, the sexiest dashboard today strictly follows Stephen Few’s rules, which includes many important ideas of Edward Tufte. Now your dashboard is cool, life is good but… wait, you still have to publish it. Well, devil is in detail.

The most important feature I learnt of Excel when I started composing dashboard is the Paste Picture Link, which is illustrated in a short video you can find in this page. This feature is fundamental to get the desired layout without the alignment constraints of rows and columns typical of Excel. Just consider the dashboard published in this XlCubed demo and look at the following detail: how do you manage the different column alignment of the two bands in this picture?

image

Blue lines highlight the required alignment for some objects in the upper zone and orange lines highlight the alignment for the bottom part. Of course, you might spend hours of your life trying to merge columns in a way that match the requirements, but every time you want to move some object or insert something new in the middle, the nightmare begins! However, using Paste Picture Link feature, you simply design single components (having he same column/rows organization) of the dashboard in different (hidden) places of your Excel sheet and “paste” these components on your dashboard sheet, without worrying about alignment compatibility! And, as you can imagine, using the XlCubed Web Edition you can publish it on the web. But I don’t want to promote XlCubed, it’s just a good product that I want to use as a reference before someone says that doing this rendering is too complex. Someone else, smaller than Microsoft, already did it.

Well, you probably already know my point now. Excel Services 2010 has been improved, it has much more features than the previous edition, in the long term I understand that 90% of the reports will be Excel sheets published on SharePoint. It’s only a matter of time. But, today, the Paste Picture Link feature is not supported by Excel Services. That’s it.

Uh, before someone with a good sense of humor tries to suggest that PerformancePoint Dashboard Designer should be used to create dashboards, I want to underline that my initial reference to the dashboard gallery is not casual. But I will completely change my opinion about a dashboard product the day I will be able to create and publish the kind of dashboard that I can create since 2007 using Excel and XlCubed.Just to make an example of how you can do more (dashboards) with less (components).

PS: I like Excel and SharePoint 2010 – it’s a big improvement over previous version, but still far from competition if you consider more vertical products.

Published Tuesday, March 02, 2010 11:45 PM by Marco Russo (SQLBI)

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Tim Kent said:

Hi Marco

Agree about PerformancePoint.  It is so far from being a dashboard building tool but it is useful as a wrapper for Excel Services reports in Sharepoint (the filters are far nicer than the built-in SharePoint functionality)

Tim

March 2, 2010 5:26 PM
 

Excel Services Developer said:

This is SharePoint - you can use multiple web-parts to generate your dashboard w/o having to go through all this - have each WP show a different named object and you will get there.

This only scales so far though - I am not sure how many linked images you use in Excel for a single dashboard, but SharePoint/Excel Services will have limitations when you start having a lot of them.

March 5, 2010 1:47 PM
 

Colin Banfield said:

"Well, what is the state-of-the art of a dashboard? Is it a page full of gauges? Is it a thermometer? Is it a mix of pie charts, 3-d histograms and colored arts? Absolutely not. They don’t have value. They are good just for a demo, but in everyday work you want to examine correlations between data and having control of a large number of parameters and indicators."

I couldn't have said it any better. Yet 99% of the products out there provide all this useless garbage. I don't see BI improving unless there's a fundamental change in visual tools (and good visual tools are critical for any insightful analysis).

"Excel Services 2010 has been improved, it has much more features than the previous edition, in the long term I understand that 90% of the reports will be Excel sheets published on SharePoint. It’s only a matter of time. But, today, the Paste Picture Link feature is not supported by Excel Services."

If Paste Picture Link was the only issue with Excel Services, I could live with it. However, Excel Services is a young developing product with the same three-year development cycle as mature Office products. It lacks VBA support, lacks data validation sopport, form control support etc. Yes, you can build some of this functionality using JavaScript and REST on the server side, but I'd like to be able to develop a complete solution in the Excel client that I can simply publish to the server - without having to re-invent elements on the server.  

April 6, 2010 6:45 PM
 

Marco Russo (SQLBI) said:

Colin, I agree that publishing Excel with 100% of feature would be great!

In my priority scale, just for designing purposes, Paste Picture Link is probably the first feature. But there are many others, like Sparklines working on PivotTable, for example...

April 6, 2010 7:28 PM
 

Colin Banfield said:

Marco, I've been using Paste Picture Link for a long time, but since Excel 2007 shipped with the new charting engine, I've not used it much at all. I might use it if I have a table in a dashboard, but sometimes I prefer to use the worksheet grid. Fonts appear better on the worksheet than in the picture. True, you can't move a range around as easily as a picture, but in practice, it's not been an issue.

April 7, 2010 5:18 PM
 

Marco Russo (SQLBI) said:

Colin, we could discuss very long, but the point is that we are in 2010 now, and the best way to publish a dashboard with MS Technologies is using Excel. And Excel is not 100% ready to publish dashboards. And here we are :)

April 7, 2010 6:50 PM
 

Colin Banfield said:

"And Excel is not 100% ready to publish dashboards."

On this point I think we are in total agreement :)

April 8, 2010 7:12 AM
 

Abhishek said:

I agree with Colin.

In many projects where Excel + Excel Services was pitched, the business users didn't like it. Reasons?

1. No Edit support via browser. (you need to have Excel Client for editing/customization/personalization). In my case, business users resented this.

2. No VBA. this cripples the ability to do validation and little other changes.

June 10, 2010 3:32 AM

Leave a Comment

(required) 
(required) 
Submit

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog

Syndication

Archives

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement