Let's Live The BI Wonder: Script

Thursday, 28 September 2017

Alternative of Section Condition in Oracle BICS

Few days back , I was exploring Oracle BICS, and I noticed that Sections in BICS has different options than OBIEE

The Bug 25327498 has been raised to fix the issue.

But till the time Oracle add this option in BICS, is there any other alternative for Condition ?

In this blog I have tried to come up with an option for the same.

I have created a simple report and dashboard, for this POC.

Next I have created a simple prompt, with radio button.

This prompt has 2 specific values only 'hidden' and 'visible', with 'hidden' set as default selection. This prompt populates a presentation variable var_display.

I went back to to page and checked the source code, to find out the section id, which is like

I have used this section id in the javascript below :

<script type="text/javascript">
var sectionId = "d:dashboard~p:asgns923547hrc7n~s:ado2gidqeb7edpvr";
var sectionDiv = document.getElementById("Embed"+sectionId);
sectionDiv.setAttribute("@{var_display}", "true");
</script>

This section id portion needs to be changed based on the dashboards and sections. And @{var_display} is for passing the presentation variable from the prompt. Rest of the code will remain the same.

I add a text object in the same section as the existing report and put the code there and enable 'contain HTML markup' check box.

So the page looks like,

And when I run the page

With section as 'visible'.

The best part is this same piece of code can be used in OBIEE also.

Monday, 14 August 2017

Auto Refresh OBIEE Dashboard / Report

Few days back one of my colleagues came up with a strange requirement. His client wants to display one OBIEE Dashboard in the giant screen and wants the reports to be automatically refreshed in the page.

We have 2 ways to achieve this.

1. Refresh the entire page
2. Refresh the individual report

Let's go a little deep into this. But before we begin, one important prerequisite of this is the tables behind the report must be Cache disabled.

1. Refresh the entire page

This one is actually pretty strait forward. What I need is the code below :

<META HTTP-EQUIV = "REFRESH" CONTENT = "60">
<CENTER><p><b>Auto Reload 60 seconds</b></p></CENTER>

I create a simple report, I can take any column from any subject area.

And I add a Static Text view and paste the above code.

The 2nd line is completely optional. If you do not want any text to be displayed, skip this line.

Let me save this report as 'Auto Load'. Next I go to the dashboard page which I want to refresh automatically. In this case I go to Sample Demo Dashboard.

And I place the 'Auto Load' in this page. If You want you can keep this section hidden too.

When I run the page I will see reports getting refreshed after every 60 seconds.

And if you check the session logs, you can get a confirmation for this also.

Next let's focus on doing it for individual reports.

2. Refresh the individual report

For this one we need to add the script for the reports, which needs to be refreshed.

First I create a report, which I want to be refreshed automatically.

I create a report with Tile view to show # of Employees

I go to the page, and add the report that needs to be refreshed automatically, and add 'Refresh' link for the report.

Next I view the source of this page and search for 'javascript:HereLink'. It will take me to the code for refresh link.

In my case it is javascript:HereLink('d:dashboard~p:f0315tdu3cqtmc6d~r:538mu6nhk4hpmbj0','Refresh')

I need the highlighted portion of the code above to use in my script. I add the script below in a Narrative View in my report

<html>

<body>

<script>

var myVar=setInterval(function(){reportrefresh()},5000);

function reportrefresh()

{

setTimeout("javascript:HereLink('d:dashboard~p:f0315tdu3cqtmc6d~r:538mu6nhk4hpmbj0','Refresh');",1000);

}

</script>

</body>

</html>

I am using 2 functions here,

a. setInterval : calls a function after every t time interval. In this case it is 5000 or 5 secs

b. setTimeout : calls a function after t time. In this case it is 1000 or 1 sec

In the compound view we can see the report getting auto refreshed.

In the dashboard page, where I have this report, I select 'Compound Layout 1' to be displayed.

It is time to test if it is working properly or not. My report is showing # of Employees as 31.

Next I add 4 new rows in my Employee table in the DB.

And I find my report in the dashboard has got automatically refreshed and is showing # of Employees as 35.

And if you check the session logs, you can get a confirmation for this also.

Pretty cool huh ... 😎😎

Thursday, 28 September 2017

Alternative of Section Condition in Oracle BICS

Monday, 14 August 2017

Auto Refresh OBIEE Dashboard / Report

Implementing & Testing Row Level Security in Power BI