SSRS #55 – SQL Server BI Quiz “Mass Mailing Your Report”
Beyond Relational is running a month long SQL Server BI quiz. Tomorrow, 6/11, my quiz will be open to accept answers from readers.
1st Prize – Apple iPad!
It is not a surprise that the quiz from me is about Reporting Services Subscription.
Question Title: Mass Mailing Your Report
Context: You have a SSRS report that takes 3 parameters
Parameter 1: Begin Date (you have set it to a default value which is the Beginning of the Reporting Month)
Parameter 2: End Date (you have set it to a default value which is the current date)
Parameter 3: Decision Type (it is a multi-valued parameter, you have set it to "Select All" from a default list that comes from a dataset query.
(Possible Decision Types are: Approval, Decline, Info Needed, First Level Management Review, Other)
Challenge: Business has asked you to send the report in an Email as an Excel attachment.
1. The Email needs to have event-driven behavior (as soon as the data is updated daily)
2. The report will only have data from the previous day (not month-to-date)
3. When the Decision Type is Approval, send the email to MgrApproval@Company.com
When the Decision Type is other than Approval, send the email to MgrOther@Company.com
Quiz: please implement a solution on Microsoft BI platform, with the following considerations
1. Do not modify the current SSRS report
2. The solution needs to be data-driven and can be automated
3. Constant manual intervention is not acceptable
Need a hint? Please check out my recent blogs on RS Subscription and research on how to implement a simple Data-driven Subscription.
You can submit answers to this question after 11 Jun 2011. The question will be open for 30 days.