OBIEE10g Auto Suggest Prompt

A client asked me if I could create an auto suggest prompt for him. (ie: Google Style Prompt). Basically he wanted an edit box prompt which would fill an suggestion box which he could tab trough to make the right selection.

Since this isn’t a standard 10g functionality I wrote some JavaScript to make it happen. But I didn’t reinvent the wheel ! The people at jQuery already did the bases, I simple adapted it for usage in OBIEE 10g.

1. Download the jQuery UI package here. Install it in your b_mozilla directory’s (or other webserver dirs you use).

2. Download the jQuerySetup from here.

3. Add the setup script to a textbox on your dashboard page:

Alter files locations if needed, don’t forget the Contains HTML Markup checkbox.

4. Add a dropdown prompt to your dashboard page.

5. Create a javascript file in your b_mozilla directory’s called: autocomplete.js

function SetAutoComplete(PromptColumn){
    var domNode = document;
    var tagName = '*';
    var tags = domNode.getElementsByTagName(tagName);
    var y ="";           
    for(i=0; i<tags.length; i++){
   
    if (tags[i].className  == 'GFPFilter') {
        if (tags[i].getAttribute('gfpbuilder').indexOf(PromptColumn) != -1)
        {   
            y = tags[i].getAttribute('sid')           
        };

        $(
        function()
        {
                $( "#"+y ).combobox();           
        });
        };   
    };
};

6. After the dropdown prompt add a textbox with:

<script src="res/b_mozilla/autocomplete.js" language="javascript"> </script>
<script language="javascript"> 
    SetAutoComplete('C1  Cust Name');
</script>

7. Add your report and run the dashboard:

Till Next Time

OBIEE10g AutoRunPrompt

A client asked me if I could create an auto run prompt for him. Basically he wanted an edit box prompt which would updated his report after each character has been typed. Since this isn’t a standard 10g functionality I wrote some JavaScript to make it happen. It uses the onkeyup event to fire the GFPDoFilters filter event.

The script can be downloaded here: download COBIEEJS.

Copy the file to your b_mozilla directory’s (or other webserver dirs you use)

How to use it?

1. Add an edit box style prompt to your dashboard:

2. Add a textbox with:

<script src="res/b_mozilla/cobieejs.js" language="javascript"> </script>
<script language="javascript"> 
  AutoRunPrompt('C1  Cust Name');
</script>

don’t forget the Contains HTML Markup checkbox

3. Add your prompted report:

Run the dashboard:

Till Next Time

OBIEE Injecting javascript at GO button

Just a piece of script to inject your own javascript at the go button. You can use it fi to check if the user made a “valid” selection.

[code]

<script type="text/javascript">
function getElementsByClass( searchClass, domNode, tagName)
{
    if (domNode == null) {
    domNode = document;
    }
    if (tagName == null) {
    tagName = '*';
    }
    var el = new Array();
    var tags = domNode.getElementsByTagName(tagName);
    var tcl = " "+searchClass+" ";
    for(i=0,j=0; i<tags.length; i++) {
        var test = " " + tags[i].className + " ";
        if (test.indexOf(tcl) != -1)
        {
            el[j++] = tags[i];
            if (tags[i].innerHTML.indexOf('DoFilters') > 0 )
            {
            tags[i].innerHTML = tags[i].innerHTML.substring(0, tags[i].innerHTML.length - 7 )
                             + " onfocus=javascript:alert('Put your javascript here'); >Go</a>"
            alert (tags[i].innerHTML);
            }
        }
    }
    return el;
}

</script>

<script type="text/javascript">

    var tabs = getElementsByClass('minibuttonOn');
</script>

[/code]

Add the code in a textbox after the prompts, don’t forget the contains HTML checkbox.

Till Next Time

OBIEE using prompt as report selector

Yes, I know Venkat did it before:
http://oraclebizint.wordpress.com/2008/01/17/oracle-bi-101332-selecting-reports-from-dashboard-prompts-and-guided-navigation-sections/
So I will do it again so you can all reference mine ;-)
Create a prompt with a dropdown list of your reports:


If you can use an LOV instead off the CASE 1=0 construction!
put it all in a presentation variable
Add some dummy reports which only rows if the right pReports are selected:


Add the prompt and your real reports to your dashboard, give each report a separate section:

Set the Guided Navigation:


And Now you have a report selector:


Till Next Time

