Reporting Options for SharePoint List Data

I came across a blog post today that summarizes the various reporting options for SharePoint and wanted to share it since it’s pretty decent: http://sharepoint-community.net/profiles/blogs/reporting-options-for-sharepoint-lists

Here’s an even shorter summary of reporting options. See the link above for details and links.

  • List views
  • Microsoft Excel / Microsoft Access (client applications and SharePoint services)
  • Custom views or pages created in SharePoint Designer
  • SSRS (SQL Server Reporting Services)
  • PerformancePoint (dashboards / KPIs)
  • Microsoft Visio (not covered in the article but described here)

You can also create a totally custom solution, for example an ASP.NET reporting solution that uses ActiveReports or some other reporting technology.

One of my personal favorites is Microsoft Excel. Excel has three powerful add-ons (all free) that allow normal business users (non-IT people) to do some pretty robust reporting:

Power Query is particularly nice. It’s a newer add-on that provides richer options for getting external data into Excel. For example, it’s aware of SharePoint lists and allows you to pull list data into Excel and even filter, sort, and manipulate it to a certain extent before you use it in tables, charts, or PowerPivot.

Power View is another nice add-on that provides some decent visualizations and basically lets you create some canned reports as worksheets within Excel.

PowerPivot has been around a little longer and essentially lets you work with your data model in Excel as a “mini database.” You can use it to view data in tables, relate the tables together to create joined records, and create some summarized values (called “measures”) for using in pivot tables and charts.

Information Governance: What It Is and Why You Need It

As an information management professional, I’m always interested in trends and ideas around how businesses can get more value from information. However, there’s so much information captured and stored nowadays that many organizations just “hoard” it and don’t do anything truly strategic with it.

If you want to combat information hoarding and do something more strategic, check out the article I published today on information governance:

https://www.linkedin.com/today/post/article/20140905142116-45935709-information-governance-what-it-is-and-why-you-need-it?trk=prof-post

 

Anonymous API Access for Office 365 Public Sites

I was recently working on a contact form for an Office 365 (SharePoint Online) public site and needed to allow anonymous access to my site’s REST API. My contact form was submitting data to a “Contact Us” list on my site using JavaScript and REST. It worked great when I was logged in, but as soon as I logged out I’d get this error from the API: “Access denied. You do not have permission to perform this action or access this resource.

The “Use Remote Interfaces” Permission

By default, SharePoint’s client-side APIs (like CSOM and REST) are configured to require a permission called “Use Remote Interfaces” to use them. Otherwise you’ll get the “Access Denied” error I mentioned above. The problem is you can’t assign that permission to anonymous users.

Normally you can work around this by turning off the “Require Use Remote Interfaces” setting for the API on a per-site collection basis. However, that setting is not available in Office 365 public sites. According to Microsoft’s documentation, the reason it’s not available is that clicking “Make Website Online” for your public site is supposed to adjust that setting for you.

Here’s the exact quote from their documentation:

On the home page of the site collection, choose MAKE WEBSITE ONLINE near the upper right corner of the page. This action also turns off the Use Remote Interfaces permission requirement.

The only problem is it doesn’t work. Either the functionality was changed after the documentation was written, or the documentation was never correct in the first place. Not sure.

The Fix (which, ironically, relies on the client-side API)

As it turns out, the answer to allowing anonymous access to the API was in the API itself.

The SPSite object in SharePoint’s server-side API has a method called UpdateClientObjectModelUseRemoteAPIsPermissionSetting (yes, the name is really that long) which updates this setting. Passing false to this method will turn off the permission requirement for the API. I wasn’t sure if this same method would work in the client-side object model, but I figured I had nothing to lose and tried calling it anyway. Turns out that was the answer.

While logged into my public site as an authenticated user (so I could use the APIs), I created a temporary page in my site and added the following JavaScript snippet to it:

<script type="text/javascript">
ExecuteOrDelayUntilScriptLoaded(function() {
   var ctx = new SP.ClientContext();
   var site = ctx.get_site();
   site.updateClientObjectModelUseRemoteAPIsPermissionSetting(false);
   ctx.executeQueryAsync(
      function() { alert('success') },
      function() { alert('error') }
   );
}, 'sp.js');
</script>

The alerts that say “success” and “error” were simply to show me quickly whether it worked or not when I loaded the page. Mine said “success” and worked the first time, but if yours says “error” for some reason then I suggest using your browser’s debugging tools to see what the actual response was from the API. This code was for one time use, so it wasn’t worth building a bunch of elaborate error-handling into the code itself.

Also, I used IE 11 to edit my page and run the JavaScript snippet even though I normally use Chrome. Chrome sometimes has problems with the timing of the ExecuteOrDelayUntilScriptLoaded() function. There are fixes for that, but again, it wasn’t worth messing around with for code I was only going to run once.

Required Permissions: I believe you need to be a site collection administrator to successfully execute the API code snippet above, but if anyone finds out otherwise please let me know and I’ll update this note.

Fair Warning: As you can see in the JavaScript snippet, opening up the APIs is an “all or none” prospect. I can’t, for example, open up the API for a single list or for a single set of functionality. I can open up all of it or none of it. So be absolutely sure if you do this that you’ve got permissions set up correctly to everything in your public site so people can’t access stuff you don’t want them to. The APIs do respect the permissions of whoever is using them (including anonymous users).

Conclusion

That’s it! After running the code snippet above one time, the client-side APIs in my public site were now accepting calls from anonymous users. As of today I have two contact forms on my public site, both of which use the REST API to submit data to behind-the-scenes lists for processing.

3 Ways to Increase SharePoint User Adoption

After having a few discussions recently around low user adoption (engagement) rates on SharePoint sites, I published a short post on LinkedIn with 3 ways to increase adoption. There are obviously more ways than just these 3, but I hope these provide a good starting point for anyone needing help with adoption rates.

Here’s the link: https://t.co/z9ap9Gf68l

Tutorial: Basic Custom List Forms in SharePoint 2013

In this video I show how to use SharePoint Designer 2013 to create a custom form and ribbon button for a Contacts list to do a “quick add” of a contact. I’ve run across this scenario a lot with clients because users often want to add a contact with just a couple of fields initially (like name and email) and add the other information later. Stuff like this is easy and quick to do and helps drive user adoption by making things easier.

The SharePoint site in the video is a SharePoint 2013 team site hosted on Office 365 (SharePoint Online).

Tasks in SharePoint Designer approval process canceled upon creation

Sometimes I run across some really weird stuff as a SharePoint developer. A problem I solved this morning is one such example.

Last night I was working on a SharePoint Designer workflow (targeting SharePoint 2010) and my workflow included the “Start approval process” action. Part of that action is to create approval task(s) and assign them to people. We have a lot of control over how the process works but not really any control over how the tasks are actually created in the workflow task list. That part normally happens automatically and is supposed to just work.

Well, at some point my tasks started magically being canceled the moment they were created. I noticed my final workflow steps involving approval/rejection weren’t running so I visited the workflow status page (by clicking the “In Process” link in my list view) and noticed all my newly created approval tasks were showing “canceled” as their status. I didn’t have access to the ULS logs for this environment so I did a Google search and found absolutely nothing (which is never a good sign when it comes to SharePoint).

After meticulously recreating the workflow this morning to track down the problem it turned out to be a data type issue with a lookup value in an email template. One of my email templates in the approval process included a link I built within the workflow and stored in a variable. When I included the link variable in the email I picked “URL” as its data type (which I assumed was okay since it’s a link) and THAT is what caused the task cancellation issue. Changing the data type of my link from URL to “string” fixed the problem.

It’s times like this when I really sympathize with end users who use SharePoint Designer on a regular basis. By comparison I’ll take debugging custom code any day over tracking down problems with SPD workflows.

