| XLCubed is now FluenceXL. The new wiki can be found here: https://help.fluencexl.com/ |
Scheduling with the SQL Repository
 |
This page describes Scheduling with the SQL Repository. For the file-based Repository, please see Scheduling with the File-based Repository. |
Contents
[hide]Introduction
XLCubed Web Edition can be used to deliver reports to users automatically.
Using a schedule you can define which reports go to which users, and can optionally parameterise the results.
Scheduling maintenance is set up using the XLCubedWeb Management application. Please see this article for more details.
This article is about configuring XLCubed Scheduling. You can also find more information about Scheduling Installation or, for using it with the file-based Repository, please see Scheduling with the File-based Repository.
Concepts
The scheduling module is made up of a few key parts.
SMTP Servers
An SMTP server is a computer that is configured to send emails. You will need to set one up so that reports can be delivered to your users.
Report Distribution
XLCubed allows two different ways to set up the recipients for a report. The first is ideal for where the report needs to be sent to just one or two people. Just type in the email addresses and it’s ready to go.
The alternative is to use either the Groups built into your Domain, or to set up custom External Groups. These are slightly more complex to set up, but once set up, it’s easy to make new reports with the same recipients. See SQL Repository for details on how to set up Groups.
Schedules
A Schedule defines:
- which reports get sent
- who they are sent to
- when they are sent (once, daily, weekly or monthly, on days of your choosing)
If you wish to have a different set of reports go to different people then this would be a new schedule.
Creating Schedules
You can create new schedules and manage existing ones through XLCubed Excel Edition. Select the XLCubed Ribbon > Publish to Web menu > Scheduling... ribbon item, then Connect to the web server. To create a new schedule:
- Click the Add button
- Enter a name for the schedule and optionally a description that you will be able to use later when editing the schedule
- Supply a schedule for when you would like it to run
- On the Reports to Send tab, add one or more reports. For each report, you can:
- specify a set of web parameters to fill in
- choose whether to send the report as an attachment, send the file to a fileshare, or include a hyperlink
- customise the filename
- choose whether you want any generated files to be PDFs or Excel files
- select the worksheets to include in the report
- Enter who you would like to send the email to
- Specify how you would like the email to read
Once created, you can then manage the schedule by selecting it in the list, then clicking the Edit button. You can also delete it by clicking the Delete button instead.
Placeholders
By entering these custom values as report parameters you can customise the file for each user.
| Code | Description |
|---|---|
| %date% | Inserts the current date |
| %time% | Inserts the current time |
| %datetime% | Inserts the current date and time |
| %rolename% | Inserts the database role or roles that are being used |
| %groupname% | Inserts the name of the distribution group |
| %distributionlist% | (V10.0.129+) Inserts the name of the distribution list |
| %email% | Inserts the email address of the recipient. Ignored if %emailtosendto% is also used |
| %displayname% | Inserts the display name of the recipient. Ignored if %emailtosendto% is also used |
| %sendiftrue% | Triggers sending of the report. May only be used once per report |
| %emailtosendto% | Retrieves a comma-delimited list of email addresses from the report. May only be used once per schedule |
%sendiftrue%
If %sendiftrue% is used the report will be generated normally, but only sent to users if this parameter is calculated to be TRUE.
This allows exception reporting, for example, if you had appropriate names defined in Excel, you could set the formula in the parameter cell to be
=Or(Sales < 1000, Losses > 50)
The report can be published with the exception-detecting cell exposed as a Web Parameter:
This parameter can then be used as the %sendiftrue% parameter when setting up the schedule:
%emailtosendto%
If %emailtosendto% is used, the email addresses defined in the schedule or distribution group will be ignored, and the addresses retrieved from this parameter instead. The value in the cell may be either one email address or a comma-delimited list.
Note that this parameter overrides the %email% and %displayname% parameters.
Finding the Scheduling Error Log
When using the SQL Repository, Scheduling errors can be found in 2 places:
- For general errors run the Configure XLCubed Web Edition application, then select the Sql Repository tab. Click on the Administration button, then select the Admin > Logs > Schedule Log menu.
- For errors and status information about a particular schedule, manage the schedule as described above switch to the Run History tab.
Controlling Schedules through SQL
Some parts of the Scheduling configuration (e.g. distribution lists) can be queried from a SQL query. You can find more information here.
Sending a Scheduled Report using Integration Services
Existing schedules can be triggered by using a web service. You can find details on how to set this up in this article.
