Search

Advertisements

SSRS - How to add Custom Code and example for using Custom Code

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 :

No comments:

Advertisements