Getting the quarter for a particular date in Reporting Services

Nice, simple, little solution here, but oddly couldn't find anything simple online, so here goes.

If you have a date, and you want to display the quarter for that date (for example, January = 1, April = 2, December = 4), this expression will do the trick (assuming that the date you want to report on is called Modified):

=CEILING(Month(Fields!Modified.Value)/3)

Developing and deploying onto a multi-server farm in SharePoint 2010

Our main SharePoint 2010 farm consists of four servers and one database server.  We originally set up a disaster-recovery environment that was built in the same way, but this has since become our development/testing environment.  One of the servers has Visual Studio 2013 installed, and this is where I do all of my SharePoint 2010 development.

Almost everyone recommends developing on a single-server development farm, and one of the reasons is because Visual Studio can't confidently deploy the development onto the SharePoint site/site collection/web application.  It will often spring up an error:

Error occurred in deployment step 'Activate Features': Feature with Id 'f8214413-dcb6-428e-9252-032ca228ba03' is not installed in this farm, and cannot be added to this scope.

This is basically occurring because, well, I'm not sure.  I know it doesn't happen on a single-server development environment (or at least, not for the same reason).  But I didn't have a choice; I have to develop on this multi-server farm.

So, my solution.  Since I'm able to use PowerShell successfully to update the SPSolution, I figured I'd just take the wsp file created after building in Visual Studio, and run Update-SPSolution instead.  However, the wsp file is not always updated after Building a solution, only when Deploying.  And since Deploying fails (and often happens to retract the solution and delete web parts), I needed to figure out a way of creating the wsp file without attempting an actual deploy.

What I did was:

1. Right-click on the project in Visual Studio, and select Properties.
2. On the left-hand side, select SharePoint to bring up the deployment options.
3. Click "New..." to create a new Deployment Configuration.
4. Call it whatever you want, and simply add "Run Pre-Deployment Command" and "Run Post-Deployment Command" into the right-hand box labelled "Selected Deployment Steps".
5. Click OK, and then change the Active Deployment Configuration to the one you just created.

Now, when you right-click your project and select Deploy, it will create a wsp file and then stop.  You can then run the following command to update your existing SPSolution:

Update-SPSolution -Identity projectname.wsp -LiteralPath "C:\Path To Visual Studio 2013\Projects\SolutionName\ProjectName\bin\Debug\projectname.wsp" -GACDeployment 

You can get the LiteralPath from the output in Visual Studio:

Successfully created package at: C:\Path To Visual Studio 2013\Projects\SolutionName\ProjectName\bin\Debug\projectname.wsp

Sure, you can't Debug in this way, but at least you can continue with your development on a multi-server farm.

Removing people from the SharePoint people picker

For a while now, I've been wondering how to remove people from the SharePoint 2010 people picker (used when adding permissions).  I've had a few occasions where the same person is listed multiple times, due to a bit of a mess-up by my organisation during a few Active Directory migrations.  Each user is normally on a different domain.

No matter how many User Profile syncs I've done, or removed user profiles from the User Profile Service Application, these names are still listed in the people picker.

Finally found a way of removing these accounts once and for all.  Well, there is another way, involving the UserInfo table in WSS_Content database, but that's not recommended by Microsoft.  This is a much safer way.

http://sharepoint/_layouts/people.aspx?MembershipGroupId=0

Simply find the account that needs to be deleted, then click Actions, and then Delete From Site Collection.  Instant result.  Just make sure you don't delete someone's actual user account...

Opening PDFs in the browser in Internet Explorer 9

Our SharePoint 2010 site is designed with the intention of opening PDFs in the browser.  For some users, this works as expected.

However, for some users, PDF links from within document libraries opened separately in Acrobat Reader 11.  This was only affecting PDF links automatically generated within a document library view - if a link was placed directly to a PDF file in the left-hand navigation or a content editor web part, the PDF would always open in the browser.

I've tried the usual things - change Browser File Handling Policy from Strict to Permissive, added "application/pdf" to allowed MIME extensions, etc.  I knew the issue was related more specifically to the browser because Google Chrome was able to consistently open PDFs in the browser.

