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

Register | Login 

Want to sponsor BOB? 
Want to sponsor BOB? (Opens a new window)  

General Notice: No events within the next 45 days.

List objects used in Web Intelligence reports
9 members found this topic helpful
Goto page Previous  1, 2, 3, 4, 5, 6, 7, 8, 9, 10  Next
 
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
hilfy
Forum Enthusiast
Forum Enthusiast



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


flag
PostPosted: Fri Jul 01, 2016 8:18 am 
Post subject: Re: List objects used in Web Intelligence reports

You can check the sample code that's available on SCN:

Java SDK: http://scn.sap.com/community/bi-platform/java-sdk
.NET SDK: http://scn.sap.com/community/bi-platform/microsoft-net-sdk
RESTful Web Services: http://scn.sap.com/community/restful-sdk

All of these have links to sample code on the left as you scroll down.

-Dell

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



Joined: 29 Jun 2016

Posts: 6



PostPosted: Thu Jul 07, 2016 10:37 am 
Post subject: Re: List objects used in Web Intelligence reports

Hello again Hilfy! Using the incredible helpful information you have me before I believe I've created a rough draft of the code necessary to accomplish my task,or I completely lost.(It's kind of hard to tell at this point crazy.gif ) . Either way there are a couple questions I have about the following code.

1:in the instance in which I'm getting the si_id " IInfoObjects infoObjects = (IInfoObjects) infoStore.query(query);..."
whenever I'm pulling into the variable "doc" am I only pulling the si_id or everything in the query (si_name and si_id)

2.How would I loop the call of the doc variable in the raylight url so that I could get the query for all the si_id's in one document
-would the the export be broken down by si_id


Code:
public class Program {
   public static void main(String[] args) {
      IEnterpriseSession enterpriseSession = null;
      ReportEngines reportEngines = null;
      try {
         System.out.println("Connecting...");
         ISessionMgr sessionMgr = CrystalEnterprise.getSessionMgr();
         enterpriseSession = sessionMgr.logon("10152569",
               "password", "localhost", "secWindAD");
         reportEngines = (ReportEngines) enterpriseSession
               .getService("ReportEngines");
         ReportEngine wiRepEngine = (ReportEngine) reportEngines
               .getService(ReportEngines.ReportEngineType.WI_REPORT_ENGINE);         

         IInfoStore infoStore =
               (IInfoStore) enterpriseSession.getService("InfoStore");
         String query = "select SI_NAME, SI_ID from CI_INFOOBJECTS "
                      + "where SI_KIND = 'Webi' and SI_INSTANCE=0";
         IInfoObjects infoObjects = (IInfoObjects) infoStore.query(query);
         for (Object object : infoObjects) {
            IInfoObject infoObject = (IInfoObject) object;
               DocumentInstance widoc = wiRepEngine.openDocument(infoObject.getID());
               String doc = infoObject.getTitle();
               System.out.println(doc);               
               printDocumentVariables(widoc);
               widoc.closeDocument();
         }
     
      }catch (SDKException ex) {
         ex.printStackTrace();
      }
      finally {
         if (reportEngines != null)
            reportEngines.close();
         if (enterpriseSession != null)
            enterpriseSession.logoff();
      }
      System.out.println("Finished!");
   
    try {
   URL url=new URL("http://localhost/biprws/raylight/v1/documents/"+doc+"/dataproviders/DP0/flows/0");
      httpURLConnection conn=(HtpURLConnection)url.openConnection();
      conn.setrequestMethof("get");
        conn.setRequestProperty("Accept","text/plain");
       
    BufferedReader br=new BufferedReader(new InputStreamReader(
         (connn.getInputStream())));
      string output;
      file f1=new File("C://query_export.csv");
      f1.createNewFile();
      filewriter fw=new filewriter(f1);
    format
      BufferedWriter bw=new BufferedWriter(fw);
      while ((output=br.readline()) !=null){
         bw.write(output);
         bw.write("\n");
      }
      bw.close();
      conn.disconnect();
   }catch (MalformedURLException e){
       e.printStackTrace();
   }catch (ProtocolException e){
       e.printStackTrace();
   }catch (IOException e){
       e.printStackTrace();
   }catch (ParseException e) {
       e.printStackTrace();
   }
    }
}   


Any other suggestions or advice would be very helpful and appreciated!
Thank You
Back to top
hilfy
Forum Enthusiast
Forum Enthusiast



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


flag
PostPosted: Thu Jul 07, 2016 12:49 pm 
Post subject: Re: List objects used in Web Intelligence reports

1. When you call the InfoStore query, you're getting the fields that you requested - both SI_ID and SI_NAME. If you didn't, then the call to infoObject.getTitle() would not work.

2. Not all documents have a "DP0". Instead, you need to first call http://localhost/biprws/raylight/v1/documents/"+doc+"/dataproviders" to get the list of providers. Parse that XML and for each data provider you would then call http://localhost/biprws/raylight/v1/documents/"+doc+"/dataproviders/" + DataProvider_ID. This will give you all of the universe objects that are used in the report along with the SQL that the report uses.

Also, you need to include the calls to the RESTful SDK inside the loop where you're getting the SI_ID values - the "doc in '"...\documents\" + doc' is the SI_ID value, not the report name.

A couple of other things:

a. I don't see that you're ever logging in to the RESTful web service - you can't send any requests if you don't get the "X-SAP-LogonToken" value and put it in your request header.

b. The only valid values for the "Accept" header are "application/xml" and "application/json" - "plain/text" won't work.

c. I'm not sure that the "flows" option will get you what you're looking for. Instead, end the URL after the ID of the data provider and use the "query" element of the XML to get the SQL.

-Dell

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



Joined: 29 Aug 2002

Posts: 6622
Location: Connecticut, USA


flag
PostPosted: Thu Jul 07, 2016 1:36 pm 
Post subject: Re: List objects used in Web Intelligence reports

hilfy wrote:
b. The only valid values for the "Accept" header are "application/xml" and "application/json" - "plain/text" won't work.

c. I'm not sure that the "flows" option will get you what you're looking for. Instead, end the URL after the ID of the data provider and use the "query" element of the XML to get the SQL.

It looks like that chunk of code (from URL url down) is intended to extract the query results. Using "text/plain" with "/flows" would be appropriate for that. But you are correct that /queryplan is the appropriate call to get the query's SQL.

Joe
Back to top
izzino
Forum Member
Forum Member



Joined: 29 Jun 2016

Posts: 6



PostPosted: Fri Jul 08, 2016 8:37 am 
Post subject: Re: List objects used in Web Intelligence reports

Good Morning Dell and Joe, hope all is well! I appreciate the quick responses. I believe I've corrected the Token issue that Hilfy had mentioned for raylight,but some of the other suggestions confused me a little bit.

1.Dell- are you saying that I need to be getting the report name for the variable "doc" that I use later for my raylight URL, or are you saying that it is currently retrieving the report name and I need to be getting the SI_ID.

2.Dell and Joe- If I understand correctly my current code will export a plain text list of the SI_ID of the report along with the the data provider ID associated with them, but not the query information that I need, so what I need to do is change it from plain/text to xml then parse that xml for the dataproviderID and make another request to( .../+doc+"/dataproviders/" + DataProvider_ID") in which I use that dataproviderid pulled from the first request to pull the query and object information for each report

Thanks for Chiming in Joe, I need all the help I can get, and Dell thanks again for coming through if it weren't for you I'd still be banging my head against the keyboard. WE WILL GET THROUGH THIS! haha
Back to top
joepeters
Forum Fanatic
Forum Fanatic



Joined: 29 Aug 2002

Posts: 6622
Location: Connecticut, USA


flag
PostPosted: Fri Jul 08, 2016 9:00 am 
Post subject: Re: List objects used in Web Intelligence reports

izzino wrote:
2.Dell and Joe- If I understand correctly my current code will export a plain text list of the SI_ID of the report along with the the data provider ID associated with them, but not the query information that I need, so what I need to do is change it from plain/text to xml then parse that xml for the dataproviderID and make another request to( .../+doc+"/dataproviders/" + DataProvider_ID") in which I use that dataproviderid pulled from the first request to pull the query and object information for each report

Yes, you first need to call:
"http://localhost/biprws/raylight/v1/documents/"+doc+"/dataproviders"
with Accept set to "application/xml"
This will give you a result like the following:
Code:
<dataproviders>
<dataprovider>
<id>DP0</id>
<name>Query 1</name>
<dataSourceId>6187</dataSourceId>
<dataSourceType>unv</dataSourceType>
<updated>2006-09-20Z</updated>
</dataprovider>
<dataprovider>
<id>DP1</id>
<name>Query 2</name>
<dataSourceId>6191</dataSourceId>
<dataSourceType>unx</dataSourceType>
<updated>2014-04-23T09:08:20.000+02:00</updated>
</dataprovider>
<dataprovider>
<id>DP2</id>
<name>Query 3</name>
<dataSourceId>6120</dataSourceId>
<dataSourceType>bex</dataSourceType>
(this is from the REST documentation
then iterate through each <id> to get the details (again using application/xml):

"http://localhost/biprws/raylight/v1/documents/"+doc+"/dataproviders/DP0"
"http://localhost/biprws/raylight/v1/documents/"+doc+"/dataproviders/DP1"
etc.

This will give you the list of objects in the query.
Back to top
hilfy
Forum Enthusiast
Forum Enthusiast



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


flag
PostPosted: Fri Jul 08, 2016 9:07 am 
Post subject: Re: List objects used in Web Intelligence reports

I'm saying that the doc variable is getting the name, not the SI_ID and you need the SI_ID.

Also, it looks like you're looping through the InfoObjects to get the parameters for each report (which you can also get through the RESTful SDK without using the ReportEngine) then, when you call the RESTful SDK, you're only calling it for the last report in the list. The calls to the RESTful SDK need to happen for each report.

Also, please bear in mind that each report may have more than one data provider. So, you can't just get the first one, you may have to go through several of them to get all of the information you're looking for.

-Dell

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



Joined: 29 Jun 2016

Posts: 6



PostPosted: Wed Jul 13, 2016 5:42 pm 
Post subject: Re: List objects used in Web Intelligence reports

Hey Dell and Joe,
Hope all is well! Sorry for the delayed response but my midpoint meeting for my internship is coming up soon so I've been preparing for that and I haven't had a chance to work on my program to much. However, I thought I'd post what I have and open it up for criticism.
Code:
public class Program {
   public static void main(String[] args) {
      IEnterpriseSession enterpriseSession = null;
      ReportEngines reportEngines = null;
      try {
         System.out.println("Connecting...");
         ISessionMgr sessionMgr = CrystalEnterprise.getSessionMgr();
         enterpriseSession = sessionMgr.logon("username","password", "localhost", "secWindAD");
         reportEngines = (ReportEngines) enterpriseSession
         .getService("ReportEngines");
         ReportEngine wiRepEngine = (ReportEngine) reportEngines
         .getService(ReportEngines.ReportEngineType.WI_REPORT_ENGINE);         

         IInfoStore infoStore =
         (IInfoStore) enterpriseSession.getService("InfoStore");
         String query = "select SI_NAME, SI_ID from CI_INFOOBJECTS "
         + "where SI_KIND = 'Webi' and SI_INSTANCE=0";
         IInfoObjects infoObjects = (IInfoObjects) infoStore.query(query);
         for (Object object : infoObjects) {
            IInfoObject infoObject = (IInfoObject) object;
            DocumentInstance widoc = wiRepEngine.openDocument(infoObject.getID());
            String doc = infoObject.getID();
            System.out.println(doc);               
            printDocumentVariables(widoc);
            widoc.closeDocument();

            try {
               URL url=new URL("localhost");
               httpURLConnection conn=(HtpURLConnection)url.openConnection();
               conn.setrequestMethof("get");
               conn.setRequestProperty("Accept","application/xml");
               String logonToken= "\"" + getLogonToken()+"\"";
               conn.setRequestProperty("X-SAP-LogonToken",logonToken);
               conn.setDoOutput(true);
               conn.setDoOutput(true);
               conn.connect();
               if (conn.getresponseCode() != 200){
                  throw new RuntimeException("Failed :Http error code : "
                     +conn.getResponseCode());
               }
               DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
               DocumentBuilder db = dbf.newDocumentBuilder();
                Document dom = db.parse("raylight.xml");//name of xml document from query above
                Element docEle = dom.getDocumentElement();
                NodeList nl = docEle.getChildNodes();
                List<Integer>providerIdList=new Arraylist<>();

                if (nl != null && nl.getLength() > 0) {
                   for (int i = 0; i < nl.getLength(); i++) {
                      if (nl.item(i).getNodeType() == Node.ELEMENT_NODE) {
                         Element el = (Element) nl.item(i);
                         if (el.getNodeName().contains("dataproviders")) {
                            String id = el.getElementsByTagName("id").item(0).getTextContent();
                            String providerId = el.getElementsByTagName("dataproviderId").item(0).getTextContent();
                            providerIdList.add(providerID);
                            for (int i=0; i<providerId.size();i++){
                               String get=providerId.get(i);
                            }
                         }
                      }
                   }

                }
             } catch (Exception e) {
                e.printStackTrace();
             }
             System.out.println("Finished!");
          }
         } catch  (Exception exec) {
            exec.printStackTrace();
         }

      }
   public static String getLogonToken() throws Parse Exception,IOException {

         String logontoken = null;

         URL url=new URL("localhost");
         httpURL connection conn=(HttpURLConnection) url.openconnection();
         conn.setRequestMethod("POST");
         conn.setRequestProperty("Accept","application/json");
         conn.setRequestProperty("Content-Type","application/xml; charset=utf-8");
         conn.setDoInput(true);
         conn.setDoOutput(true);
         String body=
         "\{
            \"username\":\"username\",
            \"password\":\"password\",
            \"auth\":\"secWinAD\"
            \}";

            int len=body.length();
            conn.setRequestProperty("Content-Length",Integer.toString(len));

            OutputStreamWriter out = new OutputStreammWriter(conn.getOutputStream());
            out.write(body, 0, len);
            out.flush();
            if (conn.getResponseCode() != 200) {
               throw new RuntimeException("Failed : HTTP error code:" + conn.getResponseCode());
            }
            BufferedReader br = new BufferedReader(new InputStreamReader(
               (conn.getInputStream())));
            String jsontxt = br.readLine();
            JSONParser parser = new JSONParser();
            JSONObject json = (JSONObject) parser.parse(jsontxt);
            logontoken = (String) json.get("logonToken");
            conn.disconnect();

            return logontoken;
      }
}


QQ Dell, other than having to make two separate connections is there any negatives to using the report engine?


Last edited by izzino on Thu Jul 14, 2016 9:18 am, edited 1 time in total
Back to top
hilfy
Forum Enthusiast
Forum Enthusiast



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


flag
PostPosted: Thu Jul 14, 2016 8:48 am 
Post subject: Re: List objects used in Web Intelligence reports

Most of the ReportEngine has been deprecated in BO 4.x. So, you won't get what you're looking for if you try to use it. Instead, you have to use the Webi RESTful SDK to get that information.

-Dell

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



Joined: 10 Nov 2015

Posts: 70



PostPosted: Tue Nov 08, 2016 5:46 pm 
Post subject: Re: List objects used in Web Intelligence reports

Hi Rupesh,

I am trying to run the link which you provided
https://drive.google.com/folderview?id=0B1uumRTyou6KWW45TjhnZFJLMEU&usp=sharing

But its showing only the header like below not extracting the details

Report Name Report Location Report ID DP Name Universe/Source Has Combined Queries Class Name Object Name Result Object/Condition Object Operands Sub Queries Combined Queries

I am running in my machine and BO Client is installed and i am using Enterprise Account.

Can you advise me
Back to top
cardawid
Forum Member
Forum Member



Joined: 19 Mar 2012

Posts: 16



PostPosted: Mon Nov 14, 2016 3:04 am 
Post subject: Re: List objects used in Web Intelligence reports

hilfy wrote:
Here's how I have done it. The loadFolders method is recursive so that it will walk down the folder tree to get the reports.

1. Log-in to the CMS. Use a token to log in to the RESTful Web Services under the same session.

2. Select SI_ID, SI_NAME from CI_INFOOBJECTS where SI_KIND = 'Folder' and SI_PARENTID = 23 (this is the top-level "Public" folder in 4.x, use 0 instead if you're on 3.1.)

3. For each folder InfoObject in the above query, call loadFolder() with the SI_ID.

4. loadFolder() logic:
a. Select SI_ID, SI_NAME from CI_INFOOBJECTS where SI_KIND = 'Folder' and SI_PARENTID = the SI_ID value from the current call to loadFolder().
b. For each sub-folder InfoObject in the query, call loadFolder() with the sub-folder's SI_ID.
c. Select SI_ID, SI_NAME CI_INFOOBJECTS where SI_KIND = 'Webi' and SI_PARENTID = the SI_ID value from the current call to loadFolder().
d. For each Webi InfoObject in the query, call loadReport().

5. loadReport() logic:
a. Get the IDs of all of the data providers in the report: http://server:6405/biprws/raylight/v1/documents/SI_ID value from current call to loadReport[/]/dataproviders
b. For each data provider, get it's query: http://server:6405/biprws/raylight/v1/documents/[i]SI_ID value from current call to loadReport[/]/dataproviders/[i]Dataprovider ID

c. In the resulting XML, get the contents of the "query" element to get the SQL. Also, the "expression" elements will tell you which objects from the universe are being use (but it doesn't give the field info for the object...)
d. Save the data for the report.

This particular process can be done with either the .NET or Java SDK.

-Dell


THANK YOU!
Back to top
Navneet Kaur
Senior Member
Senior Member



Joined: 18 Nov 2015

Posts: 33



PostPosted: Tue Dec 20, 2016 10:43 am 
Post subject: Re: List objects used in Web Intelligence reports

Hi
Is anyone having code available to get list of objects from webi reports in 4.1

Regards
Navneet Kaur
Back to top
hilfy
Forum Enthusiast
Forum Enthusiast



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


flag
PostPosted: Thu Dec 22, 2016 10:15 am 
Post subject: Re: List objects used in Web Intelligence reports

I have done it for one of my clients. Unfortunately, the actual code belongs to them as they paid me to write it for them. I posted a step-by-step description for how to do it above in the thread, though.

If you run into problems while you're trying to write this, please post the code and I may be able to help you from there.

-Dell

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



Joined: 05 Jan 2017

Posts: 1



PostPosted: Thu Jan 05, 2017 8:27 am 
Post subject: Re: List objects used in Web Intelligence reports

Hai Rupesh,

As you said,I created 3 Java classes such as DesktopApp.java,Excel.Java,ListObjectUsedWebI.java under one project and I also inserted all Jar libraries.Executing properly and I am able to see the GUI and I can login also,at that time in destination folder one text file and excel file is generated but I am not able to see the output in that.

Questions:
1.Above steps I followed is correct??
2.If I give report name as input,that report where it should present?whether it is in local system?
3.Can you please guide me to execute the app?
4.And steps for file type changing u mentioned right,whether its for login.txt?
Please help me. icon_sad.gif
Below attached IDE screenshot,whether its proper?

Regards
Hari
Back to top
Cefty
Forum Member
Forum Member



Joined: 18 Apr 2017

Posts: 1
Location: United Kingdom



PostPosted: Tue Apr 18, 2017 9:51 am 
Post subject: Re: List objects used in Web Intelligence reports

Hi izzino. Did you ever get this working?
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> SDK (VBA/ASP/JSP)  Previous TopicPrint TopicNext Topic
Page 9 of 10 All times are GMT - 5 Hours
Goto page Previous  1, 2, 3, 4, 5, 6, 7, 8, 9, 10  Next
 
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.0980 seconds using 17 queries. (SQL 0.0029 Parse 0.0635 Other 0.0316)
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