Scheduling - Parameter (default value as a formula?) 1

[davida37]

Cyrstal Server XI
Cyrstal XI

Hi,
I need to schedule a report which has a date parameter. Because I want to schedule the report to run daily - I want the default value for the parameter to be "Today". Is it possible to do this?

The only way of setting the defualt value for a date parameter is to select an actual date - and not a formula.

the only way I can get around this is by duplicating the report and changing the Stored Procedure so the date parameter is replaced with getdate() at the stored Proceedure level. this just means lots of duplicated reports.

Please help. Thanks. David

 

[synapsevampire]

Go into the parameter in Crystal. and set the default date to 1/1/1970.

Then code the SP to default to getdate() when passed 1/1/1970, otherwise use what is passed.

-k

 

[davida37]

Brilliant. Thanks.

 

[RobbieB]

Davida, there are ways to do this through Crystal as well.
Create a copy of the report, delete the parameters and set the values you need for the report.
For instance, We have a sales report someone wants in their inbox every monday morning. I make a copy of the report with SchedVers at the end of the name and put it in a scheduled reports forlder. I change the selection criteria to:

{database.post_date} in today - 7 to today

you can also use currentdate - 7 to currentdate.
to get the previous weeks sales.

If they wanted it scheduled at the end of the day and just want to see the days sales you can set the select criteria to:

{database.post_date} = today

or

{database.post_date} = currentdate.

 

[synapsevampire]

RobbieB: The problem with your solution is that it is processed in Crystal, and ALL rows have to be returned by the SP, and then Crystal starts tossing out uneeded rows, which will likely make it very slow.

Database processing should be done on the database.

If they weren't using an SP than I suggest something along the lines of your suggestion, however still use a parameterto allow for defaulting, or using the passed value if it differs from the default.

-k

 

[RobbieB]

Exactly, That's why I started my post by saying "There are ways of doing this through Crystal...". It was unclear to me if the report was based on a stored procedure or if he created a stored procedure to deal with the parameter issue. So, I suggested a very easy way of dealing with this issue through Crystal.

 

[davida37]

Thanks both - I am using Stored Procs from both SQL Server 2000 and 2005 - so K's answer did the trick - and also meant I didnt have to replicate the report (for tme this is important - in terms of less future work).

thanks Robbie - its always good to know diff ways to deal with issues - esp if I find myself in a situation where I cannot use a SP.

 

[RobbieB]

No problem, sorry I didn't fully understand if you started with and SP or if you switched to using one to fix this problem. Good luck and happy reporting.