SQL Reporting Services Viewer Page Using SOAP API






4.45/5 (37 votes)
Dec 27, 2004
5 min read

774122

2230
A report viewer page for SQL Reporting Services that uses exclusively its SOAP API.
Contents
- Introduction
- Prerequisite
- Architecture
- Using the Code
- Key Points
- Known Limitations
- Conclusion
- History
Introduction
This code is intended to provide a starting point for a SQL Reporting Services viewer that uses exclusively the SOAP API for rendering. This report will be embedded as a HTML fragment into the calling page and no IFRAME is used if the format is HTML. This is as an alternative to URL report access or to use the sample report viewer web server control that is coming with RS installation.
This article will help you if you are looking for more control over how you can integrate a report and its parameters into your web application that may use different authentication methods than Reporting Services, usually if you want something more than what you already find in the ReportViewer web server control. All prompts for parameters are created dynamically and you can easily apply different formatting or even hide some of them.
Prerequisite
- I assume you are familiar with SQL Reporting Services, what they are, design and installation. For more info about this subject, click here.
- You should have SQL Reporting Services already installed and know the address of the WSDL.
- You should also have SQL Reporting Services sample reports installed or other reports available.
Architecture
This project consists of two ASP.NET (VB.NET) pages:
- ReportViewer.aspx which displays SQL Reporting Services report, together with all the associated prompts and options for zoom and format.
- GetImage.aspx which will render any image associated with the report. Reporting services can be accessed via a web service proxy class for SQL Reporting Services WSDL. There is no call directly from the browser to the report server. Report name, report server WSDL address, username and password are hand-coded as properties of the page class. I am outlining here the report viewer page, the code behind and the image rendering page, to provide you with the concept and reasons. You can also open the project in VS and view the code directly as I have inserted as many comments as I could.
The Report Viewer Page
All report prompts will be created dynamically in the page load event. Therefore, we only need to put the placeholders that will host them in the web page. Here is the HTML code:
<%@ Page Language="vb" AutoEventWireup="false"
Codebehind="ReportViewer.aspx.vb"
Inherits="SQLRS_ReportViewer.ReportViewer"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<title>ReportViewer</title>
<meta name="GENERATOR" content="Microsoft Visual Studio .NET 7.1">
<meta name="CODE_LANGUAGE" content="Visual Basic .NET 7.1">
<meta name="vs_defaultClientScript" content="JavaScript">
<meta name="vs_targetSchema"
content="http://schemas.microsoft.com/intellisense/ie5">
<LINK href="Styles.css" type="text/css" rel="stylesheet">
</HEAD>
<body MS_POSITIONING="GridLayout">
<form id="Form1" method="get" runat="server">
<input type="hidden" name="Report" id="Report" runat="server">
<TABLE id="TablePrompts" runat="server"
class="MenuBarBkGnd ParamsGrid" width="550"></TABLE>
<div id="ReportPlaceholder" runat =server></div>
</form>
</body>
</HTML>
The Code Behind
First, we call GetReportParameters
method to get a list of parameters. The ForRendering
parameter is set to be False
.
reportParametersArray =
rs.GetReportParameters(reportPath, Nothing, False, Nothing, Nothing)
If there are any parameters associated with the report, we'll create a dynamic array of ParameterValue
to collect their values. They may come from three sources: URL parameters, previous page submission, or inside report definition. Parameters with blank values are not stored in this array. We use this array to call GetReportParameters
method again, this time specifying ForRendering
parameter to be True
. This will retrieve a list of available values based on the given parameter values, as well as the status for each parameter, which can be: HasValidValue
, HasOutstandingDependencies
, DynamicValuesUnavailable
or MissingValidValue
.
'hash table with dependecies
Dim Dependencies As New System.Collections.Hashtable
'we need a dynamic array for storing parameter values
Dim reportParameterValues As ArrayList
If reportParametersArray.Length > 0 Then
reportParameterValues = New ArrayList
'loop over each parameter and build the array of values
For Each parameter As ReportParameter In reportParametersArray
Dim value As ParameterValue = New ParameterValue
value.Name = parameter.Name
value.Label = parameter.Name
'if the report value is coming from URL or previous submission
'then it is part of the Request
If Not Request(Parameter.Name) Is Nothing Then
'set the value from URL or submission
value.Value = Request(Parameter.Name)
ElseIf Not Parameter.DefaultValues Is Nothing Then
'set the value to the one defined inside report
value.Value = Parameter.DefaultValues(0)
Else
'in this case we dont have a value for this parameter
'we cannot render this report
value.Value = ""
reportCanBeRendered = False
End If
If value.Value <> "" Then
reportParameterValues.Add(value)
End If
'check if this parameter is dependent of another one
If Not (parameter.Dependencies Is Nothing) Then
For Each d As String In parameter.Dependencies
'add the parameter as a dependecy if was not already added
If Not Dependencies.Contains(d) Then
Dependencies.Add(d, Nothing)
End If
Next
End If
Next 'from: For Each Parameter As ReportParameter In Parameters
'Get the list of parameters this time specifying to
'render the report (ForRender set to true)
'valid values for these parameters will be populated
reportParametersArray =
rs.GetReportParameters(reportPath, Nothing, True, _
reportParameterValues.ToArray(GetType(ParameterValue)), Nothing)
Then I loop again over the parameters array and build dynamically the HTML for prompts. I use HTML Controls rather than ASP Web Controls because in this way I can capture the values more easily when the form is submitted. Inside the loop, it'll be determined whether the report can be rendered based on the value of the Parameter.State
property. Some parameters may depend on other parameters and their valid values may not be specified yet.
Dim i As Integer = 0
For Each parameter As ReportParameter In reportParametersArray
' if any of parameters doesnt have valid values the
' report cannot be rendered
If parameter.State <> ParameterStateEnum.HasValidValue Then
reportCanBeRendered = False
End If
If parameter.DefaultValues(0) =
"" And parameter.AllowBlankSpecified And Not parameter.AllowBlank Then
reportCanBeRendered = False
End If
If parameter.DefaultValues(0) Is Nothing And _
parameter.NullableSpecified And Not parameter.Nullable Then
reportCanBeRendered = False
End If
If i Mod 2 = 0 Then
TR = New System.Web.UI.HtmlControls.HtmlTableRow
End If
'set the propmt text
TD = New System.Web.UI.HtmlControls.HtmlTableCell
TD.InnerHtml = Parameter.Prompt
TR.Controls.Add(TD)
'set the prompt input control
TD = New System.Web.UI.HtmlControls.HtmlTableCell
Dim promptType As String = "textbox"
If Parameter.ValidValuesQueryBased Then
promptType = "dropdown"
End If
If Not parameter.ValidValues Is Nothing Then
If parameter.ValidValues.Length > 1 Then
promptType = "dropdown"
End If
End If
Select Case promptType
Case "dropdown"
'in this case we set a drop down select list
S = New System.Web.UI.HtmlControls.HtmlSelect
S.ID = Parameter.Name
S.Name = Parameter.Name
If Not Parameter.ValidValues Is Nothing _
And Parameter.ValidValues.Length > 0 Then
If Parameter.State =
ParameterStateEnum.MissingValidValue Or
Parameter.State =
ParameterStateEnum.HasOutstandingDependencies Then
reportCanBeRendered = False
LI =
New System.Web.UI.WebControls.ListItem("<Select a Value>", "")
S.Items.Add(LI)
End If
For Each vv As ValidValue In Parameter.ValidValues
LI = New System.Web.UI.WebControls.ListItem(vv.Label, vv.Value)
'see if this value is the same with the default value
'in which case we make the current list item selected
If vv.Value = parameter.DefaultValues(0) And Parameter.State =
ParameterStateEnum.HasValidValue Then
LI.Selected = True
End If
S.Items.Add(LI)
Next
End If
'check if this parameter have dependencies.
'If it does then we need to reload the page to
'reload the value of dependent params
If Dependencies.Contains(Parameter.Name) Then
S.Attributes.Add("OnChange", "this.form.submit();return true")
End If
TD.Controls.Add(S)
Case "textbox"
'in this case we set an input text box
T = New System.Web.UI.HtmlControls.HtmlInputText
T.ID = Parameter.Name
T.Name = Parameter.Name
If parameter.DefaultValues.Length > 0 Then
T.Value = Parameter.DefaultValues(0)
End If
TD.Controls.Add(T)
End Select
TR.Controls.Add(TD)
'add this row to the table
If i Mod 2 = 1 Then
TablePrompts.Controls.Add(TR)
End If
i += 1
Next 'from: For Each Parameter As ReportParameter In Parameters
If i Mod 2 = 1 Then
TablePrompts.Controls.Add(TR)
End If
End If
The last step is to render the report according to the format
parameter and display it in the page. If the format is HTML4.0 or HTML3.2, then we build a specific DeviceInfo
parameter. StreamRoot
parameter should point to GetImage.aspx page. This is very important:
Dim streamRoot As String
streamRoot = "getimage.aspx?report=" & reportPath & "&streamid="
Select Case format
Case "HTML4.0", "HTML3.2"
deviceInfo = "<DeviceInfo>"
deviceInfo &= "<StreamRoot>" & streamRoot & </StreamRoot>"
deviceInfo &= "<Toolbar>False</Toolbar>"
deviceInfo &= "<Parameters>False</Parameters>"
deviceInfo &= "<HTMLFragment>True</HTMLFragment>"
deviceInfo &= "<StyleStream>False</StyleStream>"
deviceInfo &= "<Section>0</Section>"
deviceInfo &= "<Zoom>" & zoom & "</Zoom>"
deviceInfo &= "</DeviceInfo>"
Case Else
deviceInfo = "<DeviceInfo></DeviceInfo>"
End Select
And here is how we render and display the report:
Dim credentials As DataSourceCredentials() = Nothing
Dim showHideToggle As String = Nothing
Dim encoding As String
Dim mimeType As String
Dim warnings As Warning() = Nothing
Dim reportHistoryParameters As ParameterValue() = Nothing
Dim historyID As String = Nothing
Dim streamIDs As String() = Nothing
Dim sh As New SessionHeader
rs.SessionHeaderValue = sh
Dim result As Byte() = Nothing
'prepare report parameters array.
Dim reportParameterValues2 As ParameterValue() = Nothing
If Not reportParameterValues Is Nothing Then
reportParameterValues2 =
reportParameterValues.ToArray(GetType(ParameterValue))
End If
result = rs.Render(reportPath, format, historyID, deviceInfo, _
reportParameterValues2, credentials, showHideToggle, _
encoding, mimeType, reportHistoryParameters, warnings, _
streamIDs)
'store ReportingServices and parameters object is session
'layer in case we need it for image streams
Session("rs") = rs
Session("reportParameterValues") = reportParameterValues2
Select Case format
Case "HTML4.0", "HTML3.2"
Dim enc As System.Text.Encoding = System.Text.Encoding.UTF8
'get the report as a string
Dim tmpReport As String = enc.GetString(result)
'replace all occurrences of report server link with
'current page link
tmpReport =
tmpReport.Replace(reportServerURL.Replace("/ReportService.asmx", "?"),_
"http://" & Request("SERVER_NAME") _
& Request("SCRIPT_NAME") & "?Report=")
ReportPlaceholder.InnerHtml = tmpReport
Case Else
Response.ClearContent()
Response.AppendHeader("content-length", _
result.Length.ToString())
Response.ContentType = mimeType
Response.BinaryWrite(result)
Response.Flush()
Response.Close()
End Select
Rendering Images
By specifying StreamRoot
parameter of DeviceInfo
to point to GetImage.aspx, the report will have all images referencing that page. We pass report
and streamId
as URL parameters. For everything else, we can use the session layer. Here is the code behind GetImage.aspx:
Private Sub Page_Load(_
ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles MyBase.Load
Dim reportPath As String = Server.UrlDecode(Request("report"))
Dim streamID As String = Request("streamid")
Dim rs As ReportingService = Session("rs")
Dim encodingImage As String
Dim mimeTypeImage As String
Dim image As Byte()
image =
rs.RenderStream(reportPath, "HTML4.0", streamID, _
Nothing, Nothing, Session("reportParameterValues"), _
encodingImage, mimeTypeImage)
Response.Clear()
Response.ContentType = mimeTypeImage
Response.AppendHeader("content-length", image.Length.ToString())
Response.BinaryWrite(image)
Response.Flush()
Response.Close()
End Sub
Using the Code
To use this code as it is, you should install it as an ASP.NET application somewhere on your web server. Then modify the following variables found in the code behind:
Private reportServerURL As String =
"http://yourServer/ReportServer/ReportService.asmx"
Private reportPath As String = "/SampleReports/Product Line Sales"
Private reportUser As String = "user"
Private userPassword As String = "password"
Private domain As String = "DOMAIN"
The user specified in reportUser
should have rights to access reports on the remote reporting server. The report can be also specified as a URL parameter, together with any other report parameter:
http://localhost/SQLRS-ReportViewer/ReportViewer.aspx?
Report=%2FSampleReports%2FProduct+Line+Sales& _
ProductCategory=4&ProductSubCategory=26
To use this code in other applications:
- copy the whole logic from
Page_Load
into your page or user control. - add ReportingService.vb as an existing item to your project.
- add GetImage.aspx page to your project and be sure that
StreamRoot
indeviceInfo
structure is pointing to it. - import or modify styles from Styles.css
Key Points
I consider the most interesting point to be the way we can rebuild parameter prompts. This opens the door for customizing these prompts, for example, set a Calendar
control for a DateTime
parameter, hide some report parameters, or provide values behind the scene. I currently use it for the last reason where I have a report with a few parameters and one of them is a User ID. I evidently don't want the user to get prompted for that ID, so when I build the prompts, I loop over all the parameters except UserID
. I still specify the value for it when I build reportParameterValues
.
Known Limitations
So far the code does not perform validation. This means that if you submit a blank value for a field that does not accept blank values, the code will not show any validation message. Another limitation at this time is that formats other than HTML is rendered in the same page instead of a separate instance of Internet Explorer.
Conclusion
SQL Reporting Services can be used to build reports and display them in a completely separate application. With a little more effort, parameter prompts can be customized as much as you want.
History
- 02/21/2005
- Fixed a bug regarding reports without parameters.
- 02/07/2005
- Added support for dependent parameters
- Changed the way images and all other streams are handled. Thanks to Bart Fibrich.
- 01/27/2005
- Added support for drill down links, alternative for
ReplacementRoot
device setting.
- Added support for drill down links, alternative for
- 12/27/2004
- Initial release.