Crystal report viewer taking too much time to open

[Former Member]

Dear Team,
It is very urgent, your immediate attention is highly helpful and thankful

Environment used: WINDOWS 2008 R2 SERVER, CRYSTAL REPORT XI sp4, SQL SERVER 2008R2

I am customizing INVOICE report, when i try to open crystal report using report viewer - it is taking toooooo much of time to open
say more than 7 minutes..

Please help, here below is my coding. Please advise....

I am using the following code:

Private con As ADODB.Connection
Private rsOEINVH As ADODB.Recordset
Private rsOEINVD As ADODB.Recordset
Private rsARSAP As ADODB.Recordset
Private rsICLOC As ADODB.Recordset

Private crysapp As New CRAXDDRT.Application
Private crysrep As CRAXDDRT.Report
Private crysrep1 As CRAXDDRT.Report
Private crysdb As CRAXDDRT.Database
Private crysdb1 As CRAXDDRT.Database
Private crystbl As CRAXDDRT.DatabaseTable

'----------------------------------------------------
Dim abc, astr1, astr2, astr3, astr4 As String
Dim sWaybill, sInvoice As String
Dim inti As Integer
Dim provStr As String
'-------------------------------------- READING CREDENTIALS FROM XML
Dim sXmlFile As String
sXmlFile = App.Path & "\example.xml"
Dim oXML As New DOMDocument
oXML.Load sXmlFile
If oXML.parseError.reason <> "" Then
MsgBox oXML.parseError.reason
Exit Sub
End If
Dim oNode As IXMLDOMNode
Dim oAttr As IXMLDOMAttribute
Dim r As Integer
r = 1

For Each oNode In oXML.documentElement.childNodes

Set oAttr = oNode.Attributes.getNamedItem("vconfirm")

If r = 1 Then
'astr1 = oNode.nodeName & ": " & oNode.Text
astr1 = oNode.Text
ElseIf r = 2 Then
'astr2 = oNode.nodeName & ": " & oNode.Text
astr2 = oNode.Text
ElseIf r = 3 Then
astr3 = oNode.Text
ElseIf r = 4 Then
astr4 = oNode.Text

ElseIf r = 5 Then

mINVH1 = oNode.Text
ElseIf r = 6 Then

mINVH2 = oNode.Text

ElseIf r = 7 Then
mINVH3 = oNode.Text

ElseIf r = 8 Then
mWBH1 = oNode.Text

ElseIf r = 9 Then
mWBH2 = oNode.Text

ElseIf r = 10 Then
mWBH3 = oNode.Text

ElseIf r = 11 Then
mWBH4 = oNode.Text

End If

r = r + 1

Next oNode
'--------------------------------------------- CONNECTION STRING AND DATA
Set con = New ADODB.Connection
Set rsOEINVH = New ADODB.Recordset
Set rsOEINVD = New ADODB.Recordset
Set rsARSAP = New ADODB.Recordset
Set rsICLOC = New ADODB.Recordset

Set crysrep = Nothing
con.Provider = "sqloledb"
MsgBox ("Server info " & astr1 & astr2 & astr3 & astr4)
provStr = "Provider=MSDASQL; DRIVER=Sql Server; SERVER=" & astr1 & "; DATABASE=" & astr2 & "; UID=" & astr3 & "; PWD=" & astr4 & ";" ' xml PC
MsgBox (provStr)
con.Open provStr

MsgBox ("Opened")

sWaybill = App.Path & "\ENG\TSWAYBIL01.RPT"
sInvoice = App.Path & "\ENG\TSINV01.RPT" ' server

abc = ""
abc = mFromInvNo
'MsgBox (sInvoice)

Set crysrep = Nothing
'Set crysrep1 = Nothing
'MsgBox ("before the line crysapp.OpenReport(sInvoice, 1)")
'-------------------------------------------------------
Dim crysapp2 As New CRAXDDRT.Application
Dim crysrep2 As New CRAXDDRT.Report
Set crysapp2 = CreateObject("CrystalRuntime.Application.11")
Set crysrep2 = crysapp2.OpenReport(sInvoice, 1)

'If rsOEINVH.State = 1 Then rsOEINVH.Close

rsOEINVH.Open "select * from OEINVH", con, adOpenKeyset
'MsgBox ("rsOEINVH.RecordCount" & rsOEINVH.RecordCount)

If Not rsOEINVH.EOF Then

With crysrep2
'MsgBox ("INSIDE WITH BEFIRE .Database.Tables(1).SetDataSource (rsOEINVH)")

' MsgBox (.Database.Tables(0).SetDataSource(rsOEINVH))
.Database.Tables(1).SetDataSource (rsOEINVH)
.DiscardSavedData
End With
End If

'If rsOEINVD.State = 1 Then rsOEINVD.Close

rsOEINVD.Open "select * from OEINVD", con, adOpenKeyset

'MsgBox ("rsOEINVD.RecordCount" & rsOEINVD.RecordCount)

If Not rsOEINVD.EOF Then
With crysrep2
.Database.Tables(1).SetDataSource (rsOEINVD)
.DiscardSavedData
End With
End If

rsARSAP.Open "select * from ARSAP", con, adOpenKeyset

'MsgBox ("rsARSAP.RecordCount" & rsARSAP.RecordCount)

If Not rsARSAP.EOF Then
With crysrep2
.Database.Tables(1).SetDataSource (rsARSAP)
.DiscardSavedData
End With
End If

rsICLOC.Open "select * from ICLOC", con, adOpenKeyset

'MsgBox ("rsICLOC.RecordCount" & rsICLOC.RecordCount)

If Not rsICLOC.EOF Then
With crysrep2
.Database.Tables(1).SetDataSource (rsICLOC)
.DiscardSavedData
End With
End If


'---------------------------------------------------------------

'MsgBox ("***after the line crysapp.OpenReport(sInvoice, 1)")


inti = 1
MsgBox ("JUST BEFORE DO UNTIL-" & astr1 & astr2 & astr3 & astr4)

'MsgBox ("crysrep2.Database.Tables.Count " & crysrep2.Database.Tables.Count)

Do Until inti = crysrep2.Database.Tables.Count + 1
' MsgBox ("inside DO UNTIL- crystal logon" & astr1 & astr2 & astr3 & astr4)
'crysrep2.Database.Tables.Item(inti).SetLogOnInfo strdblocation, astr2, astr3, astr4
crysrep2.Database.Tables.Item(inti).SetLogOnInfo astr1, astr2, astr3, astr4
' MsgBox (crysrep2.Database.Tables.Item(inti).Name)
crysrep2.Database.Tables.Item(inti).SetDataSource (crysrep2.Database.Tables.Item(inti).Name)
' MsgBox ("inside DO UNTIL CR SetDataSource" & crysrep2.Database.Tables.Item(inti).Name)

inti = inti + 1

Loop



'crysapp2.LogOnServer "crdb_odbc.dll", astr1, astr2, astr3, astr4
crysrep2.DiscardSavedData
crysrep2.ParameterFields(1).AddCurrentValue (CStr(abc))
crysrep2.ParameterFields(2).AddCurrentValue (mINVH1)

crysrep2.ReadRecords


Dim copies As Integer
copies = 3


With CRViewer1

' MsgBox ("inside with CRViewer1 ")
'.Move 0, 0, Me.ScaleWidth, Me.ScaleHeight
'.ReportSource = Nothing

.Zoom (1)
.EnableExportButton = True
.EnablePrintButton = True
'.RefreshEx (True)
' .Refresh
.ReportSource = crysrep2
'.Refresh
.ViewReport


End With



ProcExit:

Exit Sub

ProcError:

MsgBox Err.Description
Resume ProcExit

END SUB

Thanks
SaroRam

[former_member11696 · Accepted solution]

XI support ended a long time ago, time to upgrade to .NET:

https://wiki.scn.sap.com/wiki/display/BOBJ/Crystal+Reports%2C+Developer+for+Visual+Studio+Downloads