Click here to Skip to main content
Click here to Skip to main content
Add your own
alternative version

SQL Reporting Services Viewer Page Using SOAP API

, 21 Feb 2005
A report viewer page for SQL Reporting Services that uses exclusively its SOAP API.
Public Class ReportViewer
    Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

    End Sub
    Protected WithEvents TablePrompts As System.Web.UI.HtmlControls.HtmlTable

    Private reportServerURL As String = "http://server/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"


    Private format As String = "HTML4.0"
    Private zoom As String = "100"


    Public reportParametersArray As ReportParameter() = Nothing
    Public values As ParameterValue() = Nothing


    ' this is a flag that will help deciding if we'll render the report
    Public reportCanBeRendered As Boolean = True

    Protected WithEvents ReportPlaceholder As System.Web.UI.HtmlControls.HtmlGenericControl
    Protected WithEvents Report As System.Web.UI.HtmlControls.HtmlInputHidden



    'NOTE: The following placeholder declaration is required by the Web Form Designer.
    'Do not delete or move it.
    Private designerPlaceholderDeclaration As System.Object

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub

#End Region

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load



        Dim rs As New ReportingService
        rs.Url = ReportServerURL

        'Set the report credentials. We use impersonation to access the report. 
        Dim reportCredentials As New System.Net.NetworkCredential(ReportUser, UserPassword, Domain)
        rs.Credentials = reportCredentials

        'see if Report URL parameter is defined and if it is then consider that report
        If Not Request("Report") Is Nothing Then
            reportPath = Request("Report")
        End If

        'set the Report hidden parameter
        Report.Value = reportPath

        'Get the list of parameters. without specifing to render the report (ForRendering set to false) 
        'valid values for these parameters will not be populated 
        reportParametersArray = rs.GetReportParameters(reportPath, Nothing, False, Nothing, Nothing)

        'If there are parametrs then we generate an array with values that should be populated from either
        '    - previous submission , 
        '    - URL parameters, 
        '    - default value defined inside the report


        'some usefull declarations
        Dim TR As System.Web.UI.HtmlControls.HtmlTableRow
        Dim TD As System.Web.UI.HtmlControls.HtmlTableCell
        Dim S As System.Web.UI.HtmlControls.HtmlSelect
        Dim LI As System.Web.UI.WebControls.ListItem
        Dim T As System.Web.UI.HtmlControls.HtmlInputText

        '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)

            'loop again over parameters and build the HTML interface for prompts
            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 'from: If Parameters.Length > 0 Then


        'add the zoom prompt
        If Not Request("Zoom") Is Nothing Then zoom = Request("Zoom")

        TR = New System.Web.UI.HtmlControls.HtmlTableRow
        TD = New System.Web.UI.HtmlControls.HtmlTableCell
        TD.InnerHtml = "Zoom:"
        TR.Controls.Add(TD)

        TD = New System.Web.UI.HtmlControls.HtmlTableCell
        S = New System.Web.UI.HtmlControls.HtmlSelect
        S.ID = "Zoom"
        S.Name = "Zoom"
        LI = New System.Web.UI.WebControls.ListItem("Page Width", "Page Width")
        If zoom = LI.Value Then LI.Selected = True
        S.Items.Add(LI)

        LI = New System.Web.UI.WebControls.ListItem("200%", "200")
        If zoom = LI.Value Then LI.Selected = True
        S.Items.Add(LI)

        LI = New System.Web.UI.WebControls.ListItem("150%", "150")
        If zoom = LI.Value Then LI.Selected = True
        S.Items.Add(LI)

        LI = New System.Web.UI.WebControls.ListItem("100%", "100")
        If zoom = LI.Value Then LI.Selected = True
        S.Items.Add(LI)

        LI = New System.Web.UI.WebControls.ListItem("75%", "75")
        If zoom = LI.Value Then LI.Selected = True
        S.Items.Add(LI)

        LI = New System.Web.UI.WebControls.ListItem("50%", "50")
        If zoom = LI.Value Then LI.Selected = True
        S.Items.Add(LI)

        LI = New System.Web.UI.WebControls.ListItem("25%", "25")
        If zoom = LI.Value Then LI.Selected = True
        S.Items.Add(LI)
        TD.Controls.Add(S)
        TR.Controls.Add(TD)

        TD = New System.Web.UI.HtmlControls.HtmlTableCell
        TR.Controls.Add(TD)


        'add the format prompt
        If Not Request("Format") Is Nothing Then format = Request("Format")

        TD = New System.Web.UI.HtmlControls.HtmlTableCell
        S = New System.Web.UI.HtmlControls.HtmlSelect
        S.ID = "Format"
        S.Name = "Format"
        LI = New System.Web.UI.WebControls.ListItem("HTML", "HTML4.0")
        If format = LI.Value Then LI.Selected = True
        S.Items.Add(LI)

        LI = New System.Web.UI.WebControls.ListItem("Acrobat (PDF) file", "PDF")
        If format = LI.Value Then LI.Selected = True
        S.Items.Add(LI)

        LI = New System.Web.UI.WebControls.ListItem("Excel", "EXCEL")
        If format = LI.Value Then LI.Selected = True
        S.Items.Add(LI)

        LI = New System.Web.UI.WebControls.ListItem("TIFF file", "IMAGE")
        If format = LI.Value Then LI.Selected = True
        S.Items.Add(LI)

        LI = New System.Web.UI.WebControls.ListItem("Web archive", "MHTML")
        If format = LI.Value Then LI.Selected = True
        S.Items.Add(LI)

        LI = New System.Web.UI.WebControls.ListItem("CSV (comma delimited)", "CSV")
        If format = LI.Value Then LI.Selected = True
        S.Items.Add(LI)

        LI = New System.Web.UI.WebControls.ListItem("XML file with report data", "XML")
        If format = LI.Value Then LI.Selected = True
        S.Items.Add(LI)

        TD.Controls.Add(S)
        TR.Controls.Add(TD)

        TablePrompts.Controls.Add(TR)

        'add the submit button
        TR = New System.Web.UI.HtmlControls.HtmlTableRow
        TD = New System.Web.UI.HtmlControls.HtmlTableCell
        Dim Button As System.Web.UI.HtmlControls.HtmlInputButton
        Button = New System.Web.UI.HtmlControls.HtmlInputButton("submit")
        Button.Value = "Generate Report"
        Button.EnableViewState = False
        TR.Controls.Add(TD)
        TD = New System.Web.UI.HtmlControls.HtmlTableCell

        Button.Value = "Generate Report"
        TD.Controls.Add(Button)
        TR.Controls.Add(TD)

        TablePrompts.Controls.Add(TR)

        If reportCanBeRendered Then
            Dim deviceInfo As String
            Dim streamRoot As String
            streamRoot = "getimage.aspx?report=" & reportPath & "&amp;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>"
                    'this is not working:
                    'deviceInfo &= "<ReplacementRoot>" & "http://" & Request("SERVER_NAME") & Request("SCRIPT_NAME") & "?Report=" & "</ReplacementRoot>"
                    deviceInfo &= "</DeviceInfo>"
                Case Else
                    deviceInfo = "<DeviceInfo></DeviceInfo>"
            End Select


            'SQLRS-ReportViewer

            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

        End If 'from: If ReportCanBeRendered Then

    End Sub

End Class

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Marian Dumitrascu
Architect Data Quadrant, Inc.
United States United States
Seek, create and combine solutions in the realm of Information Portals, Business Inteligence and Business Automation. With a focus on SharePoint. (Houston, TX)

| Advertise | Privacy | Mobile
Web01 | 2.8.140821.2 | Last Updated 21 Feb 2005
Article Copyright 2004 by Marian Dumitrascu
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid