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!








Friday, 1 September 2017

Using javascript to encode a URL with an ampersand

javascript escape function:


="javascript:void(window.open('http://xxx/reportserver?/Folder A/Report X" & "&rs:Command=Render" & "&rc:Parameters=true" & "&param1="
& replace(
Fields!description.Value, "&", "'+escape('&')+'"
)

& "'));"
 

Tuesday, 23 May 2017

SSRS chart display % on labels issue

SSRS chart display % on labels issue

To show percentage symbol after a literal value:

Chart series label - Properties
Format = 0\% or 0.00\% (depending on no. of dec. places reqd)

Monday, 13 February 2017

ssrs url javascript


="Javascript:" 
    & IIF(left(Fields!Name.Value,11)="RESTRICTED-", 
        "alert('Restricted!'); ","") & IIF(Fields!Name_Alert.Value = 1, "alert('Alternate Alert!'); ","") 
    & "void(window.open('" 
    & Globals!ReportServerUrl 
    & "/Pages/ReportViewer.aspx?%2fJPD%2fPO_Dashboard%2fJuvenile_Profile&rs:Command=Render" 
    & "&rc:Parameters=true" 
    & "&Emp_Number=" 
    & Parameters!Param1.Value 
    & “&ID=" & Fields!ID.Value & "'));"



 
Courtsey of Christoper Brown at:

http://stackoverflow.com/questions/18003013/open-ssrs-url-in-new-window



=iif(

COUNT(Fields!ABC.Value)>0,

"javascript:void(window.open('http://XXX/reportserver?/my Reports/this report"
& "&rs:Command=Render"
& "&rc:Parameters=true"
& "&p1=" & Parameters!p1.Value
& "&f1=" & Fields!f1.Value
& "&f2" & Fields!f2.Value &
"'));",NOTHING)
 







URL encoding


Replace(Fields!myfield.Value,"&","' + escape('&') + '")

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/6462b0f6-d784-4c7a-afe0-2813bec5770a/url-action-with-javascript-and-ssas-parameter?forum=sqlreportingservices

 





 
 
 




 

Tuesday, 11 October 2016

SSRS custom sort

Public Function CustomSortOrder(ByVal RAG As String) as Integer

Select Case RAG

Case = "Up to 6 month"

Return 1

Case = "7 to 12 months"

Return 2

Case = "More than 12 months"

Return 3

End Select

End Function


=Code.CustomSortOrder(Fields!RAG.Value)

Thursday, 30 June 2016

ssrs subreport background colour

Ssubreport properties.
Select Parameters and add a parameter.
The name column is the name of the parameter in the subreport (rowcolour) and value is the value to set it to.
Set Value to the same expression used to set the background color for the row.
background color =Parameters!rowcolour.Value into the expression builder.