How to get yesterdays date by default using @prompt function in universe.?

[Former Member]

Hi Expertise,

Can anyone help me in getting the yesterdays date by default after report gets opened.

for example if the report is opened on 23-june-2015 then the report must show the values of 22-june-2015.

later if the user refreshes the report, he should get the option for selecting the date. ie OPTIONAL PROMPT

The syntax I had tried is EL_EmployeeLoginTime.InTimeDate=@Prompt('Enter value(s) for Intime Date','D','My Logins\Intime Date',Mono,Free,Persistent,{'2015-09-15'},User:0,optional)

Please improve the above example to get the yesterdays date by default in the report.

I am using SAP BO 4.0 SP3

Thanks & Regards,

Santhosh.

[Former Member]

Hi Santhosh,

Create two queries on report.

Query1 : create a prompt like below

@Prompt('Enter value(s) for Intime Date','D','My Logins\Intime Date',Mono,Free,Persistent,{'9999-12-30'},User:0,optional)

& create another called prompt_Date = case when [Intime Date] = '9999-12-30' then sysdate()-1

else [Intime Date]

Now create another query

Query2 : In this query apply filter on [Intime Date] object & choose result from another query & choose result from Query1.

I hope this will help.

Thanks,

Swapnil

[Former Member]

Hi santosh

Try this promt

Date= (case when @Prompt('Enter Date DDMMYYYY:','A','Date',MONO,FREE,Not_Persistent,{ 'Current Date' },User:0)='Current Date' then convert(varchar(8),getdate()-1,112) else @Prompt('Enter Date YYYYMMDD:','A','DAte',MONO,FREE,Not_Persistent,{ 'Current Date' },User:0) end)

Regards,

Vinay G

[Former Member]

Hi

Sorry to say you that, I am unable to get that thing.

Actually I am confused of what to keep in select clause.

for now i have tried as

EL_EmployeeLoginTime.InTimeDate = (case when @Prompt('Enter Date DDMMYYYY:','A','Date',MONO,FREE,Not_Persistent,{ 'Current Date' },User:0)='Current Date' then convert(varchar(8),getdate()-1,112) else @Prompt('Enter Date YYYYMMDD:','A','Date',MONO,FREE,Not_Persistent,{ 'Current Date' },User:0) end)

wrote all the query in select and nothing in where. it is throwing the error as

Parse failed: could not prepare the statement.

Thanks,

Santhosh

[Former Member]

Hi Santhosh,

Please create a Universe Filter with below code .

convert(varchar(10),EL_EmployeeLoginTime.InTimeDate,112) = (case when @Prompt('Enter Date DDMMYYYY:','A',MONO,FREE,Not_Persistent,{ 'Current Date' },User:0)='Current Date' then convert(varchar(10),getdate()-1,112) else convert(varchar(8),@Prompt('Enter Date YYYYMMDD:','A',,MONO,FREE,Not_Persistent,{ 'Current Date' },User:0),112) end)

Regards

Ashu

[former_member193452]

Hi Santhosh,

Date manipulation is dependent on database vendor.  Check out a great explanation of date usuage here:

http://www.dagira.com/2007/08/22/dynamic-dates-part-i-yesterday-and-today/

Jacqueline

[Former Member]

Hi

As per the link, I have tried using getdate() as my DB is SQL Server. it is throwing error as Parse failed.

Are there anymore ideas to achieve this.?

Thanks,

Santhosh.

[Former Member]

getdate() will not parse but it works for SQL Server. This is because of the parsing algorithm within Designer expecting a table.column or owner.fuction construct for an object.

If you are on 4.1SP6 or higher then you can use this object as your default value in your prompt.

Regards,

Mark