With version 6.2, XLCubed introduces a new feature that we’ve had enquiries about for a while now: the ability to send scheduled reports to email recipients. In this post we’ll go over the basics of how it works and why you might want it.
The Scenario
As report designers and publishers, it’s our job to share our findings with others. In some cases, we have the opportunity to present the information in person at company meetings, and in other cases, just publishing a data-driven dashboard, table or report to XLCubed Web Edition allows our audience to examine the data at their leisure.
In many cases, however, we’d like to provide our consumers with a report on a regular basis. This might be a week end sales report, or perhaps one showing a breakdown of new issues versus issues resolved during the month. By automatically sending an email containing the report, we no longer have to worry about missing that important information.
In some situations we don’t want to have a regular report, but we do want to know when some performance measure is unexpectedly high or low. Under these circumstances it can even be distracting to be updated too frequently: it would be much better to only be notified when the measure is in the unusual condition.
Both of these scenarios are catered for by XLCubed’s new scheduling feature.
Setting up the Report
As always, the report design step is carried out in Excel. For scheduled reports, there are a couple of additional considerations when designing your report.
As with normal reports, scheduled reports can use web parameters. These are enhanced for scheduled reports, allowing you to place items such as the current time, user or role on the report. This means that the report can use those variables to allow a customised view of the data.
In addition to this, when setting up the schedule you can specify one of the web parameters to be a trigger: the report will only be sent if the value in the parameter cell is TRUE.
Scheduling Basics
Since different scenarios require different schedules, XLCubed makes it easy to control exactly what is sent and when. When setting up a schedule, there will be a few things to choose.
Firstly, each schedule contains one or more reports to send. Each report can be sent as a static Excel file or as an Acrobat document (.PDF), and can be given any parameters that were defined when publishing it. In addition to arbitrary text, there are some special codes that can be inserted as parameters:
| 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 |
| %email% | Inserts the email address of the recipient |
| %username% | Inserts the username of the recipient |
| %displayname% | Inserts the display name of the recipient |
| %sendiftrue% | Triggers sending of the report |
The last of these codes is special. Instead of inserting anything into the report, XLCubed examines the parameter’s cell and sends the report only if the value is TRUE. This means that not only can you create a trigger based on your data, but you can create complex logic based on multiple criteria in Excel.
Secondly, each schedule is set to run at particular times on particular days. As you would expect, XLCubed provides a framework that allows you to fulfil a wide range of different requirements, whether you need to run your reports every day, every other week, or even only on the 29th of February.
Lastly, each schedule of course lets you choose who to send the reports to.
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 slightly more complex to set up, but once set up, it’s easy to make new reports with the same recipients. To use this, you set up a Distribution Group, composed of any number of people. To make it easy to set up the reports as needed, each Group is assigned a Database Role to use and a Locale to format its numbers and dates. One or more Distribution Groups are assigned to each Distribution List, so that your scheduled report can be sent to more than one group at once:
To illustrate this with an example, imagine that there was a particular report that you needed to send to managers in the USA, China and Germany. Since the formatting and roles would be different for each group of managers, you would need a setup something like this:
