schedule report with stored procedure

[deepsheep]

All the reports we have are accessed via the internet. Some of our clients in more remote areas have complained that the reports are extremely slow and cannot view some of them. We are trying to fix this.

Our thought is that we can schedule a report to run one a week or so and then it could be viewed much faster becase the numbers should already be crunched.

The reports are currently run on demand with a stored procedure that takes parameters that are date values in string format. These are inputed from the website.

I have seen a couple of options that may work:
1) email the reports to an individual.
2) schedule the reports and then make the reports available for download.
3) change stored procedure so it does not take parameters (does today to -7 days for example)

I need it to be as similar to what alreay exisits as possible so current users do not need to learn anything new. It also needs to be 100% automated. I'm currently using version CR 8.5 & CE 8.

What is the best way to accomplish my goal?

 

[rhinok]

If you're using CE8, then option 1 is out. You can't set destination to email in CE8.

Option 2 is possible, with appropriate licensing for the CE8 product. The users could log into CE and view/download instances of their reports.

If you want to create recurring schedules, then you'll have to embed a derived date range. You could still use parameters in your stored procedure, however. Basically you'd have a parameters: @ScheduleType, @FromDate and @ThrougDate. Write your stored proc so that if @ScheduleType = 'RECURRING' then the date is Today - 7 Else If @ScheduleType = 'ON DEMAND' then the date from @FromDate to @ThroughDate. This way the report can be run on a recurring basis or on-demand for a specific date range.

Why are you using Stored Procedures, by the way? Can the data processing be handled in a View? If you use a View, then you can embed the Parameters and the selection Criteria in the report itself. Following is an example of the selection criteria (all of which is passed to the DB):

//This is a sample Record Selection Statement for a report that returns records created
//on the previous business day.

//Switch statement allows for both 'On Demand' and 'Recurring' scheduled reports
//Switch statement used to derive the previous business day date
//Switch statement allows the Record Selection Criteria to be passed to the Database for processing.

{v_name.date} =
Switch
(
{?Schedule Type} = 'On Demand',{?Data Date},
{?Schedule Type} = 'Recurring' and DayOfWeek(CurrentDate) = 1,(CurrentDate - 2),
{?Schedule Type} = 'Recurring' and DayOfWeek(CurrentDate) = 2,(CurrentDate - 3),
{?Schedule Type} = 'Recurring' and DayOfWeek(CurrentDate) In 3 To 7,(CurrentDate - 1)
)
and {v_name.department} = {?Department Name}

 

[chelseatech]

Or use one of the third party scheduling tools like our cViewMANAGER. They will send the email you require.

Ken Hamady has a list of 3rd party products on his web site (

http://www.kenhamady.com).

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[deepsheep]

I have to use stored procedures because the data is spread across several tables and divided by month. A report can span two or more months and therefore two or more tables and we haven't found an easy way to put it together in Crystal.

Makeing the clients log in and download is a bad idea for us. It would be too different; most of the people useing the reports are very basic users. Even if I could point a link to what they would need to download, it still may be too much.

I will look into the othe ideas though.
Thanks and keep 'em coming!

 

[jedownsjr]

Why don't you try our method -- using CE 8.5, we set security so that users can only view historical instances (they cannot view on demand). We schedule the reports to run (mostly overnight), so they are already prepared when a user logs into the ePortfolio (canned out of the box) website -- this makes viewing reports very fast. Downloading to .doc, .pdf or other formats is as simple as clicking on one icon button and telling it where to save the file. It is a good solution for remote employees and those who are "technologically challenged"! I hope I understood your question.