Crystal Reports

How do I programmatically update the default values list of parameters in Crystal Reports with VB?
How Do I programmatically add default values to the parameters in Crystal Reports with VB

 

Introduction

Crystal reports doesn't update the default values list of parameters automatically when you open it. Not even when you refresh the Report. One way of updating the list is, opening each Report in the Crystal Report Designer, edit each parameter, set default values and update the list. But if you have many reports running through your VB application and your database changes everyday, it might be cumbersome to do it every so often. So, how nice it would be if you could check and update the parameter list automatically before opening a Report? sounds good to you? read on.....

The article shows you how to update/delete/add default values to a parameter using CRAXDRT object library that comes with Crystal reports.
The article also shows how to access crystal report parameters by their names rather than by their index positions.

For the purpose of this article, I'll use Extreme Sample Database that comes with Crystal reports.

 

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 and Microsoft ActiveX Data Objects 2.x Library to your VB Project. And also add Crystal Report Viewer Control control to your form if you want to view your report from VB.

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 rs As ADODB.Recordset ' we need this to get the list of cities from the database

 

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

 

Delete defaul values list

First, delete the default values list for the given parameter, City in this example
We loop through the default values list and delete them all. To access a parameter by it's name, we make use of the GetItemByName() method of ParameterFields collection of the Report Object.

    Do Until CrRep.ParameterFields.GetItemByName("City").NumberOfDefaultValues = 0
        CrRep.ParameterFields.GetItemByName("City").DeleteNthDefaultValue (1)
    Loop

 

Should you need to access the Parameter by it index position..... use this.

    Do Until CrRep.ParameterFields(1).NumberOfDefaultValues = 0
        CrRep.ParameterFields(1).DeleteNthDefaultValue (1)
    Loop

 

Access the database

Now, get the latest values from the customer table for the City parameter. Check to see if you have a DSN called "Xtreme Sample Database 9" (Crystal automatically creates it for you when you install. You may have a different number at the end depending on your version)

	set rs = new ADODB.Recordset
    rs.Open "Select distinct City from Customer", "Xtreme Sample Database 9"

 

Add default values to the parameter

Now, loop through the recordset and add values to the default values list of the parameter

    Do Until rs.EOF = True
     CrRep.ParameterFields.GetItemByName("City").AddDefaultValue rs.Fields("City").Value
     rs.MoveNext
    Loop

 

Preview the Report

    CRViewer91.ReportSource = CrRep
    CRViewer91.ViewReport

 

Finally, Close the recordset and release the references.

    rs.Close
    Set rs = Nothing
    Set CrRep = Nothing
    Set CrApp = Nothing

 

 

Full Code

Private Sub Command1_Click()
    Dim CrApp As CRAXDRT.Application
    Dim CrRep As CRAXDRT.Report
    Dim rs As New ADODB.Recordset

    Set CrApp = New CRAXDRT.Application
    Set CrRep = CrApp.OpenReport("C:\crtests\Report1.rpt")
    
    'First, delete the default values list for the given parameter
    Do Until CrRep.ParameterFields.GetItemByName("City").NumberOfDefaultValues = 0
        CrRep.ParameterFields.GetItemByName("City").DeleteNthDefaultValue (1)
    Loop
    'get the new values from the customer table for the City parameter
    rs.Open "Select distinct City from Customer", "Xtreme Sample Database 9"
    
    'Now add values to the list
    Do Until rs.EOF = True
     CrRep.ParameterFields.GetItemByName("City").AddDefaultValue rs.Fields("City").Value
     rs.MoveNext
    Loop
    
    'Preview the report
    CRViewer91.ReportSource = CrRep
    CRViewer91.ViewReport
   
   'Release the references
    rs.Close
    Set rs = Nothing
    Set CrRep = Nothing
    Set CrApp = Nothing
End Sub

Top

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