Record Select Formula For Date Ranges

[JoyCR9]

My CR9 report requires unique date ranges every month. For eg: July31-Aug27; Aug 28-Sept 17; Sept 18-Oct 29...and so on.

I've been playing with various formulas to plug in as a record selection formula with no success. Mainly with select and case statements.

My database element is {service_call.create_dt}. Oracle/SQL/Crystal 9.

Any suggestions?

Thanks in advance,
Joy

 

[rhinok]

In order to provide you with a good answer, we need more information:

[ol][li]What are the unique date ranges used for? Do you need to calculate the appropriate range for a month in your record selection criteria, or do you need ranges for formulas (column headers, for example)?[/li]


[li]What do you mean by unique? How are the date ranges selected? Last Friday of every month is the last day of the month, 4-4-5 fiscal months, etc... Are the dates arbitrary or are they assigned via logic?[/li]


[li]Is this an ad-hoc report or do you need to run this report on a recurring basis, which would require automatic calculations and logic?[/li][/ol]

If you can provide some of this detail, we can probably help you


~Kurt

 

[JoyCR9]

1. What are the unique date ranges used for? Do you need to calculate the appropriate range for a month in your record selection criteria, or do you need ranges for formulas (column headers, for example)?

The date ranges are for the date the service call was created. I need to run this report based on a date range parameter that is not consistant every month. I want to know if I can create a formula for record selection based on the wacky date parameters. What I'll be extracting is the service calls created in these date ranges. I'm good to go with everything in the report just don't want to enter date ranges each month.

2. What do you mean by unique? How are the date ranges selected? Last Friday of every month is the last day of the month, 4-4-5 fiscal months, etc... Are the dates arbitrary or are they assigned via logic?

This question made me giggle. There is no logic, the date ranges are assigned by corporate office. As you can see in my example there is not a consistant number of days in each range. They are arbitrary.

3.Is this an ad-hoc report or do you need to run this report on a recurring basis, which would require automatic calculations and logic?

I need to run this on a reoccuring basis. In my example " eg: July31-Aug27; Aug 28-Sept 17; Sept 18-Oct 29...and so on" the report needs to be run Aug 28, Sept 18, Oct 30...and so on.

Is that enough to work on?

 

[kskid]

As far as Record Selection, you'll just need two date parameters, {?FromDate} and {?ToDate}, and place it in your Select Expert

{service_call.create_dt} in [{?FromDate} to {?ToDate}]


Create running total for each date range requested by corporate.

1. Perform a distinct count on service Calls with the following formula

For example,

{service_call.create_dt} in [date(2004,7,31) to date(2004,8,27)]

2. Place the running total in both the detail and group footer sections.

If you don't have a group defined, then create a false Group

a. In the report header, place the following formula

//@falseGroup
WhileReadingRecords;
1

b. Insert a group on @falsegroup and suppress the groupname

3. suppress the detail section

4. You should now have totals for each of running totals you created.

Cheers,

Larry

 

[rhinok]

The date ranges are for the date the service call was created. I need to run this report based on a date range parameter that is not consistant every month. I want to know if I can create a formula for record selection based on the wacky date parameters. What I'll be extracting is the service calls created in these date ranges. I'm good to go with everything in the report just don't want to enter date ranges each month.

If you're going to run this report manually (or allow the users to do so), then you can use the following record selection (assumes two date parameters):

{service_call.create_dt} in {?FromDate} to {?ToDate}

This means you (or your users) will have to plug in dates in order to manually run the report (something it appears you're trying to avoid).

This question made me giggle. There is no logic, the date ranges are assigned by corporate office. As you can see in my example there is not a consistant number of days in each range. They are arbitrary.

If the date ranges are truly arbitrary, then the reports can't really be scheduled on an automatic recurring basis. You can mimic a recurring schedule, however, if you know the dates ahead of time: [ol][li]If you use CE 10, you can create a business calendar by which to schedule the report, but you'd need to hard-code the date ranges into your record selection criteria (a bad thing).[/li]



[li]If you use CE8.5 or 9, then you'd have to schedule the reports for each month individually, but could modify the parameters for each scheduled report, which means you won't need to hardcode the selection criteria. Unfortunately, you'll be stuck creating new schedules as date ranges become known. This works in CE10, too.[/li][/ol]


~Kurt

 

[JoyCR9]

select currentdate
case Date (2004,06 ,22 ): {SERVICE_CALL.CREATE_DT} in Date (2004, 06, 10) to Date (2004, 06, 21)
case Date (2004,07 ,22 ): {SERVICE_CALL.CREATE_DT} in Date (2004, 07, 10) to Date (2004, 07, 21)
case Date (2004,08 ,22 ): {SERVICE_CALL.CREATE_DT} in Date (2004, 08, 10) to Date (2004, 08, 21)
Default: False


I think the above is doing it for me. Thanks all! I will continue to play with this.

 

[rhinok]

Joy,

If you use that formula in your record selection criteria, then it won't be passed to the database for processing. All of the records will be returned to the client (your pc) for processing, filtering out the results after you've already taken the performance hit. Run the report and open Database|Show SQL Query - you'll see that the dates aren't passed to the db in the WHERE clause.

Additionally, what's the purpose of checking if the current date is the 22n'd? Are you going to run this report every single day and try to kill the return if its not the 22nd?

I can supply more efficient code that is passed to the db, but I need to know what you're trying to accomplish.


~Kurt

 

[JoyCR9]

Kurt,

I do know ahead of time what my dates are. Since they aren't consistant I have a tendancy to forget that it's time to run the report.

I am using Visual Cut, not sure if you are familar with this, but it allows me to schedule my reports.

I can set up a schedule for each range, but I'd like to know if there's a cleaner way to do the task.

On Aug 28 I need to run the report for those new calls logged between(and including) July 31-Aug 27
On Sept 18 I need to run the report for those new calls logged between(and including) for Aug 28-Sept 17.

I snag the service_call_id and do a distinct count based on the date range service_call_create_dt.

 

[Myla944]

Hello JoyCR9,

Thanks to Synaspevampire's help in a post I had similar to yours with strange date ranges I was able to do this with his help and get the date ranges I needed based on strange date ranges:

You can create a range parameter to select beginning and ending dates and then in the Select Expert:

{table.date} in {?dateparm}



Myla