SSRS Report bursting

Question

Hi,
We’re looking for a bursting method in SSRS/SQL server.
We are known with and using the data driven subscriptions but this is not the solution we’re looking for in this case.

We currently have a tool (we’re replacing with SSRS now) which allows us to run a query once, and store the result in different files based on a column, then it mail these files to different mail addresses.
We’re looking for a similar solution.

The specific case is we have 1000 customers and we want to mail the orders of the last X Days.
So we run the query and then get 90000 order returned for 800 customers. So, during run-time there are 800 reports built and mailed to these customers.
With data driven subscription, we would run 800 times the report, but this is now performant.
Anyone has a solution for this? We have sql server and ssis available.

As an extension, we also have an open payment report, which checks for due amount. Ideally it would be in 1 mail with the orders example. Any tips on this?
Thanks!
Maarten

Answer 1

Hi @MC ,
If your query results are deployed on a report server in the form of SSRS reports, and if you have a large number of recipients, or if you want to provide different report output for each recipient, then using data-driven subscriptions in SSRS would be your best choice.

I'm not sure if you have a deep understanding of data-driven subscriptions. For the 800 customers you mentioned, you just need to create a customer information table, which contains the customer name, email address, the parameter values you choose to pass for each customer (parameters such as date, product category, etc.) and the format of the received report. So instead of running separate reports for different parameter values, you can simply create a data-driven subscription where your customers receive the report in the specified format and content at the time you specify, and depending on your subscription settings. Subscription tasks can be executed continuously on a daily/weekly/monthly basis, and you can terminate or manage these subscriptions at any time.
See more: Enhancing Customer Experiences with Subscriptions in SSRS.
Best Regards,
Joy

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @Joyzhao-MSFT
thanks for the link, we're familiar with data driven subscription.
But I'm looking for a solution were we run the query once, and split the result.
And I understand that with DDS is will run the query 800times.

If we run the query for all customers (as in no customer filter), it takes a couple of minutes. The same query for 1 customer takes 1 minute. So in case of 800 customers, this won't work efficient.