This is a favorite amongst SQL-RD users. The purpose of the wizard will be to guide you through the process of setting up a number of reports to be generated at defined intervals and delivered to one or more defined destinations - together.
Example: Your entire monthly report pack (consisting of a number of reports) can be sent to a recipient with all reports attached to the single email. You can also zip up the reports into a single zip file, or export them out to a single Excel workbook, or export them in a single PDF file.
To start the Wizard, Go to the Home Tab, then select Package.
Package Schedule - General
The General Section determines the most basic properties of the package schedule.
Package Name: The name of the package.
Create in: The SQL-RD folder where the package is stored.
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.
Continue To Package schedule - Schedule
Package schedule - 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.
Working Day: Run the schedule starting on a specific day of the month. Indicate which day of the month the schedule will run. E.G. run the schedule on the 4th working day of the month.
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 Package Report Schedule - Destinations
Packaged Report Schedule: Destinations
In this section, you will decide where your schedule will be delivered. In a Package Schedule, all reports enclosed in the package will be delivered to the same destination(s).
The list in the center will display the list of destinations you have added to the schedule. You can organize the various destinations’ order by clicking on the green up and down arrows.
By clicking Edit, you can edit any destinations you have created.
The delete button will remove that destination from your schedule.
Import allows you to add default destinations. Please see Options section for more details on default destinations.
To add destinations click on the “Add” button. Alternatively, click the small down arrow to immediately select a destination.
If you clicked the small arrow, skip this paragraph and proceed to the next. If not, a new window will appear. In the “Type” Field, select your destination from the drop down list.
These are the destinations currently support by SQL-RD:
Disk- Send a report to folder on the machine or any UNC path.
Email- Email a report via MAPI, SMTP, GroupWise, or SQL-RDMail.
Fax- Send a report via Fax Machine. (Requires a V92 Fax Modem)
FTP- Send a report via FTP or SFTP
ODBC- Output reports to a database
Printer- Send your report to a connected or networked printer.
SharePoint- Upload the report to a SharePoint directory.
SMS- Send the report as a text message.
*Please see Destinations for details.
Select the Destination that you wish to use. A window will appear that will allow you to set up the destination. Also an Inserts window will appear separately.
In this scenario, we wish to email the report to the recipient. Select email as the destination.
Setting up an Email Destination:
Enter the email address of the recipient. If there is more than one, simply place a semi colon followed by the next recipient’s email.
Add a subject to the email, and attach any additional file you desire.
In the body, you can write a customized message to your recipients. Using inserts you can add dates and user constants.
By default the report comes attached. For details on the email destination please see destinations.
Setting the Report Format:
Each destination has a format tab in its mini wizard. To select a format for your report, go to the format tab.
From the dropdown list you can select a format for your report. SQL-RD has a wide variety of formats to choose from. For details on all formats available in SQL-RD please see the Formats section.
In this Scenario, Jamie needs the report in PDF format. Select Adobe “Acrobat Format (*.pdf)” from the dropdown list.
If are finished with your destination’s settings, click OK to return to the wizard.
Click OK and you will be returned to the destinations screen where your destination (email in this case) will be listed. You can continue to add destinations by clicking on the add button and repeating the steps above.
Once you have created all of your destinations, click next to go to Report section.
Continue To Single Report Schedule - Exception Handling
Package Schedule - Report
Different from the Report section in a standard schedule, this section can add multiple reports to one schedule.
Adding a Report to the Package: To add a report to your package, select add.
Report Service URL: Enter your report Service URL. *Hint: You pull reports from multiple Report Service URLs by simply adding a different report.
Report Location: Authenticate to the server, browse to the report, and select OK.
*Hint: SQL Azure Reporting Services- when writing your report, make sure you have enabled Forms Authentication. You should use the 2010 .asmx address for your reporting service URL.
Report Name: Name your report.
Format: Set the format for your report. This format is set for this particular instance of the report.
*Note: Certain formats open up additional options below. For details on each option refer to Destinations.
Enabled: Enable or Disable this specific report in the Package.
Collect Report Fields: Pull fields from your Crystal Report. See Inserts for more information.
Selecting Parameters: Go to the parameters section.
In this section, you will determine the parameters for your report (if any). If your report has no parameters, you may skip this section by clicking next.
There are 3 requirements in order for SQL-RD to detect your SSRS 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 SQL-RD Constants from the inserts window, then drag and drop your
desired constant to the parameter field.
Multi-Select Parameters and Cascading Parameters
SQL-RD can handle SSRS Reports with Cascading parameters, or reports where you can select multiple parameter values..
Simply select the top level parameter value. The secondary parameter value options will be populated based on your previous choices. Selecting All Available Values will run the report given all the values.
Setting Report Options (DataSources):
Set database logins, refresh rate, and whether to use the saved data in the report. To set the Database login, double click the database and enter your user name and password. You can preview the report here as well.
Naming the Report:
Use Default Naming Convention: Output file name will be named the same name as the report.
Customize File Output Name: Give the output file a name of your choice.
Customize Output Extension: Change the file extension of the output file. EX. Instead .cob rather than .doc.
Append Time/Date Stamp: Add a time date stamp to your output file name.
determine options if this report in the Package is blank. Click here for more information on blank reports.
Once you have finished creating the settings for this report, click OK to return to the Reports screen.
Click on add to add more reports to the package list.
Merging Multiple Reports:
SQL-RD has the ability to take multiple Reports and merge them into a single text, PDF, or Excel file.
Simply Check the box to indicate which file types will be merged.
SQL-RD will then merge all excel outputs in the package into a single excel file.
Run Package Using Multiple Threads: Each report in the package can run concurrently. See Multi-threading for more info.
Save Snapshots: Save a picture of the report as it is produced. See Snapshots for details.
Now that you are finished setting your reports, click Next to continue to Package schedule - Exception Handling
Package schedule - Exception Handling
In this section, you will set error handling for your reports, as well as options for blank reports.
Treat as “error” if not completed in X minutes: if a report takes longer than the specified amount of time to run, this option will treat the schedule as an error and follow the appropriate action. The “Auto-calculate” option instructs SQL-RD automatically determine how long a schedule should take to run the report. If it takes longer than the calculated amount of time, then it is an error.
*Note: if manually determining the error timing, please double check the runtime of the reports in order to get the correct time estimate.
On error, retry executing schedule every X minutes up to Y times: If an error has occurred with the schedule, you can set the interval and number of times you wish the schedule to attempt a retry. By default SQL-RD will make 3 attempts.
SQL-RD and Blank Reports:
SQL-RD has the ability determine if a report is blank. Meaning that if a report genuinely has no data behind it, then SQL-RD can do a number of things with the report and the schedule.
Check if a report is blank: With this option you can check if a report is blank.
Ignore the report: if the report is blank, do not send the report. The report will not be delivered to the destination.
Method Tab: Select the Method that will determine whether a report is blank.
Native: SQL-RD will check to see if the report returns any data. If not, the report is considered blank.
File Size Check: If the report output is below a specified file size, the will be 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.
Once you are satisfied with your error handling, click next to move to Package Schedule - Custom Tasks .
Package Schedule - Custom Tasks
In the section you have the option of setting up custom tasks. Custom tasks are business process automation tools that can be auto triggered before or after a report runs. For more details on Custom Tasks, please see Automation Schedules for a full description of each task.
If you have no desire to add a Custom Task, you can click the finish button to complete the schedule.
Packaged Reports Context Menu
Right-Click on a package to see the following actions.
Add Report: Use this to add one or more reports to an existing package.
Copy: Use this to copy package. Right - click in the "white space" of the folder you wish to copy it to and select "Paste".
Rename: Rename a package
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 report is added to a package, SQL-RD caches (saves) a copy of the report. All executions of the package 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 SQL-RD. This option will refresh all the reports in a package.
Execute Package: 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. They only move on if the schedule is run automatically by one of the schedulers.
Delete Package: Selecting this option will delete the schedule.
Properties: Selecting this option will bring up the "Properties" module containing configuration information for that package. For more information on Properties, click here.
Open: This will open the package and show its constituent reports in the right hand pane as shown below:
You can right-click on each of the constituent reports to see the context-sensitive menu shown in the picture above.
Enabled: Use this to enable or disable the constituent report
Refresh: Pulls through changes to just that report which were made outside SQL-RD.
Rename: Renames the selected report
Preview: Generates a preview of the selected report.
Delete: Deletes the selected report from package
Properties: Displays the configuration properties of just that report. For more information on Properties, click here.
Test Schedule: Use this option to test the schedule and export it to selected "test" destinations.
Split into Single Schedules: This will split all the constituent reports in the package into Single Report Schedules. Note: This process will automatically delete the package once the splitting process is completed.
Ad-Hoc Email to Recipients: Select this option to send an ad-hoc email to all recipients of this package. 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 SQL-RD.
Packaged Reports Schedule - Properties
To access a Schedules Properties, right click on the package and select properties.
Similar to the wizard, you can adjust changes to your package here.
For more information on each of the menu items, select the relevant link below:
History: Review your schedule's successes, and or failures.