How do I use Data-Driven Schedules for MS Access in MARS?

A data-driven schedule allows you to drive all variables of your reporting requirements (parameters, destinations, output formats, etc.) from database tables and queries at run time.

Data-Driven Schedules for MS Access

A data-driven schedule allows you to drive all variables of your reporting requirements (parameters, destinations, output formats, etc.) from database tables and queries 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.

Scenario: 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)
MS Access. Data Driven Example in MARS
  • 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 in the database to reflect the requirements, the reports will be delivered as necessary without requiring any changes to the schedule in MARS.
Using a Data Driven schedule MARS will reel through the database table and generate and deliver a report for each record using the information provided.

How do I create Data-Driven Schedule for MS Access?

  • Go to Data-Driven.
MS Access: MARS Home Menu.

MS Access: Get values from database interface in MARS.

Building a Data Driver Wizard

  • 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.
  • ODBC DSN Name: Drop down and select a DSN.
We strongly recommend System DSNs so that the DSN is visible to MARS 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 MARS (more secure) and overcomes a large number of security restrictions which Windows places on DSNs. When using Windows Authentication, make sure that the MARS 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 MARS should use to log on to the database
  • Password: Enter the password associated with the above user.
  • Connect: Click Connect to connect to the Database.
  • Select the table from the database that holds the required data. You can refine your selection query by using the simple or advanced tabs.
  • Click Parse.
  • For more information of "Get Values From Database" interface, click here.
  • Click OK.
MS Access: Data-Driver Wizard in Data Driven Schedule in MARS.
  • 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 MARS 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.

Click Next to continue to the next wizard section.

General Wizard

MS Access: General Wizard in Data Driven Schedule in MARS.
  • Schedule Name: Name the schedule.
  • Create In: This section is for select the folder where you want to create the single report in.
  • Database Path: Browse and select the database where the report is located.
  • DB Password: Enter the password to the selected database if necessary.
  • WorkGroup Security: If your Access database uses Workgroup Security, then check this option and enter the required credentials.
  • Security File Path: Browse and select the security file (.mdw) which is used by Access to control security for this database. If you don't know what this is, or where it is stored, your System or Database administrator will be able to point you to the right file.
  • Important If you are using Workgroup Security:
    • You must join the workgroup that defines the user accounts used to access the database.
    • Your user account must have Open/Run and Open Exclusive permissions for the database object.
    • Your user account must have Administrator permissions for all the tables, queries, macros, tables and any other objects in the database, or it must be the owner of all tables in the database.
    • Your user account must have Administrator permissions for all objects in the database.
  • Report or Query: Select Report or Query to tell MARS which you wish to schedule.
  • Connect: Click to connect to the database.
  • Report Name: Select the report or query you wish to schedule from the dropdown list (only available after successful connection to the database)
  • Open Arguments: If there are some special arguments you would like MARS to run when it opens the MS Access database you should enter them here.

Click Next to continue to the next wizard.

Schedule Wizard

MS Access: Schedule Wizard in Data Driven Schedule in MARS.

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.
    • Sub options: Repeat every X Days.
Example: Run the schedule every 3 days.
MS Access: Schedule Wizard in Data Driven Schedule in MARS.
  • Weekly: Run a report on a weekly time frame.
    • Sub options: Repeat every X weeks.
Example: 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.
Example: Run every Monday, Wednesday, and Friday.
MS Access: Schedule Wizard in Data Driven Schedule in MARS.
  • Week Days: Run the schedule Monday through Friday.
MS Access: Schedule Wizard in Data Driven Schedule in MARS.
  • 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.
MS Access: Schedule Wizard in Data Driven Schedule in MARS.
  • 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.
MS Access: Schedule Wizard in Data Driven Schedule in MARS.
  • Annual: Run the schedule every year at a specified time.
MS Access: Schedule Wizard in Data Driven Schedule in MARS.
  • 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 more information.
MS Access: Schedule Wizard in Data Driven Schedule in MARS.
  • Other: Other scheduling options.
    • Run Schedule every X Minutes, hours, days, weeks, months, years.
MS Access: Schedule Wizard in Data Driven Schedule in MARS.
  • None: No scheduling is required for this item.
MS Access: Schedule Wizard in Data Driven Schedule in MARS.
  • Start Date: Enter the desired starting date for the schedule. This section can be the current date (providing schedule time has not already passed) or a date in the future.
  • End Date: If the schedule is due to end after a certain date enter that here. If the schedule is to run indefinitely, then leave it blank.
  • Schedule time: On the Next Run date, the package will run at this time.
  • Exception Calendar: Choose a calendar that will instruct the schedule to NOT run on those specified days. Please see Custom Calendars for more information.
  • Next to run on: The package will next run on this date.
  • Repeat Every: Rerun the package every x minutes from the scheduled time until your specified time.
For example, you can set up a daily package to run every day at 8 am, and to run every hour until 5 pm.
  • Until: After this date, there will be no automated scheduling of this package.
  • Enable this Schedule: Uncheck this option to Disable the package. Disabled packages are not deleted, but they do not execute automatically. You can re-start the automatic scheduling at a later date by checking this option again. Or right-clicking this schedule from the main screen and selecting Enable.

Click Next to continue to the next wizard section.

Destination Wizard

In this section, you will decide where your schedule will be delivered. 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.

MS Access: Destinations Wizard in Data Driven Schedule in MARS.
  • Add: Click here to add a destination. You have several options which are: Email, Disk, Fax, FTP, ODBC, Printer, Sharepoint, and SMS.
MS Access: Destination Wizard in Data Driven Schedule in MARS.
  • 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.
  • Import: click here to import from the list of default destinations.
  • For more information about Type of Destinations, click here.
  • For more information about Output Formats, click here.

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.

MS Access: Data Driven Example in MARS.
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, MARS 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).
MS Access. Using Data Driven Inserts in Email Destination in MARS.
  • Now MARS 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, MARS will automatically pull data from your table and use it to customize the subject, body or report format.
MS Access. Using Data Driven Inserts in Email Destination in MARS.
  • 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.

Click Next to continue to the next wizard section.

Report Wizard

MS Access: Report Wizard in Data Driven Schedule in MARS.
  • 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 MARS to detect your MS Access Report parameters and their parameter values:
    • The parameter must be in use in the report.
    • The parameter must be visible.
    • The parameter must be set to be prompted at run time.
  • 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, or drag and drop an "insert".
Tip: to enable a report parameter to run for a date constant (Current Date, Yesterday etc.), select MARS Constants from the inserts window, then drag and drop your desired constant to the parameter field.
  • Preview: View the report output given the selected parameters.
  • Advanced: Shows you the report's record selection formula and allows you to edit it:
MS Access: Report Wizard in Data Driven Report Schedule in MARS.
  • To modify the record selection formula, simply type your modification into the formula space. Once this formula is changed, MARS will always use this formula for this schedule. It will ignore the original report's formula.
  • The changes are stored in MARS. The report in your database is not changed, so it can be reused for other schedules or for its previous general use.

Click Next to continue to the next wizard.

Report Options

MS Access: Report Options Wizard in Data Driven Schedule in MARS.
  • In this section you will authenticate to the database the report runs against, set snapshots, and other options.
  • Login required for linked tables (optional): 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. Logins for individual tables can be set as well.
  • Enable Snapshots and keep snapshot for X Days: Save a Picture of the report as it is produced. Choose the amount of time will be stored in the system before deletion.
  • Report Metadata
    • Description (optional): You may write a short description to help other users identify exactly what this schedule is and what it is expected to do.
    • Keyword (optional): Enter some keywords which can be used later by Smart Folders to identify this schedule.

Click Next to continue to the next wizard section.

