Crystal Reports

How to pass parameters from VB to crystal reports using CRAXDRT?
How to pass parameters to crystal reports Programatically ?
Passing parameters to Crystal report using VB / RDC / CRAXDRT?

 

Introduction

You often need to pass parameters from your visual basic application to crystal reports before generating it. This article looks at passing various types of parameters to crystal reports programatically using CRAXDRT library objects. The article also shows you how to access crystal report parameters by their names rather than by their index positions.

For the purpose of this article, I 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 to your VB Project. And also add Crystal Report Viewer Control control to your form if you want to view your report from VB.

Parameter Types

The types of parameters you can create in crystal reports are:

Single valued Parameters
Multi valued Paramets
Range Parameters

These can be of a String, Number, Currency and DateTime datatype.

Let's CODE

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

 

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

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

 

Before passing any parametes to the Report, first discard any saved data with it, otherwise you may not see the results refreshed

CrRep.DiscardSavedData

 

String Parameters

For ex. I have a parameter called City which is a string parameter and allows only a single value.
This is how you would pass a value to it.

    CrRep.ParameterFields.GetItemByName("City").AddCurrentValue "London"

 

If the City were a multi valued parameter i.e which allows multiple values to pass, this is how you would pass a value to it.

    CrRep.ParameterFields.GetItemByName("City").AddCurrentValue "London"
    CrRep.ParameterFields.GetItemByName("City").AddCurrentValue "Sydney"
    CrRep.ParameterFields.GetItemByName("City").AddCurrentValue "Hong Kong"

 

Number Parameters

This is how you would pass a value to a number parameter that takes a single value

    CrRep.ParameterFields.GetItemByName("custid").AddCurrentValue 100

 

If it were a number range parameter, this is how you would pass a value to it

   'custid parameter is a number range parameter.
   'We use the rangeInfo enumerated constants to include lower bound and uppper bound values.
	CrRep.ParameterFields.GetItemByName("custid").AddCurrentRange 1, 10, _
	 crRangeIncludeLowerBound + crRangeIncludeUpperBound
   'So the report returns all customers whose custid is between 1 and 10 

 

Currency Parameters

This is how you would pass a value to a currency parameter

   CrRep.ParameterFields.GetItemByName("sales").AddCurrentValue 4500

 

Date and Date Range Parameters

This is how you would pass a value to a Date parameter

   'today is a  DateTime parameter, so we are passing current date time to it
   CrRep.ParameterFields.GetItemByName("today").AddCurrentValue Now()

 

If it were a Date Time range parameter, this is how you would pass a value to it

   ' Hire Date parameter is a date-time range parameter.
   ' we use the rangeInfo enumerated constants to include lower bound and uppper bound values
    CrxSubreport.ParameterFields.GetItemByName("Hire Date").AddCurrentRange CDate("9/20/1991 12:00:00 AM"), _
	 CDate("9/20/1993 12:00:00 AM"), crRangeIncludeLowerBound + crRangeIncludeUpperBound
   'so, crRangeIncludeLowerBound + crRangeIncludeUpperBound tells the report to return the rows including the start and End dates specified

 

Now assign the Report to the Viewer control and preview it.

    CRViewer91.ReportSource = CrRep
    CRViewer91.ViewReport

 

Finally, release the memory by setting all the object reference to nothing

    Set CrRep = Nothing
    Set CrApp = Nothing

 

Full Code

'How to pass parameters from VB to crystal reports using CRAXDRT? 
Private Sub Command1_Click() Dim CrApp As CRAXDRT.Application Dim CrRep As CRAXDRT.Report Set CrApp = New CRAXDRT.Application Set CrRep = CrApp.OpenReport("C:\test\Report1.rpt") 'Discard saved data CrRep.DiscardSavedData 'City is a string parameter which allows multiple values. CrRep.ParameterFields.GetItemByName("City").AddCurrentValue "London" CrRep.ParameterFields.GetItemByName("City").AddCurrentValue "Sydney" CrRep.ParameterFields.GetItemByName("City").AddCurrentValue "Hong Kong" 'custid is a number range parameter. CrRep.ParameterFields.GetItemByName("custid").AddCurrentRange 1, 10, _ crRangeIncludeLowerBound + crRangeIncludeUpperBound 'sales is a currency parameter. CrRep.ParameterFields.GetItemByName("sales").AddCurrentValue 4500 'today is date parameter. This is a date parameter that uses the Date function. CrRep.ParameterFields.GetItemByName("today").AddCurrentValue Now() 'Hire Date is a date-time range parameter. 'CrRep.ParameterFields.GetItemByName("Hire Date").AddCurrentRange CDate("9/20/1991 12:00:00 AM"), _ CDate("9/20/1993 12:00:00 AM"), crRangeIncludeLowerBound + crRangeIncludeUpperBound CRViewer91.ReportSource = CrRep CRViewer91.ViewReport 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.