Working with ASP.NET and Crystal Reports 9 with or without RAS 9

Introduction

Every one knows the problems which one faces while working with Crystal Reports due to its poor and confusing documentation. I am presenting here a ready to go code to access reports developed in Crystal Reports 9 (with or without RAS 9) using ASP.NET. This article consists of two parts: first part presents the code if the reports are placed under a RAS server, while the second part shows how to do the same job if the reports are kept without any RAS server.

1. Showing Crystal Reports from RAS 9 in PDF format using ASP.NET

This is the code which presents how to show Crystal Reports placed under a RAS 9 server using ASP.NET. The final report will be shown in PDF format. You can change the output format. I have clearly marked the place in the code where you can make modifications to change the output format of the report.

Setup

Just put the following parameters in the appsettings of your web.confog file:

• ServerName - machine on which your SQL Server resides, e.g.: machine_sqlsvr2000_1.
• DatabaseName - database name on your SQL Server, e.g.: northwind.
• RASServer - name of the server which has the reports, e.g., machine_reportserver_9_1.
• ReportPath - path on the RAS server where the reports are kept, e.g., c:\proects\project1\reports\.
• UserID and Password - user name and password for your database.

Then, you need to put this method on the ASP.NET page which will be used to call the reports.

Public Sub CreateReport()

   '************************** Mention Report Name *****************************
    Dim m_RptName As String = "report1.rpt"  
    ' you can make it dynamic, I am just hard coding here
   '****************************************************************************

        Dim _db_server As String = ConfigurationSettings.AppSettings("ServerName")
        Dim _db_name As String = ConfigurationSettings.AppSettings("DatabaseName")
        Dim _ras_server As String = ConfigurationSettings.AppSettings("RASServer")
        Dim _file_name As String = _
            ConfigurationSettings.AppSettings("ReportPath") & m_RptName
        Dim _user_id As String = ConfigurationSettings.AppSettings("UserID")
        Dim _pwd As String = ConfigurationSettings.AppSettings("Password")

        Dim oRptViewer As New RASReportViewer
        Dim rcDoc As ReportClientDocument

        rcDoc = New ReportClientDocument
        rcDoc.ReportAppServer = _ras_server
        rcDoc.Open(_file_name)

        ' logon using Logon helper function below
        oRptViewer.Logon(rcDoc, _user_id, _pwd, _db_server, _db_name)

        '****************** pass your parameters here *****************

        ' you will change this portion according
        ' to the parametrs that your report expects
        ' parameters
        oRptViewer.PassParameter(rcDoc, "", 0, Session("CategoryString"))
        oRptViewer.PassParameter(rcDoc, "", 1, Session("Terms"))
        oRptViewer.PassParameter(rcDoc, "", 2, Session("Division"))
        oRptViewer.PassParameter(rcDoc, "", 3, Session("Location"))

        '**************************************************************

        Dim byteArray As ByteArray = _
          rcDoc.PrintOutputController.Export(_
          CrReportExportFormatEnum.crReportExportFormatPDF)
        Response.ClearContent()
        Response.ClearHeaders()
        Response.ContentType = "application/pdf"
        Response.BinaryWrite(byteArray.DetachArray)
        Response.Flush()
        Response.Close()

        oRptViewer.Dispose()
        If Not oRptViewer Is Nothing Then
            oRptViewer = Nothing
        End If

    End Sub

You can make changes in the above method if you want to show your reports in some format other than PDF. You will have to change the following lines only:

Response.ClearContent()
Response.ClearHeaders()
Response.ContentType = "application/pdf"
Response.BinaryWrite(byteArray.DetachArray)
Response.Flush()

RASReportViewer Class

Here is the class that the above method is using. You can just use it as it is. I think you will not need to make any modifications at all in this class. I have developed it as a component class. If you want, you can change it into a simple class too.

