A data-driven schedule allows you to use dynamic report data that is retrieved from an external database table at run time. You can also use static text and default values should you wish. You can use Data Driven schedules for a number of scenarios.
Here are some examples:
- Distribute a report to a fluctuating list of recipients. For example, you can distribute a report throughout a large organization where recipients vary from one month to the next, or use other criteria that determines group membership from an existing set of users. E.g. select * from mytable where firstname = ‘john’ will generate the report and distribute only for records where the recipient’s first name is John.
- Filter the report output using report parameter values that are retrieved at run time. For example, the parameter value returned from the table for Last Name will be “Doe” for John Doe and “Smith” for John Smith.
- Determine the delivery type and report format dynamically at run time. For example John Smith gets the report as a PDF file by email with a customized email body and John Doe gets the report as an excel spreadsheet by FTP upload to his web server.
The following simple example will illustrate the process of creating a Data-Driven Schedule.
You have a report which has a large number of parameter and destination combinations. Representing the combinations in a table may look something like the example below (Yellow = parameters)
You need to have the report run once for each record. The parameter values entered in the table above should populate the parameters in the report and the report should be delivered to the destinations shown in the table.
Please note that with the above data when the schedule runs, the following will occur:
John Doe will receive only one email for the reports from sectors 1 and 3. He will also get the reports from sectors 2, 3 and 34 delivered to his reports disk location.
Peter Crimble will have his report printed on the Printer he specified.
Jorge Minola will have a copy of his report sent to John Doe to the disk location he specified and a copy uploaded to his FTP server and folder.
Anil Maharaja will receive an email as requested.
John Wu will have his report delivered to his email and FTP server as requested.
Note that as the data changes to reflect the requirements, the reports will be delivered as necessary without requiring any changes to the schedule in CRD.
Using a Data Driven schedule CRD will reel through the database table and generate and deliver a report for each record using the information provided. Here’s how to set up the schedule:
Go to the Home Tab, and select Data Driven.
Continue to Data-Driven Schedule - Data Driver
As you can see, Thomas received a unique report. The Report and name has been customized. Moreover, the message is customized for each user!
Data Driver - Data-Driven Schedule Properties
The Data Driver is where you set up the source for data for your data driven reports. If your data source or data selection criteria changes this is where you will need to make any required changes.
DSN Name: Drop down and select a DSN.
We strongly recommend System DSNs so that the DSN is visible to CRD even when the user associated with the DSN is logged off.
All communication to databases (including Access databases) is done through ODBC, so you must setup a system DSN to the database before you can use this facility.
When setting up DSNs we recommend (if possible) that you use Windows Authentication (Trusted Connection). This ensures that your username and password are not required to be stored in CRD (more secure) and overcomes a large number of security restrictions which Windows places on DSNs.
When using Windows Authentication, make sure that the CRD NT service user (or background application service user) has full rights to the database otherwise, though you will connect when you are logged in, automated scheduling will fail if the Service user has not got rights to the database.
When setting up a DSN to a SQL server, you must ensure that the "Default Database" setting is set to the database you are connecting to (Windows defaults this to "Master").
UserID: Enter the User ID CRD should use to log on to the database
Password: Enter the password associated with the above user.
Click “Connect” to connect to the data source.
Select the table from the database that holds the required data. you can refine your selection query by using the simple or advanced tabs.
After clicking Parse, CRD will display a list of results. Based on your Criteria CRD will use the returned data to run the report.
Click “Build” to specify the data selection criteria. This will return you to the Build Data Driver tool.
Key Column: Select the key column for the data that is returned for the reports. The information that drives the schedule is required to have a key column so that each row in the table is uniquely identified by the value in this column. The key column is there as an identifier which is used to troubleshoot in cases where there is a problem with one of the records.
Group reports by email address: You can instruct CRD to group reports that are sent to the same email address. Please note that enabling this option disables the ability to embed the reports in the email body for email destinations.
Continue to Data-Driven Schedule General
General - Data-Driven Schedule
Schedule Name: The name of the schedule
Create in: The CRD folder where the schedule is stored
Report Location:The path to the report
Description: A short description to help other users identify exactly what this schedule is and what it is expected to do
Keyword: Enter some keywords which can be used later by Smart Folders to identify this schedule.
Crystal Report Fields (Optional): Check this if you need to use Crystal Report Fields in this schedule.
Continue to Data-Driven Schedule - Schedule
Schedule - Data-Driven Schedule
In this section, you will decide when the report will execute. There are a variety of options:
Daily: Run a report every day or at a frequency of days.
Repeat every X Days- EX. Run the schedule every 3 days.
Weekly: Run a report on a weekly time frame.
Repeat every X weeks- EX. Run the schedule every 2 Weeks.
On- Select the specific days of the week the schedule will run. If only once a week, select only the day of the week it will run. EX. Run every Monday, Wednesday, and Friday.
Weekdays: Run the schedule Monday through Friday.
Monthly: Run the schedule on a monthly time frame.
Use the following options- Checking this box will enable you to select frequency options such as the “last Thursday of the month.” Also you can include or exclude specific months from the schedule.
Annually: run the schedule every year at a specified time.
Custom Calendar: Select the custom Calendar you wish to use. You can create a new custom calendar from the menu as well. Please see Custom Calendars for a tutorial.
Other: Other scheduling options.
Run Schedule every X Minutes, hours, days, weeks, months, years
None: No scheduling is required for this item.
Next, you will specify the time that the schedule will run as well as repetition options.
Schedule time: Specify the exact time you want your schedule to run. *Tip: keeping in mind how long it takes for your report, time your schedules accordingly!
Exception Calendar: choose a calendar that will instruct the schedule to NOT run on those specified days. Please see Custom Calendars for a tutorial.
Next to run on: displays the next run date of your schedule.
Repeat ever X hours or minutes: run your reports on a reoccurring basis during a specific time frame. EX Repeat every .25 hours until 5PM.
Enable this schedule: to enable the schedule leave the box checked. To create this schedule disabled, deselect this box.
Once you have finished creating the timing for your schedule, click finish to proceed to the next stage.
Continue to Data-Driven Schedule - Output
Output - Data-Driven Schedule
This screen shows the destinations which have been set up for this schedule.
Add: Click to add another destination. For more information on adding destinations, click here.
Edit: Select a destination and click to edit it's properties. Or simply double-click on the destination.
Delete: select a destination and click this button to delete it.
Click to import from the list of default destinations.
Customizing the Destination
With the Data Driven Schedule, you can determine the delivery method of your report based on your data. In your table, specify the delivery method by creating a column for each delivery type (FTP, SharePoint, Email etc.). For each record specify their email, fax number, and so on.
*Tip: for email destinations, simply add a semicolon after an email address to send the same instance of the report to another email address.
Data driving the Report's Distribution
As mentioned above, CRD will pull delivery data from your database.
In this example, we will be using email addresses found in the table.
With Data Driven inserts, simply drag and drop the field that indicates your desired destination (email in this case).
Now CRD will deliver a unique report based base on a corresponding email address.
Customize Recipient Messaging
You can also customize the messaging the recipient receives. Using Data Driven Inserts, CRD will automatically pull data from your table and use it to customize the subject, body or report format.
Simply drag and drop fields from your table to the desired position. For example, to customize an email greeting, type the greeting, and add the recipients name as shown above.
Hint: In the next step we will cover Data Driving the parameter. Worry not.
Continue creating destinations as required. Click next to continue to Data-Driven Schedule - Report
Report - Data-Driven Schedule
With CRD, you can Data Drive any Parameter in your report. Ensure the parameter values for each record are listed in your data base.
Using Data Driven Inserts you can pull these values into your report.
Drag and drop your Data Driven inserts into the desired parameter fields. If you do not wish to enter data driven value in your parameter, you can still manually type a value or select them from the dropdown box.
Repeat this process for all the parameters in your report.
If you wish to learn more about CRD and Parameters see below. Once you are finished setting up your report parameters, continue to Data-Driven Schedule - Report Options
Also About Parameters:
There are 3 requirements in order for CRD to detect your Crystal Report parameters and their parameter values:
1) The parameter must be in use in the report
2) The parameter must be visible
3) The parameter must be set to be prompted at runtime
If these three requirements are achieved, you should see the parameters in your report listed in this screen.
For each parameter, select from the drop down list the value the report must use. In a single schedule, only one parameter value can be run at a time per parameter. You can type a value into the field as well.
Set as Null: Set no value for this parameter.
Ignore: Do not run this parameter, ignore it at runtime.
Select from the dropdown list the upper and lower value for the parameter.
Include Value upper/lower: Include the selected parameter value in the range.
No lower/upper bound: include all values above the upper value, include all values below the lower value.
*Tip: to enable a report parameter to run for a date constant (Current Date, Yesterday etc.), select CRD Constants from the inserts window, then drag and drop your
desired constant to the parameter field.
Selecting Date Parameters via Calendar
If your report contains a date parameter, you can either manually enter dates as described earlier in this topic, or select the desired date from the built in calendar. Simply click in the date parameter field to open the calendar. Select the desired date. Click OK. If the parameter is also a time parameter, this can selected as well.
The buttons below enable you to preview the report, adjust formulae, and review subreports. You can also re-query the report for parameters.
Preview: view the report output given the selected parameters.
Formulae: View edit and Parse the Record Selection Formulae.
Subreports: set parameters, authenticate, and re-query subreport parameters.
Set Parameters: select the parameters for the sub report. Note: Crystal 2008 API does not allow access to sub report parameters in CRD. CRD will run the subreport as they are created in Crystal.
Re-query Parameters: Re-query the report for the new parameters.
Set Database Login: Set Authentication information for the subreport.
Set Table Login: Set Authentication information for individual tables in the subreport.
*Note: Subreport parameters must be linked to the parameters in the main report
Advanced: Shows you the report's record selection formula and allows you to edit it:
To modify the record selection formula, simply type your modification into the formula space. Once this formula is changed, CRD will always use this formula for this schedule. It will ignore the original report's formula.
The changes are stored in CRD. The report in your database is not changed, so it can be reused for other schedules or for its previous general use.
Report Options - Data-Driven Schedule
In this section we will determine the database logins, and other options.
Database Login: If the database requires logon credentials, check this option and enter the required username and password. If credentials are not required, uncheck the option and continue as normal (default). All values are optional. You only need to enter a value if your database, security, networking or infrastructure require it.
Saved Data: By default, CRD will always attempt to run the report using the very latest data in your database. If your report is saved "with data" and you want CRD to simply export the data in the report, then check this option.
Refresh: Check this option if you want to refresh the schedules before every execution.
Enable section bursting: Use this to determine what sections in a report you would like to be visible.
Continue to Data-Driven Schedule - Exception Handling
Exception Handling - Data-Driven Schedule
Reports fail for any number of reasons - database connectivity issues, database busy, network issues and more. This screen gives you the ability to tell CRD what to do if it encounters an error.
On error, retry executing: If set to 0, CRD will deem the schedule as "Failed" the first time it encounters an error. The schedule will not run again until its next scheduled time. Change the value to tell CRD how many times you want it to retry running the report before declaring it as "Failed".
Treat as failed if not completed: If a report "hangs", this option tells CRD to declare it "Failed" after a certain length of time and to move on to other schedules in the queue. A report which "hangs" will not report an error, so the "Retry Scheduling" option will not apply.
Perform checks for blank report:
If a report is blank because it genuinely returned no data, recipients can misconstrue this as an error with the scheduler. This option allows you to identify genuine empty reports and instruct CRD on what to do with them.
Check if a report is blank: With this option you can check if a report is blank.
Only run tasks if ALL reports are blank: If all reports are determined to be blank. Then run Exception Handling tasks.
Ignore the report and Subsequent tasks: if the report is blank, do not send the report. The report will not be delivered to the destination. Ignore the custom tasks as well.
Method Tab: Select the Method that will determine whether a report is blank.
Native: CRD will check to see if the report returns any data. If not, the report is considered blank.
SQL Query: Select this option to use a user made query that will determine if the report is blank. If the query returns no results, the report is blank.
Actions Tab: Select an action from the task list. This task will be executed in the event that a schedule is blank.
*Tip: You can send a notification if a report is considered blank instead of sending the report. Simply select “check if a report is blank” then select “Ignore the report.” In the actions tab, select “Send Email” from the list. Compose your email and save.
Continue to Data-Driven Schedule - Tasks
Tasks - Data-Driven Schedule
Add, Edit, delete and change the order of tasks from this screen. For more information about the Custom Tasks module, click here.
Use to export a task list, or import a previously exported task list.
Data-Driven Context Menu
Right-Click on a schedule to see the following actions.
Copy: Use this to copy an existing schedule. You can then paste it into another folder or the same folder. Simply open the folder you wish to paste to, right-click in the "white" space and select "Paste". Follow the on-screen instructions to complete the process.
Paste: Paste a previously copied schedule
Rename: Rename a schedule
Enabled: Schedules are Enabled when there is a check beside this option. To stop a schedule from running, or to "pause" it for a while, select this option to remove the check. Disabled schedules will not run till they are enabled again.
Refresh: When a schedule is first created, CRD caches (saves) a copy of the report. All executions of the scheduled are performed using this copy. If you make changes to your master report, you must select this option in order to pull the changes into CRD.
Preview Report: Click this option to preview your report.
Execute Schedule: This will execute the schedule immediately. Note that the next run date and time is not moved on as a result of a manual execution. This only moves on if the schedule is run automatically by one of the schedulers.
Delete Schedule: Selecting this option will delete the schedule.
Properties: Selecting this option will bring up the "Properties" module containing configuration information for that schedule. For more information on Properties, click here.
Test Schedule: Use this option to test the schedule and export it to selected "test" destinations.
Convert to Package: Select this option to convert a Single Report Schedule to a Packaged Reports Schedule. Please note that you can select multiple single schedules to convert into a single Packaged Report Schedule. You are given the option to select which single schedule will be used as a template for the Packaged Report Schedule. Please note that the single schedules are deleted once the Packaged Report Schedule is created. For more information on Packaged Reports Schedules, click here.
Ad-Hoc Email to Recipients: Select this option to send an ad-hoc email to all recipients of this schedule. You can use this to alert recipients to a planned system outage, or any other useful information. For more information on this feature, click here.
Create Shortcut: Use this option to create a shortcut you can save in any location on your PC. Execute the shortcut to execute the schedule in CRD.