How to put two subreports in a report?

[lotwal01]

I use CR 8.5, VB 6 and SQL server. I have a report with a subreport in it.I use TTX files in the report and use VB to fill and send the recordsets to the report. I have two recordsets.

it works just fine but the problem is that I have to add one more subreport and I don't know how to change the code because it was an example code I found on the Internet. How can I change my existing code so it will work with a third recordset too? I have tried to put the two "subreport recordsets" together but that doesn't work the way I want it to. Here is my code, is there someone who can help me??

Dim objConn As New ADODB.Connection
Dim CRXApplication As New CRAXDRT.Application

Private Sub Command1_Click()
Dim CRXDatabase As CRAXDRT.Database
Dim CRXDatabaseTables As CRAXDRT.DatabaseTables
Dim CRXDatabaseTable As CRAXDRT.DatabaseTable
Dim CRXSections As CRAXDRT.Sections
Dim CRXSection As CRAXDRT.Section
Dim CRXReportObjs As CRAXDRT.ReportObjects
Dim CRXSubReportObj As CRAXDRT.SubreportObject
Dim CRXSubreport As CRAXDRT.Report
Dim CRXReport As CRAXDRT.Report

Dim intNo As Integer
Dim strSQL As String
Dim strSQL2 As String
Dim x As Integer
Dim y As Integer
Dim rs As ADODB.Recordset
Dim rs2 As ADODB.Recordset


intNo = 9

'Open the report.
Set CRXReport = CRXApplication.OpenReport("\report.rpt", 1)

strSQL = "SELECT data FROM table WHERE something = '" & intNo & "'"

strSQL2 = "SELECT data2 FROM table2 WHERE something = '" & intNo & "'"

strConnString = "Provider=SQLOLEDB;Data Source=***;Initial Catalog=***; User Id=***;Password=***"
objConn.Open strConnString

Set rs = objConn.Execute(strSQL)
Set rs2 = objConn.Execute(strSQL2)

'Set the database, database tables and database table for the main report by using the SetDataSource method.
Set CRXDatabase = CRXReport.Database
Set CrXDatabaseTables = CRXDatabase.Tables
Set CrXDatabaseTable = CrXDatabaseTables.Item(1)
CrXDatabaseTable.SetDataSource rs, 3
'Loop through each section and reportobject. When a subreport object is found, set the subreport object to a reportobject.
Set CrXSections = CRXReport.Sections
For x = 1 To CrXSections.Count
Set CrXSection = CrXSections.Item(x)
Set CrXReportObjs = CrXSection.ReportObjects

For y = 1 To CrXReportObjs.Count
If CrXReportObjs.Item.Kind = crSubreportObject Then
Set CRXSubReportObj = CrXReportObjs.Item
Set CrXSubreport = CRXSubReportObj.OpenSubreport
'Set the database, database tables and database table for the subreport by using the SetDataSource method
Set CRXDatabase = CrXSubreport.Database
Set CrXDatabaseTables = CRXDatabase.Tables
Set CrXDatabaseTable = CrXDatabaseTables.Item(1)
CrXDatabaseTable.SetDataSource rs2, 3
End If
Next
Next

CRXReport.ExportOptions.DiskFileName = "report.pdf"
CRXReport.ExportOptions.DestinationType = crEDTDiskFile
CRXReport.ExportOptions.FormatType = crEFTPortableDocFormat
CRXReport.Export False

Set rs = Nothing
Set rs2 = Nothing

Set objConn = Nothing

End Sub

 

[olichap]

Lotwal01,

First I'll say this: if you can, you might want to consider using stored procedures instead of TTX files in the future. I've found this to be much easier and more efficient.

Here is an example of how I populate a report with 4 sub-reports. I am using stored procs and haven't so you may have to play with syntax a bit.

Private Sub SetReportVarsAllH(Optional ByVal ParNPar As Boolean)

'Report is already set to its necessary value
Dim sClient As String
Dim iint As Integer
Dim rptSubRpt As New CRAXDRT.Report

sClient = sClientName
If ParNPar = False Then
sClient = sClient & " (Par)"
Else
sClient = sClient & " (Non-Par)"
Report.Database.Tables(1).Location = "SQL.dbo.Proc(rptSavHospSum_pg1NPar;1)"
End If
For iint = 2 To 5
Set rptSubRpt = Report.OpenSubreport("rptSavSumm" & iint)

'you are explicitly opening the named sub-report instead of looking for it in the sections. Then it's easier to deal with setting variables.

With rptSubRpt
If ParNPar = True Then
.Database.Tables(1).Location = "SQL.dbo.Proc(rptSavSum_pg" & iint & "NPar;1)"
End If
'setting rpt parameter values-stored procedure variables
.ParameterFields(1).AddCurrentValue (sClient)
.ParameterFields(2).AddCurrentValue (txtCodeVar.Text)
.ParameterFields(3).AddCurrentValue (dteFromTo(0).Value)
.ParameterFields(4).AddCurrentValue (dteFromTo(1).Value)
.ParameterFields(5).AddCurrentValue (chkUmbrella.Value)
End With
Next iint
Report.EnableParameterPrompting = False
End Sub

Let me know if this helps (or doesn't, I'll be happy to try and clarify something).

Just out of curiosity, have you gotten the export process to work yet through code to PDF? At some point in the future I plan to implement this but have not yet had the need. When playing with the code in passing I was not able to get the syntax down.

Oliver

 

[lotwal01]

Hi,
thanks for your answer. No, I don't want to use stored procedures because the rest of my application doesn't. I'd rather have it all in my dll files. Do you have another solution to my problem?

The pdf export works great!

/Lotta

 

[olichap]

Are your sub-reports not getting the data from you recordset? What exactly ISN't happening?

 

[lotwal01]

I didn't wrote that it didn't work. Right now it work but I have to add one more recordset and I don't know HOW with the code I have...