Exception Handling

MS Access: Exception Handling Wizard in Data Driven Schedule in MARS.
  • 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 MARS 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.
If manually determining the error timing, please double check the run time of the report in order to get the correct time estimate.
  • On error, retry executing schedule every: If set to 0, MARS 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 MARS how many times you want it to retry running the report before declaring it as "Failed."
  • Check if the Report is Blank: 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 MARS on what to do with them.
  • 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. No custom tasks will be run.

Method

MS Access: Exception Handling Wizard in Data Driven Schedule in MARS.
  • Select the Method that will determine whether a report is blank.
  • Native: MARS will check to see if the report returns any data. If not, the report is considered blank.
MS Access: Exception Handling Wizard in Data Driven Schedule in MARS.
  • 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.
  • Click Build.
  • Get values from a database window will appear. For more information about Get values from database, click here.
MS Access: Get values from database interface in MARS.

Actions

MS Access: Exception Handling Wizard in Data Driven Schedule in MARS.
  • Select an action from the task list. This task will be executed in the event that a schedule is blank.
  • For more information about tasks, click here.
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.

Click Next to continue to the next wizard section.

Custom Tasks

MS Access: Custom Tasks Wizard in Data Driven Schedule in MARS.
  • 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 information on Custom Tasks, click here.
  • If you have no desire to add a Custom Task, you can click finish to complete the schedule.

Data-Driven Schedule Context Menu

MS Access: Data Driven Schedule Context Menu in MARS
  • Right-Click on a schedule to see the following actions:
    • Properties: Edit the schedule from here. Or you can just double click on the schedule.
    • Copy: Use this to copy the schedule. Right-click in the "white space" of the folder you wish to copy it to and select Paste button.
    • Rename: Rename the package.
    • Enabled: Schedules are enabled when there is a check icon beside this option. To stop a schedule from running, or to "pause" it for a while, select this option to remove the check icon. Disabled schedules will not run until they are enabled again.
    • Refresh: When a schedule is first created, MARS 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 MARS.
    • Preview: Preview the report before execution.
    • Execute: This button 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: Selecting this option will delete the schedule.
    • Report Design: Preview the report Design.
    • Test: Use this option to test the schedule and export it to selected "test" destinations.
    • Convert To Package: This button will move this report into a package. The package will have the name of the single schedule report.
    • Move into a package: use this option to move the report into an existing package.
    • 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.
    • 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 MARS.
    • Ad-Hoc View: This button allows you to preview the report.

Data-Driven Schedule Properties

MS Access: Data Driven Schedule Context Menu in MARS
  • To access your schedule properties, right click on a schedule and select properties.
  • Similar to the schedule wizard, you adjust settings to your schedule such as timing, error handling, or custom tasks.
  • General
MS Access: Data-Driven Schedule Properties in MARS.
  • Schedule
MS Access: Data-Driven Schedule Properties in MARS.
  • Report
MS Access: Data-Driven Schedule Properties in MARS.
  • Report Options
MS Access: Data-Driven Schedule Properties in MARS.
  • Destinations
MS Access: Data-Driven Schedule Properties in MARS.
  • Exception Handling
MS Access: Data-Driven Schedule Properties in MARS.
  • Lock Prevention
MS Access: Data-Driven Schedule Properties in MARS.
  • History: Review the schedules history. Successes, failures, and other data is located here.
12-13-2018 1-35-52 PMMS Access: Data-Driven Schedule Properties in MARS.
  • Tasks
MS Access: Data-Driven Schedule Properties in MARS.
  • Snapshots: A snapshot is a copy of each report a schedule or a package generates. Keeping snapshots enables you to resend past reports without generating them afresh - especially if the data has changed in the meantime.
    • Simply select the number of days to keep the snapshots.
MS Access: Data-Driven Schedule Properties in MARS.
  • Data-Driver
MS Access: Data-Driven Schedule Properties in MARS.