How to Change Crystal Report database name from visual basic code?

[Former Member]

Hi all,

I have created a Crystal Report (CR) with .NET VB. I also have developd some UDTs for that pusrpose and everything is OK.

However I cannot use the same CR in another Company which has the same UDTs. I have not found how Connect to Company (in other words change the DB the report reads).

Any Idea?

Thanks,

Vangelis

Edited by: Vangelis Kanellopoulos on Jul 19, 2008 6:07 PM

Edited by: Vangelis Kanellopoulos on Jul 20, 2008 10:27 AM

Edited by: Vangelis Kanellopoulos on Jul 20, 2008 10:28 AM

[Former Member · Accepted solution]

Hi Owen,

some elementary questions.

- where CrystalFunctions Class should be located (e.g. in Form1.VB or in CrystalReport.VB)?

- And the instance of CR where should be located (e.g. in CrystalReport.VB before Sub New()?

Thanks,

Vangelis

[former_member201110 · Accepted solution]

Hi Vangelis,

In the example above, I had created a separate class (eg CrystalFunctions.vb) but there's no reason why you couldn't just include the subroutines in your main class. Having a separate class is useful because I can easily import it in to other addons I'm developing that need Crystal reports.

I had a class level private variable for my instance of the report object but you could just as easily dimension and instantiate the report object within the subroutine that is used to print the report.

Kind Regards,

Owen

[Former Member]

Hi

Public Shared Sub SetCrystalParams(ByVal sFieldName As String, ByVal iDataType As ParamType, ByVal sVal As String, ByRef oRpt As CrystalDecisions.CrystalReports.Engine.ReportDocument)

Dim oFieldDefs As CrystalDecisions.CrystalReports.Engine.ParameterFieldDefinitions

Dim oFieldDef As CrystalDecisions.CrystalReports.Engine.ParameterFieldDefinition

Dim oParamVals As CrystalDecisions.Shared.ParameterValues

Dim oDiscreteVal As CrystalDecisions.Shared.ParameterDiscreteValue

oFieldDefs = oRpt.DataDefinition.ParameterFields

?????????

-

---

oFieldDef = oFieldDefs(sFieldName)

-

---

???????????

oParamVals = oFieldDef.CurrentValues

oParamVals.Clear()

oDiscreteVal = New CrystalDecisions.Shared.ParameterDiscreteValue()

Select Case iDataType

Case ParamType.Int

oDiscreteVal.Value = System.Convert.ToInt32(sVal)

Case ParamType.Text

oDiscreteVal.Value = sVal

End Select

oParamVals.Add(oDiscreteVal)

oFieldDef.ApplyCurrentValues(oParamVals)

End Sub

How is the (above marked ????) code posible?

how

can variable be used like a method over there.

Please help me to sort out this problem.

[former_member201110]

Hi Vangelis,

Here's a simple VB class that has functions for setting the login details for the report and passing parameters.

Option Strict Off
Option Explicit On

Public Class CrystalFunctions

    Enum ParamType As Integer
        Int
        Text
    End Enum

    Public Shared Sub SetCrystalLogin(ByVal sUser As String, ByVal sPassword As String, ByVal sServer As String, ByVal sCompanyDB As String, _
           ByRef oRpt As CrystalDecisions.CrystalReports.Engine.ReportDocument)

        Dim oDB As CrystalDecisions.CrystalReports.Engine.Database = oRpt.Database
        Dim oTables As CrystalDecisions.CrystalReports.Engine.Tables = oDB.Tables
        Dim oLogonInfo As CrystalDecisions.Shared.TableLogOnInfo
        Dim oConnectInfo As CrystalDecisions.Shared.ConnectionInfo = New CrystalDecisions.Shared.ConnectionInfo()
        oConnectInfo.DatabaseName = sCompanyDB
        oConnectInfo.ServerName = sServer
        oConnectInfo.UserID = sUser
        oConnectInfo.Password = sPassword
        ' Set the logon credentials for all tables
        For Each oTable As CrystalDecisions.CrystalReports.Engine.Table In oTables
            oLogonInfo = oTable.LogOnInfo
            oLogonInfo.ConnectionInfo = oConnectInfo
            oTable.ApplyLogOnInfo(oLogonInfo)
        Next
        ' Check for subreports
        Dim oSections As CrystalDecisions.CrystalReports.Engine.Sections
        Dim oSection As CrystalDecisions.CrystalReports.Engine.Section
        Dim oRptObjs As CrystalDecisions.CrystalReports.Engine.ReportObjects
        Dim oRptObj As CrystalDecisions.CrystalReports.Engine.ReportObject
        Dim oSubRptObj As CrystalDecisions.CrystalReports.Engine.SubreportObject
        Dim oSubRpt As New CrystalDecisions.CrystalReports.Engine.ReportDocument
        oSections = oRpt.ReportDefinition.Sections
        For Each oSection In oSections
            oRptObjs = oSection.ReportObjects
            For Each oRptObj In oRptObjs

                If oRptObj.Kind = CrystalDecisions.Shared.ReportObjectKind.SubreportObject Then

                    ' This is a subreport so set the logon credentials for this report's tables
                    oSubRptObj = CType(oRptObj, CrystalDecisions.CrystalReports.Engine.SubreportObject)
                    ' Open the subreport
                    oSubRpt = oSubRptObj.OpenSubreport(oSubRptObj.SubreportName)

                    oDB = oSubRpt.Database
                    oTables = oDB.Tables

                    For Each oTable As CrystalDecisions.CrystalReports.Engine.Table In oTables
                        oLogonInfo = oTable.LogOnInfo
                        oLogonInfo.ConnectionInfo = oConnectInfo
                        oTable.ApplyLogOnInfo(oLogonInfo)
                    Next

                End If

            Next
        Next

    End Sub

    Public Shared Sub SetCrystalParams(ByVal sFieldName As String, ByVal iDataType As ParamType, ByVal sVal As String, ByRef oRpt As CrystalDecisions.CrystalReports.Engine.ReportDocument)

        Dim oFieldDefs As CrystalDecisions.CrystalReports.Engine.ParameterFieldDefinitions
        Dim oFieldDef As CrystalDecisions.CrystalReports.Engine.ParameterFieldDefinition
        Dim oParamVals As CrystalDecisions.Shared.ParameterValues
        Dim oDiscreteVal As CrystalDecisions.Shared.ParameterDiscreteValue

        oFieldDefs = oRpt.DataDefinition.ParameterFields
        oFieldDef = oFieldDefs(sFieldName)
        oParamVals = oFieldDef.CurrentValues
        oParamVals.Clear()

        oDiscreteVal = New CrystalDecisions.Shared.ParameterDiscreteValue()
        Select Case iDataType
            Case ParamType.Int
                oDiscreteVal.Value = System.Convert.ToInt32(sVal)
            Case ParamType.Text
                oDiscreteVal.Value = sVal
        End Select
        oParamVals.Add(oDiscreteVal)
        oFieldDef.ApplyCurrentValues(oParamVals)
    End Sub

End Class

And here's how you would use them:

' Create an instance of the Crystal report
_rptCrystal = New CrystalDecisions.CrystalReports.Engine.ReportDocument()
_rptCrystal.Load(_oSBO.AddonPath + "\Reports\MyReport.rpt")

' Call SetCrystalLogin to see the logon information for all report tables
CrystalFunctions.SetCrystalLogin(sUser, sPassword, _oSBO.SboCompany.Server, _oSBO.SboCompany.CompanyDB, _rptCrystal)

' Set my report parameter value
CrystalFunctions.SetCrystalParams("MyParam", CrystalFunctions.ParamType.Int, 999, _rptCrystal)

' Print the report straight to the printer                           
_rptCrystal.PrintToPrinter(1, False, 0, 0)

The other way to approach this solution would be to base your Crystal report on a .NET dataset rather than a database connection. However, as you've already written your report, the code above is going to be simpler to implement.

Kind Regards,

Owen