Difference between revisions of "Controlling Scheduling with a SQL Query"

(Added SQL-based Repositories)
 
(2 intermediate revisions by the same user not shown)
Line 2: Line 2:
  
 
==Preparing the SQL Statement==
 
==Preparing the SQL Statement==
The SQL statement should return one row per report recipient, and columns configured like this:
+
The SQL statement should return one row per report configuration, and columns configured like this:
 
{{TableHeader}}
 
{{TableHeader}}
 
! Email !! Role !! Locale !! Parameter1 !! ... !! ParameterN
 
! Email !! Role !! Locale !! Parameter1 !! ... !! ParameterN
Line 14: Line 14:
 
{{TableFooter}}
 
{{TableFooter}}
  
;Email :The email address for the recipient of the scheduled report
+
;Email :The email address for the recipient of the scheduled report. From v9.1, this can be a semicolon-delimited list, which then share the rest of the parameters, for example {{Code|recipient1@example.org;recipient2@example.org}}
;Role :The database role to use when preparing the report
+
;Role :The Analysis Services role to use when preparing the report
 
;Locale :The culture to use when preparing the report
 
;Locale :The culture to use when preparing the report
 
;Parameters :Any [[Web Parameters]] to use to populate the report. See [[#Populating Web Parameters|below]] for more information
 
;Parameters :Any [[Web Parameters]] to use to populate the report. See [[#Populating Web Parameters|below]] for more information
Line 23: Line 23:
 
* On the web server, open the {{Code|distributionLists.xml}} configuration file, found in the {{Code|__xlcubed__\__scheduling__}} subfolder of the XLCubed Repository
 
* On the web server, open the {{Code|distributionLists.xml}} configuration file, found in the {{Code|__xlcubed__\__scheduling__}} subfolder of the XLCubed Repository
 
* To each dynamically populated distribution list add:
 
* To each dynamically populated distribution list add:
*# a {{Code|dbConnection}} element, containing the connection string to use to connect to the database. This can include the {{Code|User Id}} and {{Code|Password}} connection properties if appropriate
+
*# a {{Code|dbConnection}} element, containing the connection string to use to connect to the database. This can include the {{Code|User Id}} and {{Code|Password}} connection properties if appropriate, but not a {{Code|Provider}} property
 
*# a {{Code|populateQuery}} element, containing the SQL statement to run
 
*# a {{Code|populateQuery}} element, containing the SQL statement to run
 
* Your {{Code|distributionLists.xml}} file should now contain an element that looks like this:
 
* Your {{Code|distributionLists.xml}} file should now contain an element that looks like this:
Line 34: Line 34:
 
Available from v9.0
 
Available from v9.0
 
* In the SQL repository update the {{Code|XL3DistributionLists}} table:
 
* In the SQL repository update the {{Code|XL3DistributionLists}} table:
*# update the {{Code|cDatabaseConnection}} field to contain the connection string to use to connect to the database. This can include the {{Code|User Id}} and {{Code|Password}} connection properties if appropriate
+
*# update the {{Code|cDatabaseConnection}} field to contain the connection string to use to connect to the database. This can include the {{Code|User Id}} and {{Code|Password}} connection properties if appropriate, but not a {{Code|Provider}} property
 
*# update the {{Code|cPopulateQuery}} field to contain the SQL statement to run
 
*# update the {{Code|cPopulateQuery}} field to contain the SQL statement to run
  

Latest revision as of 11:31, 6 October 2017

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 configuration, and columns configured like this:

Email Role Locale Parameter1 ... ParameterN
recipient1@example.org Role1 en-US Value1 ValueN
recipient2@example.org Role2 fr-FR Value1 ValueN
recipient3@example.org Role3 fr-FR Value1 ValueN
Email 
The email address for the recipient of the scheduled report. From v9.1, this can be a semicolon-delimited list, which then share the rest of the parameters, for example recipient1@example.org;recipient2@example.org
Role 
The Analysis Services role to use when preparing the report
Locale 
The culture to use when preparing the report
Parameters 
Any Web Parameters to use to populate the report. See below for more information

Configuring the Distribution List to use the SQL Query

File-based Repositories

  • 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, but not a Provider property
    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>
  <populateQuery>ap_GetSchedulingItems</populateQuery>
</distributionlist>

SQL-based Repositories

Available from v9.0

  • In the SQL repository update the XL3DistributionLists table:
    1. update the cDatabaseConnection field to contain the connection string to use to connect to the database. This can include the User Id and Password connection properties if appropriate, but not a Provider property
    2. update the cPopulateQuery field to contain the SQL statement to run

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