Connecting to SharePoint Online’s CSOM or REST APIs from SharePoint 2010

I decided to write a post on this topic because there’s surprisingly little information out on the Web about how to do this, and most of the blog posts and articles I found that came close to addressing it were pretty old (2011 or 2012 timeframe). Considering SharePoint Online was upgraded last year to Office/SharePoint 2013 I wanted to get some more current information out there.

The Scenario

The scenario I’m covering is connecting to a SharePoint Online (Office 365) REST or CSOM API from an on-premise SharePoint 2010 farm. Why do that? Well, in my case it’s because I was building an event receiver in SharePoint 2010 to automatically copy documents (files) out to a SharePoint Online site for a hybrid on-premise/online solution. The 2010 farm I was using is slated for an upgrade to 2013 later this year but for now I was stuck with 2010. Also, because of the “background” nature of an event receiver, an implicit requirement of this scenario is non-interactive (“headless”) authentication to SharePoint Online.

Options I Considered

The “Old” Ways

I started, of course, by looking at what’s already out on the Internet. I found the two famous (but older) articles everyone reads who tries to connect to SharePoint Online: the MSDN article by Rob Bogue and the “headless authentication” blog post by Chris Johnson. I’ve worked with Rob before and he’s a smart guy. His approach does work but requires an interactive user which wasn’t an option in my case. The approach by Chris Johnson probably works too but given that it was written back in 2011 and seemed a little “hackish” I wanted to find something better if possible. (For the sake of completeness there’s also a famous blog post by Wictor Wilen on this topic but much of his approach was rolled into what Chris Johnson wrote about.)

The “New” Ways

Given SharePoint Online’s upgrade to Office 2013 last year I decided to consider the new “app” model. In particular I thought a provider-hosted app might be a good solution with the event receiver being the “provider-hosted” portion of the app. An MSDN article on OAuth authentication for apps needing on-the-fly permissions in SharePoint 2013 got me really close. The app model has concepts like authentication built right into it, after all. The problem, however, is the user at some point is still required to grant the app permissions to access what it needs (much like Facebook or mobile apps often ask you to do). High trust apps are a workaround for that requirement but can’t be used in SharePoint Online. So the app model was out.

Another newer approach is the SharePoint 2013 Client-Side Object Model and SharePointOnlineCredentials class which are built to do non-interactive authentication to SharePoint 2013/Online. However there are three problems with this:

  1. The SharePoint 2013 CSOM targets .NET 4.0+. Code running within SharePoint 2010 can only use up to .NET 3.5.
  2. Given the .NET 3.5 constraint I could use the SharePoint 2010 CSOM but the SharePointOnlineCredentials class didn’t exist in that version.
  3. Even the newer 2013 stuff doesn’t readily support calling the REST API. I wanted a common authentication mechanism for both CSOM and REST.

The Solution

I really liked the new SharePointOnlineCredentials approach in the 2013 CSOM so I decided to duplicate it in my SharePoint 2010 code. My first step was using ILSpy to crack open the 2013 code and “copy” that class (plus a few others that it uses) into my project. A few minor tweaks were needed but nothing show-stopping. In addition I also created a lightweight SharePointOnlineClientContext class that inherits from ClientContext (the 2010 version) and makes some very slight tweaks to get it working with the 2013 version of SharePoint Online.

The result is I can write code that looks like this in my 2010 event receiver:

var creds = new SharePointOnlineCredentials(
	SPO_USERNAME, StringUtilities.ToSecureString(SPO_PASSWORD));

