SharePoint (and Project Server) Shenanigans

Just a quick one.  Recently bug fixing an issue with an SSRS report that calculated the date and time an SLA was due and highlighted fields accordingly.

The Setup

A choice field in SharePoint determining the expected SLA with values as follows:

• 0.5 days
• 1 day
• 2 days
• 3 days
• 5 days
• 10 days
• 15 days
• 20 days
• 30 days
• 60 days

What I am trying to achieve

The values are inside SQL (thanks to CPS ReportLink) as a string value but I need to work out based on a start SLA date and time, when the target SLA date and time is.

So I need to strip “day” / “days”, convert to a float and then run a dateadd function to determine my target date.

In SSRS we will then compare that to today and create a nice RAG indicator in some form or another.

The Issue

The DATEADD function doesn’t appear to like a float based number.  In fact it rounded 0.5 down to 0 making the start date the same as the target date.  This ultimately raised an issue in the UAT process I am going through today at a client.

The Original SQL

DATEADD(day, CONVERT(float, REPLACE(REPLACE(wss_SLA_String, ‘days’, ”), ‘day’, ”)), CONVERT(datetime,[wss_SLA Start Time_DateTime])) as [Target SLA Finish]

The Resolution

So given that I am on-site and the developers who created the SQL are nicely on holiday, I have had to roll up the sleeves and delve in.

So taking my developers original concept, rather than working with days, if we convert it down to hours, we get the desired result without any CASE co-fuffle in the SQL. (Co-fuffle – official word of the day @ SPandPS.com)

Update the stored procedure and bob’s your uncle, we are set and another UAT issue checked off the list.

The Updated SQL

DATEADD(HOUR, CONVERT(float, REPLACE(REPLACE(wss_SLA_String, ‘days’, ”), ‘day’, ”)) * 24, CONVERT(datetime,[wss_SLA Start Time_DateTime])) as [Target SLA Finish]

And lastly, a shameless plug

So how did you get SharePoint list data into a database for reporting without breaking the rules and going directly to the content database, I hear you ask…

Why I used CPS ReportLink of course. 

It copies data from a list into a database and keeps everything in sync for easy reporting using the standard Business Intelligence tools provided as part of SharePoint (Excel Services, PerformancePoint etc…) / SQL Server (SSRS, PowerView etc…)

Full details here:

http://www.cps.co.uk/What-We-Do/Documents/ReportLink%20for%20SharePoint%20and%20Project%20Server.pdf

That’s it!, Keep SharePoint’n