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.
 |  |
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.
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).
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.
- SAP Managed Tags:
5 Comments