using (var ctx = new SharePointOnlineClientContext(SPO_URL))
{
	if (ctx != null)
	{
		ctx.Credentials = creds;

		List list = ctx.Web.Lists.GetByTitle("Shared Documents");
		var fileCreateInfo = new FileCreationInformation();
		fileCreateInfo.Content = myFileAsByteArray;
		fileCreateInfo.Overwrite = true;
		fileCreateInfo.Url = new Uri(SPO_URL, "/Shared Documents/" + myFileName).AbsoluteUri;

		File file = list.RootFolder.Files.Add(fileCreateInfo);

		ctx.ExecuteQuery();
	}
}

This obviously isn’t production-ready code (for example, it doesn’t include copying metadata, error handling, etc.) but serves to demonstrate the point. And again, this is using the SharePoint 2010 CSOM from within a 2010 farm to create a file in the current (2013) version of SharePoint Online. What’s involved in upgrading this to the 2013 CSOM later? Not much. Basically you’d just need to:

  1. Update the project references to use the 2013 CSOM DLLs (which you can download here).
  2. Delete the custom SharePointOnlineCredentials class (and other classes it uses) because they’re already built into 2013 (but you don’t have the change your code because the constructor syntax is the same).
  3. Switch the SharePointOnlineClientContext class in the ‘using’ statement back to just ‘ClientContext.’

Now what about the REST API for SharePoint Online?

Here’s a code sample that connects to the REST API:

var creds = new SharePointOnlineCredentials(
	SPO_USERNAME, StringUtilities.ToSecureString(SPO_PASSWORD));

HttpWebRequest request = (HttpWebRequest)
	HttpWebRequest.Create(SPO_URL + "/_api/web/lists");

request.Credentials = creds;
request.Method = "GET";
request.Accept = "application/json;odata=verbose";
request.Headers.Add("X-FORMS_BASED_AUTH_ACCEPTED: f");

var response = (HttpWebResponse)request.GetResponse();

using (var reader = new System.IO.StreamReader(response.GetResponseStream()))
{
	Console.WriteLine(reader.ReadToEnd());
}

This code sample was taken from a console app running on the 2010 farm (but still targeting .NET 3.5 and using my same custom SharePointOnlineCredentials class). All it does is connect to the “/lists” REST endpoint and do a text dump to the console of whatever it gets back. The key in making this work is the line that adds the “X-FORMS_BASED_AUTH_ACCEPTED” header. Without that line connecting to SharePoint Online’s REST API will fail with a 403 forbidden error.

Both of the above code snippets work without requiring an interactive user which meets my requirement for “background” code. The only caveat is you’d need an account in SharePoint Online whose username and password you can use for authentication. There’s no pass-through/impersonation of an account from the 2010 farm. But I think that’s actually a good thing because it gives you fine-grained control over what this code is allowed to do on the SharePoint Online side because you control those permissions there.

Certificate “Stuff”

One last piece of this solution is a certificate (trusted root authority) that must be installed in the SharePoint 2010 farm if you use this code from within SharePoint itself. The reason is you’re connecting to SharePoint Online over ‘https’ which means there’s a certificate on the Office 365 side your farm needs to trust. The root authority on the Office 365 side is a “Baltimore CyberTrust” certificate which you can download from https://cacert.omniroot.com/bc2025.crt. Install this certificate in your farm using either PowerShell or the “Manage Trusts” page in Central Administration and you should be good to go. Without it you’d see an error in the event viewer (not the ULS logs) about an untrusted root authority.

Get the Code

A sample project can be downloaded at http://inclinetechnical.com/learning/code/DotNet35ToSPO.zip. It’s a .NET 3.5 console application created in Visual Studio 2013 and demonstrates connecting to both the CSOM and REST APIs of SharePoint Online as I’ve written about in this post. Moving the code from the console into SharePoint is mostly a matter of installing the certificate I mentioned a moment ago but for testing purposes a console app is just easier to play with.

Conclusion

I’ve seen several people in forums, on LinkedIn, etc. asking how to do this so I’m hoping someone out there finds this information useful. And if you find ways to improve it please comment as that would benefit all of us, myself included. Likewise if you run into problems those are good to know about too. Happy coding!