But I've defined a query to retrieve the email address. I would think by clicking 'Preview' it would execute the query to get the parm value at that time... or maybe i need to provide a default value, I'll try that
You don't need to specify a parameter for the email addresses on the report. That is going to be defined in the data-driven subscription and used by that process to send the emails.
To get the idea of how this works, create a generic report with no parameters. It doesn't have to be the real report - just something that can be sent out.
Then - create a data driven subscription. In that subscription - use a query/table to pull the data for the email addresses (To, CC, BCC) - the subject, the body, the format (EXCELOPENXML) and anything else you want defined. Here, you build a query to return the data - and then select the column that maps the data to those fields.
Then schedule/run the subscription - it will send the data in Excel to the specified email addresses.
The nice thing about this is that you can return multiple rows, and the subscription will use each row to send emails. So - you can defined different formats - different parameters - different addresses as needed.
2 Likes
You cannot 'test' this feature in Visual Studio - it can only be done once the report has been deployed. Visual Studio doesn't know anything about subscriptions - or how SSRS schedules a subscription to be run.
1 Like
Looks like you are on the folder. You need to be in a report for a subscription.
it's by design since it is subscription based. it takes care of providing the needed parameters. I think you should read up deeper into subscriptions, otherwise you will not gain much traction with one -off questions in a forum. There is nothing like digging in deeper on your own into the wee hours to sink this
1 Like
I agree, @yosiasz. I'll do more research. I watched the video above.
But to @jeffw8713, I used Visual Studio to start the design of this report. And I was able to Preview the report until recently. So I'm not sure when I depart from VS and switch to Report Builder to complete it. I added the EmailAdresses parm in VS. But do parameters and subscriptions have to be added by using Report Builder? Thanks.
There are basically 3 steps you need to follow:
- Design and build the report
- Deploy the report to Report Server
- Schedule Report
Step 1 - use whichever tool you prefer to design and build the report. I prefer Visual Studio because it provides much more flexibility than Report Builder.
Step 2 - Deploy the report - in Visual Studio that means setting the target to the destination servers location. In a default installation that will be http://{servername}/ReportServer.
Step 3 - Schedule the report - using SSRS Report Manager (http://{servername}/Reports, right-click on the deployed report and setup a subscription.
There are 2 types of subscriptions you can create - a normal subscription is one where you setup the parameters for the subscription manually and they are fixed to those values. The second is a data-driven subscription where you get the parameters for the subscription and report from a query.
You DO NOT specify the email addresses as a parameter of the report. In fact, the report should not know anything about the list of email addresses.
So - as I stated before start with a simple generic report. Do not create any parameters for the report - and get that report deployed to the server. Validate the report exists in the folder and that you can run that report from Report Manager.
Once you have done that - then schedule the report using either of the 2 methods above. If you use a data-driven subscription - use a simplified query that just sets the values. For example:
Select EmailTo = 'youremailaddress@yourdomain.com'
, EmailFrom = 'somefromemail@somedomain.com'
, EmailCC = ''
, EmailBCC = ''
, EmailSubject = 'Testing data-driven subscription'
, {other columns as needed}
Use the above in the query portion of the data-driven subscription and then map EmailTo in to To address box. Map the other fields...
Set the execution time for the subscription...
Once you understand how all of these parts work - then build your final report and validate it is returning the expected data based on the report parameters. Again - the report parameters have nothing to do with the subscription...that is separate.
Now that you have a report - modify the subscription and add the actual query that gets the email addresses, CC addresses, BCC addresses, subject, body, etc... If needed, return the report parameters for each row in that query and map the report parameters in the subscription to the query.
1 Like
@jeffw8713 With your help, I've had a productive day, learned a lot. I've created and deployed 2 simple reports and created a basic subscription for one of them. The scheduled job completed successfully.
Tomorrow, I'll tackle data-driven subscriptions.
Thank you!
Good morning. @jeffw8713 I have a question specific to data-driven subscriptions.
My EmailAddresses table is structured like this:
Report Name Division EmailAddresses
Report1 123 'emailaddress1@.....com;emailaddress2@.....com;emailaddress3@...com'
So most reports will have more than 1 recipient in the same row.
Will the data-driven subscription be able to send to all recipients this way or will I have to modify my table data to contain only 1 address in each row? That would be a pain and may mean I can't use an RS solution for this effort.
Thanks
You shouldn't have to change your existing email table for that.
However...
You can easily build a list of email recipients with STRING_AGG() if you store them as 1 address per row (properly normalized). There are additional advantages, you can eliminate duplicate addresses, ensure proper email formatting/constraints, etc. Also, if you need to change or remove someone from a subscription, you only need to update 1 row, instead of however many subs they might appear in a long email string.
This is my recommendation based on a lot of RS subscription wrangling (and Crystal Reports before that).
1 Like
Ok so I've got my data-driven subscription created.
The query runs Select EmailDistribution from ......
And the Delivery options are set as shown here:
But where/how do I enter the Select EmailTo = '........' etc ? This is where I'm lost. Thanks
Wow, it's finally working as intended. I created a data-driven subscription, ran the agent job and it generated an excel format report and emailed it to 3 recipients which were retrieved from the addresses table. Pretty cool!
@jeffw8713 , I do have a question about formatting the excel report. How can I force each column heading to be generated as a drop-down?
Thanks everyone for your help!
If you mean that the Excel sheet has a filter automatically assigned? The answer is no. The only way to add that is some programming via PowerShell or VBA to modify the workbook and add the column filter. This would not work as part of a normal report subscription.
Oh that's bad news, @robert_volk. )Originally, I had devloped an HTML report, but the recipients are counting on the report being a true excel format with filtering capability. Would it be possible to "force" report server to use a template which has the filters?
@robert_volk or @jeffw8713 or @yosiasz: If you read @robert_volk response above, SSRS alone cannot generate an excel worksheet that has column filtering. So, if I generate this report via SSIS, can SSIS create an excel sheet that automatically has filtering (with no additional programming)?
Thanks
No.
SSRS designs a report in RDL, which is an XML-based definition/description of the report. SSRS "renders" a report visually, and the rendered format defaults to HTML due to its web-based implementation. HTML doesn't offer any innate filtering of tabular results either. Additionally, other rendering engines like CSV, PDF, Word and now PowerPoint have to be supported, and none of them offer interactively filtering as part of the file format.
There may be a way of modifying or customizing an Excel renderer, but I'm pretty certain that's a formidable programming task. The main point of SSRS report rendering is to display data with some common formatting regardless of destination format, but not include the entire feature set of each.
In any case, the recipients are receiving an Excel document and can add their own filter, it's literally one button click after they open the workbook. Forgive me for suggesting that if that's too difficult for them to do, there's a lot of sand that needs pounding and they should get to it.
Way back in the late 90's I did a lot of Excel macro programming and customizing worksheets for sales teams. It ended up with an Auto Open macro that would download the data for the respective sales manager's team, dump it into the correct worksheet, then recalculate a pivot table. Not a lot of code, and done as a convenience for those sales managers that treated me well. They had access to the data source over our network. (From your previous comments, this doesn't seem to be an option for your recipients.) Nothing was emailed, and sending a copy to someone via email would not auto-refresh the data. This was before SSRS was released.
My point is that it IS possible, but it's not part of the standard SSRS subscription delivery model. I had spent some time at a previous job working out how to do data-driven subscriptions using Standard Edition. The idea was that SSRS would render everything to a file, and the data-driven aspects only touched on email recipients, file naming, and some minor parameter changes that could be done with the normal subscription settings. Once the file is saved, the remaining parts would be run via scripting to send the email, etc.
If you absolutely need to add filters to your worksheets, this is the avenue I'd suggest. Just so I'm absolutely clear, this means you are BYPASSING ALL OF SSRS'S NORMAL SUBSCRIPTION BEHAVIOR and replacing it with your own. You need a separate email subsystem, including generating the recipient list, whatever scripts to modify the worksheets, a scheduler, and probably more. I should add that while I was working on this, I ended up dropping it as the company came up with the funding to use Enterprise Edition, and we simply went with data-driven subs.
If you go this route, keep in a mind that SSRS default rendering is not exactly the same when converted to Excel. Excel must have rows and columns, and if you don't have precise size and alignment of the cells and sections of your SSRS RDL, you'll get weird merged cells, splits and other goofs that could impact any programmatic addition of a filter. (I've seen WAY too many of these.) I'm sure there's other potholes as well.
If any of this sounds snarky or discouraging, that's not my intention. I do want to suggest that this will be a lot of work on your part, and the scope required to provide what is a very minor convenience, is not likely to be worth it.
Here's info on the Rendering Extensions feature: