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