When I Refresh query want to get default value as sysdate in prompt

[Former Member]

Hi All,

I have Below Prompt-Condition in my universe as follows:

@Select(A Drv(reconid)\End Date Time)<=add_months(to_date(@Prompt('Enter Date YYYYMM',,,,),'yyyy-mm'),1)

and

@Select(A Drv(reconid)\End Date Time)>=add_months(to_date(@Prompt('Enter Date YYYYMM',,,,),'yyyy-mm'),-12)

and I have Sysdate also in to_char(sysdate,'YYYYMM') for this.

Now i want to get default value as sysdate in prompt every time i refresh the query?

Note: Oracle 10 DB we are using and This report is on Webintelligence.

When I Tried bellow got an error:

@Select(Derived Table\Recon Date)<=add_months(to_date(@Prompt('Enter recon Date YYYYMM','A','@Select(Derived Table\SYS1)',mono,free),'yyyy-mm'),1)

and

@Select(Derived Table\Recon Date) >=add_months(to_date(@Prompt('Enter Recon Date YYYYMM','A','@Select(Derived Table\SYS1)',mono,free),'yyyy-mm'),-12)

Thanks in Advance!

[Former Member · Accepted solution]

Hi,

There is no possibility to define sysdate in default values.

However there is workaround I used in the past that works fine.

First create a derived table called "Dates" such like this:

select * from 
(select 'Current date' as invoice_date_char, sysdate as  invoice_date
union
select to_char(invoice_date, 'YYYY/MM/DD'), invoice_date
from Sales)   a

Create an object "Invoice_date" with the foillowing definition:

Dates.invoice_date_char

Then create filter like this:

Dates.invoice_date_char in @Prompt('Select date','A','Dates\Invoice Date Char',multi,constrained,,{'Current date'})

Regards

Didier

[Former Member]

Thank you all

[Former Member]

The code as below:

First i created END_DATE_CHAR Object:

Object customized query:

SELECT DISTINCT

to_char(.END_DATE_TIME,'YYYYMM')

FROM TRN_RECONCILIATION_RUN_H

UNION

SELECT 'SYSDATE' FROM DUAL

My Prompt code:

@Select(Prompt Drv\End Date Time)<=CASE WHEN @Prompt('PROVIDE DATE YYYYMM','A','Prompt Drv\End Date Time Char',MONO,FREE,,{'SYSDATE'})='SYSDATE' THEN

Last_day((TRUNC(SYSDATE)))

ELSE

ADD_MONTHS(TO_DATE(@Prompt('PROVIDE DATE YYYYMM','A','Prompt Drv\End Date Time Char',MONO,FREE,,),'YYYY-MM'))-1,-1) END

AND

@Select(Prompt Drv\End Date Time)>=CASE WHEN @Prompt('PROVIDE DATE YYYYMM','A','Prompt Drv\End Date Time Char',MONO,FREE,,{'SYSDATE'})='SYSDATE' THEN

ADD_MONTHS(last_day(TRUNC(SYSDATE))-1,-11)

ELSE

ADD_MONTHS(TO_DATE(@Prompt('PROVIDE DATE YYYYMM','A','Prompt Drv\End Date Time Char',MONO,FREE,,),'YYYY-MM')-1,-11) END

It is resolved.

[Former Member]

That solution is not working with Add_months

[Former Member]

Thanks for Informaton

[Former Member]

How do we do this on a OLAP universe? I am trying to create a publication for a report that has date prompt, When I try to schedule this publication the query should automatically select system date for date prompt and and send out the report. Is there a way to do this in CMC? Also wanted to note that My universe is based on SAP BW query and I can create a exit variable on BW query to do the same but wanted to know if we can do this in Universe or CMC.

Thanks in advance.

[Former Member]

Hi,

On SAP BW, user exit is the only valid solution to have sysdate as default value.

There is an MDX expression that exposes the sysdate but unfortunately this is not supported by SAP BW.

You have to pay attention that SAP BW calculations results only support numeric values, that's why people have to write a lot of user exit.

Regards

Didier