Set Crystal Database Configuration

Following my previous post for printing a Crystal Report file connected to Oracle, this is a more generic GetConnectionInfo function which support three databases: Access, SQL and ORACLE

Again what needs to be changed is highlighted in bold text.

Private Function GetConnectionInfo(ByVal config As Business.Configuration) As CrystalDecisions.Shared.ConnectionInfo
      Dim dbAttributes As CrystalDecisions.Shared.DbConnectionAttributes
      Dim crConnectionInfo As CrystalDecisions.Shared.ConnectionInfo = Nothing

      Select Case config.DatabaseType
        Case Common.EnterpriseLibrary.Data.DatabaseType.Access

          Dim reportFullPath As String = String.Concat(config.ReportsPath, “\”, “DatabaseName“)
          If Not System.IO.File.Exists(reportFullPath) Then
            Throw New ApplicationException(String.Format(“The report [{0}] does not exist”, reportFullPath))
          End If

          ‘use DAO instead of ADO
          ’setup the attributes for the connection
          dbAttributes = New CrystalDecisions.Shared.DbConnectionAttributes
          dbAttributes.Collection.Set(“Data Source”, “ReportFullPath“)
          dbAttributes.Collection.Set(“Database Type”, “Access”)
          ’setup the connection
          crConnectionInfo = New CrystalDecisions.Shared.ConnectionInfo
          crConnectionInfo.LogonProperties.Clear()
          crConnectionInfo.Attributes.Collection.Clear()
          crConnectionInfo.DatabaseName = “”
          crConnectionInfo.ServerName = “ReportFullPath“
          crConnectionInfo.UserID = “ReportsUserName“
          crConnectionInfo.Password = “ReportsPassword“
          crConnectionInfo.Attributes.Collection.Set(“Database DLL”, “crdb_dao.dll”)
          crConnectionInfo.Attributes.Collection.Set(“QE_DatabaseName”, reportFullPath)
          crConnectionInfo.Attributes.Collection.Set(“QE_DatabaseType”, “Access/Excel (DAO)”)
          crConnectionInfo.Attributes.Collection.Set(“QE_LogonProperties”, dbAttributes)
          crConnectionInfo.Attributes.Collection.Set(“QE_ServerDescription”, reportFullPath)
          crConnectionInfo.Attributes.Collection.Set(“QE_SQLDB”, True)
          crConnectionInfo.Attributes.Collection.Set(“SSO Enabled”, False)
          crConnectionInfo.LogonProperties = dbAttributes.Collection

        Case Common.EnterpriseLibrary.Data.DatabaseType.Oracle
          ’setup the attributes for the connection
          dbAttributes = New CrystalDecisions.Shared.DbConnectionAttributes
          dbAttributes.Collection.Set(“Server”, “OracleServiceName“)
          dbAttributes.Collection.Set(“Trusted_Connection”, False)
          ’setup the connection
          crConnectionInfo = New CrystalDecisions.Shared.ConnectionInfo
          crConnectionInfo.LogonProperties.Clear()
          crConnectionInfo.Attributes.Collection.Clear()
          crConnectionInfo.DatabaseName = “”
          crConnectionInfo.ServerName = “OracleServiceName“
          crConnectionInfo.UserID = “ReportsUserName“
          crConnectionInfo.Password = “ReportsPassword”
          crConnectionInfo.Attributes.Collection.Set(“Database DLL”, “crdb_oracle.dll”)
          crConnectionInfo.Attributes.Collection.Set(“QE_DatabaseName”, “”)
          crConnectionInfo.Attributes.Collection.Set(“QE_DatabaseType”, “Oracle Server”)
          crConnectionInfo.Attributes.Collection.Set(“QE_LogonProperties”, dbAttributes)
          crConnectionInfo.Attributes.Collection.Set(“QE_ServerDescription”, config.ServerName)
          crConnectionInfo.Attributes.Collection.Set(“QE_SQLDB”, True)
          crConnectionInfo.Attributes.Collection.Set(“SSO Enabled”, False)
          crConnectionInfo.LogonProperties = dbAttributes.Collection

        Case Common.EnterpriseLibrary.Data.DatabaseType.SqlServer
          ’setup the attributes for the connection
          dbAttributes = New CrystalDecisions.Shared.DbConnectionAttributes
          dbAttributes.Collection.Set(“Auto Translate”, “-1″)
          dbAttributes.Collection.Set(“Connect Timeout”, “15″)
          dbAttributes.Collection.Set(“Data Source”, “SqlServerName“)
          dbAttributes.Collection.Set(“General Timeout”, “0″)
          dbAttributes.Collection.Set(“Initial Catalog”, “DatabaseName“)
          dbAttributes.Collection.Set(“Integrated Security”, False)
          dbAttributes.Collection.Set(“Locale Identifier”, “5129″)
          dbAttributes.Collection.Set(“OLE DB Services”, “-5″)
          dbAttributes.Collection.Set(“Provider”, “SQLOLEDB”)
          dbAttributes.Collection.Set(“Tag with column collation when possible”, “0″)
          dbAttributes.Collection.Set(“Use DSN Default Properties”, False)
          dbAttributes.Collection.Set(“Use Encryption for Data”, “0″)
          ’setup the connection
          crConnectionInfo = New CrystalDecisions.Shared.ConnectionInfo
          crConnectionInfo.LogonProperties.Clear()
          crConnectionInfo.Attributes.Collection.Clear()
          crConnectionInfo.DatabaseName = “DatabaseName“
          crConnectionInfo.ServerName = “SqlServerName“
          crConnectionInfo.UserID = “ReportsUserName“
          crConnectionInfo.Password = “ReportsPassword”
          crConnectionInfo.Attributes.Collection.Set(“Database DLL”, “crdb_ado.dll”)
          crConnectionInfo.Attributes.Collection.Set(“QE_DatabaseName”, config.DatabaseName)
          crConnectionInfo.Attributes.Collection.Set(“QE_DatabaseType”, “OLE DB (ADO)”)
          crConnectionInfo.Attributes.Collection.Set(“QE_LogonProperties”, dbAttributes)
          crConnectionInfo.Attributes.Collection.Set(“QE_ServerDescription”, config.ServerName)
          crConnectionInfo.Attributes.Collection.Set(“QE_SQLDB”, True)
          crConnectionInfo.Attributes.Collection.Set(“SSO Enabled”, False)
          crConnectionInfo.LogonProperties = dbAttributes.Collection

        Case Else
          Throw New ApplicationException(“Unknown Database”)
      End Select

      Return crConnectionInfo
    End Function

