Posts Tagged ‘sql server reporting services’

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

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 @

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…)CPSLogo

Full details here:

That’s it!, Keep SharePoint’n


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

January 7, 2013 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:

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:

TechEd Europe Day One #TEE12

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…:

Ben, Mark, Robin and me

Ben, Mike, Robin and me

As part of the TLC Office stand some other cool and geeky items:

The Azure datacentre in Lego.

A cool Office 365 Lego giveaway…

and the most elaborate Microsoft Flight Simulator you will ever see…

And towards the end of the day I caught up with the Nintex crew to discuss Nintex Workflow for Project Server.

NW4PS2010_300px_72dpi_V_trans (1)

Thanks for the catch up guys.

So that just about wraps up day one.  Presentations and flights home tomorrow… till the next post – Good evening – Goedenavond

%d bloggers like this: