SSRS 2005 How to set up query and data-driven subscription when each retrieved row has its own email address

SSAS and SSRS
1

I need to query for daily orders and email the address on each order. Not sure how to set up the query or the parameters in the data driven subscription windows.

2

You might be looking at some other automated process.
Either ssis or powershell script.

  1. Fetch list of emails
  2. Send each email as parameter to Ssrs
  3. Return report object or download report to a specific location on server or a shared drive in network
  4. Attach to email and send
3

thank you, I'll try that.

4

For a data-driven subscription - you need a query that returns the following information:

Select DistributionName
        , SendTo
        , SendCc
        , SendBcc
        , ReplyTo
        , IncludeReport
        , iif(RenderFormat = 'Excel', 'ExcelOpenXml', RenderFormat) As RenderFormat
        , IncludeLink
  From {some table} 
Where {some conditions};

You then map each field in the subscription to the appropriate field returned.

If you have 2 rows in the results - then 2 emails will be sent based on the values in the SendTo column.

1 Like
5

Thank you. The report I created returns multiple rows, and each row contains an email addresses column. The idea is that each row may get emailed to a different
address. But when I set up the subscription, basically using the same query as the report, the entire report got emailed to the selected address, not just the one row. That is the part I can’t figure out. Someone else suggested that SSIS or PowerScript
must be used and I am looking at that.

i.e., row 1 should email to address1, row 2 to email address2, and so on. Instead, address1 received all the rows, address 2 received all the rows, and so on.