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+"'))"

Colour graduation function - SSRS

I needed to highlight date information dependant on it's proximity to todays date. Using a colour graduation, greater emphasis is placed on dates near todays and those in the future, while less emphasis is placed on historical dates.

I created a custom function:

Public Function MonthColourGraduation(ByVal ActualValue As integer, ByVal NeutralColour As String) As String

Select ActualValue

Case >-1

Return "#FFD2D2"

Case >-2

Return "#FFD5D5"

Case >-3

Return "#FFD9D9"

Case >-6

Return "#FFDDDD"

Case >-9

Return "#FFE1E1"

Case >-12

Return "#FFE4E4"

Case >-15

Return "#FFE8E8"

Case >-18

Return "#FFECEC"

Case >-21

Return "#FFF0F0"

Case >-24

Return "#FFF3F3"

Case >-30

Return "#FFF7F7"

Case >- 36

Return "#FFFBFB"

Case Else

Return NeutralColour

End Select

End Function

and for the table rows background colour:

=Code.MonthColourGraduation((DateDiff("m", now(),Fields!LastEndTime.Value)), "White")

To get the hex colour codes I used:

http://www.herethere.net/~samson/php/color_gradient/

CTE maximum recursion error- SQL

I was running a CTE an got the following Error message:

'The statement terminated. The maximum recursion 100 has been exhausted before statement completion.'

The statement would undertake over 100 recursions hence the message. To rectify, I added the following query hint:


OPTION (MAXRECURSION 500);

Overriding the default of 100 for this particular statement.

Syntax for checking objects existence - SQL

SET NOCOUNT ON;
USE database;
GO
IF OBJECT_ID('dbo.table) IS NOT NULL
DROP TABLE dbo.table
GO
CREATE TABLE .....

Example Select Case to get time period bandings - CR

Example Select Case to get time period bandings:

select ({fldName}-currentdate)
case upfrom_ 365 : "12 months"
case 271 to_ 365 : "9 months"
case 181 to_ 271 : "6 months"
case 91 to_ 181 : "3 months"
case 31 to_ 91 : "1 month"
case 0 to_ 31 : "IMMINENT"
case -90 to_ 0 : "OVERDUE - by up to 3 Months"
case -180 to_ -90 : "OVERDUE - by up to 6 Months"
case -365 to_ -180 : "OVERDUE - by up to 12 Months"
case -545 to_ -365 : "OVERDUE - by up to 18 Months"
case -730 to_ -545 : "OVERDUE - by up to 24 Months"
default: "CHECK STATUS"