Customize SSRS extension settings in SharePoint Integrated Mode

SQL Server Reporting Services (SSRS) is powerful way of presenting reports and data to your end users in SharePoint. But sometimes, the out of the box experience of some features of SSRS doesn’t work for your situation. A nice example of this is the field delimiter for CSV exports. This is by default a comma. But in some regions (like mine), the default delimiter is a semicolon and using a comma doesn’t work.
Another example is the different formats which are available for exporting. What if you want to remove some of those supported export formats?

When you start searching the net, you will find some information on how to achieve the above customizations by adding or changing some settings in the RSReportServer.config file. Check the link below for the information on that configuration file.

http://msdn.microsoft.com/en-us/library/ms157273(v=sql.110).aspx

While this works fine for a regular SSRS installation, it will not work for a SharePoint integrated SSRS installation because you don’t have that config file on your system.

The solution for this is PowerShell. All of those customizations can be done this way by means of changing SSRS extension settings.

Change the CSV field delimiter

Remove export formats from the supported list

The script above removes the “TIFF” and “MHTML” formats from the list of available export formats. The complete list of formats which you can include or exclude is the following:

  • XML
  • CSV
  • ATOM
  • PDF
  • RGDI
  • EXCEL
  • EXCELOPENXML
  • RPL
  • IMAGE
  • WORD
  • WORDOPENXML
  • HTML4.0
  • MHTML

If you want to add it to the list, specify “True” as value for the “Visibility” element in the -ExtensionAttributes parameter.

Process items in a Drop Off library using PowerShell

Everyone knows the Drop Off library in SharePoint, right? It routes documents to a final destination, based on rules you define.
This works OK for documents which are uploaded manually to the Drop Off library but I found out that it doesn’t work for documents which are uploaded with PowerShell (or any other method which uses the object model). Even if I fill in all required metadata and make sure the document it checked in… it still won’t leave the Drop Off library.

Seems that there’s a timer job in SharePoint (Content Organizer Processing) which runs daily and processes items which are left behind in Drop Off libraries. That’s great but what if you want to process that document immediately?

I found an article from Steve Lineberry which explains how to do this in C#. He used reflection to break open the timer job and found out that it was calling an internal method.

I needed this in PowerShell, so I did some translation and created a PowerShell function which does the same.

Filter a View on Liked By in SharePoint 2013

SharePoint 2013 allows users to rate individual items in a list or library. This rating can be in the form of a “star” rating from 0 to 5 or in the form of “likes”. This is disabled by default for a list or library.

The rating data is stored in 5 columns which are added to your list or library when you enable this functionality:

  • Number of Ratings
  • Number of Likes
  • Rating (0-5)
  • Rated By
  • Liked By

The columns “Number of Ratings”, “Number of Likes” and “Rating (0-5)” are visible to the end user. The other 2 columns “Rated By” and “Liked By” are hidden.

This information is transformed by SharePoint in a nice view which makes it somewhat sexier for the end user. You can see this in the screenshot below where the rating information is visualized in the “Favorited” column of my library.

050614_1507_CreatingaMy4

The user will see a “Like” or “Unlike” link. At the same time, the number of likes is displayed together with a smiley.

So far so good.

Imagine you want to use this rating information to filter the list or library to show you only those items which have been liked or rated by you.

The columns you need for this information is the “Liked By” and “Rated By” columns. These columns are collections of users. The problem with this is that those 2 fields are not available for filtering when you use the UI.

You can use CAML to create a query which returns you only those items where the current user is contained in one of these fields.

The query above returns items which are liked by the user who executes the query.

When you look at the SPViewCollection.Add method, you notice that it accepts a “query” parameter.

So, to have a view filtered using the query, we can simply create the view using PowerShell.

This works… on premise.
But what if you have a SharePoint Online. You can still use PowerShell but you need a slightly different approach because instead of using the server object model of SharePoint, you need to use the client object model. You can use something like below to do the same on SharePoint Online. I must admit, I’m not an expert on CSOM with PowerShell (yet :mrgreen: ) and I’m still getting up to speed with this. I suppose the code below can be simplified. But hey… it gives you an idea on the general approach what you need to do to create a view with a query on a library which is hosted on a SharePoint Online .

Close disconnected remote sessions using PowerShell

Do you know this situation? You are responsible for some servers and you planned to do some patching during the weekend. You open a remote desktop to the server with the administrator user and you see that the session is still active. Visual Studio is open with unsaved source code, some config files are open, even somebody’s Facebook is nicely ready for you…
Or you start to install some critical updates and when you want to reboot, Windows tells you that there are other users logged on… you check the users tab in the task manager and you see some “disconnected” sessions. Arggh! 👿

I grew tired of reminding people to log off instead of just closing the session. Some people listened, some are too lazy and won’t listen.

So, I wrote myself a little script which runs every evening and terminates all disconnected sessions, regardless of unsaved shizzle that’s happening in them. Really, people only learn to follow the rules when you hit them where it hurts. Sad, but true.