OBIEE dynamic columns in the by statement

Did this one on OTN http://forums.oracle.com/forums/thread.jspa?threadID=1090834&tstart=0
First get your column names into a prompt:

sql is bit like :
SELECT case when 1=0 then "D0 Time"."T02 Per Name Month" else  '"D2 Market"."M04  Region"' end FROM "Sample Sales" UNION ALL SELECT case when 1=0 then "D0 Time"."T02 Per Name Month" else  '"D0 Time"."T02 Per Name Month"' end FROM "Sample Sales"
Put the column name between single quotes!
(Yes I know a LOV is better but this is just for demo)
Create a report:

Edit the rank formula to:
Rank("F1 Revenue"."1-01  Revenue  (Sum All)" by @{pColumn}{"D0 Time"."T05 Per Name Year"})
Put it on a dashboard:


Till Next Time

OBIEE Prompt based on groupmembership

Somebody on OTN claimed that you can’t make prompts available based on the security setting of the user (See: http://forums.oracle.com/forums/thread.jspa?threadID=943712). Here is a workaround based on guided navigation:

First create a report which filters results based on the group  (noprompt) membership:

SELECT TIMES.CALENDAR_YEAR saw_0 FROM BM_SALES WHERE LOCATE( 'noprompt',VALUEOF(NQ_SESSION.GROUP)) > 0 ORDER BY saw_0

Based on being a member of nogroup the prompt will be available:

User1:

User2:

Till Next Time

OBIEE Prompts setting a default value to “force” a selection

Sometimes you want to “force” the user to make a selection to prevent heavy traffic between the DB and the BI-server. On trick is to default the prompts to non existing values. For instance for years use 9999:

For the a category:

By default the dashboard will return no rows. It a good custom to set the default “no rows” text:

`

Till Next Time

OBIEE Leading zeros calendar prompt

Goran did a good one:

http://108obiee.blogspot.com/2009/05/how-to-add-leading-zeros-after.html

Till Next Time

OBIEE Background colour prompt

One way of identifying prompts which are connected is by setting the background colour

You can find it in the left top corner of the prompt editor:

 

Till Next Time

OBIEE Prompts putting the label in front part 2

In a previous article I showed you how to get from this:

to this:

based on an OTN entry from Stijn Gabriels.

But what if you only want this effect for 1 dashboard page or when you don't have easy access to the views.css?

Simple add a text frame to your dashboard page:

And set the content to:

<style type="text/css">
td.GFPFilter br { display:none; }
</style>

don't forget the "Contains HTML Markup" chechkbox.

Till Next Time

OBIEE Prompts putting the label in front

Or how to get from this:

to this:

It was a question raised on the OTN forum: http://forums.oracle.com/forums/thread.jspa?threadID=886991

And to be quit honest I didn't think it was possible...

But Stijn Gabriels, one of the great OBIEE CSS gurus, came with a very elegant solution. Goto the views.css of the style you are using (S_ directory) and add

td.GFPFilter br { display:none; }

If you are using OC4J as webserver sync the files in the J2EE container.

Till Next Time

OBIEE Multi lingual prompt

Recently I was asked to create a multilingual prompt label. Stijn Gabriels pointed me in the right direction.

First I created a dummy table in the .RPD with a single date column:

Next I put in the presentation catalog:

In my translation table I added the column and it's translations:

Based on this column I created a prompt with no label text:

Now in the dashboard it's available in Dutch:

In English:

In French:

You can use the presentation variable for your filtering.

Till Next Time

OBIEE Image prompt

Just a nice gimic, the OBIEE image prompt. First get a image, I used this one: http://www.faqs.org/docs/htmltut/images/testmap.gif

Create a image prompt:

Next use the HTML SHAPE tag to add the coordinates: http://www.faqs.org/docs/htmltut/images/_AREA_SHAPE.html

<MAP NAME="map1"> <AREA HREF="contacts.html" ALT="Contacts" TITLE="Contacts" SHAPE=RECT COORDS="6,116,97,184"> <AREA HREF="products.html" ALT="Products" TITLE="Products" SHAPE=CIRCLE COORDS="251,143,47"> <AREA HREF="new.html" ALT="New!" TITLE="New!" SHAPE=POLY COORDS="150,217, 190,257, 150,297,110,257"> </MAP>

Press Extract Image Map from HTML

add the column and value.

Till Next Time