Archive

Posts Tagged ‘ssrs’

Changing the format of date parameters on SSRS 2012

January 10, 2014 Leave a comment

 

Had an issue today where the date parameters were being forced into US data format and not using any of the regional settings from the OS or SharePoint.

 

This has apparently been fixed in SQL 2012 SP1 CU5 update but i have not confirmed this.

This post http://www.tachytelic.net/2013/01/changing-the-format-of-date-parameters-on-a-sql-server-2012-reporting-services-report-that-is-sharepoint-integrated/

Has a workaround that required you to add a culture into the RSViewerPage.aspx located in

 

C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\LAYOUTS\ReportServer

 

or

 

C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\TEMPLATE\LAYOUTS\ReportServer

in SharePoint 2013

 

You need to add UICulture="en-GB"

 

to the end of

<%@ Page language="C#" Codebehind="RSViewerPage.aspx.cs" AutoEventWireup="false" Inherits="Microsoft.ReportingServices.SharePoint.UI.RSViewerPage,Microsoft.ReportingServices.SharePoint.UI.ServerPages,Version=11.0.0.0,Culture=neutral,PublicKeyToken=89845dcd8080cc91" %>

 

 

The full string looks like

 

<%@ Page language="C#" Codebehind="RSViewerPage.aspx.cs" AutoEventWireup="false" Inherits="Microsoft.ReportingServices.SharePoint.UI.RSViewerPage,Microsoft.ReportingServices.SharePoint.UI.ServerPages,Version=11.0.0.0,Culture=neutral,PublicKeyToken=89845dcd8080cc91" UICulture="en-GB"%>

 

If you are using Windows 2012 i had to save the updated file to a different location and then copy the file in and gain the correct permissions.

Categories: work Tags:

gantt charts in SSRS

August 17, 2012 1 comment

 

This is just a quick post while I am in a meeting on how to create gantt charts within SSRS.

http://pnarayanaswamy.blogspot.co.uk/2010/09/range-bar-chart-gantt-chart-using-ssrs.html

I hope to expand on this post in the future with my own experiences.

Categories: work Tags: ,

Generate unique X and Y coordinates or grid references from a row number

August 9, 2012 1 comment

I was creating an SSRS report and using a matrix table. I needed to be able to position data within each group by. To do this i needed create a X and Y number for each item where the 2 together would be unique.

 

This is an example of what i wanted

Row number

x

y

1

0

1

2

0

2

3

0

3

4

1

1

5

1

2

6

1

3

7

2

1

8

2

2

9

2

3

 

And here is the SQL that creates it

 

SELECT  ROW_NUMBER() over (ORDER BY prj.ProjectName) as Row       
            , ((ROW_NUMBER() over (ORDER BY prj.ProjectName)) - 1) / 3  as X
            ,  ((ROW_NUMBER() over (ORDER BY prj.ProjectName)) - 1) % 3  as   Y

This took a lot longer to work out that i care to admit. So hopefully someone else will find this useful

 

If you need more columns before the split then simply change the 3 to be what ever number you need.

Categories: work Tags: ,

REGEX to strip XML/RTF tags from WSS webservice

Found an improvement to this to cope with null data.

 

=System.Text.RegularExpressions.Regex.Replace(IIF(IsNothing(First(Fields!<FIELD NAME>.Value, <DATASET NAME>)), “”, First(Fields!<FIELD NAME>.Value, <DATASET NAME>)), “<[^>]+>”,””)

Categories: work Tags: ,