Insert data into SAP HANA from BO WEBI report through XS application

Hello All,

In this blog, I have explained about how to insert records into SAP HANA from a reporting tool like Web Intelligence. This concept can be used in most reporting tools like Dashboards, Lumira & Design Studio to insert data into SAP HANA.

Please find below steps to acheive this.

1. Create table in sap HANA

Create column table with name T_B_SALES with below structure in SAP HANA.

2. Create SAP HANA Repository

Goto Window -> Show View from Top Menu Bar

Select SAP HANA Repositories.

Right click on the Repository Area & create new repository.

Give Repository Name and click finish.

You will see the Repository successfully created in SAP HANA.

  3. reate XS Application

Go to Window -> Show View from Top Menu Bar and select Project Explorer.

4. In Project Explorer Create New XS Project

Right click in Project Explorer Area New-> Project ->SAP HANA Development->XS Project.

Give Project Name & Click on Finish. Now you will see a XS Project folder created in Project Explorer.

5. Create .xsapp & .xsaccess files in XS Project

Right Click on XS Project folder and select ‘file’ option.

  Give file name as .xsapp. Keep this file blank. This file is for XS engine to understand there is an XS application in this package.

Similarly create .xsaccess file & write below code in it.

Right Click on our XS Project XS_BOOK_STORE and create XS JavaScript Source File.

Name it as “insert_purchase_record.xsjs” and write below code in it.

  var cid = $.request.parameters.get(‘cid’);

  var bname = $.request.parameters.get(‘bname’);

var no_of_copies = $.request.parameters.get(‘no_of_copies’);   
$.response.headers.set(‘access-control-allow-origin’,‘*’);

  var conn = $.db.getConnection();

   var pstmt = conn.prepareStatement(“INSERT INTO \”UNAME\”.\”T_B_SALES\”
values (‘”+cid+“‘,'”+bname+“‘,current_date,”+no_of_copies+“)”);

var rs = pstmt.execute();

   $.response.setBody( “Entry has been created Successfully.”);

  }

  conn.commit(); 

  conn.close();

Note: UNAME is username in SAP HANA

Share Project

Right Click On Project and goto Team-> Share Project

Once your Project is shared small Yellow cylinder will appear at Project Logo.

Now Select all the 3 files (.xsapp,.xsaccess and insert_purchase_record.xsjs) and Activate the files.

Then all files will show the small Yellow cylinder at file logo after successful activation of files.

6. Create a WEBI report which will write data back to SAP HANA.

Create WEBI report with No data Source. As we do not want to see any data, and need to insert data into HANA DB.

Create 3 Variables in BO as below

Define 3 input control Text Boxes for each of variables.

Create Report with above variables as below. Try to articulate it to appeal to users.

Do not forget to select cell property of insert cell to Hyperlink, as below.

Now, right click On the “Insert” Text, Goto Linking->Hyperlink and put below code in window & parse it.

http://<HANA_SERVER>:<PORT>/XS_BOOK_STORE/insert_purchase_record.xsjs?cid=B0000001&bname=INDIA&no_of_copies=1 

  PORT: Mostly = 8000 (for HANA XS ENGINE)

Click on parse. Below window will now appear,

Now assign the BOBJ variables to URL parameters, as below.

7. Execute the BO Report

Now let’s test our XS application..!

Click on insert, this will take you to the browser and will prompt you to enter HANA password.

  After entering the password and clicking OK it you show you following window.

Now Check HANA table T_B_SALES. You will see one entry created in table..!!

You can now also try to update and delete the records in HANA DB. You can use the same table, on which we are performing DML operations, as the BOBJ report source and can see the records gets change at WEBI Report level at runtime.

Hope you find this worth of reading..!! Thanks