Crystal report

[TheKnee]

Is there a place where i could find a doc explain how to use a report made with crystal report else then report writer.....

 

[Dukester0122]

Can you give us more information what you want to do?

 

[TheKnee]

i would like to replace the report when you print sop order in with a crystal report insted off report writer

 

[bradth]

Do you have Visual Basic for Applications installed? All you need to do is write a SQL query and save it as a stored procedure, use the stored procedure to create a crystal report, and then on the form you would like the report to be generated from create a print button and use VBA to call the report. If you need help on this, let me know, I've done it many times.

Brad

 

[TheKnee]

Is there a place where i could find a sample of vba script and stored procedure...

Thanks for you help

 

[Dukester0122]

Or get a third-party app like Forms Printer by Accountable.

 

[bradth]

Hopefully this should take care of your problem.

Stored Procedure:

--Whatever you would like to select or have displayed in the crystal report
SELECT A.ITEMNMBR, A.ITEMDESC, B.QUANTITY
FROM IV00101 A INNER JOIN IV00102 B ON A.ITEMNMBR = B.ITEMNMBR
--This will give you the item number and item description from one table and the quantity from another.

Crystal Report VB Code

'Variables to use to connect to database
Dim cn As ADODB.Connection
Dim userinfo As New RetrieveGlobals.retrieveuserinfo 'new object of RetrieveGlobals.retrieveuserinfo type
Dim interCompanyId As String
Dim userId As String
Dim sqlPassword As String
Dim sqlDataSourceName As String
Dim constring As String

Dim rst As ADODB.Recordset
Dim cmd As ADODB.Command
Dim strSQL As String

Public crxApplication As New CRAXDRT.Application
Public crxReport As New CRAXDRT.Report

'**************************************************
' EVENT: btnPrint_Click()
' DESCRIPTION: When user clicks print then a crystal report is brought up
'**************************************************
Private Sub btnPrint_Click()
If crwApplication Is Nothing Then
    Set crwApplication = CreateObject("Crystal.CRPE.Application")
End If

    'Open the SQL connection
    Set cn = New ADODB.Connection
    'Concatenate string to connect to the SQL database
    constring = "Provider=MSDASQL" & _
            ";Data Source=" & sqlDataSourceName & _
            ";User ID=" & userId & _
            ";Password=" & sqlPassword & _
            ";Initial Catalog=" & interCompanyId

    With cn
        .ConnectionString = constring
        .CursorLocation = adUseNone
        .Open
    End With

    Set rst = New ADODB.Recordset
    stritemnumber = VBA.Trim$(Me.ItemNumber)
    strSQL = "execute dbo.STORED_PROCEDURE_NAME" & " '" & stritemnumber & "'"
    rst.Open strSQL, cn, adOpenDynamic, adLockOptimistic, adCmdText
    
    
    Set crxReport = crxApplication.OpenReport("d:\CRSYTALREPORTNAME.rpt", 0)
    crxReport.ParameterFields(1).SetCurrentValue (Me.ItemNumber)
    crxReport.Database.SetDataSource rst, 3, 1
    
    crxReport.DiscardSavedData 'chuck out old data
    crxReport.ReadRecords 'reads the record without actually calling the printer
    'crxReport.PrintOut 'calls the printer
    
    CrystalReportViewer.CRViewer1.ReportSource = crxReport ' set crystal report source
    'set dimension of the form where the crsytal report control is placed
    CrystalReportViewer.Height = 550
    CrystalReportViewer.Width = 750

    'set dimension for crystal report viewer
    CrystalReportViewer.CRViewer1.Top = 0
    CrystalReportViewer.CRViewer1.Left = 0
    CrystalReportViewer.CRViewer1.Height = 500
    CrystalReportViewer.CRViewer1.Width = 700
    CrystalReportViewer.CRViewer1.EnableGroupTree = False 'disable group tree button
    CrystalReportViewer.CRViewer1.EnableRefreshButton = False ' disable refresh button
    
    CrystalReportViewer.CRViewer1.ViewReport 'brings up crystal report in preview mode
    CrystalReportViewer.Show 'show the form where the crystal report control is placed

    rst.Close
    cn.Close
    Set rst = Nothing
    Set cn = Nothing
End Sub

Private Sub UserForm_Initialize()
 
 
 'RetrieveGlobals
    sqlDataSourceName = userinfo.sql_datasourcename()
    userId = userinfo.retrieve_user()
    sqlPassword = userinfo.sql_password()
    interCompanyId = userinfo.intercompany_id()
End Sub

This should hopefully get you in the right direction.

Brad