BusinessObjects Board

Optional Prompts with Extras

I’m trying to build a report that has a number of prompts based on application answers.

The DB is of format

QSTN_ID | ANSWR_NUM | ANSWR_CHAR | ANSWR_DATE plus a few other columns not relevant to this issue.

There are about 3 mandatory prompts and then about 10-12 optional.

I was building the where clause of the Webi XI 3.1 SP2 Report as (not correct syntax but the general idea)

(QSTN_ID = 1 AND MAND_PROMPT “LOCATION”) AND
(QSTN_ID = 2 AND MAND_PROMPT “BEDROOM COUNT”) AND
(QSTN_ID = 10 AND OPT_PROMPT “MAX STEPS”) AND
(QSTN_ID = 15 AND OPT_PROMPT “HIGHRISE”)

but needless to say it doesn’t work as all the and conditions mean that QSTN_ID must = 1 and 2 and 10 and 15 which obvioulsy it can never do.

I’m probably having a dumb moment being late Friday afternoon and all that, but has anyone got any suggestions ?

Bascially I’m trying to pull back all the people who answered Q1 with a specfic location, specific bedroom count for Q2 and optionally a specific step count for Q3 and optional value for for Q4 .

Thanks in advance,

Nick


Nniixx :australia: (BOB member since 2009-09-02)

Change AND to OR and try…


nithya_raj (BOB member since 2007-02-03)

Thanks for responding. :smiley:
I presume you mean

(QSTN_ID = 1 AND MAND_PROMPT “LOCATION”) OR
(QSTN_ID = 2 AND MAND_PROMPT “BEDROOM COUNT”) OR
(QSTN_ID = 10 AND OPT_PROMPT “MAX STEPS”) OR
(QSTN_ID = 15 AND OPT_PROMPT “HIGHRISE”)

which does return data but obviously means that if any of the conditions within parenthesis are true then the condition is met whereas I want both the first conditions to be met and optionally the 3rd and 4th if the prompts are answered.


Nniixx :australia: (BOB member since 2009-09-02)

Check this post - This is an FAQ on how to design an optional prompt.

Apply this to the last 2 prompts alone…


nithya_raj (BOB member since 2007-02-03)