BusinessObjects Board

BO 4.0 - how to make a re-usable, complex, optional prompt

building semantic layer
#1

We’re trying to create a template date prompt to be used across multiple universes, and also be used against multiple date fields (for instance, Transaction Date, Invoice Date, etc)

The prompt should display a list of values like the below (there’s about 30 total):

Date Range START_DATE END_DATE

D: Yesterday 12/02/2015 12/03/2015
M: Month Before Last 10/01/2015 10/31/2015
M: Month to Date 12/01/2015 12/02/2015

Our initial attempt at this (creating a derived table, and then some aliases against the derived table, with one alias for each date type such as Transaction Date, Invoice Date, etc) was a failure - the sql generated is wrong, and includes the sql that’s just supposed to provide the list of values. I think we need to use a different approach entirely.

We’d like to be able to use the same prompt for multiple date fields, but would settle for just getting this to work (using both the StartDate and EndDate) for one date, and then just reusing the SQL.

Thanks for reading so far. I would greatly appreciate any ideas! Feel free to ask questions and I’ll edit my notes to answer

persimmon (BOB member since 2015-12-03)

#2

Check this thread:
https://bobj-board.org/t/152613

BO_Chief :us: (BOB member since 2004-06-06)

#3

While this sample is set for a DB2 Utility table. It works the same with and Date field in the table you are using in your Universe.

There is a website with many such commands that you can build and set as a “Condition” and use over and over again.

CAPC_DATE_REF2.CAL_DATE >=
Case
When Dayofweek(current date) =1 Then current date - 90 DAYS
When Dayofweek(current date) =2 Then current date - 90 DAYS
When Dayofweek(current date) =3 Then current date - 90 DAYS
When Dayofweek(current date) =4 Then current date - 90 DAYS
When Dayofweek(current date) =5 Then current date - 90 DAYS
When Dayofweek(current date) =6 Then current date - 90 DAYS
When Dayofweek(current date) =7 Then current date - 90 DAYS
End
AND
CAPC_DATE_REF2.CAL_DATE <=
Case
When Dayofweek(current date) =1 Then current date - 0 DAYS
When Dayofweek(current date) =2 Then current date - 0 DAYS
When Dayofweek(current date) =3 Then current date - 0 DAYS
When Dayofweek(current date) =4 Then current date - 0 DAYS
When Dayofweek(current date) =5 Then current date - 0 DAYS
When Dayofweek(current date) =6 Then current date - 0 DAYS
When Dayofweek(current date) =7 Then current date - 0 DAYS
End

swolfe :us: (BOB member since 2014-06-20)