How do you populate sub report using recordset??? 1

[tonioJ]

Hello everyone!

I have a report and almost all of the values are taken from recordset. I am using Field Definition to design my report in Crystal Report 8.5 for ease deployment of the application. I only know how to pass data from recordset to CR with no sub report inside. To pass the values of recordset to report use the code below:

---- sample code start here
dim rs as new ADODB.RECORDSET

rs.open "Select LASTNAME, FIRSTNAME from customer", dbcnn, adOpenStatic, adLockOptimistic, adcmdtext

crpt1.reportfilename = app.path & "\report1.rpt"
Crpt1.SetTablePrivateData 0, 3, rs
Crpt1.action =1

---- end of my sample code

Again, I use the code above to pass the value of recordset to a report with no subreport.

Now, the problem is:
1. How do you pass the value of recordset to subreport of report?
2. Any sample codes for reference?

I need your help badly because I'm running out of time to implement my project. Please help. Thank you in advance.

 

[mjsilver]

I have used this method with 8.5 to crviewer
you can also find the name for each subreport and associate each with a different recordset.

note: make sure your fields in field definitions are order same as colums in view. Use example for Active data.

http://support.crystaldecisions.com/library/kbase/articles/c2002360.asp

Synopsis

A Microsoft Visual Basic application is created using the Report Designer Component (RDC). There are many reports, some of which contain one or more subreports.

At runtime, the subreport's datasource may need to change location. This article will describe how to change the database location for different types of datasources (Native, ODBC, Active Data)

Solution

The following examples will show how subreports can be accessed, opened, and manipulated through code.

*NOTE:
These examples assume a general knowledge of Visual Basic programming and the Report Designer Component automation server. Naming conventions of variables are not assuming any standards, but are simplified for examples.

The database location for a subreport is changed similar to changing the database location for the main report. Before the database location can be changed for a subreport, the subreport must be opened. The following code can be used to access any and all subreport(s) in a particular report.

'Variables declared for this example.
'General Declarations section of a form.
Dim Report As New CrystalReport1
Dim crxTables As CRAXDRT.DatabaseTables
Dim crxTable As CRAXDRT.DatabaseTable
Dim crxSections As CRAXDRT.Sections
Dim crxSection As CRAXDRT.Section
Dim crxSubreportObj As CRAXDRT.SubreportObject
Dim crxReportObjects As CRAXDRT.ReportObjects
Dim crxSubreport As CRAXDRT.Report
Dim ReportObject As Object


'This piece of code can be used in the Form_Load event
'of the startup form, which contains the Smart Viewer

'Get the sections from the Main report
Set crxSections = Report.Sections

'Go through each section in the main report...
For Each crxSection In crxSections
'Get all the objects in this section...
Set crxReportObjects = crxSection.ReportObjects
'Go through each object in the reportobjects for this section...
For Each ReportObject In crxReportObjects
'Find the object which is the SubreportObject
If ReportObject.Kind = crSubreportObject Then
'Found a subreport, now get a hold of it
Set crxSubreportObj = ReportObject
'Open the subreport and treat it as any other report
Set crxSubreport = crxSubreportObj.OpenSubreport

'----------------
'This is where the database location can be changed.
'Read below for specific types of datasources.
'----------------

End If
Next ReportObject
Next crxSection

NATIVE CONNECTION TO PC DATABASE

If the datasource of the subreport is a PC-type database (ex. MS Access), and the report is connecting natively (P2BDAO.DLL), then the following code can be used....

'Get the Tables collection for the subreport
Set crxTables = crxSubreport.Database.Tables
'Get the first table from the Tables collection
Set crxTable = crxTables.Item(1)
'Set the location of the .mdb file
crxTable.Location = "C:\My_Application\My_DB.mdb"

NATIVE CONNECTION TO SQL DATABASE

If the datasource of the subreport is SQL-type (ex. MS SQL Server), and the report is connecting natively (P2SSQL.DLL), then the following code can be used....

`Get the Tables collection for the subreport
Set crxTables = crxSubreport.Database.Tables
`Get the first table from the Tables collection
Set crxTable = crxTables.Item(1)
`Pass the connection information to the report
crxTable.SetLogOnInfo <servername>, <databasename>, <userid>, <password>
`As an option, you could set up a loop that
`goes through each table object, changing its .location
`property to its .name property to eliminate the database
`name and owner prefixes from the connection information.
`crxtables(x).Location = crxtables(x).Name `(optional)



ODBC CONNECTION TO A PC OR SQL DATABASE

If the datasource of the subreport is a PC-type (ex. MS Access) or SQL-type (ex. MS SQL Server), and the report is connecting via ODBC (P2SODBC.DLL), then the following code can be used....

'Get the Tables collection for the subreport
Set crxTables = crxSubreport.Database.Tables
'Get the first table from the Tables collection
Set crxTable = crxTables.Item(1)
'Set the location of the .mdb file
crxTable.SetLogOnInfo <ODBC_DSN>, <databasename>, <userid>, <password>

ACTIVE DATA CONNECTION TO A PC OR SQL DATABASE

If the datasource of the subreport is an active data source (ex. DAO, RDO, ADO, CDO, etc), and the report is connecting using the active data driver (P2SMON.DLL), then the following code can be used....

'Get the Tables collection for the subreport
Set crxTables = crxSubreport.Database.Tables
'Get the first table from the Tables collection
Set crxTable = crxTables.Item(1)
'Set the location of the .mdb file
crxTable.SetDataSource rs, 3

There are many ways that you could access a subreport, and change the database location for a subreport. The above examples are simplified and generic so that they can be used and altered to accommodate and application with any report.

 

[ronaknaik]

Hi,

I have a similar kind of senario. I have a ttx file and i have main report and a subreport accessing the same ttx file as there are many fields. My other development center has given me a VB exe and dat exe invokes the CR by passing a recordset. In this case only one query is passed and my subreport doesnt get any data.

I dont have the source code of the exe. How can i tackle this situation.

Thanks
Manish

 

[tonioJ]

thanks for the reply guys!