Solved

Crystal report in a VB application

Posted on 2002-05-31
13
1,665 Views
Last Modified: 2007-11-27
I have recently started report design in Crystal report 7.0 and Now I want to call the report thru VB program. I understand i need to use Crystal report viewer or crystal report control but How???
Can anyone help me???
Kirti
0
Comment
Question by:kirtirani
  • 4
  • 4
  • 2
  • +3
13 Comments
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 7046512
Find the solution at Crsytal Report Knowledge Base:
http://support.crystaldecisions.com/
0
 
LVL 3

Expert Comment

by:Elmo_
ID: 7046579
Goto Visual Basic Books Online And Goto the Find section.

Type in: "crptToWindow"

This should bring up the Crystal ActiveX Control Properties Page.

This Will give you all the information that you need.

I have used it myself and it works great.

Cheers,

Ed.
0
 
LVL 1

Expert Comment

by:Madmarlin
ID: 7047010
Find enclosed below a module..

All you need to do is create a bas file, copy this code in and pass in the report name and any params as an array...

This code was used with an older version of Crystal but its worth a try I feel..

Good luck Madmarlin






'************************************************************************************
'   Crystal Report constants used to size arrays prior to API calls
'************************************************************************************

Private Const PE_UNCHANGED As Integer = -1
Private Const PE_WORD_LEN As Byte = 2
Private Const PE_SIZEOF_JOB_INFO As Long = 10 * PE_WORD_LEN + 4
Private Const PE_FULL_NAME_LEN As Integer = 256
Private Const PE_SERVERNAME_LEN As Byte = 128
Private Const PE_DATABASENAME_LEN As Byte = 128
Private Const PE_USERID_LEN As Byte = 128
Private Const PE_PASSWORD_LEN As Byte = 128
Private Const PE_SIZEOF_LOGON_INFO As Long = 514 ' # bytes in PELogOnInfo
Private Const PE_SIZEOF_WINDOW_OPTIONS As Long = 13 * PE_WORD_LEN  ' # bytes as PEWindowOptions
Private Const WS_MINIMIZE As Long = 536870912


'************************************************************************************
'   Crystal Report Type Structure to hold information returned by API calls
'************************************************************************************

' Holds information regarding Unicode API response to PEGetJobInfo
Private Type SplitPEJobInfo
    StructSize As Integer  ' initialize to PE_SIZEOF_JOB_INFO

    NumRecordsRead1 As Integer
    NumRecordsRead2 As Integer
    NumRecordsSelected1 As Integer
    NumRecordsSelected2 As Integer
    NumRecordsPrinted1 As Integer
    NumRecordsPrinted2 As Integer
    DisplayPageN As Integer
    LatestPageN As Integer
    StartPageN As Integer
    PrintEnded As Long
End Type

' Summary of SplitPEJobInfo which amalgamates certain fields
Private Type PEJobInfo
    StructSize As Integer  ' initialize to PE_SIZEOF_JOB_INFO

    NumRecordsRead As Long
    NumRecordsSelected As Long
    NumRecordsPrinted As Long
    DisplayPageN As Integer
    LatestPageN As Integer
    StartPageN As Integer
    PrintEnded As Long
End Type

' Holds print preview window display options
Private Type PEWindowOptions
    StructSize As Integer            'initialize to PE_SIZEOF_WINDOW_OPTIONS

    hasGroupTree As Integer
    canDrillDown As Integer
    hasNavigationControls As Integer
    hasCancelButton As Integer
    hasPrintButton As Integer
    hasExportButton As Integer
    hasZoomControl As Integer
    hasCloseButton As Integer
    hasProgressControls As Integer
    hasSearchButton As Integer
    hasPrintSetupButton As Integer
    hasRefreshButton As Integer
End Type

