Suggestion needed for filtering records using formulas

[src2]

I'm using CR9. I have several reports that are run monthly for the previous month. In my record selection filter I usually use something like ...openedon in lastfullmonth rather than having start and end date parameters. This is fine for 99% of the time but occasionally I'll need to run the report for an earlier month. I'll go in and hardcode a change to my selection formula which normally works ok. However, sometimes I've created formulas using lastfullmonth and end up with an error. I have to track down each formula where I used lastfullmonth and hardcode those as well. What I thought I might be able to do was create a formula and call it something like daterangefilter and set it to lastfullmonth. Then instead of using lastfullmonth I'd use daterangefilter. The problem with this is you can't use a date range as a result in a formula. Any ideas would be appreciated.

TIA

 

[rhinok]

In almost all of my reports, I build in functionality that allows for the report to be scheduled on a recurring basis via hard-coded date criteria (in lastfullmonth, etc...) or will allow the report to be run on an on-demand basis via parameters. Furthermore, this can be done efficiently with processing on the DB server.

The first step is to create a few parameters. Generally, I have a {?Schedule Type} parameter and {?Begin Date} and {?End Date} parameters. The values for {?Schedule Type} are 'On Demand' and 'Recurring', but you can use whatever syntax you need.

The next step is to create the record selection criteria:

{table.datefield} In
Switch
(
{?Schedule Type} = 'Recurring',LastFullMonth,
{?Schedule Type} = 'On Demand',{?Begin Date} To {?End Date}
)

This statement evaluates the type of schedule and passes either the last full month or the date range to the db for processing, depending on your requirements. You won't need to modify the report in the future for a special run if you use this method...




~Kurt

 

[rhinok]

I didn't read this post clearly enough - it looks like the issue is more with your formulas, not the record selection criteria - sorry

Can you please provide an example of a formula you'd like to modify?




~Kurt

 

[src2]

Kurt

I don't really have a formula I'm trying to modify but maybe I can clarify with an example. My record selection formula is:

if {CR_View_for_Steve.OpenedOn} in LastFullMonth then
1

I also have a variable called closed that is defined as:

if {CR_View_for_Steve.ClosedOn} in LastFullMonth then
1

I may have other variables using Lastfullmonth as part of the definitions. If I need to adjust my record selection formula to something like:

{CR_View_for_Steve.OpenedOn} <= Date(2004, 03, 01)
and {CR_View_for_Steve.OpenedOn} >= Date(2004, 03, 31)

then I have to locate all other formulas that using lastfullmonth and make a hardcode change there also. What I was trying to come up with was a solution to create a formula that could be used both in the record selection formula and in other formulas. This way I would only have to change one formula and everything else would work correctly. I was also trying to do it without introducing any parameters since 99% of the time lastfullmonth is what I need. Any ideas?

Thanks for your help!!