Date Functions Not Parameters

[pcollins1]

I am writing a report that pulls information for Month to Date, Year to Date, Previous Year Month to Date, and Previous Year Year to Date. I am able to do this using Date Params {?MTD}, {?YTD}, {?PYTD}, etc.. I have it set to run on the Nth day of month in eportfolio. However; I have to reschedule it at the beginning of every month adding the new date params.
Is there a way to use Date Formulas, so I can get rid of the Date Parameters, and it will automatically populate and use the next correct date ranges?

I do have a {Table.DATE) I am selecting against.

 

[synapsevampire]

Sure, if I understand your requirements correctly, it's surprising that people won't post examples, preferring to type out an interpretaion.

Record selection:

(
{table.date} >= cdate(year(currentdate)-1,1,1)
and
{table.date} <= dateadd("yyyy",-1,currentdate)
)
and
(
{table.date} >= cdate(year(currentdate),1,1)
and
{table.date} <= currentdate
)

This encompasses everything you need as it defines last year up to the current date, and this year.

Then you can chop those up within the formulas you use, as in:

{table.date} >= dateadd("yyyy",-1,minimum(monthtodate))
and
{table.date} <= dateadd("yyyy",-1,maximum(monthtodate))

I'm sure you can figure out the rest.

-k

 

[pcollins1]

Thanks for the info. I will give it a shot tomorrow when I get back to work.

Should I build this in the record selection statement formula, or would it be better to use a formula field?

Paul

 

[synapsevampire]

The first part is the record selection formula, the second is an example of a formula.

Read the post, the first part says record selection, the second speaks of formulas.

-k

 

[pcollins1]

Thanks K. I will give it a try.


Paul