This is occurring because (from what I can tell) Adobe Acrobat Reader 11 is hijacking SharePoint 2010 PDF links in an effort to integrate better with SharePoint.  This is why document library links are acting differently to direct PDF links.  (It's possible to temporarily subvert this "feature" by holding down Ctrl when clicking on the link to the PDF document, which will open in a new tab, but will be in the browser.)

The reason Chrome and other non-IE browsers aren't affected by this is because Chrome et al have their own PDF rendering techniques, whereas IE relies on ActiveX plugins by Adobe or others.

It seems the only way to permanently fix this is a registry hack (from page 124 of the Acrobat Enterprise Administration Guide):

1. Open the registry.
2. Go to HKLM\SOFTWARE\Policies\Adobe\<ProductName>\<version>\FeatureLockDown.
3. Create a key called cSharePoint.
4. Create a DWORD value called bDisableSharePointFeatures.
5. Set its value to 1.

Unfortunately, I have to now persuade our IT department to roll out this registry hack to all users...

Sandboxed code sometimes failing with "Sandboxed code execution request failed"

Nice and short one here (which unfortunately took me a long time to pin down).  I have a simple webpart running in a sandboxed solution.  The webpart has a label and a button, and there is no other code-behind.

Sometimes, when rendering the web part, I get the error:

[SPUserCodeExecutionPipelineFailedException: Sandboxed code execution request failed.]

It's not a nice error but it only appears sometimes.  I'm running on a four-server farm so I assumed it was something to do with the sandboxed code service not running on one of the servers.  However, the service was running on all of them.

It's probably not solved anything (but it's a development environment so I'm not too bothered), but I found a workaround:

1. Go to Central Administration
2. Click on System Settings, and then Manage user solutions
3. In this screen, select "All sandboxed code runs on the same machine as a request".
4. Click OK.

The webpart now works reliably.  I'm assuming one of my servers isn't happy with the sandbox service credentials but I can move on with my life for now.

Creating a printable InfoPath form

I don't like printing.  But inevitably, someone will want to do it somewhere.

We have a list, which uses an InfoPath form for data entering and viewing.  In here, we created a Print view, which is basic black text on white background, and read-only.

However, when trying to print this view, the rest of our SharePoint master page and branding is printed with it, and it doesn't look very professional.  So we needed to create a printable version of this Print view.

Some suggestions online included using jQuery to remove everything BUT the InfoPath form, but this sounded like a messy way of doing things.  Instead, I figured I could just create a new blank page in SharePoint Designer and grab the item ID from the URL.

My first attempt failed.  I tried using a Query String Filter Web Part, but then realised that sending an ID to an InfoPath Web Part doesn't do what you'd think it would do.  It doesn't magically open the item you're referencing with the ID, it simply changes the InfoPath form item ID.  You could use this method for pre-populating an InfoPath form based on parameters in your query string, but not for actually loading an already-existing form.

I figured that you could instead use that ID to filter a List View of your list, and then send that single item to the InfoPath web part.  This would also mean I wouldn't need the Filter Web Part, as a List View has its own Filter and Parameter settings.  So, here's what I did.

1. Using SharePoint Designer, create an ASPX page in your site called PrintForm.aspx.
2. In the ASPX page, add an InfoPath form web part, and configure the web part to use the list you're trying to print items from.  Configure this web part to use the correct Print view that you've already created in InfoPath Designer.
3. Somewhere else on the page, add a Data View, and again configure to use the list you're trying to print items from.  Configure this web part to be hidden or not visible (unless you want to see the list view as well).
4. With the List View web part selected, add a Parameter.  This will be a query string parameter with the name ID, and the Query String Variable of ID as well.  (You can change this to ItemID or something else if you want)
5. Next, add a Filter for the List View web part.  This will get the Field Name ID to equal the parameter you just created, which will be displayed as [ID] or [ItemID] or whatever you chose in the previous step.
6. Finally, Add a Connection from the List View to the InfoPath web part.  You want to Send Row of Data To the InfoPath web part, using the Target Action "Get Form From".  
7. Save and check in the file as appropriate.

Now, if you try to access the URL of this page with an ID added to the end, you will get a nice, basic printable view of your data.  You'll want the URL to look like this:

http://sharepoint/sitename/Pages/PrintForm.aspx?ID=25

What you'll need to do then is add a Print button to the normal Read or Edit view of the InfoPath form, which will send the user to that URL above, with the current item ID after the ID= (in the example above, the item ID is 25).

There's also a little bit of JavaScript you can throw in to your PrintForm.aspx page which will tidy up a few last remnants of the InfoPath web part - namely, the web part name, and the light blue border.  It also presents the user with their browser Print dialog, to save them having to go to File/Print.  It looks like this:

<script src="/scripts/jquery-1.9.1.min.js" type="text/javascript"></script> 
<script type="text/javascript">
  $('.ms-WPHeader').hide();
  $('.ms-WPBorder').css('border', 'none');
  setTimeout(function() {
    window.print();
  }, 500);
</script>

Edit: added a setTimeout before printing, because it was printing a blank page, even though print preview was working correctly.

When was a SharePoint list created? Who created a SharePoint list?

Pretty simple one, this - I was surprised to find I can't easily see a list Created date in the usual SharePoint 2010 interface, so this little PowerShell snippet did the trick.

$spweb = get-spweb -identity http://sharepoint/sitename
$splist = $spweb.lists["List Name"]
$splist | format-table -property created, author

Update: added the "Created By" (author) field as well, so you can see who created the list.