Contents
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.
- 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.
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.
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>
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.
Dim Dependencies As New System.Collections.Hashtable
Dim reportParameterValues As ArrayList
If reportParametersArray.Length > 0 Then
reportParameterValues = New ArrayList
For Each parameter As ReportParameter In reportParametersArray
Dim value As ParameterValue = New ParameterValue
value.Name = parameter.Name
value.Label = parameter.Name
If Not Request(Parameter.Name) Is Nothing Then
value.Value = Request(Parameter.Name)
ElseIf Not Parameter.DefaultValues Is Nothing Then
value.Value = Parameter.DefaultValues(0)
Else
value.Value = ""
reportCanBeRendered = False
End If
If value.Value <> "" Then
reportParameterValues.Add(value)
End If
If Not (parameter.Dependencies Is Nothing) Then
For Each d As String In parameter.Dependencies
If Not Dependencies.Contains(d) Then
Dependencies.Add(d, Nothing)
End If
Next
End If
Next
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 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
TD = New System.Web.UI.HtmlControls.HtmlTableCell
TD.InnerHtml = Parameter.Prompt
TR.Controls.Add(TD)
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"
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)
If vv.Value = parameter.DefaultValues(0) And Parameter.State =
ParameterStateEnum.HasValidValue Then
LI.Selected = True
End If
S.Items.Add(LI)
Next
End If
If Dependencies.Contains(Parameter.Name) Then
S.Attributes.Add("OnChange", "this.form.submit();return true")
End If
TD.Controls.Add(S)
Case "textbox"
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)
If i Mod 2 = 1 Then
TablePrompts.Controls.Add(TR)
End If
i += 1
Next
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
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)
Session("rs") = rs
Session("reportParameterValues") = reportParameterValues2
Select Case format
Case "HTML4.0", "HTML3.2"
Dim enc As System.Text.Encoding = System.Text.Encoding.UTF8
Dim tmpReport As String = enc.GetString(result)
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
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
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 in deviceInfo structure is pointing to it.
- import or modify styles from Styles.css
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.
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.
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.
- 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.
- 12/27/2004
