BusinessObjects Board

Retrieve report's query through VBA

sdk development
#1

Hi,

Is there a way to retrieve report’s query through VBA?

Thanks.

boausdev (BOB member since 2008-08-04)

#2

Since BI4.1 the only supported mechanism for retrieving report SQL is the WebI REST API.

joepeters :us: (BOB member since 2002-08-29)

#3

“Since BI4.1 the only supported mechanism for retrieving report SQL is the WebI REST API.”

That’s true, but you can use REST API from VBA.

SuKA (BOB member since 2018-10-20)

#4

Forgot to mention that the reports are Crystal Reports and not WEBI reports. Is there any options for CR?

boausdev (BOB member since 2008-08-04)

#5

Yes. If the reports are stored in the BI Platform, you’ll use the BI Platform .NET SDK. If the reports are just Crystal reports that you’re using in another application, you’ll use the Crystal Reports for Visual Studio SDK (which does NOT connect to BusinessObjects, so please don’t confuse the two!) Both have the same classes for working with Crystal reports.

The logic for doing this will look something like this:

1a. BI Platform: Log in to the BI Platform using an EnterpriseSession and locate the report(s) by using an InfoStore object to query the CMS. Load the report InfoObject into a ReportDocument object.

1b. No BI Platform: Find the report and load it into a ReportDocument object.

  1. Use the ReportDocument.ReportClientDocument property to get a ReportClientDocument object.

  2. Use the ReportClientDocument.RowSetController property to get the RowSetController for the report.

  3. Use the RowSetController.GetSQLStatement() method to get the query for the main report.

  4. If the report has subreports, use the ReportDocument.Subreports property to get a list of SubreportObject objects.

  5. For each subreport, use SubreportObject.OpenSubreport to get a ReportDocument and repeat steps 2 through 4 to get the query for each.

-Dell

hilfy :us: (BOB member since 2007-04-16)

#6

Thanks for the pointer… but I couldn’t find a reference to use ReportDocument from VBA - do you know which reference object I need to include? I can get to ReportClientDocument, though, by including “Crystal Client Document Library 14.0”

boausdev (BOB member since 2008-08-04)