Controlling Scheduling with a SQL Query

Revision as of 12:44, 11 November 2015 by Antonio.remedios (talk) (See Also)

From XLCubed v7.1, you can use a SQL query to populate the recipients of a scheduled report, and the parameters used when preparing the report itself.

Preparing the SQL Statement

The SQL statement should return one row per report recipient, and columns configured like this:

Email Role Locale Parameter1 ... ParameterN Role1 en-US Value1 ValueN Role2 fr-FR Value1 ValueN Role3 fr-FR Value1 ValueN
The email address for the recipient of the scheduled report
The database role to use when preparing the report
The culture to use when preparing the report
Any Web Parameters to use to populate the report. See below for more information

Configuring the Distribution List to use the SQL Query

  • On the web server, open the distributionLists.xml configuration file, found in the __xlcubed__\__scheduling__ subfolder of the XLCubed Repository
  • To each dynamically populated distribution list add:
    1. a dbConnection element, containing the connection string to use to connect to the database. This can include the User Id and Password connection properties if appropriate
    2. a populateQuery element, containing the SQL statement to run
  • Your distributionLists.xml file should now contain an element that looks like this:
<distributionlist guid="xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx" name="List name">
  <dbConnection>Data Source=serverName\instance;Initial Catalog=DBName;User Id=Username;Password=ThePassword</dbConnection>

Populating Web Parameters

The columns returned by the SQL statement above can be used as dynamic parameter placeholders. For example, if we return a column named AccountName, we can now use the parameter placeholder %AccountName% when configuring the report to use. Note that SQL columns used as placeholders must be string types, i.e. char, varchar, text, or their Unicode equivalents.

For more information about parameter placeholders, see the Scheduling page.

See Also