Formatting SharePoint Data in SQL Reporting Services Report

Depending on the field type, SharePoint may format the data as “#n;Value”. Not very pretty. I found this solution somewhere, but don’t remember where. You can add the below function to your SSRS report by going to Report (when on the Data or Layout tab in Visual Studio/Business Intelligence Development Studio) –> Report Properties –> Code.

Function GetNameFromSP(pFullID As String) As String
 Dim strRet As String
 Dim iPos As Integer

 If pFullID = NOTHING Then Return ""
 If pFullID = "" Then Return ""
    iPos = Instr(pFullID, ";")
 If iPos < 1 Then Return pFullID

 Return Mid(pFullID, iPos +2)
End Function

To use this code in an expression, type:

=Code.GetNameFromSP(Fields!MyField.Value)

Trevor Seward is a Microsoft Office Apps and Services MVP who specializes in SharePoint Server administration, hybrid scenarios, and SharePoint Online. He has been working with SharePoint for 16 years from SharePoint 2003 on up, managing environments with terabytes of content for 150,000+ user organizations. Trevor is an author of Deploying SharePoint 2016 and Deploying SharePoint 2019. You can find him on Twitter and in /r/sharepoint.