Hhow to use Ccrystal Rreport with asp.net

[Robins Antony]

Hi all!

How to use crystal report with ASP.net?

How to choose database?

pls help with an eg.

Robins

[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.