Working with ASP.Net and Crystal Reports 9 with and Without RAS 9 Server

Everyone knows the problems that one faces when working with Crystal Reports due to its poor and confusing documentation. Here, I presenting 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: The first part presents the code if the reports are placed under a RAS server and 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

Introduction

This is the code that 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 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.config file:

  • ServerName—machine on which your SQL Server resides; for example, machine_sqlsvr2000_1
  • DatabaseName—database name on your SQL Server; for example, northwind
  • RASServer—name of the server which has the reports; for example, machine_reportserver_9_1
  • ReportPath—path on the RASServer where the reports are kept; for example, c:\proects\project1\reports\
  • UserID and Pasword—user name and pasword for the your database

First, you need to put this method on the asp.net page that will be used to call the reports.

Public Sub CreateReport()

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

   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 parameters
   ' 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("HDivision"))
   oRptViewer.PassParameter(rcDoc, "", 4, Session("Curriculum"))
   oRptViewer.PassParameter(rcDoc, "", 5, Session("Category"))
   oRptViewer.PassParameter(rcDoc, "", 6, Session("FTPT"))
   oRptViewer.PassParameter(rcDoc, "", 7, Session("Active"))
   oRptViewer.PassParameter(rcDoc, "", 8, 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 to show your reports in some format other than PDF. You will have to change only the following lines:

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

RASReportViewer class

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

****************************************************************
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.SetCurrent _
      Values(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 a 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 the 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 tell RAS how to connect to the
      ' database. But, this information is not sufficient for
      ' RAS to find the table/stored proc. For instance, in the
      ' Northwind database, you can create two tables with the
      ' same name as follows:
      '   Northwind.dbo.Customers
      '   Northwind.gabe.Customers
      ' The syntax is <database>.<owner>.<table/stored proc>. This
      ' information is saved in the Table.QualifiedName property.
      ' If you are not changing database name or owner,
      ' you can leave this property alone, but if you are
      ' changing them, you must set them. In other words, if you
      ' created a report 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, the qualified_name will look like
      ' ".<owner>.<table>"
      ' now add new database name.
      ' NOTE that we are assuming that this new database name is
      ' in the 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 a 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 the
      ' property. if you are using ODBC DSN, you must set the
      ' "DSN" property and pass your DSN name here.
      li("Data Source") = server

      ' set database (catalog) name; note that we are setting the
      ' "Initial Catalog" property.
      ' if you are using ODBC DSN, you must set the "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

Working with ASP.Net and Crystal Reports 9 with and Without RAS 9 Server

2. Class to Show Crystal Reports 9 Reports in PDF Format Without RAS

Introduction

You can use this class as it is to access reports developed using Crystal Reports 9 through your ASP.NET pages. You will just pass the parameters to the CreateReport Method and that's it. All the subsequent stuff is taken care of. You will get a nice report in a PDF format at the end.

Setup

You just have to set up few parameters in your web.config file; these are 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
*****************************************************************

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
      'Pass the connection info to the logoninfo object and then
      ' apply the logon info 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 receiving
            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

Here, also, you can modify the code easily to display the report in your own desired format.

Reference

Crystal Reports Documentation and Samples from http://www.businessobjects.com.



About the Author

Muhammad Musa Ali

MCAD, MCSD - Early Achiever in .Net, has more than seven years of experience in software development and system analysis, currently working as a consultant for a fortune 500 company in Chicago.

Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • This paper introduces IBM Java on the IBM PowerLinux 7R2 server and describes IBM's implementation of the Java platform, which includes IBM's Java Virtual Machine and development toolkit.

  • The explosion in mobile devices and applications has generated a great deal of interest in APIs. Today's businesses are under increased pressure to make it easy to build apps, supply tools to help developers work more quickly, and deploy operational analytics so they can track users, developers, application performance, and more. Apigee Edge provides comprehensive API delivery tools and both operational and business-level analytics in an integrated platform. It is available as on-premise software or through …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds