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