Someone Please Help!!!

[StephanieHarris]

Hello,

I have a report that runs in 3.5 minutes when I run it from within Crystal Reports. But when I run it from my vb app, it takes at least 20 minutes to run. I'm using an access database and the report is doing nothing fancy. One group with a few running total fields. There are about 230k records in the database. Here is the code I'm using to bring up the report. If anyone could please help I would really appreciate it!

Dim Report As New craxdrt.Report
Dim Appl As New craxdrt.Application
Private Sub Form_Load()

    Set Report = Appl.OpenReport(App.Path & "\Cost Center Summary.rpt")
    
    Report.ParameterFields.GetItemByName("BeginDate").ClearCurrentValueAndRange
    Report.ParameterFields.GetItemByName("BeginDate").AddCurrentValue CDate(CostCenterSummary_BeginDate)
    Report.ParameterFields.GetItemByName("EndDate").ClearCurrentValueAndRange
    Report.ParameterFields.GetItemByName("EndDate").AddCurrentValue CDate(CostCenterSummary_EndDate)
    Report.ParameterFields.GetItemByName("SiteCode").ClearCurrentValueAndRange
    Report.ParameterFields.GetItemByName("SiteCode").AddCurrentValue CostCenterSummary_SiteCode
    Report.ParameterFields.GetItemByName("DirectionFlag").ClearCurrentValueAndRange
    Report.ParameterFields.GetItemByName("DirectionFlag").AddCurrentValue CostCenterSummary_Direction
    Report.ParameterFields.GetItemByName("DomainFlag").ClearCurrentValueAndRange
    Report.ParameterFields.GetItemByName("DomainFlag").AddCurrentValue CostCenterSummary_Domain
    Report.ParameterFields.GetItemByName("CallType").ClearCurrentValueAndRange
    Report.ParameterFields.GetItemByName("CallType").AddCurrentValue CostCenterSummary_CallType
    Report.ParameterFields.GetItemByName("DivisionName").ClearCurrentValueAndRange
    Report.ParameterFields.GetItemByName("DivisionName").AddCurrentValue CostCenterSummary_DivisionName
    Report.ParameterFields.GetItemByName("CostCenterName").ClearCurrentValueAndRange
    Report.ParameterFields.GetItemByName("CostCenterName").AddCurrentValue CostCenterSummary_CostCenterName
    Report.ParameterFields.GetItemByName("DepartmentName").ClearCurrentValueAndRange
    Report.ParameterFields.GetItemByName("DepartmentName").AddCurrentValue CostCenterSummary_DepartmentName
    Report.ParameterFields.GetItemByName("ExtensionName").ClearCurrentValueAndRange
    Report.ParameterFields.GetItemByName("ExtensionName").AddCurrentValue CostCenterSummary_ExtensionName
    Report.ParameterFields.GetItemByName("AccountCode").ClearCurrentValueAndRange
    Report.ParameterFields.GetItemByName("AccountCode").AddCurrentValue CostCenterSummary_AccountCode
    Report.ParameterFields.GetItemByName("TrunkGroup").ClearCurrentValueAndRange
    Report.ParameterFields.GetItemByName("TrunkGroup").AddCurrentValue CostCenterSummary_TrunkGroupNumber
    Report.ParameterFields.GetItemByName("Trunk").ClearCurrentValueAndRange
    Report.ParameterFields.GetItemByName("Trunk").AddCurrentValue CostCenterSummary_TrunkMember
    Report.ParameterFields.GetItemByName("City").ClearCurrentValueAndRange
    Report.ParameterFields.GetItemByName("City").AddCurrentValue CostCenterSummary_City
    Report.ParameterFields.GetItemByName("State").ClearCurrentValueAndRange
    Report.ParameterFields.GetItemByName("State").AddCurrentValue CostCenterSummary_State
    
    Report.ParameterFields.GetItemByName("DurationGroupGreater").ClearCurrentValueAndRange
    Report.ParameterFields.GetItemByName("DurationGroupLess").ClearCurrentValueAndRange
    Report.ParameterFields.GetItemByName("DurationGroupNothing").ClearCurrentValueAndRange
    Report.ParameterFields.GetItemByName("DurationValue").ClearCurrentValueAndRange
    Report.ParameterFields.GetItemByName("CostGroupGreater").ClearCurrentValueAndRange
    Report.ParameterFields.GetItemByName("CostGroupLess").ClearCurrentValueAndRange
    Report.ParameterFields.GetItemByName("CostGroupNothing").ClearCurrentValueAndRange
    Report.ParameterFields.GetItemByName("CostValue").ClearCurrentValueAndRange
    
    Select Case CostCenterSummary_Duration
        Case Is = "1"
            Report.ParameterFields.GetItemByName("DurationGroupGreater").AddCurrentValue True
            Report.ParameterFields.GetItemByName("DurationGroupLess").AddCurrentValue False
            Report.ParameterFields.GetItemByName("DurationGroupNothing").AddCurrentValue False
            Report.ParameterFields.GetItemByName("DurationValue").AddCurrentValue CostCenterSummary_DurationValue
        Case Is = "2"
            Report.ParameterFields.GetItemByName("DurationGroupGreater").AddCurrentValue False
            Report.ParameterFields.GetItemByName("DurationGroupLess").AddCurrentValue True
            Report.ParameterFields.GetItemByName("DurationGroupNothing").AddCurrentValue False
            Report.ParameterFields.GetItemByName("DurationValue").AddCurrentValue CostCenterSummary_DurationValue
        Case Is = "3"
            Report.ParameterFields.GetItemByName("DurationGroupGreater").AddCurrentValue False
            Report.ParameterFields.GetItemByName("DurationGroupLess").AddCurrentValue False
            Report.ParameterFields.GetItemByName("DurationGroupNothing").AddCurrentValue True
            Report.ParameterFields.GetItemByName("DurationValue").AddCurrentValue CostCenterSummary_DurationValue
    End Select
    
    Select Case CostCenterSummary_Cost
        Case Is = "1"
            Report.ParameterFields.GetItemByName("CostGroupGreater").AddCurrentValue True
            Report.ParameterFields.GetItemByName("CostGroupLess").AddCurrentValue False
            Report.ParameterFields.GetItemByName("CostGroupNothing").AddCurrentValue False
            Report.ParameterFields.GetItemByName("CostValue").AddCurrentValue CostCenterSummary_CostValue
        Case Is = "2"
            Report.ParameterFields.GetItemByName("CostGroupGreater").AddCurrentValue False
            Report.ParameterFields.GetItemByName("CostGroupLess").AddCurrentValue True
            Report.ParameterFields.GetItemByName("CostGroupNothing").AddCurrentValue False
            Report.ParameterFields.GetItemByName("CostValue").AddCurrentValue CostCenterSummary_CostValue
        Case Is = "3"
            Report.ParameterFields.GetItemByName("CostGroupGreater").AddCurrentValue False
            Report.ParameterFields.GetItemByName("CostGroupLess").AddCurrentValue False
            Report.ParameterFields.GetItemByName("CostGroupNothing").AddCurrentValue True
            Report.ParameterFields.GetItemByName("CostValue").AddCurrentValue CostCenterSummary_CostValue
    End Select
    
    
    'Screen.MousePointer = vbHourglass
    MousePointer = 11
    CRViewer91.ReportSource = Report
    CRViewer91.ViewReport
    'Screen.MousePointer = vbDefault
    MousePointer = 0
    
