CODEWITHSUNDEEP
sqlbusiness-objects
tarheels058
tarheels058

Reputation: 469

SAP BusinessObjects - Create a WHERE condition based on user prompt input

I have a report with a typical date column. I want the user to select a start date from @prompt. Then I want the end date to be 3 months less than the user selected start date.

Example:

SELECT Date,Y,Z
FROM TABLE
WHERE Table.Date  >=  @prompt('Enter value(s) for Acct Open Dt','D',Object',Mono,Free,Persistent,,User:0)
AND
Table.Date <= USER ENTERED DATE - 3 months

Is this possible? I have done this very easily in other reporting tools. It seems odd that BOBJ would not have this functionality.

Upvotes: 0

Views: 6531

Answers (2)

Maria Ruchko
Maria Ruchko

Reputation: 224

you can create new objects in the universe [promptDate] = @prompt('Enter value(s) for Acct Open Dt','D',Object',Mono,Free,Persistent,,User:0)

[promptDatePlusThreeMonths] = dateadd(month, 3, [promptDate])

and add a condition in the report

WHERE Table.Date between [promptDate] and [promptDatePlusThreeMonths]

Or just create a condition in the universe:

Table.Date between @prompt('Enter value(s) for Acct Open Dt','D',Object',Mono,Free,Persistent,,User:0) and dateadd(month, 3, @prompt('Enter value(s) for Acct Open Dt','D',Object',Mono,Free,Persistent,,User:0))

Upvotes: 1

Matt Busche
Matt Busche

Reputation: 14333

Unless I'm missing something, this is what you're looking for

SELECT Date,Y,Z
FROM TABLE
WHERE Table.Date  >=  @prompt('Enter value(s) for Acct Open     Dt','D',Object',Mono,Free,Persistent,,User:0)
AND Table.Date BETWEEN userentereddate AND dateadd(m,-3,userentereddate)

Upvotes: 1

Related Questions