n w    w w w w

baner
You are here:   Home FAQ's CRM On Demand Getting a rolling date range report criteria
large small default
Getting a rolling date range report criteria
User Rating: / 0
PoorBest 
Ok say you want to have a report that selects opportunities (could be any record) for the current financial year and the next.  That is the filter needs to select from the 1 day of the current financial year to the last  of the next financial year (could be any number of years ahead).  The session variables  such as next_year, current_year etc, do not return a specific date but rather just the year value, so they cant work as a date filter.  You could hard code the filter, but remember to change it every year.

The way to do this using an expression is a combination of CAST and session variables.

Assuming you have fiscal year beginning April the from formula should be;

CAST(concat('1-APR-',cast(VALUEOF(NQ_SESSION.current_year)as char(4))) AS DATE)

This basically sets the from date to be 1-Apr-2009, if you run it when you are in 2009

the to date should be

CAST(concat('31-MAR-',cast(cast(VALUEOF(NQ_SESSION.current_year) as integer)+2 as char(4))) AS DATE)

Which sets the date to 31-Mar-2011

This is a good example of using CAST to manipulate dates.  Note the conversion of the year to an integer so that the '+2' arithmetic can be performed before conversion back to a date.

This approach could of course create any span of years forward or back.  You can use this approach on months to, except if you add or subtract the months to go outside the range 1-12, you'll create nonsense dates and generate an error.  Instead add the months with the timestampadd() function.  Basically something like


timestampadd(SQL_TSI_MONTH, 18,CAST(concat('1-APR-',cast(VALUEOF(NQ_SESSION.current_year)as char(4))) AS DATE))

This creates a date eighteen months forward from the beginning of the current year ie 1-Oct-2010.  Note the SQL_TSI_MONTHS, which indicates you are adding months rather than days, weeks, etc.