Crytal Reports...Expor t Error with parms

[Tim Jones]

Hey all,

I am trying to try get a crytal report running properly. A "Hello World"
report that does not add criteria to the record selection formula of the
report (either in the development tool, or in the code) works fine.

However, when I try to add any parameter through the record selection
formula and/or the report parameters method, the report crashes.

I have tried each of the methods:

1. Use the Record Selection Formula property:

crReportDocumen t.DataDefinitio n.RecordSelecti onFormula = "{qryTest.I D} =
54"

2. Use the Crystal Parameters collection(s)

crParameterFiel dDefinitions =
crReportDocumen t.DataDefinitio n.ParameterFiel ds

crParameterFiel dDefinition = crParameterFiel dDefinitions.It em("[@TestID]")
crParameterValu es = crParameterFiel dDefinition.Cur rentValues
crParameterDisc reteValue = New
CrystalDecision s.Shared.Parame terDiscreteValu e()

crParameterDisc reteValue.Value = 54
crParameterValu es.Add(crParame terDiscreteValu e)
crParameterFiel dDefinition.App lyCurrentValues (crParameterVal ues)
Both of the methods above work fine on my development machine but the
testing server gives these errors.

I also set the record selection formula in the report to automatically set
the TestID parameter = 54 and then tested that report on the testing server
and everything worked.

So the issue seems to be with populating the crystal report's parameters at
runtime. The error that comes back with a logon failure at the .Export
line.

I have tried two methods to logon to the reports:
############### #####
Basic:
With tliTableLogonIn fo.ConnectionIn fo
.ServerName = m_strDSNName
.DatabaseName = m_strDatabase
.UserID = m_strUserId
.Password = m_strPassword
End With

'setup each connection in the report
For Each tTable In crReportDocumen t.Database.Tabl es
tTable.ApplyLog OnInfo(tliTable LogonInfo)
Next tTable
############### #####
and Complex (loops through sub-reports):
Dim mySection As Section
Dim mySections As Sections
Dim myReportObject As ReportObject
Dim myReportObjects As ReportObjects
Dim mySubReportObje ct As SubreportObject
Dim mySubRepDoc As New ReportDocument( )

'Declare all of the sections of the main report
mySections = crReportDocumen t.ReportDefinit ion.Sections

'Loop through the sections in the main report to find the subreport objects
'then set the logon information to the subreport
Dim myLogin As CrystalDecision s.Shared.TableL ogOnInfo

For Each mySection In mySections
myReportObjects = mySection.Repor tObjects

For Each myReportObject In myReportObjects
If myReportObject. Kind = ReportObjectKin d.SubreportObje ct Then
'Subreport Found - convert the report object to a sub report type
mySubReportObje ct = CType(myReportO bject, SubreportObject )
'Set the specific instance of this subreport
mySubRepDoc = mySubReportObje ct.OpenSubrepor t
(mySubReportObj ect.SubreportNa me)

'Set the login information for the current subreport(found )
For Each crTable In mySubRepDoc.Dat abase.Tables
myLogin = crTable.LogOnIn fo
myLogin.Connect ionInfo.ServerN ame = m_strDSNName
myLogin.Connect ionInfo.Databas eName = m_strDatabase
myLogin.Connect ionInfo.UserID = m_strUserId
myLogin.Connect ionInfo.Passwor d = m_strPassword
crTable.ApplyLo gOnInfo(myLogin )
Next
End If
Next
Next
############### #####
Does anyone know of other methods to filter a report at runtime? Or is
there a Crystal DLL that I need to move from my dev machine to the server
to allow for the record selection properties to be set at runtime?

OR ANY OTHER INFO WOULD BE GREAT!!!

Thanks,

TJ

[Jerry Boone]

This is working code... In the report design, the parameter name is @pBDate
and is a DateTime type. In the Select Expert, I use.... is equal to and
the value is {?@pBDate} which takes the passed parameter to the cr query
renderer.

If you encounter login problems, let me know - I have some gotcha's that
remedy that.
Imports CrystalDecision s.CrystalReport s.Engine
Imports CrystalDecision s.Shared

'public class....
'inherits....

'Web Form Designer....

Dim cdoc As ReportDocument

Private Sub Page_Load...

'load cr object...
cdoc = New CrystalReport1

'...or you can also do this...
'crDoc.Load("C: \reports\Crysta lReport1.rpt")

Dim crDatabase As Database
Dim crTables As Tables
Dim crTable As Table
Dim crTableLogOnInf o As TableLogOnInfo
Dim crConnectionInf o As ConnectionInfo

crConnectionInf o = New ConnectionInfo
With crConnectionInf o
.ServerName = "sqlServer"
.DatabaseName = "sqlDatabas e"
.UserID = "sqlLogin"
.Password = "password"
End With

