Is it possible to display the Report SQL?

[ericb1]

Using CR10, when designing a report, I can use the "Show SQL query" option to display the sql being used in the report.

Now when I put out a report, sometimes b/c they're fairly complex, people question what tables, fields I'm using, etc.

So I was wondering if there is a way to say have a little link or pop-up to automatically display the SQL being used in the report. This way people can see it, and even cut and paste it into something else if they wanted to.

Any help is appreciated, thanks!!

 

[synapsevampire]

I guess you mean from a viewer, so the short answer is no.

I generally create a second Report Header and suppress it, then place a good deal of information in there for the next developer.

So you could conditionally suppress the report header, then based on a parameter, you could include what was in there, inclusive of the SQL you copy and paste into this section.

You could also have a formula containing the SQL and conditionally suppress it as well.

-k

 

[vidru]

If you were to write your own viewer application, then you could output the report's SQLQueryString property to a text box or some other editable field on a form.

-dave

 

[Turkbear]

Hi,
I cannot seem to find that property..What object is it a part of?
Can you post a part of the code you used?

Thanks..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[ericb1]

Thanks all! Looks like Synapsevampire's suggestion is the easiest, to get it in there into a formula or something, then display it when requested.

I didn't know there was a SQLQueryString property, but if there is one, seems like I should be able to call it from the crystal activeX viewer, no?

Thanks again!

 

[vidru]

Using the RDC, the SQLQueryString is a property of the Report object. For a quick and dirty test, here's the code (VB 6), with the output going to the Immediate window:

Private Sub Form_Load()

    Dim crxApp As New CRAXDRT.Application
    Dim crxRpt As CRAXDRT.Report
    
    Set crxRpt = crxApp.OpenReport("C:\ReportFile.rpt")
    [green]
    'Set any necessary login info[/green]
    crxRpt.Database.Tables(1).ConnectionProperties("Password") = "Password"
    [green]
    'Calling SQLQueryString will prompt for parameters, if necessary[/green]
    Debug.Print crxRpt.SQLQueryString

    Unload Me
End Sub

-dave

 

[synapsevampire]

You can't call it from within Crystal, but you can write code to pull almost everything out of a report using an API, such as the RDC.

I assumed that you were not using code, rather just CR.

-k

 

[ericb1]

No, not using code, just CE10 and displaying the reports in the activeX cr viewer.

Thanks!

 

[Turkbear]

Hi,
BTW, synapse , is there a CE object/property/collection ( from CI_INFOOBJECTS, for example) that also contains the Sql code for the report? I cannot seem to find any reference to it for CE published reports and we do not use the RDC .






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Turkbear]

Hi,
Never mind..I finally found it ( sometimes I think they outsource Documentation design to another planet) :
I added one more query to my standard User Interface page.

QueryResult = IStore.Query ("Select SI_PROCESSINFO.SI_RECORD_FORMULA From CI_INFOOBJECTS Where SI_ID = " + ReportID)
qry =  QueryResult.Item(1).PluginInterface.RecordFormula;
response.Write(qry)

To Paraphrase:"The Help you get is proportional to the Help you give.."