Is prompted Vs Presntation variable and Protect Filter

Both “Is prompted” and Presentation variable will do the same work, i.e Pass value from prompt to report. But there is a slight difference in between. Let me discuss on the same.. 

Isprompted

Create a prompt using “Employee.ename”.

 

Create a report with "Office.oname"(Office Name),  "Employee.ename"(Employee Name),"Employee.salary" (Salary) as a columns.Add filter

where Employee Name taken from "Employee.ename".

 

Get the prompt and report into dashboard. Now change ename to ‘E1/kumar’, then the report filter out according to ‘E1/kumar’.

 

Let the columns in the report remains same, we will change the filter condition now. Let us take “Office.ename” is prompted.

 

Now you can observe that, if you change ename to ‘E1/Kumar’ then also the report is not filtering according to passed values.

 

Reason: If the prompt column and the column, which used in the filter (for a report) are taken from the same table, then Is prompted will work.

Presentation variable

Create a presentation variable called ‘name’ for the column “Employee.ename”.

 

  Now, in the report change the filter "Employee.ename" to "Office.ename" and use presentation variable.
 

 

Login as ‘kumar’. By default prompt shows ‘kumar’,because we used session variable as default value. If you change ename as ‘kumar/E1’ then the report is filtered according to value passed.

Protect Filter

Use session variable in the report filter.

  Login as ‘kumar’, by default prompt value is ‘kumar’, because we used session variable as a default value. If you change the ename to ‘E1’ then the report also filters out according ‘E1’. But user is not expected that.

 

Now give protection to the filter i.e apply ‘Protect Filter’.

 

Now login as ‘kumar. If you chage ename to ‘E1’also the report won’t be filtered out according to the value ('E1') passed.

Reason: Protect Filter will not allow override, hence by default only the value which is defined in the filter of the report will be passed as a value and hence you can see only "kumar’s" data is showing above, even if you change prompt to "E1"still "kumar’s" data is showing up in the report.

Update using Writeback method in OBIEE

Introduction:

      Write Back is like Write(Insert/Update) the data Back to database from UI.  This could be useful by end users may want to update/Insert the data. This makes OBIEE a reporting tool and also as a mini application for modifying business data.

Sample demonstration with an example to perform update operation using write back:

Employee table contains eid(datatype:int),ename(datatype:varchar) and salary(datatype:int) columns.

Add one more column called firstname(datatype:varchar) to Employee table.

In my case I set the connection pool,office

Double click on the Employee table that will open the new window as shown below here uncheck the cacheable for Employee table.

By default cacheable will be checked here, this should be unchecked because, whenever we post any data from the dashboard to database the data will be updated in the database but this will not reflect in the dashboard since the cacheable is checked and it holds the old data.

Add the new physical column (firstname) to Employee table and get the column into Presentation layer.

Now make one XML file for Update/Insert and save in the location, C:\OracleBI\web\msgdb\customMessages

 <?xml version="1.0" encoding="utf-8" ?>
<WebMessageTables xmlns:sawm="com.siebel.analytics.web/message/v1">
<WebMessageTable lang="en-us" system="WriteBack" table="Templates">
<WebMessage name="NEW">
<XML>
<writeBack connectionPool="office">  <!--In my case connection pool is office-->
<insert>   </insert> <!-- Space should be there between opening and closing tags -->
<update>UPDATE Employee SET firstname='@{c3}' WHERE eid=@{c0}</update>

<!--(Where @{c3} and @{c0} are the column positions.)-->
</writeBack>
</XML>
</WebMessage>
</WebMessageTable>
</WebMessageTables>


Note: 

• For the Data type CHAR and Varchar enclose @{Cn} (column position in answers) with ' '. where n=0,1,2,3...
• <insert>  and <update> tags must be present in XML.

Here I saved the XML file with UPDATE.xml. (Not mandatory to save the xml file in the same name, its your choice to choose the name.)

Restart the OBI server and presentation services.

Go to Manage previlages and change  WriteBack permissions  for Administrator.

Open up the Answer and add the columns eid, ename, salary and firstname .

Where @{c0}=eid; @{c1}=ename; @{c2}=salary and @{c3}=firstname.

Writeback application should be applied for firstname  column since this is the column we are updating currently in our example . Select Write Back option from the firstname column format as shown in below figure.

Write Back option is available only in table view, if you need to perform any write back option then it is compulsory that you should perform this in table view.

Select “Write Back properties” from Table view and enter the Template name and operation button.

The outcome of the above changes will look as below, here column firstname will be blank and that will be used to perform write back (Here Update) operation.

Now let us enter B in the below space and click on update button, this throws a strange error as below. The reason for this is Update command works only if the data already exists, here firstname is a column which does not hold any data but by default contains NULL. We need to avoid this NULL in order to remove this error.

For that fill NULL values with some value. I filled the NULL values with ‘a’.

Now we can UPDATE firstname.I updated firstname with B for the first row. Where eid=101, no error as earlier now.  Also we can see that first row is updated with B and all other is not NULL but ‘a’, since we have replaced Null values with ‘a’ in the previous step.

Now if we check in the database, B is updated for the eid,101

Finally get the report into Dashboard and do required Updates and check in the data base.