Member Avatar for Robins Antony

Hi all!

How to use crystal report with ASP.net?

How to choose database?

pls help with an eg.

Robins

  • 2 Contributors
  • 1 Reply
  • 525 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by Kusno

Dani AI

Generated

Good, practical follow-up to help get moving and to tighten up ’s approach.

Crystal Reports in ASP.NET works two main ways: let the .rpt connect directly to the database (simpler, designer-driven) or run your queries in code and bind an ADO.NET object (DataSet/DataTable) to the report at runtime (more flexible). For production I usually recommend pushing a typed DataSet to the report because it avoids owner/schema mismatches, works reliably with views and stored procedures, and gives you full control over credentials and paging.

A few concrete gaps to watch for in ’s pattern: subreports must receive the same logon info as the main report (apply logon to each subreport table), and reports that reference views can fail if the runtime user/schema differs from the designer environment. To avoid those headaches either (a) call ReportDocument.SetDataSource(myDataSet) and bind a DataTable you control, or (b) make sure the report uses fully qualified object names and that the runtime DB user has the same permissions. Also prefer passing runtime values as report parameters (using SetParameterValue) rather than changing formula text at runtime — parameters are clearer and refresh-safe.

Deployment and reliability tips: install the Crystal runtime on the server that exactly matches the designer version; mismatch is a common cause of runtime errors. Always call ReportDocument.Close() and ReportDocument.Dispose() (page Unload or finally) to prevent memory leaks; if you cache reports in Session, ensure they are disposed on Session_End. For exports prefer exporting to a stream and writing to Response instead of creating many files on disk (permission and cleanup issues).

Quick checklist for troubleshooting: run Verify Database in the designer, test a minimal single-table report first, confirm connection strings and app-pool identity, apply logon info to subreports, check export folder permissions, and capture the exact runtime error and .rpt version when diagnosing.

Member Avatar for Kusno

Dear Mr. Robins,
I try to help you. In my method :

1. Add aspx form as Report form, ex : RptMaster.aspx
2. In RptMaster.aspx, add CrystalReportViewer and CrystalReportSource

<CR:CrystalReportSource ID="Crs" runat="server">
        </CR:CrystalReportSource>    
        <CR:CrystalReportViewer ID="Crv" runat="server" AutodataBind="true" HasRefreshButton="True" ReportSourceID="Crs" HyperlinkTarget="_new" HasCrystalLogo="False" ToolbarStyle-BackColor="#C0FFFF" ToolbarStyle-BorderColor="White" DisplayGroupTree="False" EnableDrillDown="False" HasDrillUpButton="False" HasToggleGroupTreeButton="False" />

3. in VB.Net code

Imports CrystalDecisions.Shared
Imports CrystalDecisions.Web
Imports System.IO 

