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 !!!!
