Call oracle stored procedure from BIRT

Problem
------------
One lengthy report executes for long time and it times out. Mostly time out happens when exporting report in excel, works fine in browser view and export  to PDF.

Cause
-----------
When report is exported to excel, BIRT report executes once again and generates whole report and loads into memory. Probably below sequence would be followed

• Execute report logic
• Load all the rows in memory
• Pass the result set to excel emitter
• Emitter writes it to excel
• OutputStream writer writes to file

Whole process takes a long time to complete the operation "export report to excel". Connects times out and browser terminated the connection and export aborts. One error message in the log "Connection aborted by software on your machine".

Solution
-------------
Follow the steps below in sequence

1. Write stored procedure with the whole report logic into it (SQL of all the datasets). Make sure there is a return statement in the end procedure even though it doesn't return anything
2. Generate the result set and write it in a temp_table in the database
3. Write one more dataset in BIRT to fetch result from temp_table and publish it on the report
4. Writing report logic in stored procedure will bring down the report execution time and export will be successfull
5. Sample code of how to call stored procedure from BIRT
   
   Open method
   -------------------------
   

   maximoDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), this.getName());

   maximoDataSet.open();

   
   

   var sqlText = new String();

   
   

   　

   // Add query to sqlText variable.

   
   

   sqlText = "call myproc('PARAM1', 'PARAM2')"

   // Include the Maximo where clause

   //+ " where " + params["where"]

   ;

   　

   maximoDataSet.setQuery(sqlText);
   
   
   fetch method
   ----------------------
   

   if (!maximoDataSet.fetch())

   return (false);

   
   

   　

   return (true);
   
   Hope this helps someone, difficulties implementing .. contact me :-)
   

Display user's "Default Insert Site" on maximo title bar

Hello,

Here I come with an interesting post on Maximo customization.

Problem
-----------
Display logged in user's "Default insert site" on the maximo header bar.


Solution
-----------
Follow the steps below, this solution is tested on Maximo 7.5. You need to modify the code according to the Maximo version you are working on.

1. Navigate to path IBM\SMP\maximo\applications\maximo\maximouiweb\webmodule\webclient\components on you application server
2. Search for file titlebar.jsp
3. Open file and locate code below.
4. <span id="txtappname" class="<%if(useHomeButton){%>homeButton<%}%>txtappname">&nbsp;  <%=apptitle%>
                                   </span>
5. Below the above code and under the same <td> tag. Paste the code below
6.     <%
              String site = "ERROR";
              String user = "TEMP";
                                      
              MboSetRemote sessionSet = control.getWebClientSession().getMXSession().getMboSet("MAXSESSION");
               sessionSet.setWhere(" MAXSESSIONUID = "+control.getWebClientSession().getMXSession().getUserInfo().getMaxSessionID()+" ");
               sessionSet.reset();
                                      
               if (sessionSet.getMbo(0) != null){
                        user = sessionSet.getMbo(0).getString("USERID");
                                          
                        // Get current insert site of user logged in
                        MboSetRemote userSet = control.getWebClientSession().getMXSession().getMboSet("MAXUSER");
                        userSet.setWhere(" userid ='"+user+"' ");
                        userSet.reset();
                                          
                        if (userSet.getMbo(0) != null){
                              site = userSet.getMbo(0).getString("DEFSITE");
                        }
                  userSet.close();
         }
                                      
         sessionSet.close();
     %>
      <font face="Arial" color="Yellow" size="4">
    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp <B>Site :</B>  <%=site%>
      </font>
7. You are done.
8. Difficulties to implement, write to me. I will be happy to help :-)

BIRT report export in excel fails

Problem Summary

Lengthy BIRT report with approx 15 datasets runs fine in browser view with 32 pages but when it is exported to excel. It fails with below error message

Caused by: java.io.IOException: Async IO operation failed (1), reason: RC: 10053  An established connection was aborted by the software in your host machine.

Cause

When a BIRT report runs in browser view it doesn't fetch all the records before publishing it on screen because of this report is published properly in browser view.

When report is exported to excel it fetches all the records of report output and writes it to excel before prompting user to save the file. Report export in excel causes below events

- execute whole report logic again
- fetch all the records from memory
- write them to excel using excel emmitter
- prompt user to save file

Whole operation takes a lot of time to complete because of report complexity, this causes report export to time out.

Solution

Below are the list of solutions that can be applied to this

- Increase webserver read/write time out property in websphere
- If first solution doesn't help then try to optimize SQLs in the report design
- If above two doesn't work then try to develop report in some third party tool like excel, connect directly to the database, execute logic, fetch records and publish in report.