Welcome to EMC Consulting Blogs Sign in | Join | Help

Christian Wade's Blog

Excel Services Programmability, JSOM aka ECMAScript, Excel as a BI Client Tool

Many posts in the blogosphere start like this: “It’s been a while since my last blog post …” I started writing this post the same way and realized that would be an understatement.

It’s been 4 years since my last blog post.  I’m still here.  Still the same person as I was before.  Just been busy in other areas of my life like spending time with my wonderful wife and kids, working out (a lot), surfing (or trying to), skiing (or trying to), playing church-league softball (it’s nothing like cricket), enjoying life …

I like the focus on Excel as the primary Analytical BI client tool on the Microsoft stack.  Excel is the tool the users know and love.  They’ve been using it for up to 20 years, making them less averse to it than other tools.  Plus it's on every desktop.

If the users author the Excel reports against the cube and save them to Sharepoint, they might still need some help from IT Support to get them published as a web page.  Most of the (power) users I know would be comfortable saving a report they created to Sharepoint, but using PerformancePoint to create an Excel Services page with interaction between multiple web parts?  Maybe.

Even so, I still anticipate that custom development, implying time-consuming release cycles and dependencies on report developers can go down by up to 80%, compared to say a Reporting Services based portal, which is by definition very development heavy.  An end-user oriented, Excel Services focused BI portal will result in greater user involvement in the report-authoring process.

I think about 80% of Excel Services reports can be delivered easily using PerformancePoint for filtering and web-part layout.

·         The Sharepoint filters are clunky to say the least; I don’t like them.

·         The PerformancePoint (AJAX like) partial page loading provides a nice user experience.

·         The ability to base the filter set on MDX gives flexibility.  For example show a rolling 1 year window of dates rather than all dates from/to the beginning/end of time (in the cube of course).

What about the remaining 20%?  What about when you need that extra bit of user interactivity that doesn’t quite come out of the box?  That’s where Excel Services JSOM (aka ECMAScript) comes in.  It addresses the remainder quite well, but is Javascript based (no one is perfect).

Here are some use cases that cannot be addressed with PerformancePoint leveraging Excel Services – but can be delivered using JSOM.

·         Conditional rendering of chart/grid based on current member selection in the filter.  For example, if the user selects a low enough level in the filter, we would like to display a different chart/grid in the child web part that shows different, low-level measures.

·         Handling user click event.  What if the user wants to click on a member in one web part and filter another based on that selection?

·         Multi-webpart filtering when using JSOM.  Unfortunately, JSOM doesn’t work on a PerformancePoint page, so you are stuck with the Sharepoint filter (yuck!).  Or alternatively, you can use the Excel Services filter from one web part to filter the other – another JSOM use case.

·         Formulas mode works nicely with JSOM for customized interactivity.

I was shocked to find there is almost no documentation on the web for JSOM.  When evaluating Sharepoint 2010 on the Beta program, I assumed MSDN just hadn’t been updated yet.  That is understandable for a Beta product.  However, even today, an MSDN JSOM page still looks like this …



Very helpful!  Especially when we are talking about a Javascript object model, which is by definition one of the most difficult things to debug, or take a trial/error approach to.

Most of the information out there is currently in a few good blog posts by Shahar Prish –  http://blogs.msdn.com/b/cumgranosalis/   I’d like to add a few more examples!

In my next post, I will address each of the use cases listed above with sample code.


Published Tuesday, November 09, 2010 9:35 PM by christian.wade

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



Stephen D. Oliver said:


I totally apologize for the lack of documentation for JSOM. There's a reason behind it but it's not excuse! I'm the newly assigned writer for Excel Services and I'm happy to tell you that we will republish a complete JSOM API reference set at the end of June 2011.

In the meantime, you've produced quite a handy bit of documentation here. It is much appreciated!



June 15, 2011 11:33 PM

Leave a Comment

Powered by Community Server (Personal Edition), by Telligent Systems