List of employees for example. Quick way to find duplicate values using LAG windowing function
;with c as (
SELECT [FirstName]
,[LastName],
[FirstName] + ' ' + [LastName] x
,[StaffNumber]
FROM Employee
),
cc as(
SELECT FirstName, LastName, StaffNumber, x , lag (x) over (partition by LastName order by x) dup
from c)
select FirstName, LastName, StaffNumber, x , dup From cc
where x=dup
order by LastName
SQL Server, SSRS and Crystal Reports
Wednesday, 6 June 2018
Thursday, 24 May 2018
Count Distinct Over Partition
To get a count of distinct values over a windowing partition:
DENSE_RANK() OVER (PARTITION BY e.Dept_Desc order by e.Staff_No)
+ DENSE_RANK() OVER (PARTITION BY e.Dept_Desc order by e.Staff_No desc)
- 1
DENSE_RANK() OVER (PARTITION BY e.Dept_Desc order by e.Staff_No)
+ DENSE_RANK() OVER (PARTITION BY e.Dept_Desc order by e.Staff_No desc)
- 1
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" & "¶m1="
& replace(
Fields!description.Value, "&", "'+escape('&')+'"
)
& "'));"
="javascript:void(window.open('http://xxx/reportserver?/Folder A/Report X" & "&rs:Command=Render" & "&rc:Parameters=true" & "¶m1="
& 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)
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 & "'));"
Courtesy 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
Subscribe to:
Posts (Atom)