So, here’s the script…

I created a task in the task scheduler to run this every evening at 9PM.

You can find this script in my GitHub PowerShell repository.

Configure Object Caching accounts using PowerShell

If you want your Object Caching to work properly in SharePoint, you need to set 2 user accounts:

  • Portal Super Reader
  • Portal Super User

Additionally, they must be configured correctly. That is, you need to do 2 things:

  • Add a “Full Control” user policy to your web application for the Portal Super User and use PowerShell to create a web application property “portalsuperuseraccount” which has a value that’s exactly the same as the displayname of that user in the user policy.
  • Add a “Full Read” user policy to your web application for the Portal Super Reader and use PowerShell to create a web application property “portalsuperreaderaccount” which has a value that’s exactly the same as the displayname of that user in the user policy.

A lot of mistakes are made when doing this manually as a result of typo’s. Especially when you are working with claims.

To avoid this, you can use the script below to do the necessary actions. You simply provide the URL of the web application and both user accounts and the script will do the rest.

And voila, your life just got a little less complicated. 🙂

Enabling BLOB Cache in SharePoint using PowerShell

One of the ways of improving performance of your SharePoint is by enabling the BLOB Cache. The BLOB cache is a disk-based cache that stores binary large objects (BLOB’s) on the web server to reduce the load on the database server by avoiding unneccessary round trips to that server. Read all about it on TechNet.

The BLOB Cache is disabled by default and enabling it needs to be done on the web application level in the web.config. There’s a specific setting in the web.config where you can enable this.

To enable it, you just change the value of the “enabled” attribute to true and there… it’s done.

But wait, if you have multiple web servers, you need to do this on each server. Not quite amusing. Let’s do something about that. If you use a SPWebConfigModification, the change is pushed automatically to all web servers which have the WFE role. Time to flex those fingers and create some PowerShell code.

This script accepts 2 parameters:

  • Url : URL of the web application for which you want to enable the BLOBCache
  • Location : the place on disk where the cache is stored

In the script, 3 updates are performed:

  • Enabled=”true” : Enables the BLOB Cache
  • max-age=”86400″ : Sets the age (in seconds) that a browser caches BLOBs
  • location : Sets the path on disk where the cache is stored

Creating Managed & Crawled Properties using PowerShell

Have you ever created and mapped a decent amount of managed properties in SharePoint using the UI? If you have, you probably know that it’s not the most exciting job. Lots of clicking, waiting, reloading,…

If you are someone like me… someone who doesn’t like doing things manually… and you are reading this, you are probably expecting to find an automated way of creating those things. And you will not be dissappointed. I’ll even throw in something extra: add “crawled properties”.
Normally, crawled properties are added during the crawling process. I’ll show you how you can add those properties manually using PowerShell without having the need to kick-off a crawl before those properties show up.

Configuration File

First of all, the script which is shown here, requires an XML file which has the crawled and managed properties you want to add. You can find a sample of such an XML below.

To find the values you need to provide for the different attributes, you can look at the documentation for New-SPEnterpriseSearchMetadataCrawledProperty and New-SPEnterpriseSearchMetadataManagedProperty.

Script

Once you have this file, you can feed it into the following script:

Fix variation labels with a wrong locale

Last week, I was doing some support for a customer which had some issues with variations on their intranet which is based on SharePoint 2010.
They have 3 variations:

  • English (Source)
  • Dutch
  • French

While investigating an issue, I started playing around with the browser language and noticed the following  :

  • Browser language = English => Redirect to the “English” variation
  • Browser language = Dutch => Redirect to the “English” variation
  • Browser language = French => Redirect to the “French” variation

Dutch users were redirected to the English site… awkward. I looked at the variation labels and it was pretty clear why this was happening.

011614_1034_FixVariatio1

The english variation is created with the “Dutch” locale. The problem with this is… you can’t change it once it has been created.

011614_1034_FixVariatio2

The solution for this lies in PowerShell. Variation labels are stored in a hidden list on the root web of the site collection and is called “Variation Labels”.

To change the locale, you can use the following script:

Just replace the URL for the site with the url where the variation hierarchy has been created and the “English” with the title for the variation label with the wrong Locale.
Once this is done, the Locale of the variation label is changed and the users are directed to the correct site.

011614_1034_FixVariatio3

Get sites, webs, lists and number of items with their permission inheritance in SharePoint

Working with permissions in SharePoint can become complicated very quickly. Especially when you are starting to break the inheritance on several levels. Finding out which sites and lists have their inheritance broken is a task which can set you back a few hours, depending on the size of your SharePoint environment.
Since I’m always short of time and don’t like doing things manually, I made my life a little less complicated and just scripted it. This gives me a nice CSV output which I can pull into Excel and voila… instant report! PowerShell still rulez! 😎
Here’s the script.