This bursting query example is based on an invoice report. This report will be sent to each customer’s particular e-mail address by CUSTOMER_ID.
Bursting is a process of splitting data into blocks, generating documents for each block, and delivering the documents to one or more destinations. The data for the report is generated by executing a query once and then splitting the data based on a “Key” value. For each block of the data, a separate document is generated and delivered.
This example assumes that each customer’s delivery and formatting choices are stored in the database table “CUSTOMERS.” The following columns from the CUSTOMERS database will be retrieved to generate the delivery XML dynamically at runtime:
- CST_TEMPLATE
- CST_LOCALE
- CST_FORMAT
- CST_EMAIL_ADDRESS
The CUSTOMER ID will act as both the KEY and the name of the output file.
For this example, the SQL code to generate the delivery data set is as follows:
SELECT distinct CUSTOMER_ID as "KEY", CST_TEMPLATE TEMPLATE, CST_LOCALE LOCALE, CST_FORMAT OUTPUT_FORMAT, CUSTOMER_ID OUTPUT_NAME, 'EMAIL' DEL_CHANNEL, CST_EMAIL_ADDRESS PARAMETER1, 'accounts.receivable@example.com' PARAMETER2, 'bip-collections@example.com' PARAMETER3, 'Your Invoices' PARAMETER4, 'Hi'||CUST_FIRST_NAME||chr(13)|| 'Please find attached your invoices.' PARAMETER5, 'true' PARAMETER6, 'donotreply@mycompany.com' PARAMETER7 FROM CUSTOMERS