Javascript in OBIEE to Search a Report and Custom Data Format


How it would be if we can incorporate a Search kind of functionality so that when user put the search string in text box and press Go it will search from the list of reports from inside catalog ?

This is more or less similar kind of functionality that Catalog  Search functionality gives :

Javascript Search Catalog Report1

However there is limitation as this  Search only maps to the name of the Physical file inside the catalog directory and this name might not what end user interested for while searching . So idea is that maintain a logical catalog file name and actual physical catalog file name mapping in a backend table .Moreover end user might not have access to the answers for Searching or they might interested for this features from Dashboard page itself . Then you need to think something different to achieve your goal as one of our client demands the direct Report Search facility from Dashboard .

To achieve this I have imported the default EMP and DEPT table from Scott Schema .Create a table called “REPORT_SEARCH” and insert few rows as below .

Javascript Search Catalog Report2

Create the below simple reports .

Javascript Search Catalog Report3

And place the ‘Emp_Rep’ under ‘Emp’ Dashboard page ,’Dept_Rep’ under ‘Dept’  page , ‘Emp_Sal’ under ‘Sal’ page . And finally the Report Search template under ‘Report Search’ Page .

Javascript Search Catalog Report4

The objective is to input the report names i.e in the database in ‘Report_Name’ column and click on Find (Don’t press enter it will not work) will find the report it and you can click on it to open it .

Lets Edit the dashboard page ‘Report Search’ to see  the components .

Javascript Search Catalog Report5

So its simple text type having HTML Markup code as below .Note that I have added Javascript here to catch the user input and pass this value to URL using the Action Navigation and passing filters as parameters mechanism .(Take the reference of the detail from Bookshelf http://download.oracle.com/docs/cd/E12103_01/books/AnyWebAdm/AnyWebAdm_APIWebIntegrate6.html)

==================================

<html>
<script language=”javascript” type=”text/javascript”>
function foo(f){
var input=f.str.value;
var url=’saw.dll?Dashboard&PortalPath=/shared/Test%20Reports/Report%20Search&Page=Target&Action=Navigate&P0=1&P1=cany&P2=”REPORT_SEARCH”.”REPORT_NAME”&P3=’+input;
location.href=url;
}
</script>
<body>
<form name=’authentication’ method=’post’>
<font Color=Blue><i><b> Enter the Name of the Report to Search : </font>
<br><br>
<input type=’text’ id=’str’>
<input type=button value=’Find’ onclick=”foo(this.form);”>
</form>
</body>
</html>
===============================

Now My Javascript is ready to handle any input string .See the url portion .As per the Portal Path definition I have ‘Report Search’ report saved under “/shared/Test Reports” and the report has been placed under page called “Target” . However the Target page kept hidden .Lets see the target page then :

Javascript Search Catalog Report7

Its a combination of Tabular report and a narrative using the both option open i.e either click on “Go to Report” or Click on the Report name itself . So here is Report search Criteria .Note that the last Report Catalog column is hidden to drive the narratives using “@3” syntax .

Javascript Search Catalog Report8

Javascript Search Catalog Report9

Now see the “Table” view “Custom Text Format ” .This will necessarily take us to the physical location of the report where it actually saved as the catalog column fetched the data from database as “Catalog_Path” and I have converted it to Custom format for ease of navigation .

Javascript Search Catalog Report10

And finally the “Narrative” as below :

Javascript Search Catalog Report11

How hide that “Target” Page and Go to Report Search Page and put the report name say “Emp” and Click on Find (Don’t press Enter ,it will not work as per Jscript code)

Javascript Search Catalog Report6 You should not see below error at the bottom left of your Browser Status bar .If this display it will indicate there must have some problem in your Java script code.

Otherwise enter ‘Emp’ as initial of “Employee” Report Name .Your report list will be filtered out and display as below . Click either on “Go to Report” or “Emp Salary” or “Employee” to redirect to the intended page .Note that since the Action Navigate use  cany as P1 parameter that means it will search for “Contains Any” word match and hence result two reports . Two return only employee reframe your search using “Employee” . Similary do the same for “Department” .

In my case I Search “Salary” and it fires the below query :

select distinct D1.c1 as c1,
D1.c2 as c2,
D1.c2 as c3
from
(select distinct T248.REPORT_NAME as c1,
T248.CATALOG_PATH as c2
from
REPORT_SEARCH T248
where ( T248.REPORT_NAME like concat(concat(‘%’, ‘Salary’), ‘%’) )
) D1
order by c1, c2

Then click on ‘Emp Salary’ or ‘Go to Report’  let me take on the  Employee Salary Page .Thus we achieve the report search functionalities across several steps .However the maintainability is the concern if the report moves from one catalog folder to other then we need to track them down and modify the backend table with the updated link otherwise the report throw error 🙂

Javascript Search Catalog Report13

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s