Dynamically setting parameters for scheduled reports 4

[jraheja]

I have a report that I want to run every Monday. I want the parameters to be set dynamically by a routine i.e to run the report from last Monday to this Sunday (preceding the Monday it is to be run). Every time I go and set the parameters manually and schedule the report. Is there a way to do this dynamically?

 

[Turkbear]

Hi,
If the parameter is used to set a selection criteria based on the date then just place a formula in the criteria and skip using a parameter, something like:

{table.date_field} in (CurrentDate - 7) to CurrentDate


will return all records dated in last 7 days ( from date report is run)

 

[jraheja]

That is a truly great idea. I will try this out...

 

[bdreed35]

You can also just use the Last7Days function:

{table.date_field} in Last7Days

But I think you actually want is this:

{table.date_field} in (CurrentDate - 7) to (CurrentDate - 1)

But if for whatever reason you can't run it on Monday, and need to run it on Tuesday, the above would give you Tuesday to Monday.
If you want the flexibility to run it any point from Monday to Saturday and still get the previous week Monday to Sunday then use this:

{table.date_field} in (minimum(lastfullweek)+1) to (maximum(lastfullweek)+1)

The only day that this would be off is if you tried to run it on a Sunday due to Crystal's defined week of Sunday to Saturday.

~Brian

 

[jraheja]

Thanks! Can I make a business calendar and capture against that?

 

[bdreed35]

What version of CE are you using? CE 10 has calendars built in, and you can purchase a calendar add in for versions before that.

But the short answer is yes you can, and it will either be free or you have to buy an add in to get that functionality.

~Brian

 

[jraheja]

Yes, I have CE10. What I am thinking is I make a business calendar and then use above approach to retrieve the date from that. Is that possible?

 

[jraheja]

Simple question for some of you - how do I say

{MCTCase.Case_Open_Date} in "2004/1/1" to CurrentDate - gives me error "string is required here" Instead of "2004/1/1" if I used CurrentDate - 3 it worked fine...

 

[lbass]

Try:

{MCTCase.Case_Open_Date} in date(2004,01,01) to CurrentDate

-LB

 

[saltcoats]

I have a similar problem; I am running CE10 against an Oracle Db. I scheduled a report to run at midnight Sunday night with the selection of Current datetime to currentdatetime + 7 based on a date time field. By the time the report runs it is 12:01am, or later and I miss the data that has a time value of 00:00 am. A lot of activity seems to be happening on the server at that time. I would like to run the report a few hours later when the activity dies down. Can I schedule for current date and force the time at 00:00 am?

 

[bdreed35]

Sure. Use currentdate instead of currentdatetime

{table.datetime} in currentdate to currentdate + 7

~Brian

 

[saltcoats]

Thanks, this helps. I am still not capturing current date though. Do I have to schedule it the day before? i.e.

{table.datetime} in currentdate to currentdate + 8

 

[caimoss]

We have a situation where we also run some reports regularly, whether that is daily or weekly. Here's how we got around the problem (using CE 9):

In our case, our reports run off of stored procedures. We designed the stored procedures to expect a start and end date as parameters (you could probably use a single "Run Date").

Within the stored procedure, we add logic to see if either date has been provided, or is it has been set to NULL.

If the dates have been set to NULL, then there is logic within the stored procedure to figure out the "previous week" or "previous day", and it will set it's own start and end dates.

Then, in crystal, you just schedule the report to run at the interval you wish, but tell it to run with NULL dates (since you don't want to have to change the date every time). Whenever it runs, the stored proc will figure out the date range you need.

It just takes some planning and organization to make sure you know when the report will run, and then work backwards into the stored procedure to figure out the dates it should choose.

For example, our weekly report must gather data from Sunday at midnight till the following Saturday at 23:59:59. Our first business rule is that the report can not be run in an automated fashion (with the NULL dates passed in) for the current week--it will only ever select data for the most recent fully completed week as specified above. This allows us to run the report any day of the current week--it will figure out what the previous Saturday 23:59:59 date is, work backwards to the Sunday midnight date, then use those dates to select the data. All of our "automated" scheduled instances are set with NULL dates.

Business rule #2 is that is someone wants data from the current week, they simply need to request that report "ad-hoc", and specify their own start and end dates. The stored proc works just the same, but since the user provides dates, it will use whatever dates they have asked for.

Hope that helps!

 

[caimoss]

...one more thing I forgot to mention...

Your date logic in the stored procedure can't be something like 'currentdate + 8' or 'currentdate - 7'. The key is being able to establish a business rule for a timeframe like "a full 24 hour period" or "a full week". Then, you compare the current date/time to your business timeframe, and find the last complete business timeframe that doesn't include the current date/time. This gives you a bit more flexibility to run the reports at something other than "Sunday exactly at midnight". In the case of a daily report, for example, that allows you to run it at any time in the next 24 hours, before the next "daily" period has been completed.

 

[saltcoats]

Thanks.