SPELL Nav, the successor of the Easy Tabs

The SPELL library is reaching version 0.8 this month. My main achievement in this release is the completion of the “Inline Navigation” module.

SPELL supports SharePoint 2007, 2010, 2013 and Office 365, and to demonstrate it I have updated all my sites:

All pages run the same version of SPELL, and the look of each menu is controlled via options. The idea is the same as with the original Easy Tabs – reuse the Web Part titles to automatically generate the navigation – but the code has been completely refactored. It is now more solid, for example when it comes to synchronization with the rest of the page, styling, print preview, and inclusion in wiki pages. It also offers more features:

  • Option to have more than one Web Part per tab
  • Compatible with display forms (this is especially useful if like me you are a fan of the “Related Items” feature)
  • Direct link to activate a specific tab (for example access the SharePoint Hosting section on the UMS home page)
  • Can be implemented in the middle of a zone, not necessarily at the top
  • Can include hyperlinks to external page (see the demos)
  • etc.

If you have subscribed to the SPELL interest list, you’ll receive a code sample by the end of this month.

As I have already mentioned, I consider that the Easy Tabs code is obsolete and I won’t update it. Also, it has become more and more time consuming to support such UI widgets because of the increasing number of SharePoint versions, browsers and supported devices (for example tablets and mobile), so I am more careful than before when it comes to releasing such tools.

That said, I understand that not everybody is willing to subscribe to the SPELL program. To allow end users to enjoy a smooth upgrade to SP 2013, in the next couple months I’ll publish in the SharePoint User Toolkit a lightweight version of the SPELL Nav that will cover most of the features offered by the current Easy Tabs.

2013
01/22
CATEGORY
Easy Tabs
Menus
navigation
Office365
SharePoint 2007
SharePoint 2010
SharePoint 2013
SP2007
SP2010
SP2013
User Managed Solutions
20 comments

Discussion: How do I exclude weekends when counting the number of days between a start and end time?

The only purpose of this post is to relay a discussion that has been active for almost two years on LinkedIn, in the SharePoint User Managed Solutions group:

How do I exclude weekends when counting the number of days between a start and end time?

You would think that such a question would bring a straightforward answer. But apparently it’s not the case, and depending on their site settings people get different results.

Feel free to try out the suggestions in the discussion, and let the group know which one(s) worked for you!

The SharePoint User Managed Solutions group on LinkedIn currently has ~600 members. It’s purpose is to discuss customizations done from the user’s side (Calculated Columns, SharePoint Designer, Web services, client-side scripts, Office 365, etc.).

 

 

2012
12/19
CATEGORY
Calculated Columns
3 comments

Interactive demo: charting in SharePoint

Last month I published a first demo based on the SPELL mini-BI solution, showcasing the ability to build multi-level drill-down charts.

Today we are taking a look at the edit mode, thanks to an interactive demo. I think the best way to understand what this is about is to simply follow the link, read the instructions on the page, and start playing with the charts. Then you’re welcome to post feedback and questions here, and I’ll try my best to respond.

The previous demo used the Google GeoChart API. This time, the pie and column charts rely on the Dojo Toolkit. Dojo offers a wide range of charts, as well as multiple themes (I only picked a handful for the live demo). It also comes with plugins like animations and tooltips (also featured in the demo).

The Matrix View is homemade, and part of SPELL.

Here again, the data comes from grouped views (3 to choose from for the demo). This time I have only set up a two level drill-down, and clicking on a chart element will directly open the SharePoint list.

I made a recent addition to the demo that I find interesting, I call it “client side calculated column”. It allows you to enter directly in the form a formula, similar to what can be done with calculated columns in SharePoint lists, just much simpler (currently only simple operations). In the demo, the “Value” field plays the role of the calculated column (see the context help for an example).

I won’t describe this advanced customization in detail on my blog, but I’ll be happy to elaborate on specific points. If you think it could address your business needs, you’re welcome to subscribe to the SPELL interest list and you’ll receive in a couple weeks a sample to test on your own site. And if you are looking for a free, entry-level charting tool, remember to check out the SharePoint User Toolkit!

2012
11/06
CATEGORY
Business Intelligence
Calculated Columns
Charts
SharePoint User Toolkit
SPELL
2 comments

Trick or Treat? Text to html, the wicked no-code way (Part II)

At the end of the previous post, we had this aha moment when we realized that rendering a field as rich text might not be that difficult, after all.

I’m back, after taking the time to set up a brand new demo to confirm our assumptions:

Demo

 

It works! As you guessed, just using a SharePoint Designer workflow to copy the content of the calculated column to a rich text field did the trick. For the record, here are the detailed steps:

  1. Create a list (I used the Tasks template here).
  2. Create a calculated column called cc, with the html formula. For the above demo I used calculated gradients (just did a copy/paste of the formula in the post).
  3. Create a column of type “Multiple lines of text” called HTMLcc, and specify that the type of text is “Enhanced rich text”.
  4. Create a one step workflow that copies the cc column to the HTMLcc column, every time an item is created or modified (screenshot below)
  5. We’re done!

 

The benefit of this approach is that we now have a clean field that renders our graphic – no ugly code around. I left the cc field visible for the purpose of the demo, but in production I would naturally make it a hidden field.

Although this looks great and easy, there are a couple gotchas to be aware of, if you decide to use this trick.

