Tuesday, June 16, 2009

SharePoint jQuery: Managing Scripts

In the two previous posts, I've shown some jQuery scripts for manipulating the SharePoint user interface by adding the scripts directly as source text in a Content Editor Web Part (CEWP). This is, however, not a good way of adding the scripts to pages considering maintenance and sharing of the provided functionality across multiple pages. Just think of making a correction to the generic scrolling view with frozen header script to improve its performance, after adding it directly in CEWPs on tens or hundreds of view pages.

A better approach is to store the scripts as files that are included in CEWP just like the jQuery library itself. You can use a single document library to store the script files; but as some of your jQuery scripts will be more generic (reusable) than others, I would recommend using several managed document libraries based on script classification.

I like to classify the scripts like this:
  • Unique: the script is unique to the web page
  • Shared: the script is shared between multiple related web pages, typically for all view pages of a specific list
  • Common: the script is common for unrelated web pages, typically for view pages for more than one list
Store common scripts as include files in a ”MasterScriptLibrary” in the root site-collection to make them common for all site-collections within the same SharePoint web application. This includes the jQuery script itself. Store shared scripts as include files in a "ScriptLibrary" per managed site-collection to make them shared between all sites and subsites within the site-collection. Use a document library in the top-level site of each site-collection as the script library to store your jQuery JavaScript files.

In addition, any custom CSS style sheets you use in your scripts should also be stored in the script libraries. Remember to assign ”Read” permissions on the script libraries to the group ”Authenticated Users”.

Unique scripts need no script library as they are included directly in CEWPs. Really large companies should consider having a global script library that stores jQuery files used across all SharePoint web applications.

Add the include files in this order in a CEWP: jQuery, styles, common and then shared. Add any unique script after all included styles and script.

Try to avoid using more JavaScript files than strictly necessary, as many include files will impact the load time of the page.

Always use page relative (../path/file.js) or server relative (/path/file.js) URLs in the <script src= > attribute. See this post for how to get site relative URLs. Avoid using absolute URLs at all costs. The web application root site-collection is the "server" in SharePoint terms.

If you don't want to expose and manage the scripts in document libraries, you can always provision the script files to the [12] hive using a feature: SharePoint jQuery deployment feature. See this EndUserSharePoint post for more alternatives for managing include files.

Related slidedeck

SharePoint Site Relative CEWP Included Script

This post is about injecting site relative JavaScript include files in SharePoint using CEWP. It is not about injecting JavaScript files using the server-side ASP.NET script manager or any other server-side mechanism.

We all know that the src attribute of the <script> element is either page relative or server relative; or God forbid, absolute. You cannot use the tilde (~) to make the URL site relative, but you can achieve this using the SharePoint page L_Menu_BaseUrl variable and inject the include file into the page dynamically:

<script type="text/javascript">
' src="' + L_Menu_BaseUrl +'/ScriptLibrary/SiteSpecificScript.js"',

' type="text/javascript"><\/script>');

This actually makes the include file URL server relative, but without hardcoding the site path into the URL - making it equivalent to being site relative.

Friday, June 12, 2009

SharePoint jQuery: Scrolling View with Frozen Header

SharePoint lists that contain a lot of numeric data type columns can be hard to look at when the column headers scroll out of view. Such lists are typically created by moving data entry into SharePoint from Excel, where users could easily freeze the header row. As I showed back in 2005 for ASP.NET datagrids, you can achieve a frozen header row also for HTML tables by applying a simple CSS style.

