Crystal Reports
Google
Web www.mahipalreddy.com

 

Opening a crystal report, passing it the Record Selection Formula and displaying it using Visual Basic

'Code by Mahipal Padigela
'Add a Command button to your Form(Form1)
'Add a Form(Form2) to the project and add a 'Crystal Report Viewer Control 9' from Project-->Components to it.
'Reference 'Crystal Reports 9 ActiveX Designer Run Time Library' from Project-->References
'Replace "C:\myCRReport.rpt" with your CR Report file path
'Replace the value of strSelectionfrm variable with your selection formula 
'Paste the following in to the code window of your form

Private Sub Command1_Click()
  Me.MousePointer = vbHourglass
    Dim crApp As CRAXDRT.Application
    Dim Report As CRAXDRT.Report
    Dim strSelectionfrm As String
    Set crApp = New CRAXDRT.Application
    Set Report = crApp.OpenReport("C:\myCRReport.rpt")

'You don't need the next line of code if you are using a MS Access databse, but if you are using
'any other databse then Crystal reports expects you to pass Logon credentials before opening the report. So replace the
'DSN name, Databse name, user name, password with yours. Though the next line of code is passing Logon info to
'only the first table in the Tables array of the report, in theory, it should work fine as crystal passes the same info to any other
'tables in the report.For more info on passing Logon credentials to each and every table in the main report as well as all subreports,
'please refer to my article programmatically exporting Crystal Reports to PDF and other formats

    Report.Database.Tables(1).SetLogOnInfo "DSN name", "Databse name", "user name", "password"

'Replace the value of strSelectionfrm variable with your selection formula 
    strSelectionfrm = "{Expected_Sales.Dept}='Analyst Group' and {Expected_Sales.Qtr}='Qtr1'"
    Report.RecordSelectionFormula = strSelectionfrm
    Report.DiscardSavedData
    Form2.CRViewer91.ReportSource = Report
    Form2.CRViewer91.ViewReport
    Form2.Show
  Me.MousePointer = vbNormal
End Sub

Top

Convert the data saved as seconds in the database to proper Time format(HH:MM:SS) using Crystal syntax in Crystal Reports

//Code by Mahipal Padigela
//Create formula in your Crystal Reports
//Paste the following magic code into the formula window
//Replace '{Your_Table.Seconds_Column}' with your database field having the seconds data

If fix({Your_Table.Seconds_Column}/86400) >= 1  Then //If more than 24 hours....
    totext(truncate({Your_Table.Seconds_Column}/3600),0) & ":" & 
    totext(truncate(({Your_Table.Seconds_Column}-(truncate({Your_Table.Seconds_Column}/3600)*3600))/60),0) & ":" & 
    totext(truncate({Your_Table.Seconds_Column}-(truncate({Your_Table.Seconds_Column}/3600)*3600)-
    (truncate(({Your_Table.Seconds_Column}-(truncate({Your_Table.Seconds_Column}/3600)*3600))/60)*60)),0)
Else //fine, Crystal can handle it....
    ToText(ctime({Your_Table.Seconds_Column}/86400))

Top

Accessing / Modifying a crosstab in Crystal Reports at runtime using Visual Basic - RDC Programming

Cross-tabs are specialized subreports.CrossTabObject objects can be obtained from a report much like subreports. A CrossTabObject is implemented as a single report object accessible through the ReportObjects collection.
The following code opens a Crystal report saved on the disk, loops through all the sections, and all the objects in each section, then tests to see if they are of the type Crosstab and applies formatting features to make them stand out from the rest of the report.


'Reference Crystal Reports9 ActiveX Designer Runtime Library
'Add a Command Button and a Crystal Report9 Viewer Control to your Form
'Replace C:\crtests\CrossTab.rpt with your Report file path
'Paste the following code in the Form code window

Private Sub Command1_Click()
    Dim CRXApp As CRAXDRT.Application
    Dim CRXReport As CRAXDRT.Report
    Dim CRXCrossTab As CRAXDRT.CrossTabObject
    Dim CRXSection As Section
    Dim CRXReportObject As Object
    
    Set CRXApp = New CRAXDRT.Application
    Set CRXReport = CRXApp.OpenReport("C:\crtests\CrossTab.rpt")
    
    For Each CRXSection In CRXReport.Sections
        For Each CRXReportObject In CRXSection.ReportObjects
            If CRXReportObject.Kind = crCrossTabObject Then
                Set CRXCrossTab = CRXReportObject
                CRXCrossTab.BorderColor = RGB(255, 0, 0)
                CRXCrossTab.HasDropShadow = True
                Set CRXCrossTab = Nothing
            End If
        Next
    Next
   
    CRViewer91.ReportSource = CRXReport
    CRViewer91.ViewReport
    
    Set CRXReportObject = Nothing
    Set CRXSection = Nothing
    Set CRXReport = Nothing
    Set CRXApp = Nothing
