Passing an (on demand) ADO recordset to a subreport ?

[Former Member]

Hi.

Development applications I use are Visual Basic 6.0 SP6 and Crystal Reports XI R2.

Googleing the internet, Iu2019ve found document with examples regarding using TTX files and

passing an ADO recordset to a subreport using RDC or Crystal Automation Server:

https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/5050904e-911e-2b10-009f-b5c857d0...

Although that document was Crystal Reports 8 related, I guess itu2019s partially or even totally applicable to CR XI procedures.

Both examples show how to pass a recordset to a subreport at startup i.e. when subreport datau2019s known in advance,

but is there a way to pass a recordset to a subreport u201Con demandu201D ?

Example: main report is a list of buyers and subreport is a list of items selected buyer purchased last month. If a report user clicks some buyer, I should query database and pass a query result to a subreport.

[former_member183750 · Accepted solution]

Not sure if I understand the question completely. Do you mean to say that the list the user selects; apple, orange, pear has no place holder in the subreport? E.G.; you are trying to essentially build the report / subreport on the fly (in other words, report creation APIs)?

Ludek

[Former Member · Accepted solution]

Hi.

There are placeholders in the subreport, i.e. Iu2019m not trying to build subreport on the fly.

Type of subreport is u201COn-Demandu201D, datasources for report and subreport are TTX files.

First, I pass ADO recordset containing general data of buyers (name, rank, balance etc.) to main report. Iu2019d like to avoid execution of all subreports at the execution time of the main report because there might be thousands of buyers, but instead to execute a subreport i.e. query a database (selectu2026fromu2026where buyerid=u2026) and pass a recordset to subreport as a reaction to end useru2019s click on some buyer from the main report.

[former_member183750 · Accepted solution]

Well, you will have to know which subreport to populate. Once you know that, you could use the following code:

************************************************************************************************************

Dim subrpt As CRAXDRT.Report

Private Sub Form_Load()

Screen.MousePointer = vbHourglass

Report.Database.Tables.Item(1).SetDataSource rs1, 3

Set subrpt = Report.OpenSubreport("sub1")

subrpt.Database.Tables.Item(1).SetDataSource rs1, 3

CRViewer91.ReportSource = Report

CRViewer91.ViewReport

Screen.MousePointer = vbDefault

End Sub

************************************************************************************************************

Or, you can loop through the main report, looking for the correct subreport:

Dim Report As New CrystalReport1

Dim subReport As SubreportObject

Dim sect As Section

Dim rptObject As Object

For Each sect In Report.Sections

For Each rptObject In sect.ReportObjects

If rptObject.Kind = crSubreportObject Then

Set subReport = rptObject

subReport.BackColor = RGB(255, 0, 255)

Set subReport = Nothing

End If

Next

Next

Ludek

[Former Member · Accepted solution]

There is only 1 subreport, letu2019s call it 'Details'. Iu2019d like to send the data (recordset) to the subreport so that I can display the details. Is there some way to read BuyerID from the main reportu2019s row which u2018Detailsu2026u2019 link/subreport end user clicked?

If so, I could pass apropriate recordset to subreport from DrillOnSubreport event of CR XI viewer:

Private crRpt As CRAXDRT.Report

-

---

Private Sub rptCRViewer_DrillOnSubreport(GroupNameList As Variant, ByVal SubreportName As String, ByVal Title As String, ByVal PageNumber As Long, ByVal Index As Long, UseDefault As Boolean)

Dim oSubRep As CRAXDRT.Report, sSQL As String

Dim AdoRs As New ADODB.Recordset

Dim lBuyerID as Long

lBuyerID = ??? u2018u2026a value from a row of the main report e.g. 5478 if it could be retrievedu2026

Set oSubRep = crRpt.OpenSubreport(SubreportName)

sSQL = u201CEXEC rptGetBuyersDetails u201C & CStr(lBuyerID)

Set AdoRs = DB.Execute(sSQL)

oSubRep.Database.Tables.Item(1).SetDataSource AdoRs, 3

End Sub

Here is a sample of the main report:

BuyerID | Name | Rank | Balance |

-

---

1234 | ACME | 3 | -100.000,00| Details...

5478 | C&C INC | 1 | 820.000,00| Details... <--- clicked by end user

2786 | SOME CO | 2 | 90.000,00| Details...

[former_member200290 · Accepted solution]

Unfortunately you will not be able to do this in any easy way. Through our SDK the subreport object you get is template subreport; you cannot get the individual instance of the currently printing subreport.

I can think of only one way you might accomplish this. If you know that there are going to be just say five fields in the subreport, what you could do is create a User Function Library (UFL - com based dll) with five functions in it. Then in the subreport create five formulas to return the value of those functions.

On the OnDrill event you could then save the data of the five fields "some where" that your UFL functions then could pick up and pass back to the subreport formulas. This is not elegant, though it may work.

You can find a thread on UFLs here:

Trevor