' Holds information which is used to logon to the server (Server name takes DSN)
Private Type PELogOnInfo
    StructSize As Integer   ' initialize to # bytes in PELogOnInfo

    ServerName As String * PE_SERVERNAME_LEN
    DatabaseName  As String * PE_DATABASENAME_LEN
    UserId As String * PE_USERID_LEN
    Password  As String * PE_PASSWORD_LEN
End Type



'************************************************************************************
'   Crystal Report API declarations used in the production of the reports.
'************************************************************************************

' Initiate and close the print engine and print jobs
'Private Declare Function PEOpenEngine Lib "crpe32.dll" () As Integer
Private Declare Function PECanCloseEngine Lib "crpe32.dll" () As Integer
Private Declare Sub PECloseEngine Lib "crpe32.dll" ()
Private Declare Function PEOpenPrintJob Lib "crpe32.dll" (ByVal RptName As String) As Integer
Private Declare Sub PEClosePrintJob Lib "crpe32.dll" (ByVal printJob As Integer)
Private Declare Function PEStartPrintJob Lib "crpe32.dll" (ByVal printJob As Integer, ByVal WaitOrNot As Integer) As Integer

' Retrieve report database types, logon info and parameters
Private Declare Function PESetNthTableLogOnInfo Lib "crpe32.dll" (ByVal printJob As Integer, ByVal TableN As Integer, LogOnInfo As PELogOnInfo, ByVal Propagate As Integer) As Integer
Private Declare Function PEGetNParams Lib "crpe32.dll" (ByVal printJob As Integer) As Integer
Private Declare Function PEGetNthParam Lib "crpe32.dll" (ByVal printJob As Integer, ByVal paramN As Integer, textHandle As Long, textLength As Integer) As Integer
Private Declare Function PESetNthParam Lib "crpe32.dll" (ByVal printJob As Integer, ByVal paramN As Integer, ByVal ParamValue As String) As Integer


' Print job error codes and messages
Private Declare Function PEGetErrorCode Lib "crpe32.dll" (ByVal printJob As Integer) As Integer
Private Declare Function PEGetErrorText Lib "crpe32.dll" (ByVal printJob As Integer, textHandle As Long, textLength As Integer) As Integer
Private Declare Function PEGetHandleString Lib "crpe32.dll" (ByVal textHandle As Long, ByVal Buffer As String, ByVal BufferLength As Integer) As Integer

' Report window display and control
Private Declare Function PESetWindowOptions Lib "crpe32.dll" (ByVal printJob As Integer, Options As PEWindowOptions) As Integer
Private Declare Function PEZoomPreviewWindow Lib "crpe32.dll" (ByVal printJob As Integer, ByVal ZoomLevel As Integer) As Integer
Private Declare Function PEGetWindowHandle Lib "crpe32.dll" (ByVal printJob As Integer) As Integer
Private Declare Sub PECloseWindow Lib "crpe32.dll" (ByVal printJob As Integer)
Private Declare Function PEOutputToWindow Lib "crpe32.dll" (ByVal printJob As Integer, ByVal title As String, ByVal Left As Long, ByVal Top As Long, ByVal Width As Long, ByVal Height As Long, ByVal style As Long, ByVal PWindow As Long) As Integer

' Job status declaration which returns number of records read
Private Declare Function RealPEGetJobStatus Lib "crpe32.dll" Alias "PEGetJobStatus" (ByVal printJob As Integer, JobInfo As SplitPEJobInfo) As Integer

' User32 API call to show the report window (if records have been read)
Private Declare Function SetWindowPos Lib "user32" (ByVal hwnd As Long, ByVal hWndInsertAfter As Long, ByVal X As Long, ByVal Y As Long, ByVal cx As Long, ByVal cy As Long, ByVal wFlags As Long) As Long

'Tim uses to pass internal params
''Private Declare Function PEGetNParameterFields Lib "crpe32.dll" (ByVal printJob%) As Integer
'Private Declare Function crPEGetNthParameterField Lib "crwrap16.dll" Alias "crvbPEGetNthParameterField" (ByVal printJob%, ByVal parameterN%, ByRef ValueType%, ByRef DefaultValueSet%, ByRef CurrentValueSet%, ByRef Name$, ByRef Prompt$, ByRef DefaultValue As Any, ByRef CurrentValue As Any) As Integer
'Private Declare Function crPESetNthParameterField Lib "crwrap16.dll" (ByVal printJob%, ByVal parameterN%, ByVal ValueType%, ByVal DefaultValueSet%, ByVal CurrentValueSet%, ByVal Name$, ByVal Prompt$, ByRef DefaultValue As Any, ByRef CurrentValue As Any) As Integer

     
   
' Form button constants
Private Const bRunRep As Byte = 1
Private Const bCloseForm As Byte = 3

' Crystal report engine open flag
Private bEngineOpen As Boolean

' Timer increment to close report engine after inactivity
Private iTimeout As Integer

' Array of reports for users with no access to prevent requerying the database
Private arrNoAccessReps() As String

' Adhoc counter and resultset
'Private rs As rdoResultset
Private iLoop As Integer


'tims Constants For Report internal params
Global Const PE_PF_REPORT_NAME_LEN = 128
Global Const PE_PF_NAME_LEN = 256
Global Const PE_PF_PROMPT_LEN = 256
Global Const PE_PF_VALUE_LEN = 256

Global Const PE_PF_NUMBER = 0
Global Const PE_PF_CURRENCY = 1
Global Const PE_PF_BOOLEAN = 2
Global Const PE_PF_DATE = 3
Global Const PE_PF_STRING = 4


Type PEParameterFieldInfo
    'Initialize to PE_SIZEOF_PARAMETER_FIELD_INFO.
    StructSize As Integer

    'PE_PF_ constant
    ValueType As Integer

    'Indicate the default value is set in PEParameterFieldInfo.
    DefaultValueSet As Integer

    'Indicate the current value is set in PEParameterFieldInfo.
    CurrentValueSet As Integer

    'All strings are null-terminated.
    Name As String * PE_PF_NAME_LEN
    Prompt As String * PE_PF_PROMPT_LEN

    ' Could be Number, Date, DateTime, Time, Boolean, or String
    DefaultValue As String * PE_PF_VALUE_LEN
    CurrentValue As String * PE_PF_VALUE_LEN

    'name of report where the field belongs, only used in PEGetNthParameterField
    ReportName As String * PE_PF_REPORT_NAME_LEN

    'returns false (0) if parameter is linked, not in use, or has current value set
    needsCurrentValue As Integer
End Type

Option Explicit

Public Sub RunRep(ByVal ReportName As String, rReportParams() As String)
On Error GoTo ErrHandler

