• Home
• About  me
• Knowledge Bank
• Guestbook
• Photo Album
• Contact  Me

How do I programmatically check the syntax of Formulas in Crystal Reports with VB?
How do I programmatically check the Formulas for errors in Crystal Reports?
How do I programmatically loop through all the Formulas in Crystal Reports and get their text using visual basic?

Introduction

The article shows you how to access formulas in crystal reports, check their syntax and get the error message to you if there is any error using CRAXDRT object library that comes with Crystal reports installation.

Library References

Crystal Reports software comes with Crystal Reports ActiveX Designer Run Time Library which provides the necessory objects to do this. Though I've used version 9.0 objects in the article, it should work with version 8/8.5/9/10/XI without any trouble, I think. So, before getting started, you need to reference this object library to your VB Project.

Decalrations

First, let's declare the Object variables to hold the Crystal Application and report objects

Dim CrApp As CRAXDRT.Application
Dim CrRep As CRAXDRT.Report
Dim strError As String  'String to hold the error message returned by the formula checker
Dim blnHasNoError As Boolean  'Boolean indicating if there is an error in the formula or not
Dim strText As String

Instantiate the Application object and open the Crystal Report from the disk

Set CrApp = New CRAXDRT.Application
Set CrRep = CrApp.OpenReport("C:\crtests\Report1.rpt")

First, check to see if there are any formulas in the given Report.

    If CrRep.FormulaFields.Count = 0 Then
        MsgBox "This Report has no Formula Fields"
    End If

The next few lines of code loop through all the Formulas in the Report, check each formula for syntax errors, and if there is any error then add it to the List box together with the name of the formula. The variable strText contains the formula text, should you need to display it.

The code makes use of the Check method of the FormulaFields collection. It takes two parameters: first one is boolean and tells you if there is an error or not, and the second one returns you the actual error message.

    For i = 1 To CrRep.FormulaFields.Count
        CrRep.FormulaFields(i).Check blnHasNoError, strError
      
        If blnHasNoError = True Then
        'fine, there are no errors
        Else
            List1.AddItem CrRep.FormulaFields(i).FormulaFieldName & " --  " & strError
            strText = CrRep.FormulaFields(i).Text
        End If
    Next i

Finally, Close the recordset and release the references.

    Set CrRep = Nothing
    Set CrApp = Nothing

Full Code

'Add a command button, a List box and a Text box to your form (make them a bit wider)
'Chage the 'Multiline' property of Text box to 'True'
'Reference Crystal Reports 8/8.5/9/10 ActiveX Designer Run time Library
'Paste the following code into the form code window
'Change the path "C:\crtests\Report1.rpt" to suit yours

Dim CrApp As CRAXDRT.Application
Dim CrRep As CRAXDRT.Report
Dim strError As String  'String to hold the error message returned by the formula checker
Dim blnHasNoError As Boolean  'Boolean indicating if there is an error in the formula or not
Dim strText As String
Private Sub Command1_Click()


    blnHasError = False
   
    Set CrApp = New CRAXDRT.Application
    Set CrRep = CrApp.OpenReport("C:\crtests\Report1.rpt")
    
    If CrRep.FormulaFields.Count = 0 Then
        MsgBox "This Report has no Formula Fields"
        Exit Sub
    End If

    For i = 1 To CrRep.FormulaFields.Count
        CrRep.FormulaFields(i).Check blnHasNoError, strError
        
        If blnHasNoError = True Then
            ' fine, there are no errors
        Else
            List1.AddItem CrRep.FormulaFields(i).FormulaFieldName & " --  " & strError
            strText = CrRep.FormulaFields(i).Text
        End If
    Next i

    Set CrRep = Nothing
    Set CrApp = Nothing

End Sub
'Dispay formula Text in the text box when an item is clicked in the list box
Private Sub List1_Click()
    Text1.Text = strText
End Sub

Top