How to print crystal reports using .Net

The following code is a console application (.Net 1 or 2) which loads a crystal report file and set the database setting to a defined ORACLE database (9i, or 10g) and print the report to a local or network printer.

Step1: Create a console application

Step2: I used Crystal XI for thsi application and thats what I recommend

Add reference to

• CrystalDecisions.CrystalReports.Engine (11.5.33)
• CrystalDecisions.Shared (11.5.33)

 

Step3: Copy the following code to Module1, and change the report name and required parameters (highlighted with bold text).

 

Source code:

 

Module Module1
 

  Public _PrinterAddress As String = Nothing
  Public _ServiceName As String = Nothing
  Public _UserName As String = Nothing
  Public _Password As String = Nothing
  Public _ReportPath As String = Nothing
  Public Const _ReportName As String = “Report.RPT“
  Public _ReportDocument As CrystalDecisions.CrystalReports.Engine.ReportDocument

 

  Sub Main()
    Try
      Console.WriteLine(“Application started.” & Environment.NewLine)
      Console.Write(“Enter Printer Network Address:”)
      _PrinterAddress = Console.ReadLine     
      Console.Write(“Enter Oracle Service Name:”)
      _ServiceName = Console.ReadLine
      Console.Write(“Enter Oracle Username:”)
      _UserName = Console.ReadLine
      Console.Write(“Enter Oracle Password:”)
      _Password = Console.ReadLine
      Console.Write(“Enter Oracle Report Path:”)
      _ReportPath = Console.ReadLine
      If LoadReportFile() Then
        SetDatabaseLogonForReport(_ReportDocument)
        AddReportParameters()
        PrintToPrinter()
      End If
      _ReportDocument = Nothing
      Console.WriteLine(“Application end.”)
      Console.Read()
    Catch ex As Exception
      Console.WriteLine(ex.Message)
      Console.WriteLine(ex.StackTrace)
    End Try
  End Sub

  Function GetActiveDatabaseConnection() As Boolean
    While _ServiceName = Nothing
      Console.Write(“Enter Oracle Service Name:”)
      _ServiceName = Console.ReadLine
    End While
    While _UserName = Nothing
      Console.Write(“Enter Oracle Username:”)
      _UserName = Console.ReadLine
    End While
    While _Password = Nothing
      Console.Write(“Enter Oracle Password:”)
      _Password = Console.ReadLine
    End While
    While _ReportPath = Nothing
      Console.Write(“Enter Oracle Report Path:”)
      _ReportPath = Console.ReadLine
    End While
    Return True
  End Function
 

  Public Function LoadReportFile() As Boolean
    Dim dStart As Date = Now
    Console.WriteLine(dStart.ToLongTimeString & ” – Loading the report file”)
    _ReportDocument = New CrystalDecisions.CrystalReports.Engine.ReportDocument
    Dim reportFullPath As String = System.IO.Path.Combine(_ReportPath, _ReportName)
    If System.IO.File.Exists(reportFullPath) Then
      _ReportDocument.Load(reportFullPath)
    Else
      Console.WriteLine(“Error: could not find “, reportFullPath)
    End If
    Dim dEnd As Date = Now
    Console.WriteLine(“[" & reportFullPath & "] loaded in ” & DateAndTime.DateDiff(DateInterval.Second, dStart, dEnd).ToString & ” second” & Environment.NewLine)
    Return True
  End Function
 

