Here are code samples for creating YTD and MTD prompts in the universe designer,the prompts are written for Oracle :
1. YTD
This prompt allows the user to enter a date and get all the dates from the month of the chosen date till the beginning of the current month, for example:
If the user will type 05/05/2010 (mm/dd/yyyy) he will get the range: 05/01/2010 till the first day of the current month.
Code:
Table.date BETWEEN trunc(To_Date(@Prompt('Enter Date:','D',,Mono,Free),'mm/dd/yyyy hh24:mi:ss'),'mm') AND TRUNC(SYSDATE,'MM')
2. YTD specific year:
This prompt allows the user to enter a date and get all the dates from the first day of the year of the chosen date till the chosen date, for example:
If the user will type 05/05/2011 (mm/dd/yyyy) he will get the range: 01/01/2011 till 05/05/2011.
Code:
Table.date between trunc(to_date(substr(@Prompt('Pick date','D','folder\date_object',mono,free),1,10),'mm/dd/YYYY'), 'YEAR') and to_date(substr(@Prompt('Pick target date','D','folder\date_object',mono,free),1,10),'mm/dd/YYYY')
3.MTD
This prompt allows the user to enter a date and get all the dates from the first day of the month of the chosen date till the chosen date, for example:
If the user will type 05/05/2011 (mm/dd/yyyy) he will get the range: 05/01/2011 till 05/05/2011.
Code:
trunc(Table.date) BETWEEN trunc(to_date(@Prompt('Enter Date:','A',,mono,free),'DD/MM/RR'),'MM')
AND to_date(@Prompt('Enter Date:','A',,mono,free),'DD/MM/RR