crDatabase = cdoc.Database
crTables = crDatabase.Tabl es

For Each crTable In crTables
crTableLogOnInf o = crTable.LogOnIn fo
crTableLogOnInf o.ConnectionInf o = crConnectionInf o
crTable.ApplyLo gOnInfo(crTable LogOnInfo)
Next

Dim crPFDs As ParameterFieldD efinitions
Dim crPFD As ParameterFieldD efinition

Dim crParameterValu es As New ParameterValues
Dim crPBdate As New ParameterDiscre teValue

crPFDs = cdoc.DataDefini tion.ParameterF ields
crPBdate.Value = "11/28/03"
crPFD = crPFDs.Item("@p BDate")
crParameterValu es = crPFD.CurrentVa lues
crParameterValu es.Add(crPBdate )
crPFD.ApplyCurr entValues(crPar ameterValues)

Me.CrystalRepor tViewer1.Report Source = cdoc

End Sub

--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz

"Tim Jones" <ti**********@y ahoo.ca> wrote in message
news:Xn******** *************** **********@24.7 0.95.211...

Hey all,

I am trying to try get a crytal report running properly. A "Hello World"
report that does not add criteria to the record selection formula of the
report (either in the development tool, or in the code) works fine.

However, when I try to add any parameter through the record selection
formula and/or the report parameters method, the report crashes.

I have tried each of the methods:

1. Use the Record Selection Formula property:

crReportDocumen t.DataDefinitio n.RecordSelecti onFormula = "{qryTest.I D} =
54"

2. Use the Crystal Parameters collection(s)

crParameterFiel dDefinitions =
crReportDocumen t.DataDefinitio n.ParameterFiel ds

crParameterFiel dDefinition = crParameterFiel dDefinitions.It em("[@TestID]")
crParameterValu es = crParameterFiel dDefinition.Cur rentValues
crParameterDisc reteValue = New
CrystalDecision s.Shared.Parame terDiscreteValu e()

crParameterDisc reteValue.Value = 54
crParameterValu es.Add(crParame terDiscreteValu e)
crParameterFiel dDefinition.App lyCurrentValues (crParameterVal ues)
Both of the methods above work fine on my development machine but the
testing server gives these errors.

I also set the record selection formula in the report to automatically set
the TestID parameter = 54 and then tested that report on the testing server and everything worked.

So the issue seems to be with populating the crystal report's parameters at runtime. The error that comes back with a logon failure at the .Export
line.

I have tried two methods to logon to the reports:
############### #####
Basic:
With tliTableLogonIn fo.ConnectionIn fo
.ServerName = m_strDSNName
.DatabaseName = m_strDatabase
.UserID = m_strUserId
.Password = m_strPassword
End With

'setup each connection in the report
For Each tTable In crReportDocumen t.Database.Tabl es
tTable.ApplyLog OnInfo(tliTable LogonInfo)
Next tTable
############### #####
and Complex (loops through sub-reports):
Dim mySection As Section
Dim mySections As Sections
Dim myReportObject As ReportObject
Dim myReportObjects As ReportObjects
Dim mySubReportObje ct As SubreportObject
Dim mySubRepDoc As New ReportDocument( )

'Declare all of the sections of the main report
mySections = crReportDocumen t.ReportDefinit ion.Sections

'Loop through the sections in the main report to find the subreport objects 'then set the logon information to the subreport
Dim myLogin As CrystalDecision s.Shared.TableL ogOnInfo

For Each mySection In mySections
myReportObjects = mySection.Repor tObjects

For Each myReportObject In myReportObjects
If myReportObject. Kind = ReportObjectKin d.SubreportObje ct Then
'Subreport Found - convert the report object to a sub report type
mySubReportObje ct = CType(myReportO bject, SubreportObject )
'Set the specific instance of this subreport
mySubRepDoc = mySubReportObje ct.OpenSubrepor t
(mySubReportObj ect.SubreportNa me)

'Set the login information for the current subreport(found )
For Each crTable In mySubRepDoc.Dat abase.Tables
myLogin = crTable.LogOnIn fo
myLogin.Connect ionInfo.ServerN ame = m_strDSNName
myLogin.Connect ionInfo.Databas eName = m_strDatabase myLogin.Connect ionInfo.UserID = m_strUserId
myLogin.Connect ionInfo.Passwor d = m_strPassword
crTable.ApplyLo gOnInfo(myLogin )
Next
End If
Next
Next
############### #####
Does anyone know of other methods to filter a report at runtime? Or is
there a Crystal DLL that I need to move from my dev machine to the server
to allow for the record selection properties to be set at runtime?

OR ANY OTHER INFO WOULD BE GREAT!!!

Thanks,

TJ