We help IT Professionals succeed at work.

Editing SQL query statement in subreport

zerobro
zerobro asked
on
Hi Experts,

In crystal 8 or 8.5, is there a way to edit the subreport's query at runtime?
If this can be done, please show me a VB or Delphi code on how to do this.

TIA,
zerobro
Comment
Watch Question

Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Yes it can be done.
the VB Crystal OCX Object has a property called SubReportToChange.  

CRReport.SubReportToChange = "YOUR SUBREPORT.RPT"
CRReport.ReplaceSelectionFormula = "SELECT WHATEVER"

If you need more than this I can provide it but it is on a different machine without internet access.

good luck
mlmcc

Author

Commented:
Hi mlmcc,

You mean with CRReport.ReplaceSelectionFormula property, I can edit the subreport's SQL query? The "SELECT....FROM" statement? This is the one I need to edit. I need to modify the FROM area of the subreport's query.

zerobro
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
The ReplaceSelectionFormula doesn't really edit the query but rather replaces it when the VB program issues the CRReport.Action = 1 and runs the report.  I do this all the time with the report's query with no problem  I looked at the options VB provided for the CR object and saw you could designate which subreport to change.

Hope this helps.  If you want a complete example let me know and I'll add it at work tomorrow.

good luck
mlmcc

Author

Commented:
Hi mlmcc,

Yes please...
Please show me an sample code lines on how to do this.

Thanks,
zerobro
Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013
Commented:
On the MDI or a form that is always open add

    Dim WithEvents CRReport as CrystalReport

In the load event for the form

    Set CRReport = Controls.Add("Crystal.CrystalReport","CRReport")


To run the report add the following code

dim str_Sel as string

'Select report to print
CRReport.ReportFileName = "FullReportPath\ReportName.rpt"
'Modify the main report selection string
str_Sel = "{ReportViewName.Field} = Value"  (Any valid where clause without the where)

CRReport.ReplaceSelectionFormula  str_Sel

'Select subreport to modify
CRReport.SubreportToChange = "MySubReport.rpt"
'Modify the subreport selectioin formula
str_Sel = "{SubRptView.SubrptField} = Value"
CRReport.ReplaceSelectionFormula  str_Sel

'Setup to preview
CRReport.WindowState = vbMaximized
CRReport.Destination = crptToWindow
CRReport.WindowShowCloseBtn = True
CRReport.WindowShowPrintSetupBtn = True

'Setup to print without preview
CRReport.Destination = crptToPrinter

'Execute the report
CRReport.Action = 1
CRReport.Reset

good luck
mlmcc

Author

Commented:
Hi mimcc...your solution more or less solved my problem...thanks...

Explore More ContentExplore courses, solutions, and other research materials related to this topic.