Writeback with dropdown feature in OBIEE Report

In this blog we will see how to enable Writeback in OBIEE and one example like where users will be given option to choose values from dropdown to select any one and update in backend rather than typing in freeform text field in the OBIEE report. We will use Jquery to achieve it.

Below are the high level steps to enable write back in OBIEE.

·         File level configurations

·         RPD changes

·         Report changes

File level configurations

Step 1: Edit instanceconfig.xml

Here is the path to find instanceconfig.xml file

<Middleware>/instances/instance1/config/OracleBIPresentationServicesComponent/coreapplication_obipsn

And add below line under <DSN>

<LightWriteBack >true</LightWriteBack >

Step 2. Create WriteBack template  

We need to provide a template to the BI Server so that it will handle the writeback operation based on logic provided in template.

And place this template in below location in the Middleware server.

<Middleware>/instances/instance1/bifoundation/OracleBIPresentationServicesComponent/coreapplication_obips1/analyticsRes/customMessages

 Sample template code

<?xml version="1.0" encoding="utf-8" ?> <WebMessageTables xmlns:sawm="com.siebel.analytics.web/message/v1"> <WebMessageTable lang="en-us" system="WriteBack" table="Messages">    <WebMessage name="Update_Comments">      <XML>       <writeBack connectionPool="Oracle Data Warehouse Connection Pool">           <insert> </insert>           <update>UPDATE EMPLOYEES set COMMENTS='@2' WHERE EMP_NAME='@1'</update> <!-- Identify the columns that are to be referenced. We can use the column position like @1 for first column, or by column id as you see them in the XML definition/Table view. -->         </writeBack>       </XML>     </WebMessage> </WebMessageTable> </WebMessageTables>

And name it like anything let say WriteBackTemplate.xml and copy to customMessages folder in the server.  

RPD Changes

Step 3: Enable Write Back in the RPD logical column

Navigate to to BMM table and expand column which you want to use for writeback

And enable the Writable checkbox under general table of logical column.

 

 

 

Step 4: Set Read/Write permission to Writable column

Navigate to Subject Area-> Presentation Table-> Writeable column (here column is comments in our example)

Double click on Permissions and select Read/Write for the application role for which you like to enable writeback feature

 

 

 

 

Here for BIAdministrator Read/Write permission enabled on this writable column.So that any user with this application role can writeback data from obiee report.

Step 5. Allow “Execute Direct Database Request” to database.

We need to set the permission to application role so that user with role should be able to contact database for insert/update data in backend.

Navigate Manage > Identity > Application Roles > BIAdministrator > Permission>Query Limits tab

And select “Execute Direct Database Requests” to Allow for the Database where in which you writable table or column    .

 

Step 6. Disable Cacheable option for physical table.

Navigate to Physical Table and uncheck Cacheable checkbox.

Step 7. Grant Writeback privilege to Apllication Role or Users

Login Analytics and navigate to Administrator->Manage Privileges

And search for Write Back property and “Write Back to Database”

Grant access to BIAdministrator role 

Report Changes

Step 8. Create a OBIEE report

Create a report on this subject area and pull employee name and comments and another dummy column (Select Comment)

Note: Here Comments is the real write back column.whatever the value we select from the dropdown will be updated into this writeback column.
 

 

 

Here we will use the JQuery to display drop-down on "Select Comment" column

 

Edit formula and add below code.

'<select id="user_comment" onchange=obipswb.Grid.Change(event)> <option value="No Comment">Select Comment</option> <option value="Good">Good</option> <option value="Bad">Bad</option> </select>'

 

Make sure you selected checkbox for “Contains HTML markup”.

On clicking OK then it thrown error as 

We can ignore this error and proceed further.

Next override default data format to HTML.

Now enable Writeback to the Comments column

 

Create Table view and place this drop down column at second position in view (since we used @2 to update the value in the backend table).

Open table properties and provide WebMessage name (This should be taken from your WriteBackTemplate.xml).

Check the report to test WriteBack feature.

Once you select any value from dropdown  then Apply button will be enabled and record can be updated on clicking apply. Note that drop-down column will not be updated with value you selceted and you can only see this value in the next column "Comments" which is write back column.ON clicking apply report will be refreshed and new value will be displayed on report.