Direct database request in OBIEE - Part 3

Example No 3:  Calling the database function from the OBIEE Answers

Below I have written an oracle function which accepts 3 input parameters & return 1 output column.

Function name is FIND_CUSTID. Input parameters are Customer first name, Customer last name & Customer birth year & output parameter (Return value) is Customer ID.

Note: Ensure that function return SINGLE value for the unique combination of INPUT parameters.

Let us check by calling the function from the oracle database itself just to check if it is working fine or not.

In OBIEE Answers, we will create the dashboard prompts on the columns Customer First Name, Customer Last Name & Customer Birth Year. In the Direct database request, we will call the function FINDCUSTID which will return the customer id with respect to the combination of parameters selected from the prompt.

Let us create a direct database request. (Report name is Calling a function using DDR)

Presentation variables-

     A-     Customer First Name

     B-      Customer Last Name

     C-      Customer Birth Year

Function Call –

SELECT FIND_CUSTID ( ( @{A}{'Abel'} ),( @{B}{'Aaron'} ),( @{C}{1960} ) ) as CUSTID from DUAL

Let us create the dashboard prompts for the columns- Customer First Name (uses Presentation variable A), Customer Last Name (uses Presentation variable B) and Customer Birth Year (uses Presentation variable C)

Ensure that only 1 value is returned for the combination of customer first name, customer last name and customer birth year and therefore we have put constraints.

Prompt is saved with the name Function prompt.

Note:  As mentioned in the example 2 – we have to put the single quotes around the columns Customer First Name & Customer Last Name. (Not required around the column Customer birth year since its data type is numeric). 

Set the presentation variables for the respective columns. Do not select the all choices option.

Click the Constrain check box.

It is not required to set the default values as in the database function call we have passed the default values for the 3 input columns.

Set the appropriate labels.

Preview the Prompt.

Now let us go back to dashboard page and pull the Function prompt and the direct database request calling a function using DDR

You can see the customer ID (14762)- This has been returned by the function FIND_CUSTID and the default values we passed to this function.

Select some other combination – Don’t leave any prompt empty. Make selection from all the prompts.

To clear the selected values in the prompt you can click on the Page settings option & click on “Clear My Selections” and select some other combination.

You can see a button below the prompt with the name “Clear”. This does the same job as the “Clear My Selections” option.

To add this button- open the dashboard creation console-Below the prompt Function prompt- add one Text object & paste the below JavaScript there.

<div class="XUIPromptEntry minibuttonOn"><a href="#" onclick="return PersonalizationEditor.removeDefaultSelection(false )">Clear</a></div>

Note: Don’t forget to select the “Contains HTML Markup” 

Press “Clear” button to clear the selected values in the dashboard prompt.

Alternatively you can use the guided navigation so that the second section (Customer ID) will appear only when function returns a single value for the combination of customer first name, customer last name & customer birth year. You have to make selection from each prompt.

Have a nice day !!!!