First, it is rather heavy: two fields and one workflow! And of course it won’t work for you if SharePoint Designer is not allowed in your environment (or you’ll need another way to copy cc to HTMLcc).

Second, it won’t help in views such as calendars, as these views don’t render rich text fields.

Third, as with any workflow action there’s a latency. The HTMLcc field will not refresh as quickly as other fields.

One last, important point: SharePoint is going to “sanitize” the html you copy to the HTMLcc field, and might decide to remove some of the content. This can actually be considered a good thing, as it addresses a security concern I reported in a previous post. On the other hand, it will block some script-based enhancements, like hover effects or countdowns.

Enjoy, and see you for next year’s trick: how to sort a list by month name. Happy Halloween!

2012
10/31
CATEGORY
SharePoint Designer
The HTML calculated column
Tips and tricks
6 comments

Trick or Treat? Text to html, the wicked no-code way

As I am actively working on a new training program called SPELL, it recently occurred to me that I should make Halloween a special day. So let’s start the tradition, the SPELL program annual trick or treat!

I have described many times my HTML Calculated Column technique on this blog. The idea is to use a calculated column to build html content. You have certainly already tried them out, for example for color coded calendars, progress bars, or KPIs.

The catch is that SharePoint won’t naturally interpret the string as html, so extra work is needed to do the conversion – usually done using JavaScript or xslt.

Now, rendering an html string as actual html is something any rich text editor can do, why is it so complicated with SharePoint? Oh yes, here is the thing: the output of calculated columns is just plain text. Ah, if only I could change it to rich text! Or find a way to transfer the content of my calculated column to a field that understands rich text!

But…Wait…

[To be continued]

2012
10/31
CATEGORY
SharePoint Designer
The HTML calculated column
Tips and tricks
1 comment

Workaround for the SP 2010 calendar resize bug

In SharePoint 2010, the calendar views are rendered via a script on the client side. This is a significant improvement over the heavy calendars of the previous versions (2003 and 2007), and in the 2013 version client side rendering has been extended to all list views.

Unfortunately, the SP 2010 calendar also came with a bug that I reported a couple years ago. It is made of two layers – one for the grid and one for the events – and when the calendar gets resized the two layers get out of sync. Really annoying! As far as I know, Microsoft has never fixed this. I keep receiving reports of this bug from users, especially as it occurs when calendar views are combined with the Easy Tabs.

If you have to deal with this annoying behavior, here is a simple workaround: go to the Web Part settings and give the Web Part a fixed width.

Hope this helps!

2012
10/24
CATEGORY
Calendars
Office365
SharePoint 2010
SP2010
Tips and tricks
5 comments

Teaser: real time Business Intelligence in SharePoint

StatesMap

I am making progress on my SPELL project. Its main component, the SPELL JavaScript library ($P), recently reached version 0.6 and has been implemented on a couple sites. I expect to reach version 0.7 by the end of the month and version 1 by the end of the year.

To showcase some of the capabilities of the SPELL library, I have set up a live demo featuring airline ontime statistics. In this demo, you can explore data across 3 dimensions: State (first level), Month and Carrier (second level). The third level are the list items themselves.

The first level is rendered via Google GeoCharts (for v1 SPELL will have other map options). The second level – matrix view – is a custom SPELL solution that mimics an Excel pivot table (much simpler though). You saw it in the slides if you read my previous post.

“Real time” refers to the fact that data is directly pulled from the SharePoint list. The charts always read the latest updates, as opposed to traditional BI patterns where data transfers are done at regular intervals. And there is no need for intermediate storage, as would be the case for Excel Services for example.

Note: for practical reasons the demo takes some shortcuts. For example the map doesn’t collect data directly from the list.

 I collected the airline data  (January to July 2012) from the Bureau of Transportation Statistics, I’ll add August as soon as it becomes available. Obviously interacting with more than 3 million items would not be reasonable, so I have done some pre-processing to aggregate the data by month and state. This brings the number of items down to ~500/month, for a total of 3,500 since the beginning of the year.

This is the downside of this solution: because it directly interacts with lists, it is subject to SharePoint’s usual limitations, for example the 5000 item threshold on views in SP 2010. Technically the tool could work with much bigger data sets, but this would require some adjustments and might not be desirable.

The solution showcased here has no ambition to compete with well established tools like PerformancePoint, SSRS or even Excel, but rather to offer a lightweight alternative. It presents a number of advantages that make it attractive :

  • Compatible with SharePoint 2007, 2010, Office 365 (evaluation of SP 2013 in progress)
  • Can easily be implemented by an experienced end user. No server side install, files are simply uploaded to a document library.
  • Direct, real time access to the SharePoint data (which means for example that it follows site, list and item level permissions).
  • Pure html (no Flash or Silverlight), accessible from both desktop and mobile devices.

This could for example be the ideal tool for a mid-size matrix organization, with multiple teams working on multiple programs. Managers could monitor the organization health, use the matrix view to analyze trends (what brings my organization up/down, a specific project or a specific team?), and finally access the items themselves.

2012
10/02
CATEGORY
Business Intelligence
Charts
KPI
SharePoint 2007
SharePoint 2010
SPELL
15 comments
Follow

Get every new post delivered to your Inbox.

Join 261 other followers