Introduction
In Part 2, we talked about deploying SSRS data sources. Now we are ready for reports.
Reports are deployed using rs.CreateReport() method. It accepts array of bytes instead of .rdl file name. This is relatively minor, since reading contents of a file is not that hard. Here’s the code:
Dim len As Integer
Dim fileBytes As Byte()
Using stream As FileStream = File.OpenRead(path)
len = stream.Length
fileBytes = New [Byte](len - 1) {}
stream.Read(fileBytes, 0, len)
End Using
rs.CreateReport("MyReport", "Reports Folder", overwrite, fileBytes, Nothing)
It would be even easier if Microsoft did not mess up the VB example at the above link: they don’t read the last byte of the stream and the result is invalid XML. Interestingly, in the SQL 2000 example, they messed up the array initialization instead, so they send an extra byte to the SSRS server instead. The result is the server complaining about illegal 0×00 character at the end of the report.
It seems difficult to comprehend, even for Microsoft people, that in VB.NET, the number of elements in your array is different from the number you specify in Dim.
Updating Data Source References
Another hurdle is that report definitions, as stored by BIDS, contain invalid data source references. When you upload a report as outlined above, you will get a warning similar to this:
The dataset `DataSet1' refers to the shared data source `MyDataSource',
which is not published on the report server.
HTTP traffic sniffing shows that when BIDS deploys a report, it gets the same warning. The solution is to update the data source references after the report has been uploaded, changing them to existing data sources in the "/Data Sources" folder.
This requires several steps. First, we need to load the report file as XML and locate all data source references.
Dim doc As System.Xml.XmlDocument = New System.Xml.XmlDocument()
doc.Load(rdlPath)
Dim nsManager As XmlNamespaceManager = New XmlNamespaceManager(doc.NameTable)
nsManager.AddNamespace("r", "http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition")
Dim nodes As XmlNodeList = doc.SelectNodes("/r:Report/r:DataSources/r:DataSource/r:DataSourceReference",
nsManager)
Then we need to convert those nodes to DataSource objects recognized by SSRS:
Dim dataSources As DataSource() = New DataSource(nodes.Count - 1) {}
For i As Integer = 0 To nodes.Count - 1
dataSources(i) = CreateDataSourceObj(nodes.Item(i))
Next
Private Function CreateDataSourceObj(ByVal refNode As XmlNode) As DataSource
Dim reference As DataSourceReference = New DataSourceReference
reference.Reference = "/Data Sources/" + refNode.InnerText
Dim result As DataSource = New DataSource
result.Name = CType(refNode.ParentNode, XmlElement).GetAttribute("Name")
result.Item = reference
Return result
End Function
And finally, we need to call rs.SetItemDataSources method:
rs.SetItemDataSources(serverReportPath, dataSources)
The complete code for creating a report is here.
The combined file for creating a data source and a report is here.
It was fun, isn’t it? I am not sure why Microsoft did not make it simpler: SSRS reports are typically needed in an enterprise environment, and production deployment is an important part of development for the enterprise. It would also help if the code sample for CreateReport were right: I spent quite some time trying to figure out why I am getting weird exceptions from SSRS before I noticed the size error.
I hope this text will save others hours of boilerplate coding and frustration.
CodeProject
