Archive

Posts Tagged ‘SSRS 2008 R2’

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

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

Advertisements

SSRS Web Part Performance 2008 R2 vs. 2012 Initial Results #SharePoint #SP2010 #in

April 30, 2012 11 comments

On one of my client projects, we heavily use SQL Server Reporting Services in SharePoint integration mode.

The benefits in comparison to Native mode are primarily around the RS SharePoint Add-in which allows us to use query string (URL) filter web parts to pass parameters to reports and create powerful dynamic dashboards as well as using SharePoint to permission and store the reports.

There is an issue however.  Performance in this mode is not amazing, as detailed here:

Now that SQL Server 2012 has gone to RTM, we can start to get a measure of how well the new web part performs and I am pleased to say that the improvements appear to be great so far…

The Scenario

  • SharePoint Version: 2010 SP1 – December 2011 CU with Project Server
  • DB Server: SQL Server 2008 R2 SP1
  • SSRS Version: 2008 R2 SP1
  • SSRS Add-in: 2012 (Denali) – RTM
  • Server Architecture: 1 x WFE / App Server (Virtual) + 1 x SQL Server (Virtual)

NB: All testing has been carried out on our DEV server, please always try out new software on a development / test environment first to ensure the benefits and to safe guard production from issues.

The Reports:

Table based reports rolling up Project Server and SharePoint data based on parameters provided by the end user (Cannot show due to client confidentiality I’m afraid).

The Results:

Now this is just a teaser really since we will be doing full testing on our master development environment, but for now…

Project Portfolio Table Report

  • Number of returns items: 17 rows
  • Number of parameters: 1 (Portfolio ID)

SSRS2008R2vs2012

NB: All time taken in seconds using Fiddler2

SSRS2008R2vs2012_ProjectPortfolioWholePage_Graph SSRS2008R2vs2012_ProjectPortfolioSSRS_Graph
SSRS2008R2vs2012_ProjectPortfolioWholePage_Pie SSRS2008R2vs2012_ProjectPortfolioSSRS_Pie

As you can see, we are seeing significant time increases with the new web part and within my client we have the go ahead to carry out further testing.

More useful links:

%d bloggers like this: