Building up and customizing your own Worklist

I’m quite sure that at least once for the last few years, you had the though of having your own worklist design and functionality, but still somehow make use of the standard SAP technology for creating and reading the records. I am also sure that the first thing that came to your mind was the worklist API provided from SAP and this is of course the natural way. However real-time experiences show a drastic performance drop when dealing with more than 100 worklist items. We used Willy Introscope to test the load on the server and no wonder. Per API call the system executes no more and no less than 42 SQL queries, which makes 4200 to load the complete list (on a basis of 100 items). At first I was sure that should have been some kind of mistake. Well it ain’t we reran the tests multiple times and the results were all the same – very lame response time and lots of unnecessary SQL queries ran on the backend.

The purpose of this blog is to give you another way to fulfil your dream of having your own worklist UI and functionality. It is also not to be seen as a how-to since its purpose is only to give you the guidelines on how we did it and not following each and every step on the way.

First of all we decided to use Web Dynpro for Java in our worklist since it is very robust, meaning quick to design and implement. In any case you can use any UI technology you like; the basic principles are all the same.

To give you a first impression, I’ll provide you with a screenshot of the design time of the application and then I’ll brief you on what actually it’s purpose is and why did we had to customize the standard universal worklist from SAP.

Before explaining the functionality, just a short notice what is the scenario. So we use guided procedures with web dynpro callable objects. Each step generates a work item that has to be processed by certain user groups. The web dynpro objects on their side communicate with composite application services and save certain data persistently to the backend. Now the worklist should not only provide data that the guided procedures API has access to, but also details coming from the backend. Such complex table can only be achieved using custom worklist and that is why I am writing this blog.

The contents of the table itself are not so interesting, as I mentioned they are a mix from GP and CAF data (how we retrieve this data will come later on in this blog). The more interesting things are the functionalities that the web dynpro table offers us. In that case – filtering, sorting, forwarding and export to excel. All of those were quite important for the customer, but not available in the standard UWL.

Now to the logic needed to retrieve the items. As I mentioned in the beginning of this blog, we won’t be using the standard Worklist API, due to 2 reasons:

1. Already mentioned above – drastic performance drop, when having more than 100 items in the list (main reason – 90%).
2. Due to the fact that we cannot retrieve all data needed from both sources using the API (GP and CAF) (secondary reason – 10%).

The most effective and performance optimized way we found so far for our task was to use direct custom written SQL statements that deliver us the content we need with only one SQL call to the backend. There are certain drawbacks to that method (to be discussed), but if you manage to accept those the performance boost is tremendous.

However to be able to access the backend and run those custom SQL statements you would need to create a data source that is based on some newer drivers (depending on the database you are using) and not on native SQL, since it does not support all possible joins and SQL statements that you might want to use for sure.

The next step is to connect to that data source. This can be done as following:

try {
InitialContext ctx = new InitialContext();
Properties sysProperties = System.getProperties();
String sysname = sysProperties.getProperty("SAPSYSTEMNAME");
String dbName = "jdbc/" + "YOUR_DATA_SOURCE_NAME";
DataSource ds = (DataSource) ctx.lookup(dbName);
this.conn = ds.getConnection();
} catch (NamingException e) {
e.printStackTrace();
msgManager.reportException(e.toString(), false);
} catch (SQLException e) {
e.printStackTrace();
msgManager.reportException(e.toString(), false);
}

After having the connection, you’re not so far from executing the SQL. You need to create a statement:

this.stmt = this.conn.createStatement();
ResultSet rs = stmt.executeQuery(“ YOUR QUERY “);

The next step is to read the result set and don’t forget to close the statement and eventually the connection if you don’t plan to use it anymore. You would say: was that it – it is easy. Well it is until this very point, where you have to define exactly how the internal structure of those tables, that you need, look like.

This could be an easy task if you have access to a database manager and an user that has permissions to browse into the portal database structure of the portal. If you do not have such a user the situation is much more complicated.

The answer – how should I know what kind of fields and what foreign keys do exist between my tables is answered in a blog that I’m planning to release soon.