Dynamic Package Schedules allow you to create packages of at least two reports which share a common key parameter. This allows you to create a package of reports to be delivered to a unique destination.
Example: We will create a Dynamic Package to deliver the Marketing and Sales reports for clients and deliver this set to a unique destination.
To start the wizard, Select the Home Tab, then click on Dynamic Package.
Continue To Dynamic Package Schedule - General
Dynamic Package Schedule - General
In this section you will define the most basic properties of the package. Similar to the basic Package Schedule, the reports will be selected later on in the wizard.
Package Name: The name of the schedule
Create in: The SQL-RD folder where the schedule 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 Dynamic Package Schedule - Schedule
Dynamic Package Schedule - Schedule
This is the Schedule section. Here, you will define when the schedule will execute. Also you will determine the destination type.
Select from the dropdown list the destination of the report. You will configure the destination in a later step.
There are a variety of options for creating your schedule:
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 Dynamic Package Schedule - Reports
Dynamic Package Schedule - Reports
Here we will select the various reports for our schedule.
Click on Add, to add a report to your schedule.
*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.
After browsing and selecting the report, Name the report And select the decided format for the report. In a Package Schedule, different reports can have different formats.
Now we will determine the parameters for the report. Using inserts pull the appropriate key parameters from your inserts list and drop them into the report's parameter fields.
There are 3 requirements in order for SQL-RD to detect yourSSRS 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.
If you want the parameter to run for a specific static value (rather than being dynamically populated), simply select it from the dropdown list or manually type the value in your parameter field.
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.
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
In the naming section, we can define the name of the output file.
Use Default Naming Convention: Use the report name as the file output name.
Customize file Output name: Type in your own name for the output file.
*Tip: Use your dynamic parameters to further customize the file output name. Drag and drop the parameter values into the customize file name field. Now each file output will have a unique file name.
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.
Setting Report Options:
Set database logins, refresh rate, and whether to use the saved data in the report. Please check Package Schedule - Report Options for more info.
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.
To continue adding reports click add.
Now that you have added all of your dynamic reports, you have the option to merge these reports into a single PDF or Excel document.
To merge multiple reports, check the box on the appropriate format, then give the name of the merged file. The reports that were selected to be outputted to PDF would be merged if you check the PDF Merge Option; Excel outputs would be merged into a single Excel Document.
Continue To Dynamic Package - Key Parameter
Dynamic Package - Key Parameter
Now it is time to determine your key parameter. This the parameter that will be used to look up values from your database.
Similar to a Dynamic Schedule you have 2 choices in the way the parameters will populate:
Populate the key parameter with static data: rather than dynamically populating the key parameter, all reports in the package will run for a specific parameter.
Populate key parameter with data from the database using a query: Using SQL-RD's Simple Querying tool, create a query that will look up values in your database and populate the key parameter with each of the database values. Select this option.
Use SQL-RD's simple Query Tool to identify the database, table and column that holds the parameter values.
DSN Name: Drop down and select a DSN.
We strongly recommend System DSNs so that the DSN is visible to SQL-RD 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 SQL-RD (more secure) and overcomes a large number of security restrictions which Windows places on DSNs. When using Windows Authentication, make sure that the SQL-RD 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 SQL-RD should use to log on to the database
Password: Enter the password associated with the above user.
*Tip: Notice that the field in the report and the column in the database can be different. Either way, the parameter values must match the values in your report.
Only Return Records where: Use this section to parse out any items in the database you want/do not want included when the schedule runs. EX Only run for USA Stores.
Advanced Users: Use the advanced tab to write your own query.
Parse: Click on Parse to reveal your query's results.
Click OK and then OK again to return to the Key Parameter window.
Now that we have established how the report will be populated, click next to proceed to the Linking section.
Continue To Dynamic Package Schedule - Linking
Dynamic Package Schedule - Linking
In this section, a connection between your key parameter values and their corresponding destinations must be established.
There are two options you can pursue:
Use A static destination for this dynamic schedule: Rather than looking up a corresponding destination for reach report output, send all generated reports to a single email address, folder, SharePoint etc.
Populate the destination with data from a database: Link the individual report output to the corresponding destination such as an email or folder.
Select the database and table destination data is housed. Select the Column that matches your key parameter values. Select the field that identifies the destination for the report.
Now you have established the link between your key parameter values and your destinations. Click next to proceed to the Destinations section.
Continue To Dynamic Package Schedule - Destinations
Dynamic Package Schedule - Destinations
Throughout the wizard, we selected our destination type, and identified where the destination information is stored. Now it is time to finish setting up our destination.
First you will notice the destination type you selected in the schedule section will immediately appear.
If you have select disk, you may select cancel in the "browse for folder" window.
The Destination Configuration window will appear. In inserts, go to the "Dynamic Schedule Table Fields" list.
Select the field that contains your destination, then drag it to the appropriate field for your destination.
For this example, Drag and drop the destination field into the file path field.
For more information on setting up a Disk destination click here.
Notice Format is not decided in this section. Format was defined in the Reports section.
Click OK to return to the main destinations screen.
Date/Time Stamp: add a date or time stamp to the file output name.
Resume with Cached data: SQL-RD will cache data when it runs the reports. If there is an error or failure for any reason during runtime, SQL-RD can restart the schedule from where it left off.
Continue To Dynamic Package Schedule - Exception Handling
Dynamic 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.
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 Dynamic Package Schedule - Custom Tasks
Dynamic 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.
Dynamic Package 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 Package: When a schedule is first created, SQL-RD caches (saves) a copy of the report. All executions of the scheduled are performed using this copy. If you make changes to your master reports, you must select this option in order to pull the changes into SQL-RD.
Open: This opens your schedule in a new desktop view to allow access to the reports.
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. They only move 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.
Test Schedule: This allows you to test the schedule using a destination you can select or create.
Split into Single Schedules: This allows you to split the package into Single Report schedules.
Adhoc Email: This allows you to send an Adhoc email to recipients of the schedule. See Adhoc Email for more information.
Create Shortcut: This allows you to create a shortcut that can be used to execute the schedule.
Dynamic Package Schedule - Properties
To reach the Properties menu, right click on the package and then select properties.
For more information on each of the menu items, select the relevant link below:
History: Review your schedule's history. View its success, failures and any associated information.