To add Custom code in SSRS reports :
Goto Report - > Report Properties ... -> Code
or right-click outside the body of the report - > Report Properties ... -> Code
Consider this is my Dataset for this example :
Dataset Name : Tablix_Replace_Custom_Code
Dataset Query :
--Sample Data
DECLARE @FY TABLE (Products VARCHAR(20),Q1 VARCHAR(10),Q2 VARCHAR(10),Q3 VARCHAR(10))
INSERT @FY SELECT 'SQLServer2008',1000,200,100
INSERT @FY SELECT 'SQLServer2012',1000,1000,1500
INSERT @FY SELECT 'SQLServer2014',1000,3000,5000
INSERT @FY SELECT 'SQLServer2016','X','X','X'
SELECT * FROM @FY
In this example I have added custom code to replace value "X" with "NULL"
Public Function ReplaceNull(ByVal s As String) As String
Dim strBuilder As New System.Text.StringBuilder(s)
If s.Contains("X") Then
strBuilder.Replace("X","NULL")
Return strBuilder.ToString()
Else : Return s
End If
End Function
I have added below expressions for Q1 , Q2 & Q3 columns : (referencing custom code)
=Code.ReplaceNull(Fields!Q1.Value)
=Code.ReplaceNull(Fields!Q2.Value)
=Code.ReplaceNull(Fields!Q3.Value)
Now click on Preview pane :
Reference - http://technet.microsoft.com/en-us/library/ms155798(v=sql.100).aspx
See Also :
Goto Report - > Report Properties ... -> Code
or right-click outside the body of the report - > Report Properties ... -> Code
Consider this is my Dataset for this example :
Dataset Name : Tablix_Replace_Custom_Code
Dataset Query :
--Sample Data
DECLARE @FY TABLE (Products VARCHAR(20),Q1 VARCHAR(10),Q2 VARCHAR(10),Q3 VARCHAR(10))
INSERT @FY SELECT 'SQLServer2008',1000,200,100
INSERT @FY SELECT 'SQLServer2012',1000,1000,1500
INSERT @FY SELECT 'SQLServer2014',1000,3000,5000
INSERT @FY SELECT 'SQLServer2016','X','X','X'
SELECT * FROM @FY
In this example I have added custom code to replace value "X" with "NULL"
Public Function ReplaceNull(ByVal s As String) As String
Dim strBuilder As New System.Text.StringBuilder(s)
If s.Contains("X") Then
strBuilder.Replace("X","NULL")
Return strBuilder.ToString()
Else : Return s
End If
End Function
I have added below expressions for Q1 , Q2 & Q3 columns : (referencing custom code)
=Code.ReplaceNull(Fields!Q1.Value)
=Code.ReplaceNull(Fields!Q2.Value)
=Code.ReplaceNull(Fields!Q3.Value)
Now click on Preview pane :
Reference - http://technet.microsoft.com/en-us/library/ms155798(v=sql.100).aspx
See Also :
No comments:
Post a Comment