Open-Ended Date Ranges (null dates) with SharePoint queries in SSRS reports

Yesterday I was building an SSRS report in Report Builder 3.0 for a client. The report was querying data in a SharePoint 2013 site, and the client asked me to add date parameters so he could specify a date range for the data that comes back.

Seems simple enough, right? I thought I just needed to add two date parameters to my report, update my query to use them, and I’d be good to go.

Well, that was almost enough.

You see, in my case I wanted to support open-ended date ranges, which means one or both date boundaries can be null/empty. For example, the client might specify January 1, 2015, as the start date, but by leaving the end date null, the client is telling the report to pull everything after the start date. Or, as another example, leaving both dates null would return all data in the list.

The problem is SSRS doesn’t work that way. Since SSRS was originally created to query SQL data, it’s very literal in its interpretation of a null date. As far as it’s concerned, passing a null value for a date parameter means you want data where a particular date field is null. It doesn’t provide an option to interpret the null value as “I don’t care” (open-ended boundary).

The good news is I was able to get SSRS to do exactly what I wanted. If you need this same behavior, check out the YouTube video I made showing how I did it.

SharePoint OpenSearch URL Tokens

In SharePoint 2013, we can configure OpenSearch result sources for searching external providers outside of SharePoint.

The URL for an OpenSearch result source allows several replaceable tokens to be used, but so far I’ve been unable to find the complete set of tokens documented anywhere (even on MDSN).

Therefore, I’m documenting them here for reference. All of the following are valid tokens in OpenSearch provider URLs.

SharePoint OpenSearch URL Token Reference

Token Replaced With
{searchTerms} Search query (keywords)
{startIndex} Start index of first item to display, relative to total number of results

Example: If SharePoint is displaying 10 items per page, the start index on page 1 is 0, the start index on page 2 is 10, and so on.

{startItem} Same as {startIndex}
{startPage} Despite the name, behaves same as {startIndex}
{count} Number of items to display per page (setting in search results web part)
{itemsPerPage} Same as {count}
{language} Language, for example “en-US”
{inputEncoding} Encoding of search query request, for example “utf-8″
{outputEncoding} Desired encoding of query response, for example “utf-8″

While the last few tokens may not be used very much, the others are fairly important because features like paging won’t work correctly without them.

As noted above, several tokens behave the same way, so just choose the one you want in those cases. For example, you can pick {count} or {itemsPerPage} to pass the “number of results per page” to the search provider. They both mean the same thing.

More Details on Paging

For paging in particular, it’s important to know that two things are required to get it working properly in SharePoint. Almost every example I’ve seen of OpenSearch paging with SharePoint misses at least one of these, which is why so many people struggle with it.

#1: The OpenSearch Provider Needs to Accept Paging Parameters

The OpenSearch provider needs to accept parameters for paging such as “start index” and “items per page” and take those into account when searching. If the OpenSearch provider has those arguments, you can pass values for them using the tokens I listed above.

For some services you might want to query such as YouTube, you’ll have to write your own web service as a go-between because YouTube’s v3 API doesn’t support start indexes. Instead, it requires “back” and “next” tokens for paging, which are special strings it sends back with results. SharePoint can’t supply those.

#2: SharePoint Must Know Total Number of Results

The other part of paging is letting SharePoint know how many total results there are. The reason why is simple. When SharePoint receives results, it asks, “Are there more total results than what I’ve been asked to show on one page?” If the answer is yes, paging links are generated. Otherwise, they’re not.

For OpenSearch results, there’s a “totalresults” element which can be included in the XML to let SharePoint know this information. Take a look at the ATOM response example in the OpenSearch specifications for an example. Without this information, SharePoint can’t support paging correctly. You either won’t get paging at all, or you’ll get limited paging functionality that doesn’t traverse the entire result set.

Many ATOM and RSS feeds do not include the OpenSearch-specific elements such as “totalresults,” so depending which provider you’re using, you may need to create your own web service in the middle to add those additional elements.

How to Create a SharePoint 2013 Timer Job – Course Discount

