Thursday, 18 April 2013

SharePoint 2010 - When [Today] just isn't your day

Sometimes your SharePoint environment doesn't want to play ball with OOTB features, so if you like me have once had an issue with the [Today] option not working on list filters, here is the slower but successful way of getting it to work:

Create the view using CAML Query in SharePoint Designer 2010.

The CAML equivalent of [Today] is:
<Value Type="DateTime"><Today/></Value>

Or if you want to be a few days ahead or behind today

<Value Type="DateTime"><Today OffsetDays="-31"/></Value>

Cheers,
The Baretta

SharePoint 2010 - List View Threshold


Any well utilised SharePoint farm will hit this limit at one point or another, the annoying but not hard to resolve LIST VIEW THRESHOLD ISSUE!!!!!!


If you're on the ball, you'll know to cater for this when first creating a list you know is going to be quite popular with the lads.  This involves creating indexed columns, and creating views that filter on the indexed columns.

But woe to those who wait for the dreadful message to appear, and also to those who prepared for it, but it is still occurring anyway.   WHHHHHHYYYYYYYYYYYYYYYYYYY!!!!!!!!!!!!....

STOP!  CAML TIME!

Well it's because when you modify a view using the SharePoint GUI, and you filter on a indexed column and maybe a few others, SharePoint turns your filters & options into a CAML query, and if that query contained a few different column filters, SharePoint occasionally doesn't render the CAML query in a manner that ensures you are always querying less than the List View Threshold!

OH NOES!

Oh yes.  If this happens, it means that you will have to manually create the CAML query on the page using SharePoint Designer.  Just make sure that the FIRST column you filter on is an indexed column that will bring back less items than the List View Threshold limit.

Never had to write CAML?  well there's plenty of info on Google for CAML queries, so I won't explain it here.  but I will supply an example below for interests sake.  Any questions let me know :)

EXAMPLE CAML:

<Query>
<Where>
<And>
<Eq>
<FieldRef Name="Status"/>
<Value Type="Text">Open</Value>
</Eq>
<Or>
<Eq>
<FieldRef Name="AssignedTo"/>
<Value Type="User">Simon Blob</Value>
</Eq>
<Or>
<Eq>
<FieldRef Name="AssignedTo"/>
<Value Type="User">Roger Reporter</Value>
</Eq>
<Or>
<Eq>
<FieldRef Name="AssignedTo"/>
<Value Type="User">Moses Roses</Value>
</Eq>
<Eq>
<FieldRef Name="AssignedTo"/>
<Value Type="User">Daniel Dogood</Value>
</Eq>
</Or>
</Or>
</Or>
</And>
</Where>
</Query>
END OF EXAMPLE CAML.

SharePoint 2010 - CSS & JS Across Multiple Web Applications

An issue I came across the other day involved creating a web application with the look & feel as another web application on the SharePoint 2010 Farm.

I decided to link all the CSS from the second Web Application to the first Web Application, which works fine for the majority of functions (background, colours, images, page width, etc).

Here's what was failing for me, and the only workaround was to make a copy of the CSS on both Web Applications:

  • Certain JQuery & Javascript files failed to render
  • Custom Markup Styles in the Ribbon failed to appear
Moral of the story, Don't point Master pages to another Web Applications CSS & JS files.  Make a copy of them on each Web App

SharePoint 2010 - SQL Server 2012 Reporting Services Integration Steps (Multiple Server Farm)

Most SharePoint customers suffer no issues when integrating SQL Server Reporting Services 2012 (SSRS 2012) with SharePoint 2010 (SP 2010), I however, had quite a bit of trouble.  So for those at home who may also be having some trouble, I thought I'd prescibe you some troubleshooting medicine along with a few spoonfuls of errors.

The official steps you may have read about:
- Install SQL Server with Reporting Services Add-in for SharePoint
- Install SharePoint on the Reporting Services Server and connect that server up to the farm (using Configuration Wizard)
- Install the rsSharePoint.msi file provided by Microsoft on all your front-end servers running SharePoint.
- Run these two commands on all front-end servers running sharepoint

  • Install-SPRSService
  • Install-SPRSServiceProxy
- Navigate to Central Administration > System Settings > Manage services on server.  Start the SQL Server Reporting Services Service
- Navigate to Central Administration > Application Management > Manage Service Applications.  Create a new SQL Server Reporting Services Service Application

----------------------------------------------------------------------------------------

Here's the issues you may come across and the solutions I found to the issues:

Issue 1:
"Install-SPRSService : The term 'Install-SPRSService' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again"

Solution:
You need to install the Reporting Services SharePoint Add-In (Found on your SQL Server 2012 installation media) on every server running SharePoint.  Please don't use the rsSharePoint.msi provided on Microsoft's website, here's why....

----------------------------------------------------------------------------------------

Issue 2:
"When I try to create a SQL Server Reporting Services Service Application under 'Manage Service Applications', I receive an error relating to permissions to a registry key"

Solution:
There's nothing wrong with your registry except that SharePoint is looking for files that don't exist.  This is because you most likely installed the Reporting Services SharePoint Add-In with the rsSharePoint.msi file that Microsoft provides on their website.  That file is a cut-down version of what gets installed when you install the Add-In from the SQL Server 2012 installation media.  Uninstall it from Control Panel, and re-install it with the SQL Server 2012 installation media.  

Things to note:  
  • Your Windows servers running SharePoint will need to be updated to SP1 if WS2008R2, or SP2 if WS2008
  • Trying to create the Service Application may have created the databases on your SQL Server but not deleted them.  You will have to remove them manually (at your own risk!).
----------------------------------------------------------------------------------------

Issue 3:
"I've created my SQL Server Reporting Services Service Application, but when I try to access any of the links inside it (system settings for instance), I'm receiving a 503 unauthorised error message"

Solution:
This may vary from case to case, but for me it was because I ran these commands on every front end server:
  • Install-SPRSService
  • Install-SPRSServiceProxy
When in actual fact, depending on your scenario (SharePoint Standard Licensing I think is the cause), you should only run these scripts on the server that you wish to be running the Server Service.  You will need to uninstall them by using the following scripts (remember to delete the application and stop the service  in Central Admin first)
  • Install-SPRSServiceProxy -uninstall
  • Install-SPRSService -uninstall
----------------------------------------------------------------------------------------

That's it!  You should now be winning at SharePoint life.  I did spend quite a bit of time on this, so if anyone has any questions, please feel free to comment.