Working with ASP.NET and Crystal Reports 9 with or without RAS 9
4.36/5 (20 votes)
Jun 8, 2005
3 min read
151314
An article on how to use Crystal Reports 9 with ASP.NET.
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\.UserIDandPassword- 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,UserIDandPassword- basically your connection string.
Arguments:
sReport- report name;arParams- parameters expected by the report;DoParams-Falseif 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.