12-Month Running Total

[jlhart76]

I have 20+ reports that are run each month, most are updated to show the most current 12-month span. Instead of changing the span on each report, can I automate it to only list the last 12 months? Also, these reports list the timeframe in the page header & in a graph. Can I automate these to change each month?

 

[synapsevampire]

By Changing the span on each report, I gather that you mean you're either using parameters or hard coding values in the report? And by last 12 months does that mean the last 12 full months, or are you including the current month, and is 12 months from the current date, or back to the 1st of the month?

To use the last 12 months use the Report-Selection Formulas->Record and place something like:

{table.date} >= dateserial(year(currentdate),month(currentdate)-12,1)
and
{table.date} <= dateserial(year(currentdate),month(currentdate),1)-1

This should help you understand the steps, but you may have to make an adjustment based on what your requirements are.

-k

 

[kskid]

Or you could use the following

{table.date} in
[dateserial(year(currentdate),month(currentdate)-12,1)
to_
dateserial(year(currentdate),month(currentdate),1)]


the to_ means to exclude this date (March 1, 2005)

Cheers,
-LW

 

[jlhart76]

Sorry, I forget to be specific when asking for help.

Data is entered on last day of month for totals for that specific month. Reports are then run for last full 12 month period.

Thanks so much to both of you for your quick replies. I appreciate it.

 

[kskid]

Then the formula would be

{table.date} in
[dateserial(year(currentdate),month(currentdate)-11,1)
to
currentdate

Cheers,
-LW

 

[kskid]

This assumes that you are sure that no one will update the database after you have completed running the report.

Otherwise, I would run it on the first day of the new month in which case the formula would be

{table.date} in
[dateserial(year(currentdate),month(currentdate)-12,1)
to
currentdate - 1

Cheers,
-LW

 

[synapsevampire]

Try my formula, I think it does what you want:

// Go back to the first day of the 12 month period
{table.date} >= dateserial(year(currentdate),month(currentdate)-12,1)
and
// Until the end of the last full month
{table.date} <= dateserial(year(currentdate),month(currentdate),1)-1

-k

 

[jlhart76]

Thank you so much! It worked perfectly.