Public Sub SetDatabaseLogonForReport(ByRef reportDocument As CrystalDecisions.CrystalReports.Engine.ReportDocument)
    Dim dStart As Date = Now
    Console.WriteLine(dStart.ToLongTimeString & ” – Logon to tables”)
    Dim crTableLogOnInfo As CrystalDecisions.Shared.TableLogOnInfo
    Dim connectionInfo As CrystalDecisions.Shared.ConnectionInfo = GetConnectionInfo()
    ‘Loop through all tables in the report and apply the connection information for each table.
    For Each crTable As CrystalDecisions.CrystalReports.Engine.Table In reportDocument.Database.Tables
      crTableLogOnInfo = crTable.LogOnInfo
      crTableLogOnInfo.ConnectionInfo = connectionInfo
      crTable.ApplyLogOnInfo(crTableLogOnInfo)
      Console.WriteLine(dStart.ToLongTimeString & ” – ” & crTable.Name)
    Next
    ‘update subreports
    Dim crSubreportObject As CrystalDecisions.CrystalReports.Engine.SubreportObject
    ‘loop through all the sections to find all the report objects
    For Each crSection As CrystalDecisions.CrystalReports.Engine.Section In reportDocument.ReportDefinition.Sections
      ‘loop through all the report objects to find all subreports
      For Each crReportObject As CrystalDecisions.CrystalReports.Engine.ReportObject In crSection.ReportObjects
        If crReportObject.Kind = CrystalDecisions.Shared.ReportObjectKind.SubreportObject Then
          Console.WriteLine(dStart.ToLongTimeString & ” — Subreport: ” & crReportObject.Name)
          crSubreportObject = CType(crReportObject, CrystalDecisions.CrystalReports.Engine.SubreportObject)
          ‘open the subreport and set the database logon for it
          SetDatabaseLogonForReport(crSubreportObject.OpenSubreport(crSubreportObject.SubreportName))
        End If
      Next
    Next
    Console.WriteLine(dStart.ToLongTimeString & ” – Start verifing database”)
    reportDocument.VerifyDatabase()
    Dim dEnd As Date = Now
    Console.WriteLine(“Logon process finished in ” & DateAndTime.DateDiff(DateInterval.Second, dStart, dEnd).ToString & ” second” & Environment.NewLine)
  End Sub

  Private Function GetConnectionInfo() As CrystalDecisions.Shared.ConnectionInfo
    Dim dbAttributes As CrystalDecisions.Shared.DbConnectionAttributes
    Dim crConnectionInfo As CrystalDecisions.Shared.ConnectionInfo = Nothing
    ’setup the attributes for the connection
    dbAttributes = New CrystalDecisions.Shared.DbConnectionAttributes
    dbAttributes.Collection.Set(“Server”, _ServiceName)
    dbAttributes.Collection.Set(“Trusted_Connection”, False)
    ’setup the connection
    crConnectionInfo = New CrystalDecisions.Shared.ConnectionInfo
    crConnectionInfo.LogonProperties.Clear()
    crConnectionInfo.Attributes.Collection.Clear()
    crConnectionInfo.DatabaseName = “”
    crConnectionInfo.ServerName = _ServiceName
    crConnectionInfo.UserID = _UserName
    crConnectionInfo.Password = _Password
    crConnectionInfo.Attributes.Collection.Set(“Database DLL”, “crdb_oracle.dll”)
    crConnectionInfo.Attributes.Collection.Set(“QE_DatabaseName”, “”)
    crConnectionInfo.Attributes.Collection.Set(“QE_DatabaseType”, “Oracle Server”)
    crConnectionInfo.Attributes.Collection.Set(“QE_LogonProperties”, dbAttributes)
    crConnectionInfo.Attributes.Collection.Set(“QE_ServerDescription”, _ServiceName)
    crConnectionInfo.Attributes.Collection.Set(“QE_SQLDB”, True)
    crConnectionInfo.Attributes.Collection.Set(“SSO Enabled”, False)
    crConnectionInfo.LogonProperties = dbAttributes.Collection
    Return crConnectionInfo
  End Function

  Public Sub AddReportParameters()
    Dim dStart As Date = Now
    Console.WriteLine(dStart.ToLongTimeString & ” – Add report parameters”)
    Dim strRpt As String = “{TableName.FieldName1} = Value1″ & _
    ” AND {TableName.FieldName2} = Value2″
    _ReportDocument.RecordSelectionFormula = strRpt
    _ReportDocument.Refresh()
    Dim dEnd As Date = Now
    Console.WriteLine(“Report parameters added in ” & DateAndTime.DateDiff(DateInterval.Second, dStart, dEnd).ToString & ” second” & Environment.NewLine)
  End Sub
 

