sql server reporting services | SharePoint (and Project Server) Shenanigans

DATEADD DAY parameters and the float based values #SQL #SSRS #SharePoint #CPSReportLink

July 24, 2013 Giles Hamson Leave a comment

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

CPS ReportLink

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

Categories: Giles Hamson, Work Tags: CPS ReportLink, DATEADD, SharePoint 2010, SQL, sql server reporting services, SSRS, SSRS 2008 R2

#SharePoint Integration Mode and no Data Driven Subscriptions #SP2013 #SP2010 #SSRS

January 7, 2013 Giles Hamson 2 comments

Just come back after the New Year and was posed with an interesting problem.

SharePoint 2010, SSRS in integration mode but under the manage subscriptions link within a report, the Add Data Driven Subscription button was missing from the screen.

So after some googling / binging around, I found that the answer was because we were using SQL Server 2008 Standard and Data Driver Subscriptions require the Enterprise version of SQL Server.

Anyway, just a quick post in case I forget in future. Useful links below:

SSRS Feature Support – Integration Mode: http://msdn.microsoft.com/en-us/library/bb326290(v=sql.100).aspx
How to use Data Driven Subscriptions within SharePoint: http://blogs.devhorizon.com/reza/2008/10/26/ssrsdata-driven-subscriptions-in-integrated-mode-part-1/
Required versions for Data Driven Subscription support: http://msdn.microsoft.com/en-us/library/ms159150(v=sql.100).aspx

Although in this scenario I am talking about SQL Server 2008, the same is true of SQL Server 2008 R2. In SQL Server 2012, Enterprise or Business Intelligence editions are required:

http://technet.microsoft.com/library/cc645993(SQL.110).aspx

Categories: CPS Cross Post, Work Tags: Data Driven Subscription, Integration Mode, SharePoint, sql server reporting services

TechEd Europe Day One #TEE12

June 27, 2012 Giles Hamson Leave a comment

So I am here in Amsterdam having completed day one of TechEd Europe 2012 and once again, the conference is an awesome experience. There is one major difference however…

Now I actually know some people from the last conference I went to (MSPC12). So I am catching up with some colleagues (Alex Burton – Nintex and Ben Howard – Applepark) as well as getting to know new colleagues (Robin Kruithof – CXS and Mike Wϋbbold – SolutionTime).

The focus of the conference is very much about the cloud:

Innovations in Windows Server 2012 to support private and public clouds
Windows Azure advances with websites, Hyper-V, SSRS to truly support private and public cloud infrastructure in anyway you can imagine.
And of course Office 365 incorporating SharePoint Online, Exchange Online and Lync Online

Day One Keynote

The keynote was an impressive site with equally bold statements on how Microsoft are not only in the cloud, but mature in the cloud with innovation on how it should be done!!!

Very bold statements indeed but with one difference… evidence!

Discussions on how Microsoft have achieved the scalability of Azure through the use of automation technologies such as PowerShell.

How Microsoft have worked with storage and network vendors to ensure optimum throughput on the Windows Server 2012 platform.

Copying a 10GB file is seconds across the network in seconds! I couldn’t take a picture fast enough…

And lastly, how Hyper-V has come of age as a true competitor to VMWare in the virtualisation market with IOPS stats they can only dream of…

That’s 1,026,020.31 IOPS and only limited by current hardware, not the software!

Azure Reporting Services

Moving on from this and after catching up with colleagues. I moved into Azure SSRS which was launched at the North America TechEd event at the beginning of June.

SQL Server Reporting Services is now available in the cloud and we can finally say that Business Intelligence is starting to become available in a public cloud environment.

Some key things to note:

The API interfaces of SSRS are available:

However, some restrictions to the current launch do apply:

No support for custom assemblies, extensions, report items or elements
Single data source – Windows Azure SQL Server
Basic security model
Scheduling, subscriptions and alerting are not supported
Limited Report Builder support and BIDS / SQL Data Tools is the recommend report creation tool for now

These are some serious limitations, but we are assured that this is being worked on and we will see improvement in future.

No word or official comment on integration with Office 365 but it wouldn’t surprise me if SharePoint Online integration is not coming down the line.

Staffing the Microsoft Project – Technical Learning Centre (TLC)

To gain attendance to one of the biggest Microsoft conferences, some of us have to pay our way in kind. 🙂

This humble bloggers approach is through presentations (more on that tomorrow…) and staffing the Microsoft Project booth at TechEd to help customers understand how Microsoft Project 2010 and Microsoft Project Server 2010 fit into the Office Server eco-system with SharePoint and other products.

We also provide help to anyone who would like to pose us with problems… almost like a live MSDN forum answer session.

It is also a good opportunity to catch up fellow colleagues in the EPM space and discuss approaches to client implementations etc…: