BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Follow BOB on Twitter! 
Follow BOB on Twitter! (Opens a new window)  

General Notice: No events within the next 45 days.

Retrieve report's query through VBA


 
Search this topic... | Search SDK (VBA/ASP/JSP)... | Search Box
Register or Login to Post    Forum Index -> SDK (VBA/ASP/JSP)  Previous TopicPrint TopicNext Topic
Author Message
boausdev
Forum Member
Forum Member



Joined: 04 Aug 2008

Posts: 22



PostPosted: Wed Oct 31, 2018 7:34 pm 
Post subject: Retrieve report's query through VBA

Hi,

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

Thanks.
Back to top
joepeters
Forum Fanatic
Forum Fanatic



Joined: 29 Aug 2002

Posts: 6464
Location: Connecticut, USA


flag
PostPosted: Thu Nov 01, 2018 7:09 am 
Post subject: Re: Retrieve report's query through VBA

Since BI4.1 the only supported mechanism for retrieving report SQL is the WebI REST API.
Back to top
SuKA
Forum Member
Forum Member



Joined: 20 Oct 2018

Posts: 3



PostPosted: Thu Nov 01, 2018 9:42 am 
Post subject: Re: Retrieve report's query through VBA

"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.
Back to top
boausdev
Forum Member
Forum Member



Joined: 04 Aug 2008

Posts: 22



PostPosted: Mon Nov 05, 2018 12:49 am 
Post subject: Re: Retrieve report's query through VBA

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



Joined: 16 Apr 2007
ASUG Icon
speaker.gif*3
Posts: 1855
Location: Atlanta, GA


flag
PostPosted: Thu Nov 29, 2018 10:28 am 
Post subject: Re: Retrieve report's query through VBA

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.

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

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

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

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

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

-Dell

_________________
Protiviti
Data & Analytics Practice
https://www.protiviti.com/US-en/data-management-advanced-analytics
Back to top
boausdev
Forum Member
Forum Member



Joined: 04 Aug 2008

Posts: 22



PostPosted: Mon Dec 03, 2018 1:09 am 
Post subject: Re: Retrieve report's query through VBA

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"
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> SDK (VBA/ASP/JSP)  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0332 seconds using 17 queries. (SQL 0.0029 Parse 0.0010 Other 0.0293)
CCBot/2.0 (https://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo