|
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
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))
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
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
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
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
|
