View source for Controlling Scheduling with a SQL Query

From FluenceXL 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:
{{TableHeader}}
! 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
|-
{{TableFooter}}

;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 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 [[#Populating Web Parameters|below]] for more information

==Configuring the Distribution List to use the SQL Query==
===File-based Repositories===
* On the web server, open the {{Code|distributionLists.xml}} configuration file, found in the {{Code|__xlcubed__\__scheduling__}} subfolder of the FluenceXL Repository
* 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, but not a {{Code|Provider}} property
*# a {{Code|populateQuery}} element, containing the SQL statement to run
* Your {{Code|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 {{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, but not a {{Code|Provider}} property
*# update the {{Code|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 {{Code|AccountName}}, we can now use the parameter placeholder {{Code|%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 [[Scheduling#Placeholders|the Scheduling page]].

==See Also==
* [[Scheduling with the File-based Repository]]
* [[Scheduling with the SQL Repository]]
* [[Web Parameters]]

[[Category:Web Edition]]
[[Category:Scheduling]]