The following jQuery script contains a selector to first find the table that contains the view of the specified list. Then it wraps the table in a scrolling pane and finally applies the CSS style that freeze the header row. The style class itself is also included above the actual jQuery script. The script shows how to use a jQuery object as the selector context to speed up element lookup.

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.2.6/jquery.min.js" 
<style type="text/css">
.DataGridFixedHeader { position: relative; top: expression(this.offsetParent.scrollTop);}
<script type="text/javascript">
var $table = $("TABLE[ID^='{4C9CFF20-B467-4E10-820C-0A132442CF98}']:first"
$table.wrap("<DIV style='OVERFLOW: auto; HEIGHT: 420px'></DIV>");
$("TR.ms-viewheadertr:first", $table).addClass("DataGridFixedHeader");

The ID attribute of the SharePoint view <TABLE> element is a combination of the the list's GUID and the view's GUID. The script shows how to use the attribute starts with filter (^) on the table's ID. The found table is kept in a jQuery object named $table for manipulation and for use as a jQuery context. Use View-Source to view the page HTML, search for ContextInfo and look for the first <TABLE> element beneath it to find the list's GUID.

The $table object is then injected into a DIV with a fixed height and overflow:auto to get a scrollbar on the list, using the wrap(html) manipulation function. The script then looks up the table's header TR element based on it's class and appends the DataGridFixedHeader style to the header row.

To avoid hardcoding list GUIDs into your scripts, you can use the SharePoint JavaScript ContextInfo object to set the value of the table ID attribute filter:

$("TABLE[ID^='" + ctx.listName + "']")

Each view page has an instance of the ContextInfo object named ctx, and you can access any JavaScript object on the page from jQuery - afterall it is just a JavaScript DSL.

Using the ContextInfo object allows you to put generic jQuery scripts into common files that you include using a CEWP in view pages for more than just one list. In addition, I recommend putting script that only applies to a specific list in a list specific file that is only included in the view pages of the list.

[UPDATE] As the most common problem people have with this is getting their jQuery selector right, please see this post by Chris O'Brien on useful jQuery tools.

[UPDATE] As the used CSS expression is for IE7 and not supported in other browsers or newer versions of IE, go to the jQuery Plugin site to look for standard compliant solutions for fixed header.

Wednesday, June 10, 2009

SharePoint jQuery: Setting View Column Width

A very common request for changes to SharePoint list views is how to set the column width. This is not possible to do using the ootb "List Settings", and the common suggested fix is to use SharePoint Designer (SPD) and convert the view into an "XSLT Data View": How can I manage columns widths in list views? Most large companies do, however, prevent the use of SPD.

With jQuery there is no need to use SPD or to convert the view. In the following example jQuery will change the width of the two columns "Status description" and "Type of Work" by changing their CSS style attribute.

Start by looking up the HTML markup for the two table headers using View-Source. I'm using jQuery filters that look for the TH elements using a CSS class selector and a content filter. Then add a Content Editor web-part (CEWP) to your page and enter this script in the source editor:

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.2.6/jquery.min.js" 
<script type="text/javascript">
$("TH.ms-vh2-nograd:contains('Status description')").css("width", "150px");
$("TH.ms-vb:contains('Type of Work')").css("width", "150px");

Note that not all column header text containers are TH elements, that the CSS class name varies by column type and that the jQuery contains(text) selector content filter is case-sensitive. I've also used the shorthand to the $(document).ready() function in the script.

You can also speed up the selector by explicitly specifying the jQuery context, such as the id of an enclosing table DOM element - typically "#MSO_ContentTable". Thanks to Paul Grenier for this tip over at the EndUserSharePoint cross-posting. Also consider using the :first filter to stop looking for elements after finding the first matching column.

You can be even more specific and have different layouts for different views of the list, as the id of the inner table representing the view is the combination of the GUIDs for the list and the view. The jQuery context would then apply to a specific view. I would recommend using the SharePoint JavaScript ContextInfo object for the view to get the GUIDs using the ctx.listName and ctx.view properties.

Next time, I'll show how to create a frozen header row that will stay fixed even when scrolling.

[UPDATE] As the most common problem people have with this is getting their jQuery selector right, please see this post by Chris O'Brien on useful jQuery tools.

Sunday, June 07, 2009

Excel Solutions in SharePoint

In my last post, I talked about the situational solutions that users themselves has created in Excel - and how these isolated workbook islands counters knowledge management and the ability to leverage the information and people ecosystem of your company. To subject the Excel workbook sprawl to shared knowledge management, you must provide Excel-style services as components of your Enterprise 2.0 (E2) platform.

The nice thing about Excel workbooks is that it gives the users great flexibility and information processing capabilities in a tool that is quite simple to learn and use. The flexibility of Excel must be preserved if you are to gain the promised E2 benefits: innovation by harvesting your company's collective intelligence through sharing and collaboration.

This post is about the state of most existing Excel 2003 and 2007 solutions I've seen, and how to incorporate them into the SharePoint 2007 platform. I will also talk about the future of Office automation (VBA, macros) and Excel Services/Excel Web Access in the upcoming SharePoint 2010 (SP2010).

Excel has good integration with SharePoint lists, and storing data in SharePoint lists has the benefit of shared data entry and collaboration on the list and collateral information. In Office 2003 a linked SharePoint list can be edited in both Excel and SharePoint and the changes then synchronized back and forth. The list can even be edited offline. This two-way link synchronization mechanism was removed in Excel 2007 - data can still be linked and the link updated, but there is no ootb way to edit that data in Excel 2007 and push it back into Excel. A list sync add-in exists, but this will require installation on client PCs, possibly an issue in most large companies.
Still, data can still be edited in SharePoint lists and linked into Excel workbooks to use e.g. the impressive charting capabilities of Excel. The simplest way to do this is to switch the list into datasheet view and open the task pane using the action menu (click to enlarge figure). Export the data as a linked list to Excel, create the chart or pivot table from the linked list, enable auto-update of the linked list, then save the workbook back into a document library. Your users can then open the workbook to do reporting based on the live SharePoint list data.

So, what if you want to have those charts and pivot tables as parts of your SharePoint sites? Wouldn't it be nice to have a unified, shared Excel-style workspace?

Excel 2003 users can try this: Publish a graph/chart created in MS Excel onto your SharePoint Site. Note that Excel 2003 uses the Office Web Components (OWC) that are deprecated. Excel 2007 users can also publish charts as web-pages as shown here. In both cases, the Page Viewer web-part is used to show the published workbook elements.

If you have Excel 2007 and MOSS enterprise edition, then the workbook can be published to Excel Services and instead be made directly available as part of SharePoint through Excel Web Access!?? You think, but there are pitfalls...

Excel Services (MOSS enterprise edition only) is a natural choice for enabling shared access to workbook solutions, while allowing the users to create the workbooks themselves. This combines the flexibility of Excel while providing shared management and publishing of the solutions. There are, however, some limitations like no data editing, no VBA macros or add-ins and, the most painful in MOSS, SharePoint Excel Services cannot consume live data stored in linked SharePoint lists! The latter limitation is a very common topic on the Excel Services forum. According to information on the forum, this problem is very likely to be fixed in SP2010. In the meantime, have a look at consuming SharePoint lists using Excel UDF.

The same problem applies to SQL Server Reporting Services (SSRS), there is no simple ootb way to report on data stored in SharePoint lists. Again, this problem is very likely to be fixed in SP2010. In the meantime, have a look at consuming SharePoint lists using SSRS. Another issue with using SSRS is that there is no self-service provisiong of new report types, not exactly enabling E2 freeform, emergent social collaboration on solving business problems.

Also keep an eye on the coming Office 14 web applications and the integration with SP2010. This might give the best of both Excel and SharePoint Excel Services, with a more seamless edit-publish mechanism than today. Maybe even working editable shared web-enabled workbooks will become a reality.

So if you somehow enable your Excel Services workbook to consume data from SharePoint lists (I won't even mention the unsupported database view approach), then you will most likely want to provide a richer user experience by allowing filtering of the information shown in the Excel Web Access web-parts (EWA). And provided that there are more EWA web-parts on your page, you may also want to connect them, e.g. when clicking a cell in one EWA web-part triggers an event that affects other EWA web-parts.

The EWA web-parts are filter connections consumers. The MOSS filter web-parts can be used to build a powerful filtering UI that connects to Excel Services parameters that do the actual data filtering. Note that the Filter Web Parts are in the MOSS enterprise edition only, so if you're on standard MOSS then you're left with the rather crude HTML Form Web Part or 3rd party filtering web-parts.

You can also use the ExcelServicesAjax.js script in a Content Editor Web Part to connect EWA web-parts using JavaScript and to use AJAX to access the Excel Services API. Refer to Shahar Prish's blog or his book Professional Excel Services for more details. To avoid scripting connections, whole worksheets can be published instead of multiple single elements. The latter requires less of non-technical users.

If Excel Services is not an option for you, then have a look at Dundas Charts for SharePoint or FusionCharts. Dundas can consume data from a variety for data sources, and Dundas has ootb support for SharePoint lists. This gives the users the E2 flexibility to create mashups by connecting data to charts themselves, within the collaboration platform. The Fusion Flash components also include maps, but there seems to be no ootb "for SharePoint" integration.

The chart creation user experience with Dundas is quite good when all that is needed is configuration, but there is a disconnect to SharePoint terms that will put non-technical users off (e.g. Dundas use "int32" instead of "number", and what happened to "choice"?). In addition, any customization needed beyond configuration, will require VB.NET/C# knowledge, again a duh! for user self-service solutions.

These charting web-parts are also subject to the same filtering and connections aspects as the EWA web-parts. Dundas uses parameter filtering and supports standard web-part connections. Note the lack of wildcards or (all) or (blank) choices in the filter parameters - this makes Excel style chart filters on text values impossible to recreate.

All in all, if you have data in SharePoint lists, then using SharePoint linked workbook lists in the Excel client may be the best approach while waiting for SP2010. If your data is in a store supported by Excel Services, then I would recommend using Excel Services as this combines user flexibility, self-service, sharing and knowledge management.

I had intended to write about what to do with Excel VBA macros and linked workbooks, and the future of VBA with relation to Excel Services, OOXML, VSTA/VSTO/.NET and Office 14-16 in this post, but due to the length that will be the topic of another post.