Public Sub PrintToPrinter()
    Dim dStart As Date = Now
    Console.WriteLine(dStart.ToLongTimeString & ” – Start to print”)
    If _PrinterAddress Is Nothing Then
      Console.WriteLine(“Error: No printer is entered”)
      Exit Sub
    End If
    Dim pd As New System.Drawing.Printing.PrintDocument
    pd.PrinterSettings.PrinterName = _PrinterAddress.Trim
    If pd.PrinterSettings.IsValid Then
      _ReportDocument.PrintOptions.PrinterName = _PrinterAddress.Trim
      _ReportDocument.PrintOptions.PaperSource = CrystalDecisions.[Shared].PaperSource.Auto
    Else
      Console.WriteLine(“Error: Printer setting is invalid”)
      Exit Sub
    End If
    _ReportDocument.PrintToPrinter(1, False, 0, 0)
    Dim dEnd As Date = Now
    Console.WriteLine(“Print finished in ” & DateAndTime.DateDiff(DateInterval.Second, dStart, dEnd).ToString & ” second” & Environment.NewLine)
  End Sub

End Module

Error in File UNKNOWN.RPT

Crystal Report can be sometimes very tricky. When we faced the following bizarre  error message on a ASP.Net 2 web sites using Crystal 11, we learned that there are unknown undocumented secrets about distributing Crystal object.

Error in File UNKNOWN.RPT: The request could not be submitted for background processing.

We installed a web-application which was working fine on development environment on a Windows 2000 Server and we got following error message:

As far as we know there was no UNKNOWN.RPT in our project so we start searching the web for any hint about the source of the problem. There are number of posts on ASP.Net forum and other web sites about similar problems.

Finally after lots of research, following article turned to be helpful and fixed our problem. Interestingly the error message does not mach the actual addressed issue in the article, also we used Crystal 11, but this article is about Crystal 10.

BusinessObjects: https://boc.sdn.sap.com/node/231%23comment-2457/    (Comment (July 2009)/ this article is no longer available in BusinessObject website.)

Synopsis A Microsoft Visual Studio .NET application uses Crystal Reports 10 for Visual Studio .NET SDK as the reporting development tool. When the application is deployed to a computer that does not have a C drive, the following error message appears: “Load Report Failed” Why does this error message appear and how can it be resolved? It is common to not have a C drive on Terminal servers and Citrix servers. Solution This error message appears because the application sets registry values that point to the C drive. To resolve the error message, use the following steps to change these registry values in the Registry Editor. 1. On the ‘Start’ menu, click ‘Run’. 2. In the ‘Run’ dialog box, type “Regedit” then click ‘OK’. 3. In the Registry Editor browse to the following subkey: HKEY_LOCAL_MACHINE\SOFTWARE\Crystal Decisions\10.0\Report Application Server\InprocServer\LocalConnectionMgr 4. Right-click the ‘ConnectionDirectoryPath’ string value, then click ‘Modify’. 5. In the ‘Value data’ text box, change “c:\” to the drive letter where the following directory exists (You may need to search your computer to find where this directory exists): \Program Files\Common Files\Crystal Decisions\2.5\bin Click ‘OK’. 6. Right-click the ‘LocalConnectionMgr’ subkey, click ‘New’ then click ‘String Value’. 7. Name this String Value “ReportDirectoryPath”. 8. Right-click the ‘ReportDirectoryPath’ string value and click ‘Modify’. 9 In the ‘Value data’ text box, type the same drive letter as you typed in step 5. ==================== NOTE: After making changes to the registry, restart the affected service or application as required.

Well I will add this solution to my support notes and I guess this is one of those issues that I will face it again on different platforms of windows. The whole problem sounds like a bug and I do not know why BusinessObject hasn’t release any patch to fix it.