Partial Class RptMaster
    Inherits System.Web.UI.Page

    Dim FileName As String = ""
    Dim ErrorMessage As String
    Dim ExportToExcel As String = "0" 
    Dim Folder As String = "" 

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not Request.QueryString("ExportToExcel") Is Nothing Then ExportToExcel = Request.QueryString("ExportToExcel") 
        If Not Request.QueryString("folder") Is Nothing Then Folder = Request.QueryString("folder").Trim

        FileName = Request.QueryString("FileName")
        If FileName = "" Then Response.Redirect("../Others/Sorry.aspx?ErrorMessage=File is not found !")
        If Not IsPostBack Then
            ConfigureCrsytalReports()
            ExportReport() 
        End If
    End Sub

    Sub ExportReport()
        Dim WebMainFolder As String = Request.PhysicalApplicationPath
        Dim PdfFolder As String = ""
        Dim PdfFile As String = "" 
        Try
            PdfFolder = WebMainFolder & "Pdf\"
            If ExportToExcel.Trim = "1" Then
                PdfFile = "\" & Session("UserId") & "-" & Replace(FileName.ToLower, ".rpt", ".xls")                
                Crs.ReportDocument.ExportToDisk(ExportFormatType.Excel, PdfFolder & PdfFile)             
            Else
                PdfFile = Session("UserId") & "-" & Replace(FileName.ToLower, ".rpt", ".pdf")         
                Crs.ReportDocument.ExportToDisk(ExportFormatType.PortableDocFormat, PdfFolder & PdfFile)
            End If
            Response.Redirect(Request.ApplicationPath & "\Pdf\" & PdfFile, True)
        Catch ex As Exception
         
    End Sub 

    Sub ConfigureCrsytalReports()
        If Folder <> "" Then
            Crs.Report.FileName = Folder & "\" & FileName
        Else
            Crs.Report.FileName = FileName
        End If
        Try
            SetDBLogonForReport()
            If IsDBNull(Session("RecordSelection")) = False Then
                If Session("RecordSelection") <> "" Then
                    Crs.ReportDocument.RecordSelectionFormula = Trim(Session("RecordSelection"))
                Else
                    Crs.ReportDocument.RecordSelectionFormula = ""
                End If
            End If
            Crs.ReportDocument.Refresh()
            HandlerFormulas()
        Catch ex As Exception 

        End Try
    End Sub

    Private Sub HandlerFormulas()
        Dim I As SByte
        Dim GetFormula As String = ""

        For I = 0 To Crs.ReportDocument.DataDefinition.FormulaFields.Count - 1
            GetFormula = LCase(Crs.ReportDocument.DataDefinition.FormulaFields(I).Name).Trim
            Select Case GetFormula
                Case "userid"
                    Crs.ReportDocument.DataDefinition.FormulaFields(I).Text = "'" & IIf(Session("UserName") = Nothing, "", Session("UserName")) & "'"
                Case "period"
                    Crs.ReportDocument.DataDefinition.FormulaFields(I).Text = "'" & IIf(Session("Period") = Nothing, "", Session("Period")) & "'"
                Case "header"
                    Crs.ReportDocument.DataDefinition.FormulaFields(I).Text = "'" & IIf(Session("Judul") = Nothing, "", Session("Header")) & "'"
            End Select
        Next
    End Sub

    Sub SetDBLogonForReport()
        Try
            Dim Table As CrystalDecisions.CrystalReports.Engine.Table
            For Each Table In Crs.ReportDocument.Database.Tables
                Dim Logon As CrystalDecisions.Shared.TableLogOnInfo
                Logon = Table.LogOnInfo
                Logon.ConnectionInfo.ServerName = ConfigurationManager.AppSettings("Server").Trim
                Logon.ConnectionInfo.DatabaseName = ConfigurationManager.AppSettings("Database").Trim
                Logon.ConnectionInfo.Password = ConfigurationManager.AppSettings("Password").Trim
                Logon.ConnectionInfo.UserID = ConfigurationManager.AppSettings("UID").Trim
                Table.ApplyLogOnInfo(Logon)
            Next
        Catch ex As Exception 

        End Try
    End Sub
End Class

4. In each report form

Protected Sub CmdPrint_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles CmdPrint.Click
             Session("Period") = Format(CDate(TxtDate.Text.Trim), "MMMM dd, yyyy")
        Session("RecordSelection") = "(({Trx.DateSettled} = Date('1900,1,1')) OR ({Trx.DateSettled} > Date(" & CDate(TxtDate.Text.Trim).Year & "," & CDate(TxtDate.Text.Trim).Month & "," & CDate(TxtDate.Text.Trim).Day & "))) AND {Trx.DateIssued} <= Date(" & CDate(TxtDate.Text.Trim).Year & "," & CDate(TxtDate.Text.Trim).Month & "," & CDate(TxtDate.Text.Trim).Day & ") AND {Trx.TrxStatus} <> 'D' "

        Dim S As String = "<SCRIPT>window.open('../../Reports/RptMaster.aspx?fileName=RptTDOutstanding.rpt&ExportToExcel=" & IIf(ChkExportToExcel.Checked = True, "1", "0") & "&Folder=TD');</SCRIPT>"
        If Not ClientScript.IsClientScriptBlockRegistered("p") Then
            ClientScript.RegisterClientScriptBlock(Me.GetType(), "p", S)
        End If
    End Sub

note : only works for Table not View.


Thanks,

Kusno.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.