I need to write a function for use in reports that will take a specified date and determine the starting date of the month that is x number of months in the past.
Any suggestions?
Could you please clarify your request with more details or an example ? i don't think this will be a problem once we know what is exactly required ..
The report as it is now has the following prompts which the user has to enter. If the user does not enter exactly a period of 12 months, which is what should be entered, the report may include more or less than 12 months of data. This is contrary to the businss reules for ths report.
The report query has the following detail filter:
[Posting Date] between ?BeginDate? and ?EndDate?
I need to use only an ending date (which might be defaluted to the current date) and then change the filter so as to retrive data for 12 months,
• Beginning with the 1st day of the month 12 months prior to the ending-date month
• Thru the last day of the ending-date month
If this logic could be encapsulated in a Function, then the logic become “portabale†for use in other reports of this type.
OOPS, sent the previous reply prematurely...
The report as it is now has the following prompts which the user has to enter. If the user does not enter exactly a period of 12 months, which is what should be entered, the report may include more or less than 12 months of data. This is contrary to the business rules for the report.
Report Begin Date
Report End Date
The report query has the following detail filter:
[Posting Date] between ?BeginDate? and ?EndDate?
I need to use only an ending date (which might be defaulted to the current date) and then change the filter so as to retrieve data for 12 months,
• Beginning with the 1st day of the month 12 months prior to the ending-date month
• Thru the last day of the ending-date month
If this logic could be encapsulated in a Function, then the logic becomes portable for use in other reports of this type.
And if the "number of months back" could be a variable, that would be even better.
If it would be easier, here is my email address: srramirez@cvty.com
TIA :)
Sam,
Try something along these lines.
[POSTING_DATE] between _first_of_month(TO_DATE(TO_NUMBER(current_date) - 365)) and _last_of_month(current_date)
or you could try substituting your parameter name for the current_date.
[POSTING_DATE] between _first_of_month(TO_DATE(TO_NUMBER(?EndDate?) - 365)) and _last_of_month(?EndDate?)
This worked for me in Report Studio 8.3
Best of luck!