Imports CrystalDecisions.ReportAppServer.ClientDoc
Imports CrystalDecisions.ReportAppServer.Controllers
Imports CrystalDecisions.ReportAppServer.DataDefModel
Imports CrystalDecisions.ReportAppServer.CommonObjectModel

Public Class RASReportViewer
    Inherits System.ComponentModel.Component

    ' CR variables
    Dim rcDoc As ReportClientDocument
    Dim m_last_error As String

#Region " Component Designer generated code "
     Public Sub New(ByVal Container As System.ComponentModel.IContainer)
        MyClass.New()

        'Required for Windows.Forms Class Composition Designer support
        Container.Add(Me)
    End Sub

    Public Sub New()
        MyBase.New()

        'This call is required by the Component Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub
    ' passes a parameter value to report
    '
    ' PARAMETERS    [in]    doc         RAS Report Client Document
    '               [in]    report_name subreport name 
    '                       (pass empty string for main report)
    '               [in]    param_name  report parameter name
    '               [in]    param_value report parmaeter value
    Public Sub PassParameter( _
            ByRef doc As ReportClientDocument, _
            ByVal report_name As String, _
            ByVal param_name As String, _
            ByVal param_value As Object)

        ' create parameter discrete value
        Dim param_val As New ParameterFieldDiscreteValue

        ' set parameter value
        param_val.Value = param_value

        ' create parameter value collection
        Dim vals As New Values

        ' add parameter value to this collection
        vals.Add(param_val)

        ' set current value
        doc.DataDefController.ParameterFieldController._
           SetCurrentValues(report_name, param_name, vals)
    End Sub

    ' identical to method above except this method
    ' takes index of the parameter instead of name
    Public Sub PassParameter( _
            ByVal doc As ReportClientDocument, _
            ByVal report_name As String, _
            ByVal param_index As Integer, _
            ByVal param_value As Object)

        PassParameter(doc, report_name, _
           doc.DataDefinition.ParameterFields(param_index).Name, _
           param_value)
    End Sub
   
    ' Reusable ApplyLogon method. This method takes RAS database object,
    ' ConnectionInfo, and report name, and applies this connection info to
    ' all tables in the database. This method is used by Logon method below.
    ' Normally you do not have to call this
    ' method directly, but simply use Logon method.
    '
    ' Parameters [in]  doc         RAS report client document
    '            [in]  database    RAS database (contains collecion of tables)
    '            [in]  ci          ConnectionInfo that contains SQL credentials
    '            [in]  report_name subreport name (empty for main)
    Private Function ApplyLogon( _
            ByVal doc As ReportClientDocument, _
            ByVal database As Database, _
            ByVal ci As ConnectionInfo, _
            ByVal report_name As String) As Boolean

        ' loop through each table in database tables collection
        Dim table_old As _
          CrystalDecisions.ReportAppServer.DataDefModel.Table

        For Each table_old In database.Tables
            ' now create a new table object
            Dim table_new As New _
              CrystalDecisions.ReportAppServer.DataDefModel.Table

            ' set new table name
            table_new.Name = table_old.Name

            ' set new connection info
            table_new.ConnectionInfo = ci

            ' set qualified name. Connection Info object
            ' simply contains logon credentials
            ' that tells RAS how to connect to database.
            ' But this information is not sufficient
            ' for RAS to find the table/stored proc.
            ' For instance, in Northwind database, you
            ' can create two tables with same name as follows:
            '  Northwind.dbo.Customers
            '  Northwind.gabe.Customers
            ' The syntax is database.owner.table/stored proc.
            ' This information is saved
            ' in Table.QualifiedName property.
            ' If you are not changing database name or owner,
            ' then you can leave this property alone,
            ' but if you are changing them, you must
            ' set them. In another words, you created
            ' a reprot off Northwind.dbo.Customers, and
            ' wish to change this to Northwind2.dbo.Customers
            ' at runtime, you must set this
            ' QualifiedName property.
            Dim qualified_name As String = table_old.QualifiedName

            ' now strip off database name from qualified name
            qualified_name = _
              qualified_name.Substring(qualified_name.IndexOf("."))

            ' at this point, qualified_name will look like .owner.table
            ' now add new database name.
            ' NOTE that we are assuming that
            ' this new database name is in ConnectionInfo
            ' property.
            qualified_name = CType(ci.Attributes("QE_LogonProperties"), _
               PropertyBag)("Initial Catalog").ToString() + qualified_name
            table_new.QualifiedName = qualified_name

            ' now apply the changes
            Try
                If (report_name.Length > 0) Then
                    ' this is a subreport
                    doc.SubreportController.SetTableLocation(report_name,_
                                                     table_old, table_new)
                Else
                    ' this is a main report
                    doc.DatabaseController.SetTableLocation(table_old,_
                                                              table_new)
                End If
            Catch x As Exception
                m_last_error = x.Message
                Return (False)
            End Try
        Next
        Return (True)
    End Function

    ' Reusable Logon method. This method takes RAS report client document, and
    ' SQL logon credentials. This sample is designed for Microsoft SQL Server
    ' using SQL authentication. You will need to change this code a little to
    ' make it to work with ODBC DSN or other data source
    '
    ' PARAMETERS        [in]    doc         RAS report client document
    '                   [in]    uid         database user id
    '                   [in]    pass        database password
    '                   [in]    server      database server name
    '                   [in]    database    database (catalog) name
    Public Function Logon( _
    ByVal doc As ReportClientDocument, _
    ByVal uid As String, _
    ByVal pass As String, _
    ByVal server As String, _
    ByVal database As String) As Boolean
        ' grab existing connection information from main report, and clone it
        Dim ci As ConnectionInfo = _
          doc.DatabaseController.GetConnectionInfos(Nothing)(0).Clone(True)

        ' set new user id and password
        ci.UserName = uid
        ci.Password = pass

        ' grab logon properties bag (this property
        ' bag contains your server and database name)
        Dim li As PropertyBag = ci.Attributes("QE_LogonProperties")

        ' set new server name; note that we are
        ' setting "Data Source" property. if you are
        ' using ODBC DSN, you must set "DSN" property and pass your DSN name here.
        li("Data Source") = server

        ' set database (catalog) name; note that
        ' we are setting "Initial Catalog" property.
        ' if you are using ODBC DSN, you must set
        ' "Database" property and pass your database
        ' name here.
        li("Initial Catalog") = database

        ' optionally you can set other properties here.
        ' for instance, if you created a report
        ' using Integrated Security, and wish
        ' to pass SQL logon credentials (because RAS
        ' service cannot impersonate), you can set
        ' this property to false as follows:
        ' li["Integrated Security"] = false;

        ' now our 'new' connection info object
        ' is ready to be passed to the report

        ' pass this connection info to all tables in the main report
        ' NOTE: main report has empty report name
        If (Not ApplyLogon(doc, doc.Database, ci, "")) Then
            Return (False)
        End If

        ' now loop through each subreport and pass this connection info
        Dim sub_name As String
        For Each sub_name In doc.SubreportController.QuerySubreportNames()
            If (Not ApplyLogon(doc, _
                doc.SubreportController.GetSubreportDatabase(sub_name), _
                ci, sub_name)) Then
                     Return (False)
            End If
        Next
        Return (True)
    End Function

    'Component overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Component Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Component Designer
    'It can be modified using the Component Designer.
    'Do not modify it using the code editor.
    <System.Diagnostics.DebuggerStepThrough()>
    Private Sub InitializeComponent()
        components = New System.ComponentModel.Container
    End Sub

