Oracle BI EE : Comment from OBI Dashboard

I was facing one problem in my application and I was searching for a suitable solution for that but did not get any thing from anyone. Finally I used my J2EE knowledge and build a functionality for OBI EE. You must be thinking what I am taliking about. It is putting comments on dashboards.

The requirement statement goes this way –

1> We have a set of dashboards for different purposes

2> We have a set of user of those dashboard reports

3> Our user want to provide some comments ob the dashboards

4> Those comments will be saved in the database and will be displayed in the report.

The first way to meet this requirement was to consider the so called write-back functionality. But it has some draw backs as well. Not only complex implementation but also the managing the updated data and also show them on the dashboard just after the users press update.

The approach that I have taken is little bit different. These are the steps for that –

1> Create a simple jsp page that can execure an SQL command on database. Use DB connection pool in the coding if the Application Server supports.

2> Create a dummy table in the data base. Named it DUMMY and in this table there will be only one column Dummy_Field as verchar2(1) and insert a row like this INSERT INTO dummy VALUES(‘X’); Create another table USERS_COMMENTS with these fields Comment_By, Comment_On, Comment_Text.

3> Now import this table into the physical layer and create a self join so that you build a fact to dim relation between its two alias views. Expose this table to presentation layer.

4> In the answer create a report with the Dummy_Field. As this column has one row so it will never give you a no data found error. Now go to the Narrative section of the report and create an HTML form with a multi-line text area (id=comment) and one submit button. When the submit button is called it will call a javascript function that will create a SQL like INSERT INTO user_comments VALUES(‘VALUEOF(‘USER’)’,sysdate,’comment.value’); with the text entered in the multi-line text area and call the jsp file using AJAX. The code is very simple and widely available in internet. If any one need sample code please let me know. Add this report at the end of your dashboard. It will show you a comment area with a submit button.

5> Now create a simple report that can fetch and display rows from the USER_COMMENTS table. Make the by pass saw server cache as ticked in the advance setting of the report.

6> Finally add a cookie check section on the jsp file so that it can match the OBI security mechanism.

If anyone need on any of these parts please let me know I will update the blog with that information.