Conditional Prompts

building semantic layer
1

I was trying to implement conditional prompt (not optional) in the universe i.e I want a prompt to pop up only when some condition is true otherwise use some other value.

To implement this, I created a dummy object with the conditional prompt in the where clause. On selecting this object for creating a report, the query generated is:

SELECT
MY_TABLE.COLUMN_1
FROM
MY_TABLE
WHERE
( (MY_TABLE.COLUMN_1= CASE WHEN (1=1) ’ the Condition (1=1) is always true
THEN ‘12407’
ELSE (@Prompt('Enter a value ',‘A’,‘My_Class\My_Object’, mono,free,persistent))
END
)
)

The problem is even though the condition should restrict the prompt from executing but it does pop up. My take on this is that the BO query parser is parsing the condition statement (1=1) only after resolving all the values in its THEN-ELSE clause. Hence the prompt is getting executed no matter what the condition is.

Can somebody suggest a solution

candyfloss (BOB member since 2011-03-09)

2

Hi,

Close :slight_smile:

First, BO checks for all occurrences of @prompt() and @variable() functions in a SQL statement. If there are any then it asks a user for values (in a form of prompts) then it replaces the functions with the values provided in prompts, finalizes the SQL statement and then it fires it against a database.

So, it’s not possible to have a conditional prompt in a query that would pop up under some conditions and would not pop up if the conditions are not met.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

3

Thanks Marek,

The parsing sequence that you mentioned seems perfect.
Are you sure that its not at all possible to have conditional prompts? Is it not possible to have a get around this by any means?
I’m a bit worried since this is one the major business requirements :frowning: . Any help would be appreciated.

Regards,

candyfloss (BOB member since 2011-03-09)

4

I am afraid but there is no alternative. If there is a prompt defined in a query then it will appear as a prompt to a user when he/she refreshes a report.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

5

All @ functions must be resolved before any SQL is executed. The syntax for @Prompt() and the other @ functions is processed by the query writing engine of whatever tool you’re using prior to sending any SQL code to the database. That means there is no way to make a prompt conditional on a database row result.

Dave Rathbun :us: (BOB member since 2002-06-06)