#End Region

End Class

2. Class to show Crystal Reports 9 reports in PDF format without RAS

Most of the time, the reports are not kept on a RAS server. Instead they are just kept in another folder. In that case, you can use this class to access reports developed using Crystal Reports 9 through your ASP.NET pages. You will just have to pass the parameters to the CreateReport method of this class and that's it. All the subsequent stuff is taken care of by this code. You will get a nice report in PDF at the end. Just like the previous class, you can modify this class too to get the report in some other format.

Setup

You just have to set up a few parameters in your web.config file which are:

• ReportPath - the path of the report that you want to show;
• ServerName , DatabaseName, UserID and Password - basically your connection string.

Arguments:

• sReport - report name;
• arParams - parameters expected by the report;
• DoParams - False if the report has no parameters.

Here is the class

Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Imports CrystalDecisions.Web.Design
Imports System.IO


Public Class ShowCRInPDF
    Public Sub CreateReport(ByVal sReport As String, ByVal _
               arParams As Array, Optional ByVal _
               DoParams As Boolean = True)
        Dim oRpt As New ReportDocument
        Dim oSubRpt As New ReportDocument
        Dim Counter As Integer
        Dim crSections As Sections
        Dim crSection As Section
        Dim crReportObjects As ReportObjects
        Dim crReportObject As ReportObject
        Dim crSubreportObject As SubreportObject
        Dim crDatabase As Database
        Dim crTables As Tables
        Dim crTable As Table
        Dim crLogOnInfo As TableLogOnInfo
        Dim crConnInfo As New ConnectionInfo
        Dim crParameterValues As ParameterValues
        Dim crParameterDiscreteValue As ParameterDiscreteValue
        Dim crParameterRangeValue As ParameterRangeValue
        Dim crParameterFieldDefinitions As ParameterFieldDefinitions
        Dim crParameterFieldDefinition As ParameterFieldDefinition
        Dim crParameterFieldDefinition2 As ParameterFieldDefinition
        Dim strFile As String
        Dim fi As FileInfo
        Dim tstr As String
        Dim sPath As String
        Dim configurationAppSettings As _
            System.Configuration.AppSettingsReader = _
            New System.Configuration.AppSettingsReader

        Dim sReportPath As String = _
            configurationAppSettings.GetValue("ReportPath", _
            GetType(System.String)) & sReport
        Dim pos As Integer
        'Try
        tstr = Microsoft.VisualBasic.Format(Now, "MM/dd/yyyy HH:mm:ss")
        'load report 
        oRpt.Load(sReportPath)
        'log on to SQL server 
        'Report code starts here
        'Set the database and the tables objects to the main report 'oRpt'
        crDatabase = oRpt.Database
        crTables = crDatabase.Tables
        'Loop through each table and set the connection info 
        'Pess the connection info to the logoninfo object then apply the 
        'logoninfo to the main report
        For Each crTable In crTables
            With crConnInfo
                .ServerName = _
                  configurationAppSettings.GetValue("ServerName", _
                  GetType(System.String))
                .DatabaseName = _
                  configurationAppSettings.GetValue("DatabaseName", _
                  GetType(System.String))
                .UserID = _
                  configurationAppSettings.GetValue("UserID", _
                  GetType(System.String))
                .Password = _
                  configurationAppSettings.GetValue("Password", _
                  GetType(System.String))
            End With
            crLogOnInfo = crTable.LogOnInfo
            crLogOnInfo.ConnectionInfo = crConnInfo
            crTable.ApplyLogOnInfo(crLogOnInfo)
        Next
        'Set the sections collection with report sections
        crSections = oRpt.ReportDefinition.Sections
        'Loop through each section and find all the report objects
        'Loop through all the report objects 
        'to find all subreport objects, then set the 
        'logoninfo to the subreport
        For Each crSection In crSections
            crReportObjects = crSection.ReportObjects
            For Each crReportObject In crReportObjects
                If crReportObject.Kind = ReportObjectKind.SubreportObject Then
                    'If you find a subreport, typecast 
                    'the reportobject to a subreport object
                    crSubreportObject = CType(crReportObject, SubreportObject)
                    'Open the subreport
                    oSubRpt = _
                      crSubreportObject.OpenSubreport(_
                      crSubreportObject.SubreportName)
                    crDatabase = oSubRpt.Database
                    crTables = crDatabase.Tables
                    'Loop through each table and set the connection info 
                    'Pess the connection info 
                    'to the logoninfo object then apply the 
                    'logoninfo to the subreport
                    For Each crTable In crTables
                        With crConnInfo
                           .ServerName = _
                             configurationAppSettings.GetValue("ServerName", _
                             GetType(System.String)) 
                           .UserID = _
                             configurationAppSettings.GetValue("UserID", _
                             GetType(System.String))
                           .Password = _
                             configurationAppSettings.GetValue("Password", _
                             GetType(System.String))
                        End With
                        crLogOnInfo = crTable.LogOnInfo
                        crLogOnInfo.ConnectionInfo = crConnInfo
                        crTable.ApplyLogOnInfo(crLogOnInfo)
                    Next
                End If
            Next
        Next
        ' Set the parameters
        If DoParams Then
            'Get the collection of parameters from the report
            crParameterFieldDefinitions = oRpt.DataDefinition.ParameterFields()
            For Counter = 0 To UBound(arParams)
                crParameterFieldDefinition = _
                  crParameterFieldDefinitions.Item(Counter)
                ' Response.Write(crParameterFieldDefinition.ParameterFieldName_
                '                                                  & "<br>")
                'Get the current values from the parameter field.
                crParameterValues = crParameterFieldDefinition.CurrentValues
                If Not IsArray(arParams(Counter)) Then
                    'Test if param passed in matches CR param recieving
                    Dim test As String
                    Dim paramcounter As Integer
                    For paramcounter = 0 To UBound(arParams)
                        ' Response.Write(arParams(paramcounter))
                        test = arParams(paramcounter)
                        'Grabs param from CR and compares against param passed in
                        If test.StartsWith(_
                          crParameterFieldDefinition.ParameterFieldName()) Then
                            'arParams(Counter) = _
                            '   test.Substring(test.IndexOf("=") + 1)
                            'Set the current values for the parameter field 0
                            crParameterDiscreteValue = New ParameterDiscreteValue
                            crParameterDiscreteValue.Value = _
                              test.Substring(test.IndexOf("=") + 1)
                            'Add the first current value for the parameter field
                            crParameterValues.Add(crParameterDiscreteValue)
                            'Response.Write(crParameterFieldDefinition.
                            '    ParameterFieldName() & "- " & _
                            '    crParameterDiscreteValue.Value)
                            'Response.Flush()
                            Exit For
                        End If
                    Next
                Else
                    crParameterRangeValue = New ParameterRangeValue
                    crParameterRangeValue.StartValue = arParams(Counter)(0)
                    crParameterRangeValue.EndValue = arParams(Counter)(1)
                    crParameterValues.Add(crParameterRangeValue)
                End If
                'All current parameter values 
                'must be applied for the parameter field.
                crParameterFieldDefinition.ApplyCurrentValues(crParameterValues)
            Next
        End If
        Dim s As System.IO.MemoryStream = _
          oRpt.ExportToStream(ExportFormatType.PortableDocFormat)
        ' the code below will create pdfs
        ' in memory and stream them to the browser
        ' instead of creating files on disk.
        With HttpContext.Current.Response
            .ClearContent()
            .ClearHeaders()
            .ContentType = "application/pdf"
            .AddHeader("Content-Disposition", "inline; filename=Report.pdf")
            .BinaryWrite(s.ToArray)
            .End()
        End With
        'Catch ex As System.Exception
        'Finally
        '    Erase arParams
        'End Try
    End Sub
End Class

Reference: Crystal Reports and RAS 9.0 Documentation and Samples from www.businessobjects.com.