Using javascripts in Analytics Reports

By using javascripts, many requirements that seems to be impossible in analytics can be implemented easily. The following report is one such example.

This report has a column selector on Time dimension that has Quarter/Month/Week and a fact "# of Service Requests". The tricky part about this report is to show 2 quarters when Quarter is selected in Column Selector, 12 months when month is selected and 8 weeks when Week is selected in the column selector.

1. Create a report with columns Quarter and # of Service Requests in criteria and sort Qaurter in descending order. Apply filter for 12 months as it is the largest span in all 3 required conditions.

2. Click on Column Properties of Open Qaurter and in Data Format select Override Default Data Format.





3. Select Custom Text Format from Drop down menu and paste the following code

@[html]"<""script"">""document.getElementById(""\"Test\""").innerHTML""=""\"3\""";"document.getElementById(""\"columnHeading\""").innerHTML""=""\"Quarter\""</"script">"@



4. Click on Results Tab.




5. Add a Column Selector View and add Month and Week columns to it.

6. Select Month from the column selector and click on Criteria.

7. Click on column properties and change the data format to custom text. Copy the following code in the Custom Text Format:

@[html]"<""script"">""document.getElementById(""\"Test\""").innerHTML""=""\"13\""";"document.getElementById(""\"columnHeading\""").innerHTML""=""\"Month\""</"script">"@

8. Repeat steps 5,6 & 7 for column Week and use the following code for Week column.

@[html]"<""script"">""document.getElementById(""\"Test\""").innerHTML""=""\"9\""";"document.getElementById(""\"columnHeading\""").innerHTML""=""\"Week\""</"script">"@

9. Go to results tab and create a narrative view.

10. In narrative view check the contains HTML Markup checkbox and add the following code

Prefix:
<a Id="Test"></a><table Id= "myTable" class="ResultsTable" onmouseover="RTOver(event)" onmouseout="RTOut(event)" title="Results" border="1" cellpadding="2" cellspacing="0"><tr><th Id="columnHeading" scope="col" class="ColumnHdgD"></th><th scope="col" class="ColumnHdgD"># of Calls</td></tr>

Narrative:
<tr><td>@1</td><td>@2</td></tr>

Postfix:
</table><Script>var counter=document.getElementById("Test").innerHTML;var numberOfRows=document.getElementById("myTable").rows.length;for (i = 0 ; i <= (numberOfRows-counter); i++ ){document.getElementById("myTable").deleteRow(counter);} </Script>

11. In Compund View delete the table view and add Narrative view.

Act As Functionality in Oracle BI

Implementing Act As functionality or the Proxy User functionality is very well explained by Venkat in his blog. This feature uses "Execute Physical" for getting the list of Target Users. That means we need to give access for executing physical requests to the database in Oracle BI Administration tool. This can be easily done for the users by assigning them to a group and adding permission to execute physical requests on the database. However when the users are coming from a different system, say Siebel for instance and we dont have control on groups then its a little tricky to get through. Its recommended to go through this blog before proceeding.

After the Proxy User functionality is set as per given in the Venkat's blog,

1. Create a new Business Model and drag the Table PROXYTABLE in BMM Layer.

2. Create a Dummy Fact using the same table.

3. Create a complex join between the two logical tables in Business Model Daigram.

4. Create Primary key for the Logical Table PROXYTABLE to make the Business model consistent.

5. Drag the Business Model in the presentation Layer and delete the Dummy Fact.

6. Change the contents of the webmessage file located at {OracleBIData}\web\msgdb\customMessages to:

<?xml version="1.0" encoding="utf-8" ?> <WebMessageTables xmlns:sawm="com.siebel.analytics.web.messageSystem"> <WebMessageTable system="SecurityTemplates" table="Messages"> <WebMessage name="LogonParamSQLTemplate"> <XML> <logonParam name="RUNAS"> <getValues> SELECT PROXYTABLE.TARGETUSER saw_0 FROM Proxy WHERE PROXYTABLE.PROXYUSER = VALUEOF(NQ_SESSION."USER") </getValues> <verifyValue>SELECT PROXYTABLE.TARGETUSER saw_0 FROM Proxy WHERE PROXYTABLE.PROXYUSER = VALUEOF(NQ_SESSION."USER") and TARGETUSER='@{VALUE}'</verifyValue> <getDelegateUsers>SELECT PROXYTABLE.PROXYUSER , PROXYTABLE.PROXYLEVEL FROM Proxy WHERE PROXYTABLE.TARGETUSER = VALUEOF(NQ_SESSION."USER") </getDelegateUsers> </logonParam> </XML> </WebMessage> </WebMessageTable> </WebMessageTables>

This approach is similar to the way SA Systems Subject Area work.