Direct database request in OBIEE - Part 2

Example No 2:  Playing with Direct database Request and the dashboard prompt

I want to select the customer name from the dashboard prompt & display it along with its last name using the sql in Direct Database Request option. Let us see how we can achieve this.

Look at the below SQL in DDR.

SELECT ( @{Cust_Fname_Var}{'Abel'} ) AS Customer_Firstname, CUST_LAST_NAME  FROM CUSTOMERS

Cust_Fname_Var is a presentation variable and the default value is ‘Abel’. Also I observed that specifying default value was something required. And note the spaces between the brackets in the sql.

I have saved this report with the name “Customer First/Last Name”

Let us create a dashboard prompt on the Customer First Name column.

Now I will pull the prompt and the report on the dashboard.

Note: Abel was the default value of the customer first name.

Let us select some other first name & see what happens.

I have got the error. See the details below-

The workaround is we need to put the single quotes around the Customer First Name.

So we have to do the necessary modifications in the SQL for the Customer First Name prompt.

Use the below SQL in the prompt.

SELECT CHAR (39) ||CUSTOMERS.CUST_FIRST_NAME||CHAR (39) FROM SA2 

(Note:  39 is the ASCII code of single quote character (‘) & the function CHAR (number) - Converts a numerical value between 0 and 255 to the character value corresponding to the ASCII code)

Preview the prompt. Observe the single quotes around the customer first names. 

Save the new changes to the prompt & go back to the dashboard.

Now it is working –

Few examples - 

Example 3 is shown in part 3.