D J Horton Consulting Ltd

Personal reference to useful tips and tricks for SQL Server, SQL Server Reporting Services and Crystal Reports.



My alternative to scraps of paper lying about.



Others may find bits of it useful!








Monday, 22 March 2010

Remove Carriage Return, Line Feed and Tab - SQL

Remove Carriage Return, Line Feed and Tab characters in T-SQL.

REPLACE(REPLACE(REPLACE(Field, CHAR(10), ''), CHAR(13), ''), CHAR(9), '')

Friday, 12 March 2010

Check whether a string was all in uppercase - CR

I needed a quick way to check whether a string was all uppercase.
Using the InStr function:

InStr ({fieldname},(UpperCase ({fieldname})) ,0 )

The syntax being:
InStr([start,]string1,string2[,compare])
start- an optional starting position
string1,2 - strings to compare
compare - either 0 or 1, 0 being a binary comparison, 1 being textual.

http://msdn.microsoft.com/en-us/library/wybb344c(VS.85).aspx

Wednesday, 3 March 2010

Create Button Style - SSRS

To create a reasonable 'button style' for a text box:

BackgroundColor = WhiteSmoke
BorderColor = WhiteSmoke
BorderStyle = Outset
BorderWidth = 2pt
Color = Black



Tuesday, 2 March 2010

SSRS URL Parameters - SSRS

To pass a report parameter through to another report via a new browser:

On the Navigation tab for the report objects properties add the following javascript command to the Jump to URL expression box:
















="javascript:void(window.open('http://SERVER NAME/ReportServer/Pages/ReportViewer.aspx?%FOLDER NAME%REPORT NAME%&rs:Command=Render&PARAMETER NAME="+Fields!Pass value.Value+"'))"

For example:

="javascript:void(window.open('http://dom-pc/ReportServer/Pages/ReportViewer.aspx?%2fCAD+GIS+DB+reports%2fJobs+By+Establishment&rs:Command=Render&EstablishmentParameter="+Fields!SITE_NAME.Value+"'))"