The example below illustrates the simple idea behind dynamic report schedules and what they are intended to achieve:
The Springfield Donut Company has 1500 stores worldwide. Ray is the Crystal Reports guru at Head Office. He has to send each store manager a report of their weekly statistics every Monday. He does not want to write 1500 static report schedules.
To make matters worse, stores which are not doing well are regularly closed off, and new stores are being brought on-line weekly.
And that's not all. It has just been announced that SDC has just acquired a rival company and 800 new stores are coming on board tomorrow morning.
It's the middle of summer, so 2 of Ray's 3 staff are on vacation. He has a meeting with the Chairman in 3 hours, and, if he can demonstrate that everything will be in place an running by 5pm today, he will get a $25,000 bonus.
"What I need", he thinks to himself, "is a schedule which will
- reel through the database,
- populate the StoreId parameter automatically,
- run the report for that store,
- email it to the store manager using the email address in the database,
- and repeat the above actions for every store in the database.
I need this to happen automatically every Monday at 0100 hours."
This is the most basic problem which the Dynamic Report Schedule feature attempts to resolve. There are many other functions which the Dynamic Report Schedule will perform, and many are related to your needs, and the level of SQL language and programming expertise you have. The instructions and examples below are written for NON advanced users and therefore constitute some of the more simple functionality. Advanced users should simply experiment.
To resolve the issue illustrated above:
Start by writing a report with a single parameter called StoreID. Write the report so that when a StoreID is entered into this parameter, the resulting report is populated by that store's sales information only. We will call StoreID the Key Parameter.
Make sure that a database table somewhere (ODBC compliant) has some of the other information we will need e.g. the email address to email each store's report to, or the folder you want to export it to, or the printer you would like the report to print out on. For example:
The values can be stored in the same database or even the same table, but are typically stored in a different database e.g. HR database as these are maintained on a daily basis and are therefore more likely to be right. It also takes a lot of mundane administration work from you!
In simple terms here is what SQL-RD will do with a dynamic schedule:
It will look at a list of StoreIDs defined by you. e.g. Select StoreID from mytable where closeddown <> 'T' (Don't be scared, the wizard will walk you through setting up SQL queries)
It will pick the first StoreID in the list and enter it into the report.
The report will be generated for that store.
The generated report will now be delivered to the specified email address and SQL-RD knows this because you will tell SQL-RD where to get the email address e.g. select email from anothertable where StoreID = [StoreID]
If you are still confused, don't worry. As we go through the wizard, it should all become clearer.
To start the Wizard, click File - New and select "Dynamic Schedule" on the Menu.
The wizard may also be started by selecting it from the "New Schedule" dropdown on the Toolbar. Or you can select "Dynamic Schedule on the Task Pane.
To begin creating a Dynamic Schedule, go to the Home Tab and select Dynamic.
Continue to Dynamic Schedule - General
Dynamic Schedule - General
To begin creating a Dynamic Schedule, go to the Home Tab and select Dynamic.
The first section of the wizard will appear.
Create in: The SQL-RD folder where the schedule is stored
Report Service URL: Enter the URL for your SSRS reports server.
Report Location: Authenticate to your reports server, and browse to the desired report.
*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.
Schedule Name: The name of the schedule.
Description: A short description to help other users identify exactly what this schedule is and what it is expected to do. SQL-RD can pull in a description from your report's properties as well.
Keyword: Enter some keywords which can be used later by Smart Folders to identify this schedule.
Continue To Dynamic Schedule - Schedule
Dynamic Schedule - Schedule
This section involves setting the time for schedule execution.
Destination: Select how you would like your report to be delivered. This will also determine some of the options that will show up later in the process. For example, if you select email, you will be asked for email address later.
There are variety of options you can use to determine when a schedule will execute:
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.
In this Scenario, Ray will need to run this schedule every day.
Continue To Dynamic Schedule - Key Parameter
Dynamic Schedule - Key Parameter
Similar to the Report Section, Ray will determine the parameters for his report. In a Dynamic schedule, the key parameter value will determine how the report will be run.
Set the Report parameter: Select from the dropdown list which parameter will be used to look up your destination. Values in this parameter will be used to look up both the appropriate destination and also generate a unique report for each value.
Populating the Parameters:
There are two routes you can pursue:
1. Populate Key Parameter with data from a database:
Ray wants to run the report for a large number of stores. Normally, he would print a list of all the StoreID that needed a report. He would then run the report, enter one ID, export and email the report. He would move to the next store in the list and do the same again. For SQL-RD to automate RAY's actions, SQL-RD will need a list of StoreIDs which it will reel through - producing a unique report for each store and sending it to the corresponding email address. Using this option, Ray can build the list of stores the report should run for. In short, the list of parameter values which SQL-RD should use to run the report.
This is the most common option for users creating dynamic schedules. SQL-RD will use a query to automatically feed key parameter values into your report based on a table. 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.
The Results of your query will be displayed. Based on your criteria, These records will be the parameter values will feed into your report.
You should now notice your report parameter being populated with an insert. This means SQL-RD will reel through your table, pull the Key parameter values and feed them into your report. A unique file will be generated for each value.
*Hint: SQL-RD will only run the report for values found in your query, not all values found in your report. Therefore, only the data in you system must be maintained rather than SQL-RD!
2. Populating the Parameter using Static data:
Ray wants to run the report against the same StoreID. He cannot use a Static schedule as, unfortunately, the store's email address changes weekly. This would mean he would have to edit the static schedule and enter a new email address each time the email changed.
So he writes a Dynamic schedule and uses this option to enter a static StoreID e.g. [?StoreID:] = "1991". The report will now always run for StoreID number 1991 only, but the email address it will be sent to will be the value entered in the database against this store at the time the schedule is run.
If the database is maintained daily, then the report will always be sent to the latest email address.
This option would be chosen if you DO NOT want SQL-RD to feed multiple parameters in to the report via database. Rather, one parameter value will be chosen, and it will run for a changing list of recipients.
Continue To Dynamic Schedule - Linking
Dynamic Schedule - Linking
Here is where Ray connects to the database and tells SQL-RD where it will find the StoreID value, and what the corresponding email address is.
In this Section we will establish a link between the key parameter values we have selected earlier, and the location of the corresponding destination information.
DSN Name: Select the name of the DSN which connects to the database.
UserID: Enter the database logon username (Optional).
Password: Enter the database logon password (Optional)
Click Connect to establish a connection to the database.
Select the table name (e.g. ContactInfo) and the column in that table (e.g. StoreID) which matches the parameter value (StoreID) which you determined in the previous step..
Select the column in the table that holds the email address ( or folder path or printer name) that corresponds to the above parameter value.
Using a Static Destination: If you want all your exported reports to go to a single destination e.g. a folder. Use the "Use static destination" option to make this happen.
In the instructions and explanations above, it is assumed that the email address column resides in the same table as the key parameter (StoreID). It is also assumed that all key parameter and email records qualify to receive a report. Very often, this is not true. There are certain criteria which must be met (e.g. Ray may wish to run reports only for stores whose ID's are between 10 and 97 only, with email addresses which are not blank or null, and email addresses which do not end in ".co.uk". Also, the email address which corresponds to the key parameter is actually stored in a separate table) before a report is sent.
To achieve the above advanced functions, select "Advanced" from the dropdown list shown in the picture.
This functionality is for advanced users only.
Note: The screen is structured in a standard format which will be familiar to database programmers and people who are familiar with SQL or boolean statements.
For more information on how to write SQL statements, please see your database administrator.
You can build your database query visually using the form provided, as shown above.
If you are fluent in SQL, you may wish to enter your SQL statement manually.
Click to see the advanced interface. Here we can add the extra limiting criteria. You can type directly in the "Edit Query" box and enter the SQL statement.
Continue To Dynamic Schedules - Destinations
Dynamic Schedules - Destinations
In this section, We can customize our email message, and determine the format of the reports.
*Note: A specified Destination will immediately appear. It has already been specified in an earlier step.
*Note: The "To" field is not visible. The recipients have already been specified in earlier steps.
Use the Inserts Function to customize the email subject and body as desired. Simply drag and drop Dynamic Table Fields from your Inserts list.
Select the format for your report.
Continue To Dynamic Schedules - Report Options
Dynamic Schedules - Report Options
This section covers database logins for your report, cached data, and snapshots.
Database Login: If the database requires logon credentials, check this option and enter the required username and password. You only need to enter a value if your database, security, networking or infrastructure require it.
DB Name: Enter the name of the database.
Credentials: Enter the User ID and Password for the Database. Clicking on the Advanced tab, you can set logins for each table.
Resume with Cached Data: Enabling this option instructs SQL-RD to resume any failed schedule run where it left off using the cached data instead of starting the schedule from the beginning.
Save Snapshots: Save a Picture of the report as it is produced. Choose the amount of time will be stored in the system before deletion. View Snapshots for more info.
Continue To Dynamic Schedule - Exception Handling
Dynamic Schedule - Exception Handling
In this section, you will set error handling for your report, 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 report 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.
Continue To Dynamic Schedule - Custom Tasks
Dynamic 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 Schedule 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, 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 report, you must select this option in order to pull the changes into SQL-RD.
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. 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. For more information on Properties.
Dynamic Schedule - Properties
To access your schedule properties, right click on a schedule and select properties.
For more information on each of the menu items, select the relevant link below:
History: Review the schedules history. Successes, failures, and other data is located here.