I recently updated a couple of lectures in my course about how to create SharePoint 2013 timer jobs and added two brand new lectures about timer jobs for SharePoint Online (Office 365).

The next 100 people who use the coupon code NEWTIMER15 when purchasing can get the course for $49 rather than $79 (almost 40% off)!

I’m a developer myself, and I know developers generally aren’t stupid. They know how to find a lot stuff for free on the Internet. I’m aware there are lots of articles and blog posts telling you how to create timer jobs for SharePoint.

But most of those articles don’t tell you:

  • Why SPJobDefinition isn’t always the right choice as a base class
  • How to create a server-side timer job for SharePoint Online
  • How to handle error-handling, progress-reporting, and configuration in real-life scenarios

And that’s the value I bring in my course. I cover all those and WAY more. I also add and update lectures periodically, and my course – unlike many other training options – is a one-time purchase for LIFETIME ACCESS. There’s no monthly or annual subscription fee. Once you purchase, you get all new and updated lectures at NO ADDITIONAL COST.

So what are you waiting for? Only 100 people get the discount, so sign up today and start developing timer jobs for SharePoint 2013!

Missing SearchNav Property in SharePoint Client Object Model

If you’ve ever customized SharePoint 2013’s (or SharePoint Online’s) search navigation through code, you might be familiar with the SPNavigation.SearchNav property in the server-side object model. This property was introduced in SharePoint 2013 (SharePoint 2010 used a different – and now deprecated – concept called “scopes”).

The SearchNav property gives you access to the list of navigation nodes (also known as “search verticals” or “search experiences”) that show up in a SharePoint site search box as pictured below.


That’s great for server-side code. But what about client-side code?

Well, this is where things get awkward. SharePoint’s client-side object model doesn’t include the SearchNav property on its Navigation object.

But don’t worry! You can still customize search navigation with client-side code! The trick is to use a slightly different approach.

Here’s an extension method I wrote for the Microsoft.SharePoint.Client.Navigation class that illustrates the approach:

using System;
using Microsoft.SharePoint.Client;

namespace YourNamespace
    public static class NavigationExtensions
        const int SEARCH_PARENT_NODE_ID = 1040;

        public static NavigationNode GetSearchNavRoot(this Navigation navigation)
            return navigation.GetNodeById(SEARCH_PARENT_NODE_ID);

This is actually what the server-side SearchNav property does behind-the-scenes anyway. It just fetches a special navigation node with a well-known ID of 1040. Microsoft uses that as the root/parent node for the search navigation menu.

The only difference between the code above and the implementation of the server-side SearchNav property is that the server-side property returns the children (SPNavigationNodeCollection) of the search navigation root node, whereas I just return the root node itself.

I do that because in the client-side object model, operations are batched together and sent to the server as a single request. In order to return the child nodes in my extension method like the server-side property does, I’d have to first load the child node collection into my context and then make a call to ClientContext.ExecuteQuery(). However, I think calls to ExecuteQuery() should happen outside of the extension method, and that’s why I return the root node itself.

A typical usage of the above extension method in client-side code would be something like this:

NavigationNode searchNavRoot = web.Navigation.GetSearchNavRoot();
context.load(searchNavRoot, root => root.Children);

NavigationNodeCollection searchNav = searchNavRoot.Children;

// Now, the searchNav variable above is the *exact* equivalent of
// what I get from calling SPNavigation.SearchNav in server-side
// code. I can add and remove nodes in that collection to customize
// search navigation.

So as you can see, it is possible to access and customize search navigation in client-side code.

Hopefully you found this helpful. Happy coding!

Beta version of CAML tool with intellisense is ready!

If you haven’t already read my post about the browser-based CAML testing tool with intellisense that I’ve been working on, please read that first!

If you’d like to try the tool yourself, you can download it on GitHub here:

As noted on GitHub, please use the Issues page there to submit bugs, questions, or feature requests. While I may respond to a few things here or on LinkedIn when I have time, GitHub is the primary forum for that kind of stuff.

What I’m really looking for right now is for people (mostly developers) to download the tool, test it, and report back any major issues they find (like browser compatibility problems and things like that). I also welcome suggestions for new features.