Dim iResult As Integer, iJobnum As Integer, iParamCount As Integer
Dim LogOnInfo As PELogOnInfo, WindowOptions As PEWindowOptions, JobInfo As PEJobInfo
Dim lTextHandle As Long, iTextLength As Integer, sParamText As String
Dim iJobIndex As Integer, iSkillIndex As Integer
Dim ParameterfieldInfo As PEParameterFieldInfo

 
    Screen.MousePointer = vbHourglass

    ' Check to see if engine is open, if required
    If Not bEngineOpen Then
       
        iResult = PEOpenEngine
        If iResult = 0 Then ' PEOpenEngine failed
            Screen.MousePointer = vbDefault
            MsgBox "Crystal Report Print Engine could not be opened.", vbCritical, "Open Print Engine Error"
            bEngineOpen = False
            Exit Sub
        Else
            bEngineOpen = True
        End If
    End If

    ' Open a new print job to the specified report
    iJobnum = PEOpenPrintJob(App.Path & "\Reports\" & ReportName & ".rpt")
    If iJobnum = 0 Then ' PEOpenPrintJob failed
        Screen.MousePointer = vbDefault
        'Utils.Error_Handler "CrystalReportEngine - OpenPrintJob", "", "New print job could not be established"
        Exit Sub
    End If
   
    ' Retrieve userid and password from INI file and logon (DSN and DBName to stay constant
    LogOnInfo.StructSize = PE_SIZEOF_LOGON_INFO
    LogOnInfo.ServerName = g_strServer & Chr$(0)
    LogOnInfo.UserId = g_UId & Chr$(0) ' UserName
    LogOnInfo.Password = g_UPWd & Chr$(0) ' Password
    LogOnInfo.DatabaseName = g_strDB & Chr$(0) ' DBName
    iResult = PESetNthTableLogOnInfo(iJobnum, 0, LogOnInfo, 1)
   
    If iResult <> 1 Then ' PESetNthTableLogonInfo failed
        Screen.MousePointer = vbDefault
        'Utils.Error_Handler "CrystalReportEngine - SetLogonInfo", CStr(iResult), GetErrorString(iJobnum)
        Exit Sub
    End If
   
    ' Retrieve number of stored procedure parameters
    iParamCount = PEGetNParams(iJobnum)
   
'    ' Loop through setting all report parameters
'    For iLoop = 0 To iParamCount - 1
'        ' Get handle to parameter string
'        iResult = PEGetNthParam(iJobnum, iLoop, lTextHandle, iTextLength)
'        ' Initialise text string
'        sParamText = String$(iTextLength, 0)
'        ' Read in parameter text from buffer
'        iResult = PEGetHandleString(lTextHandle, sParamText, iTextLength)
'        Select Case sParamText
'
'            Case "0" & Chr$(0) 'Benchmark Level (1-3)
'                    iResult = PESetNthParam(iJobnum, iLoop, "4")
'            Case Else
'                MsgBox "Unsure of Param = " & sParamText, vbOKOnly
'        End Select
'        If iResult = False Then ' PESetNthParam failed
'            Screen.MousePointer = vbDefault
'            'Utils.Error_Handler "CrystalReportEngine - SetParameter", CStr(iResult), GetErrorString(iJobnum)
''            frmMain.sbar.Panels(1).Text = "Ready"
'            Exit Sub
'        End If
'    Next iLoop
           
           
           
    iParamCount = PEGetNParameterFields(iJobnum)

    If iParamCount < 1 Then
'       MsgBox "There are no field parameters in this report. Execution must halt.", MB_OK + MB_ICONSTOP, "No Field Parameters"
    Else
    ' If there are parameters, load them into the sections form
    'Load Sections
   ' 'CenterForm Sample, Sections
'    Sections.Tag = "Field Parameter List"

    For iLoop = 0 To iParamCount - 1
        ' Get parameter
        iResult = crPEGetNthParameterField(iJobnum, 0, ParameterfieldInfo.ValueType, ParameterfieldInfo.DefaultValueSet, ParameterfieldInfo.CurrentValueSet, ParameterfieldInfo.Name, ParameterfieldInfo.Prompt, ParameterfieldInfo.DefaultValue, ParameterfieldInfo.CurrentValue)
        ParameterfieldInfo.CurrentValue = rReportParams(iLoop + 1) & Chr$(0)
        ParameterfieldInfo.CurrentValueSet = 1

        iResult = crPESetNthParameterField(iJobnum, iLoop, ParameterfieldInfo.ValueType, ParameterfieldInfo.DefaultValueSet, ParameterfieldInfo.CurrentValueSet, ParameterfieldInfo.Name, ParameterfieldInfo.Prompt, ParameterfieldInfo.DefaultValue, ParameterfieldInfo.CurrentValue)
'
    Next iLoop
    End If

           
    ' Set global report preview options
    With WindowOptions
        .StructSize = PE_SIZEOF_WINDOW_OPTIONS
        .hasGroupTree = 0
        .canDrillDown = PE_UNCHANGED
        .hasNavigationControls = 1
        .hasCancelButton = 0
        .hasZoomControl = 1
        .hasProgressControls = 1
        .hasSearchButton = 1
        .hasRefreshButton = 1
    End With

   
    'Report Printing. Set to show print buttons
        With WindowOptions
            .hasCloseButton = 1
            .hasPrintButton = 1
            .hasExportButton = 1
            .hasPrintSetupButton = 1
        End With
   
    ' Set window options
    iResult = PESetWindowOptions(iJobnum, WindowOptions)
    If iResult = 0 Then ' PESetWindowOptions failed
        Screen.MousePointer = vbDefault
        'Utils.Error_Handler "CrystalReportEngine - SetWindowOptions", CStr(iResult), GetErrorString(iJobnum)
        Exit Sub
    End If

    ' Prepare for output to window
    iResult = PEOutputToWindow(iJobnum, ReportName, 0, 0, 10, 10, 0, 0)
    If iResult = 0 Then ' PEOutputToWindow failed
        Screen.MousePointer = vbDefault
       ' Utils.Error_Handler "CrystalReportEngine - OutputToWindow", CStr(iResult), GetErrorString(iJobnum)
        Exit Sub
    End If
 
           
    ' Inform user of progress
    'frmMain.sbar.Panels(1).Text = "Report parameters and options set.     Retrieving data........Please wait"

    ' Execute report to window
    iResult = PEStartPrintJob(iJobnum, True)
    If iResult = 0 Then ' PEStartPrintJob failed
        Screen.MousePointer = vbDefault
         MsgBox "CrystalReportEngine - StartPrintJob"
     '   Utils.Error_Handler "CrystalReportEngine - StartPrintJob", CStr(iResult), GetErrorString(iJobnum)
        Exit Sub
    End If
   
    ' Set Zoom precentage
    iResult = PEZoomPreviewWindow(iJobnum, 85)
   
    ' Initialise print job info structure and retrieve records read from function
    JobInfo.StructSize = PE_SIZEOF_JOB_INFO
    'iResult = PEGetJobStatus(iJobnum, JobInfo)
   
    ' Reset timeout variable as new report run
    iTimeout = 0
   
   ' Destroy if no records found else display
   'If JobInfo.NumRecordsRead = 0 Then
   '     PECloseWindow iJobnum
   '     Screen.MousePointer = vbDefault
   '     MsgBox "There is no Management Information available for the details keyed", vbInformation, "No Records Found"
       
   'Else
        iResult = PEGetWindowHandle(iJobnum)
        SetWindowPos iResult, 0, 100, 100, 1150, 1000, 0
    'End If
       
ErrHandler_Resume:
    PEClosePrintJob iJobnum
'    frmMain.sbar.Panels(1).Text = "Ready"
    Screen.MousePointer = vbDefault
'    Enable Me, bRunRep
    Exit Sub
ErrHandler:
    PECloseEngine
    Err.Raise Err.Number, Err.Source, Err.Description
    Resume ErrHandler_Resume
End Sub


0
 
LVL 100

Expert Comment

by:mlmcc
ID: 7047108
listening
0
 
LVL 18

Expert Comment

by:bobbit31
ID: 7047112
using cr viewer:
(make sure to add the crystal report viewer control under project/components and to reference the crystal report activex designer runtime under project/references)

Dim myApp As New CRAXDRT.Application
Dim myRpt As New CRAXDRT.Report

Set myRpt = myApp.OpenReport("<path to report>")

CRViewer1.ReportSource = myRpt '' CRViewer1 is your viewer control
CRViewer1.ViewReport

Set myRpt = Nothing
Set myApp = Nothing

0
 
LVL 18

Expert Comment

by:bobbit31
ID: 7047148
to use crystal report control:

under project/components, select crystal report control (crystl32.ocx)

place the crystal report control on your form (it will be invisible at runtime)

then you could do something like this:

CrystalReport1.ReportFileName = "<path to report>"
CrystalReport1.Destination = 0 '' crptToWindow
CrystalReport1.PrintReport
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:kirtirani
ID: 7052948
Hi bobbit31,

thanks for ur help.
I have another problem. I wanted to send parameter value from VB to crystal report. Like I need to generate the data for one specific date. so i need to send the date value to the parameter of crystal report. How to do that?

right now I am just viewing the report from the VB crystal report control. there is another problem, when I refresh the report and enter a new date the report is giving 0 records. but when  go to crystal report to check and enter the same date there its giving the matching records. Why its not refreshing the report thru VB crystal report contorl. do i need to set any property for that? please advice.

Kirti
0
 
LVL 3

Accepted Solution

by:
Elmo_ earned 50 total points
ID: 7053296
Kirti,

This is in the Help book. You should get everthing you need from there.  I have posted below the section you require to pass parameters.

SelectionFormula
Description
Specifies the records to be used when printing the report.
Runtime Usage
[form.]CrystalReport.SelectionFormulaProperty[= SelectionFormula$]
Enter the selection formula just as you would enter it in the Formula Editor in Crystal Reports. For example, to include only those records that have a quantity bigger than 5 in the {file.Qty} field, you would enter "{file.QTY} > 5" as your selection formula, i.e.,  CrystalReport1.SelectionFormula = "{file.QTY} > 5"
Remarks
·     Make certain that you enclose your selection formula in double quotes.
·     If your selection formula includes internal quotes, for example:
{file.STATE} = "CA"
     
change all of the internal double quotes to single quotes and then surround the entire selection formula in double quotes like this:
"{file.STATE} = 'CA'"
     
·     If you have created a selection formula in your report at design time, any selection formula you enter here will be appended to that selection formula. Thus, your records will be selected based on a combination of the two selection formulas.
Availability
Design time; Runtime


-----------------------

As to your other problem, I would think that you are not getting your records because you are not refreshing the report correctly.  The best way to deal with this is to treat the refreshed report as a new report and go through all the steps involved in creating it again.  This should gaurantee that you are doing everything correctly.  

Hope this helps.

Cheers,

Ed.
0
 

Author Comment

by:kirtirani
ID: 7053308
Hi ELmo,
which online book u r refering for? can u give me the site?

I am generating daily patients report so user can enter the date to generate the report. For that i have created a parameter in crystal report.
Now the problem is when I refresh the report from the crystal report and enter any date its working fine. but when I am calling it thru my VB appl using crystal report control, and while refreshing I enter the date, the report is not refreashing and even its not giving anything.

Kirti
0
 

Author Comment

by:kirtirani
ID: 7053312
Hi ELmo,
which online book u r refering for? can u give me the site?

I am generating daily patients report so user can enter the date to generate the report. For that i have created a parameter in crystal report.
Now the problem is when I refresh the report from the crystal report and enter any date its working fine. but when I am calling it thru my VB appl using crystal report control, and while refreshing I enter the date, the report is not refreashing and even its not giving anything.

Kirti
0
 

Author Comment

by:kirtirani
ID: 7053333
Hi ELmo,
which online book u r refering for? can u give me the site?

I am generating daily patients report so user can enter the date to generate the report. For that i have created a parameter in crystal report.
Now the problem is when I refresh the report from the crystal report and enter any date its working fine. but when I am calling it thru my VB appl using crystal report control, and while refreshing I enter the date, the report is not refreashing and even its not giving anything.

Kirti
0
 
LVL 3

Expert Comment

by:Elmo_
ID: 7053368
Kirti,

I am refering to the Book that is supplied with Visual Basic.  I am using Visual Basic 5.  It should be in the Visual Basic Help menu and also the Visual Basic Start Menu - "Books Online..."

I would thinik that the problem you are having with the report refreshing without any data is that you are not passing the Parameter properly and the Report is quering the database with bad parameters.

What version of VB are you using?

Ed.
0
 
LVL 3

Expert Comment

by:Elmo_
ID: 7095768
Kirti,

Any Update here?

Ed.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now