Skip to Content

Today’s topic is a little more involved than my previous ones – today I’m discussing how to view the SQL used by a BOE XI 3.1 Web Intelligence report by using the Java Report Engine (REBean) SDK. It’s not actually hard per se, there are just some things to be aware of. I’m just going to jump right into it, so if you’re not already familiar with them, here are the API Reference and the Developer’s Guide for the Java REBean SDK, as well the Developer’s Library site.

There’s a little too much code in the accompanying sample to put it in-line here, so here’s a link to it: Java_REBean_BE115_View_WebI_Queries

Background

The first thing is to know that the queries themselves inside a WebI report aren’t always just a simple statement. You can have multiple SQL statements, say if you’re using multiple data providers. You can also have “combined” queries that use union, intersection, or minus operators to join queries. Then there are filters that can be added. Each of these requires different kinds of processing. 

2012-03-29 Combined Query.png 2012-03-29 Multiple Queries.png

The next thing is that the kind of SQL WebI is going to generate will be different based both on the capabilities of the database you’re connecting to and on the nature of the filters or objects you’ve added to the query. Sometimes, even though you create multiple queries in WebI, it will combine them into a single SQL. This is usually because you’re using a single data provider, but can also be dependent on features of your database. Other times, especially if you’re doing client-side record filtering, you can get more SQL than you were expecting. The point is, if you’re hoping to alter the query in any way, say by removing filters, you might not always be able to get to the SQL the way you were thinking.

Another thing you find as you start to look into the SDK, is that the most obvious place to look, Query.getSQL(), has been deprecated. This isn’t to say that it won’t work, just that, if anything does go wrong when you’re using it, we in support won’t be able to help you with it. It generally means that it’s planned to be removed in a later release, but hasn’t yet. (This call is still in the BI 4.0 REBean SDK API Reference, and it’s still listed as deprecated.) The method suggested as the replacement, SQLSelectStatement.getSQL(), works fine, but as we’ll see later, if you want direct access to the query filters, you need to use the Query object.

It’s also possible, when the query has prompts, to view either the definition of the prompt or the last value that was used for the prompt. If you’d like to see the definition, you need to use the SQLSelectStatement object. We’ll see this later as well.

Finally, queries can be of arbitrary length and complexity, so they’re stored as a tree of containers and nodes. The nodes indicate a particular query while the containers indicate the operators that join the queries. Because of this, just getting 1 SQL statement isn’t necessarily enough – you’ll need to traverse the tree. I hope you’re familiar with recursion.

2012-03-29 SQLContainer Tree.png

The API’s

Now we get into the guts of it. I’ll take it as read that you’ve already attached to Enterprise, logged on, and queried for and gotten the InfoObject for the report. The sample associated with this blog does all of that, so we don’t need to discuss it here.

When you open the webi document, it returns to you the document instance. We use this to get the Data Providers that contain the SQL. You may have more than one of them, so you’ll need to loop through them all.

At this point, you need to decide if you’re interested in viewing and having access to the filters that went into the query or if you want to see the definition of the parameters that go into it.

Query Filters (Query Object)

To get the query FilterConditionObjects, we’ll need to get the queries first, and to do that, we check if it’s a single query or a combined query by using DataProvider.hasCombinedQueries(). If it’s a combined query, we call a recursive method (discussed below) to handle each of the queries that has been combined. We send it the QueryContainer object: DataProvider.getCombinedQueries(). If it’s a single query, then we just process the query itself: DataProvider.getQuery().

I mentioned earlier that Query.getSQL() has been deprecated, but it’s just that method; the Query object itself is still supported.

Once we have the query, we use another recursive method to handle the arbitrary tree of filter conditions, sending in Query.getCondition(). Finally, when we get to a leaf node in the FilterConditionContainer tree, its name will contain the filter itself: FilterConditionObject.getName(). It’s here that you’ll be able to remove the filters, if that’s what you’d like to do.

Parameter Definitions (SQLSelectStatement object)

If instead you’d like to view the SQL with the parameter definitions in it instead of the values that were used, then you’ll use the SQLDataProvider.getSQLContainer(boolean) method.  If you send in true, you’ll see the actual values that were used in the query. If you send in false, you’ll see how the prompt is defined. For example:

SQLDataProvider.getSQLContainer( true )
SELECT Outlet_Lookup.City, Outlet_Lookup.State, Outlet_Lookup.Shop_name FROM Outlet_Lookup WHERE Outlet_Lookup.State = ‘Texas’

SQLDataProvider.getSQLContainer( false )
SELECT Outlet_Lookup.City, Outlet_Lookup.State, Outlet_Lookup.Shop_name FROM Outlet_Lookup WHERE Outlet_Lookup.State = @prompt(‘Enter State:’, ‘A’, ‘Store\State’, Mono, Constrained, Not_Persistent,, User:0)

And you may have noticed that we’re getting an SQLContainer object, so once again, we’ll need to use recursion to traverse it.

Walk the Tree (Recursion)

A number of the objects we’re dealing with here (the queries, the filters), are in containers, which contain either leaf nodes and/or more containers. In order to process the whole tree, we write a method that takes a container as a parameter, then processes each of its children. For the leaf nodes, we write out the information we want (SQLSelectStatement.getSQL(), Query.getSQL(), or FilterConditionObject.getName()) – this is the “recursion base.” For the container nodes, we call the same method again, giving it the new container to start all over again. In this way, each container that’s found gets processed and each leaf node gets printed out and then the method returns. It then rewinds and processes the other nodes in the container until everything has been processed.

In Practice

What I have found, looking inside the WebI reports at their SQL, is that there’s not very often a whole “tree” of nodes. Often times is just 1 node with all of the SQL, even where there are combined queries. Other times, like when a compound query uses INTERSECTION as the join, there will be more than one node, but the SQL for each node is the entire SQL, complete with the INTERSECTION operator in it, both times (or more).

2012-03-29 Repeated Queries.png

So in the end, if your goal is just to view the SQL, you may be able to get away with just viewing one node of the tree, but I would certainly do a lot of testing with that first. But if you’re looking to do more, you’re going to need to look at your reports and their design to see if the SQL you’re getting back is in a form you can use.

Until next time.

Questions? Comments? Please let me know what you think.

To report this post you need to login first.

5 Comments

You must be Logged on to comment or reply to a post.

  1. Colin King

    Hi Bryan, thank you for writing this – it was very informative. I have a need to extract the SQL from multiple instances of Webi reports and have been using similar logic. Unfortunately we have recently upgraded to SBOP4 and a large number of interfaces in the Java Webi SDK are no longer functional, including the SQLDataProvider interface. I cannot see any alternative interfaces or classes that would allow me to do this – can you advise please?

    Regards,

    Colin

    (0) 
    1. Bryan Booth Post author

      Hi, Colin,

      I’m glad my article has caught your interest. As you’ve seen however, there are a great number of Report Engine API’s that are no longer available in BI 4.0. For an idea of just how many, here’s the BI 4.0 Java Report Engine Object Model Diagram. Everything that’s in red is no longer supported. Effectively, the only thing remaining is the ability to view WebI reports, and there are currently no other interfaces available.

      Sorry to be the bearer of bad news.
      Bryan

      (0) 
      1. Colin King

        Thanks for the quick reply Bryan. Unfortunately I can’t open the object model diagram (Adobe is telling me the file is corrupt) but I get the idea from the package list in the API reference. Can you shed any light on why the API was trimmed back so much? Security, no value in maintaining it? I’m going to add a suggestion to the Ideas Place to try and get this aspect added in again.

        Thanks,

        Colin

        (0) 
        1. Bryan Booth Post author

          Hi, Colin,

          Obviously the diagram opens for me, so here’s where I got it from:

          SAP Help Portal
          http://help.sap.com/boall_en/

          In the 2nd drop-down, select “SAP BusinessObjects 4.0”. The diagram is “Report Engine SDK Java OMD”. If you still can’t read it, then either it’s incompatible with your PDF viewer (one or the other of them is too old or too new) or you’d need to contact the Help Portal people.

          As for why so much of the SDK has been redacted, I’m afraid I’m as much in the dark as you. The more people you can get to vote for it on Idea Place, the better.

          Regards,
          Bryan

          (0) 
          1. Colin King

            Still having problems – will get my PDF viewer sorted out. I’ve raised the suggestion on Idea Place – thanks again for your help.

            Regards,

            Colin

            (0) 

Leave a Reply