End Sub

Private Sub Form_Resize()
    
    CRViewer91.Top = 0
    CRViewer91.Left = 0
    CRViewer91.Height = ScaleHeight
    CRViewer91.Width = ScaleWidth

End Sub

Private Sub Form_Unload(Cancel As Integer)
    
    Set Report = Nothing
    Load CostCenterSummary_Main
    CostCenterSummary_Main.Show
    Unload Me
    
End Sub

[springsoft]

I've had a quick look.
Deeper analysis takes longer, according to my therapist....:-))

Firstly, you are banging away at the COM layer unnecessarily, and each time you do it the way your code shows, you are doing 2 unnecessary calls to COM layer - 1 for report object, and 1 for ParameterFields. Wrap it in a WITH END WITH block, eg

WITH Report.ParameterFields
.GetItemByName("BeginDate").ClearCurrentValueAndRange
.GetItemByName("BeginDate").AddCurrentValue CDate(CostCenterSummary_BeginDate)
.GetItemByName("EndDate").ClearCurrentValueAndRange
.GetItemByName("EndDate").AddCurrentValue CDate(CostCenterSummary_EndDate)
.GetItemByName("SiteCode").ClearCurrentValueAndRange
.GetItemByName("SiteCode").AddCurrentValue CostCenterSummary_SiteCode
.GetItemByName("DirectionFlag").ClearCurrentValueAndRange
.GetItemByName("DirectionFlag").AddCurrentValue CostCenterSummary_Direction
.GetItemByName("DomainFlag").ClearCurrentValueAndRange
.GetItemByName("DomainFlag").AddCurrentValue CostCenterSummary_Domain
.GetItemByName("CallType").ClearCurrentValueAndRange
etc
END WITH

Secondly, you have a lot of code to clear values within the report, and I believe you could do the same by doing:
set Report = Nothing ' to ensure any existing object is disposed of.
Set Report = Appl.OpenReport(App.Path & "\Cost Center Summary.rpt")
Once the existing report instance has been disposed of, the values within the ParameterFields collection should be uninitialised.

Thirdly - you are using the same name for the object as its COM type (Report) - that's a programming practice bound for a disaster.
Prefix your Report object with and o or obj to diffferentiate it from the type declaration
eg
dim oReport as new CRAXDRT.Report

Fourth - that's a lot of parameters.........

If the report takes 3.5 mins in the Crystal Environment and 20 mins within VB, we need to investigate the differences in direct Crystal IDE vs VB, because I don't think there's 17.5 mins to be gained in the above suggestions.

Are you using the same ODBC connector/driver for CR and VB?
Same PC?
Same user account?
Is this time measure "time to print"? Or "time to viewer"?
No exporting to different formats?

Dave

[springsoft]

"Verify on every print" option is on or off?

[Kiko_BG]

Are you solve this?

I think don't use GetItemByName() use Items by index. With so many parameters it is very important.