SQL Reporting Services Viewer Page Using SOAP API

Marian Dumitrascu

4.45/5 (37 votes)

Dec 27, 2004

5 min read

775074

2230

A report viewer page for SQL Reporting Services that uses exclusively its SOAP API.

Download source - 46.7 Kb

SQLRS - ReportViewer

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 in deviceInfo 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.
12/27/2004
- Initial release.

SQL Reporting Services Viewer Page Using SOAP API

Contents