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:proectsproject1reports
  • 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

More by Author

Must Read