I have a report with 3 parameters: AccountId, FromDate and ToDate. The report is invoice layout. Customer want to view all members which means we have 300 members, system will generates 300 reports in pdf or excel format and send to customer.
How to set member id for this in subscription? I cannot do one by one and create 300 subscriptions in manually :|
If you're not clear, please comment below and I will correct it asap.
The data-driven subscription which Manoj deal with is required SQL Report has Enterprise or Developer editon. If I don't have, do you have any solution for work around?
If you are stuck with the Standard edition of SQL Server, you'll need to create a SSIS package to generate the reports. I used the article below to set up a package that now creates and emails our invoices, order confirmations, and shipping acknowledgments. I like this article over other ones I found because it's so easy to add more reports to it as you go along without having to create a new package each time.
If you plan to use this for more than one report, I would change the parameter to be a PK so that you know you're always going to pass in one integer regardless of which report you're calling. Another change I made was to create one table for the report generation piece, and one for the email piece. In my case, I only want to send one email that may have multiple attachments, so that was the best way to do it. In your stored proc that builds this data, make sure you have some checks for if the email is valid.
update TABLE
set SendStatus = 'Invalid Email Address'
where email NOT LIKE '%_@__%.__%' --valid email format
or patindex ('%[ &'',":;!=\/()<>]%', email) > 0 -- Invalid characters
or patindex ('[@.-_]%', email) > 0 -- Valid but cannot be starting character
or patindex ('%[@.-_]', email) > 0 -- Valid but cannot be ending character
or email not like '%@%.%' -- Must contain at least one @ and one .
or email like '%..%' -- Cannot have two periods in a row
or email like '%@%@%' -- Cannot have two @ anywhere
or email like '%.@%' or email like '%@.%' -- Cant have @ and . next to each other
--0
When I set this up, I had a lot of issues getting the default credentials to work. The SSRS services would crash every time, but the rest of the SQL services would keep working. I ended up having our network guy make a new set with a static password just for this. If you do that, change the default credential line to this one.
rs.Credentials = new System.Net.NetworkCredential("acct", "p@ssword", "domain");
If you run into errors or issues, the SSRS logs and Google are your best friends. Or leave a comment below and I'll help.
Here's that article: http://technet.microsoft.com/en-us/library/ff793463(v=sql.105).aspx