SSRS byte stream into VB.net dataset

Question

Edit: Updated question for clarity: I need to deserialize the SSRS byte stream to a dataset while in memory.

I need to get an SSRS report from VB.Net and get the result into a dataset. I can read and write to XML files, but but for some reason my brain is breaking on putting the XML report directly into a dataset so I can manipulate it.

I can get a report and stream it to an XML file:

 Public Sub GetReportNames(serverName As String)

        Dim rs As New ReportingService2010()
        rs.Credentials = System.Net.CredentialCache.DefaultCredentials

        Dim items As CatalogItem() = Nothing

        ' Retrieve a list of all items from the report server database. 
        Try
            items = rs.ListChildren("/", True)

        Catch e As SoapException
            MessageBox.Show(e.ToString())
        End Try

        ' Serialize the contents as an XML document and write the contents to a file.
        Try
            Dim fs As New FileStream("ReportNames.xml", FileMode.Create)
            Dim writer As New XmlTextWriter(fs, Encoding.Unicode)

            Dim serializer As New XmlSerializer(GetType(CatalogItem())) ''
            serializer.Serialize(writer, items)

            'MessageBox.Show("Report names successfully written to a file.")

            writer.Close()

        Catch e As Exception
            MessageBox.Show(e.ToString())
        End Try

    End Sub

I can read an XML file into a VB.net dataset:

Public Sub FileToDataset()

' Serialize the contents as a Dataset
Dim myXMLfile As String = "C:\MyFile.xml"
Dim ds As New DataSet()
' Create new FileStream with which to read the schema.

Dim fsReadXml As New System.IO.FileStream(myXMLfile, FileMode.Open)

ds.ReadXml(fsReadXml)

End Sub

For my project I am starting with getting report names, then will move on to getting the parameters for each report, then will move on to calling reports. I have simple example code for each step, but currently it all involves writing to a XML file on disk...

Edit: It looks like I should be able to do what I need to do from the CatalogItem. Basically I want to load the report names into a TreeView, so CatalogItem should be fine.

BClaydon BClaydon · Accepted Answer · 2013-10-16T23:04:36

So, as you can see here, the answer is to render the SSRS report to result() as type Byte, move to MemoryStream, and them ReadXml the stream into your dataset. Maybe someone can make the example simpler, I pulled it out of something pretty complicated.

Public Sub fillADatasetFromRdl

Const ReportName As String = "My Report Name"
Private _reportNamePath As String = "/Optional SSRS Directory/" + ReportName
Private _formatXml As String = "XML"


' Prepare report parameter lists. 
Private parametersRdl(4) As ParameterValue

    ' Set RDL parameters
parametersRdl(0) = New ParameterValue()
parametersRdl(0).Name = "StartDate"
parametersRdl(0).Value = "[Current Date].[YearMonthDate].[Month].&[2012-10-01T00:00:00]"

parametersRdl(1) = New ParameterValue()
parametersRdl(1).Name = "EndDate"
parametersRdl(1).Value = "[Current Date].[YearMonthDate].[Month].&[2012-12-01T00:00:00]"

parametersRdl(2) = New ParameterValue()
parametersRdl(2).Name = "ProvidersProviderLocation"
parametersRdl(2).Value = "[Stores].[Store Location].[Stores].&[123456]" 

parametersRdl(3) = New ParameterValue()
parametersRdl(3).Name = "ProviderGroupName"
parametersRdl(3).Value = "Enterprise Report"

' Load dev dataset
Dim devDataSet As New DataSet

Dim rs As New ReportExecutionService
rs.Credentials = System.Net.CredentialCache.DefaultCredentials

' Render arguments
Dim result As Byte() = Nothing
Dim historyID As String = Nothing
Dim devInfo As String = "<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>"

Dim encoding As String = ""
Dim mimeType As String = ""
Dim warnings As shpdevdb01_Execution_Service.Warning() = Nothing
Dim reportHistoryParameters As MyServer_Management_Service.ParameterValue() = Nothing
Dim streamIDs As String() = Nothing

Dim execInfo As New ExecutionInfo()
Dim execHeader As New ExecutionHeader()
Dim SessionId As String
Dim extension As String = ""

rs.ExecutionHeaderValue = execHeader

execInfo = rs.LoadReport(reportNamePath, historyID)


rs.SetExecutionParameters(parameters, "en-us")

SessionId = rs.ExecutionHeaderValue.ExecutionID

Try
    result = rs.Render(format, devInfo, extension, _
       encoding, mimeType, warnings, streamIDs)

    execInfo = rs.GetExecutionInfo()

    Dim bytes() As Byte = result
    Dim s As New System.IO.MemoryStream(bytes)
    dsDevData.ReadXml(s)

Catch e1 As SoapException

End Try


Catch e As Exception
End Try

End Sub

SSRS byte stream into VB.net dataset

1 Answers