End Sub

Top

Referencing / accessing / changing /editing a Formula value or Formula text in crystal reports using RDC Programming in Visual Basic How do I access FormulaFieldDefinitions in Crystal reports using RDC Programming in VB

The following code sample opens a crystal report, loops through all of the FormulaFieldDefinitions and searches for a perticular formula to change it's text. It shows how to reference a report formula at runtime using the formula name instead of referencing the formula by index.


'Reference Crystal Reports9 ActiveX Designer Runtime Library
'Add a Command Button and a Crystal Report Viewer Control to your Form
'Replace C:\crtests\CrossTab.rpt with your Report file path
'Create a formula in your crystal report called  Mahipal_Formula and enter " " into it
'Place the formula some where on the report......like report header
'Paste the following code in the Form code window

Private Sub Command1_Click()
    Dim CRXApp As CRAXDRT.Application
    Dim CRXReport As CRAXDRT.Report
    Dim CRXFormulaFields As CRAXDRT.FormulaFieldDefinitions
    Dim CRXFormulaField As CRAXDRT.FormulaFieldDefinition
    
    Set CRXApp = New CRAXDRT.Application
    Set CRXReport = CRXApp.OpenReport("C:\crtests\Report1.rpt")
    Set CRXFormulaFields = CRXReport.FormulaFields
    
    For Each CRXFormulaField In CRXFormulaFields
        If CRXFormulaField.Name = "{@Mahipal_Formula}" Then
            CRXFormulaField.Text = "'www.mahipalreddy.com'"  'see the single codes...
			'use this if you want to assign another field......."{Customer.City}" 
        End If
    Next
    
    CRViewer91.ReportSource = CRXReport
    CRViewer91.ViewReport
    
    Set CRXFormulaFields = Nothing
    Set CRXFormulaField = Nothing
    Set CRXReport = Nothing
    Set CRXApp = Nothing
End Sub

Top

programmatically adding a new group to the Crystal reports using RDC Programming in Visual Basic.

The following code sample adds two groups to the Crystal report, the first being City field and the other being Region field.
The code assumes that you added Customer table from the extreme sample database


'Reference Crystal Reports9 ActiveX Designer Runtime Library
'Add a Command Button and a Crystal Report Viewer Control to your Form
'Create a crystal report with extreme sample database as your data source and add Customer table to the report
'Add Customer name, City and Region fields to the Report
'Replace C:\crtests\Report1.rpt with your Report path
'Paste the following code in the Form code window

Private Sub Command1_Click()
    Dim CRXApp As CRAXDRT.Application
    Dim CRXReport As CRAXDRT.Report
    Dim CRXDBField As CRAXDRT.DatabaseFieldDefinition
    
    Set CRXApp = New CRAXDRT.Application
    Set CRXReport = CRXApp.OpenReport("C:\crtests\Report1.rpt")
	
	'add the Region Field
    Set CRXDBField = CRXReport.Database.Tables(1).Fields(12)	'Region is the 12 th field in Customer table
    CRXReport.AddGroup 0, CRXDBField, crGCAnyValue, crAscendingOrder	
    'GroupNumber is 0 for the first group
    'CRXDBField is the ConditionField
    'crGCAnyValue is the condition on which the group changes
    'crAscendingOrder is the SortDirection
	
	'Now add the City Field    
    Set CRXDBField = CRXReport.Database.Tables(1).Fields(11)  'City is the 11 th field in Customer table
    CRXReport.AddGroup 1, CRXDBField, crGCAnyValue, crAscendingOrder
    'Now view the report
    CRViewer91.ReportSource = CRXReport
    CRViewer91.ViewReport
    
    Set CRXDBField = Nothing
    Set CRXReport = Nothing
    Set CRXApp = Nothing
End Sub

Top

programmatically modifying a group's sort direction at runtime in Crystal reports using Visual Basic


'Reference Crystal Reports9 ActiveX Designer Runtime Library
'Add a Command Button and a Crystal Report9 Viewer Control to your Form
'Replace "C:\crtests\Report1.rpt" with your Report file path
'Paste the following code in the Form code window
Private Sub Command1_Click()
    Dim CRXApp As CRAXDRT.Application
    Dim CRXReport As CRAXDRT.Report

    Set CRXApp = New CRAXDRT.Application
    Set CRXReport = CRXApp.OpenReport("C:\crtests\Report1.rpt")
    'The Item parameter "GH1" refers to the first group header
	'to access the second group header you would use "GH2"
  
    CRXReport.Areas.Item("GH1").SortDirection = crDescendingOrder
    
    CRViewer91.ReportSource = CRXReport
    CRViewer91.ViewReport

    Set CRXReport = Nothing
    Set CRXApp = Nothing
End Sub

Top

www.mahipalreddy.com
Terms and Conditions of Use
Copyright 2004 - 2006 Mahipal Padigela. All rights reserved.