I’ll be adding more updates on the tool in coming weeks, including putting out a tutorial on how to use all the query options.

Extension method to safely get list item field value in SharePoint 2013

For SharePoint 2013, here’s an extension method I wrote for SPListItem that’s turned out to be quite useful. It gets the value of a list item field in a “safe” fashion – that is, by returning a known default value if the field value is null or can’t be converted to the type you expect.

public static T GetValue<T>(this SPListItem item, string fieldName, T defaultValue = default(T))
    if (item == null)
        throw new ArgumentNullException("item");

    T value = defaultValue;
    object rawValue = item[fieldName];

        if (rawValue != null)
            value = (T)rawValue;
            // Direct cast failed, but try a type conversion just in case
            // we can still convert the raw value correctly (for example,
            // if the raw value is the string "true" and T is a boolean,
            // this allows us to correctly return a true boolean value).
            value = (T)Convert.ChangeType(rawValue, typeof(T));
        catch { }

    return value;

To use this, you need to put it inside a static class (like any other C# extension method) and add a using statement for Microsoft.SharePoint.

The defaultValue parameter is optional and specifies the default value to return. You can either pass in your own value or let C# determine it using the default keyword. The default keyword returns null for reference types, 0 for numeric types, and false for boolean types. For structs, it returns a new struct with each member initialized as described previously.

Because this is implemented using the standard SPListItem.Item[string] indexer, the behavior for finding fields by name is as follows:

  • First, SharePoint treats fieldName as the internal name of a field
  • If no match, SharePoint treats fieldName as the display name (title) of a field
  • If still no match, SharePoint treats fieldName as the static name of a field
  • If none of the above result in a match, an exception is thrown saying the field cannot be found

So as Microsoft says, the best performance comes when you pass fieldName as the internal name of a field because only the first check is done and the others are skipped.

Examples of usage:

// gets the integer value of "MyIntegerField" or -1 (programmer-specified default)
int value = listItem.GetValue("MyIntegerField", -1);

// gets the boolean value of "MyBooleanField" or false (C#-defined default)
bool value = listItem.GetValue("MyBooleanField");

// gets the lookup value of "MyLookupField" or null (C#-defined default)
SPFieldLookupValue value = listItem.GetValue("MyLookupField");

SharePoint 2010 compatibility: This extension won’t work as-is with SharePoint 2010 because of the optional defaultValue parameter. SharePoint 2010 uses version 3.5 of the .NET framework, and optional parameters are only supported in .NET 4.0 and above. For SharePoint 2010, I’d recommend overloading this method where one signature takes the optional parameter and one does not.

SharePoint CAML Query Testing App with Intellisense – Coming soon!

Update – Jan 21, 2015: You can now download and test a beta version of this tool! See this post for details:

Good news for SharePoint developers! I’m pretty far along on developing a free, browser-based CAML tool with intellisense and other cool features that lets you write and test CAML queries right from your browser. This is actually a tool I’ve been using myself for a while, but I’m adding a few things to it and releasing it to the larger community.

Here’s a sneak preview that shows the code editor (with intellisense) and some of the options:


The tool is still a work-in-progress so the above screenshot may not reflect how the final version looks, but you get the general idea.

I know some of you will wonder why I’m creating this when there are other CAML tools out there, so here are my reasons:

  • The best intellisense for CAML. Honestly, the coolest feature of this tool is the intellisense! It’s context-sensitive (aware of “where you are” in the query), and LIST FIELDS are included in the intellisense suggestions when you’re typing an attribute that takes a field name! How cool is that?! The intellisense feature has you covered for most tag names and attribute values you’re likely to run across.
  • View and page through results right in your browser. The results of your query are displayed in tabular form right in your browser for easy viewing. You can also copy results out in CSV format if you’d like to paste into Excel or another tool. In addition, paging of results is supported so you can keep your row limit (items per page) small to maximize query speed but still get the data you need.
  • Lots of query options. This tool gives you a pretty full set of query options (comparable to what you’d see in server-side code), including options like “Calendar Date” and “Expand Recurrence” (for querying and expanding recurring events). It’s also smart enough to enable and disable certain options depending which client-side API it has to use to execute the query (see “APIs and Query Functionality” later in this post).
  • No desktop software to install. Some companies are strict about (a) who has admin rights to install software on their PC, and (b) what software is allowed to be installed on local PCs, including running periodic scans to ensure compliance. When I work with clients like that, I often turn to browser-based tools like this to avoid red tape and get work done faster.
  • This is a code-oriented tool. I’m a developer. I write code. And when I don’t know the syntax for something, I rely on intellisense and other resources to help me learn the language I’m coding in so I can get more proficient at it. Why? Because once I’m proficient, I can just write code and test things quickly. I don’t have to deal with cumbersome UIs or various layers of abstraction. This is a code-oriented tool. If you need help learning CAML or remembering syntax, you’ll get it. But if you already know CAML, you can just write code and go.
  • No dependencies on other tools (besides SharePoint itself). Not much else to say on this one. There are a couple of JavaScript dependencies (see “Technologies and frameworks” later in this post), but I load those from a CDN so there’s nothing to install or upload to use them.
  • It will teach you about CAML and the client-side APIs. If you mess up something in your CAML query, you’ll find out quickly, can tweak it, and try again. (For example, did you know the order of your <FieldRef /> tags matters inside of a <DateRangesOverlap> tag? I found that out by using this tool!) And if you’re curious about how I did anything in this tool, all you have to do is right-click the page and view the source. It’s just JavaScript and HTML! Nothing is hidden.

What can this be used for? Well, here’s how I’ve used it so far:

  • Finding internal names of list fields.  There are probably 100 ways to find internal names of list fields in SharePoint, but this tool makes it super simple and easy. I usually just pull it up and start typing like you see in the screenshot above, and bam, I’ve got my field names for using in XSL or client object model code or whatever.
  • Testing large list limits. I’ve built several solutions involving large lists, and testing the query throttling limits is always a pain. This tools makes it easy because I find out real quick what kinds of queries generate query throttling exceptions, and I can tweak them.
  • Plain old CAML query testing. Perhaps the most obvious use case, I use it to write and test CAML queries before inserting the queries into code elsewhere. In the past I’d use PowerShell, but this is even easier, and PowerShell isn’t always an option depending on the environment and restrictions.
  • Ad-hoc reporting. Sometimes people ask me for random, one-time reports of data in SharePoint. I use this tool to grab what they want and export it to Excel. In some cases I’d make a SharePoint list view and use the “Export to Excel” button in the ribbon, but depending on the type of query I’m doing, list view don’t always cut it. (And honestly, as someone proficient in CAML, this is even faster for me than creating list views most of the time.)

The name of the tool will be QuickCAML, which I think plainly describes its purpose: to write and test CAML queries QUICKLY.

Regarding distribution, I thought about the “app model” but am mostly convinced that’s a bad idea for a variety of reasons. If you’re wondering why, just read this post: Bjørn does a great job summarizing what I think about the app model.

Instead, I’ll likely make this available as a download you can just “snap into SharePoint” by (a) uploading an ASPX file to a Site Pages library, or (b) copying-and-pasting code directly into Script Editor and Content Editor web parts. Again, the goal here is simplicity. I don’t see a reason to mess with sandboxed solutions or apps for this as neither are necessary.

Technologies and frameworks: I built this tool using Knockout JS, jQuery, and the Ace Editor for JavaScript (the last of which I made a few enhancements to in order to support CAML syntax better).

APIs and Query Functionality: I used two client-side APIs to grab data from SharePoint: the JSOM (JavaScript Object Model) and the “Lists” web service. I didn’t want to use the latter API at all but had no choice since not all query options supported by the web service are supported by the JSOM API. Oddly enough, the reverse is true too. JSOM supports some stuff the web service doesn’t, so I had to use both to get as close as possible to the server-side API. Don’t worry, though, you don’t have to pick an API when using the tool. Just write your code, pick your options, and the tool will figure it out for you (and tell you on the results page which API was used to run your query).

Look for more updates soon.