Skip to Content
avatar image
Former Member

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

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Sep 16, 2015 at 08:30 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member 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

  • avatar image
    Former Member
    Sep 19, 2015 at 04:33 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member 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

  • avatar image
    Former Member
    Oct 15, 2015 at 10:13 PM

    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

    Add comment
    10|10000 characters needed characters exceeded