Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSRS - Sending Excel attachment & Body in same email


SSRS - Sending Excel attachment & Body in same email

Author
Message
John Paul-702936
John Paul-702936
SSC-Enthusiastic
SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)

Group: General Forum Members
Points: 142 Visits: 633
Hello ,

please advise ,

I have 2 data sets -
1st data set result email as in excel attachment -- Detail
and 2nd as Summary as HTML in Body of the email -- Summary count of the 1st result set

how can we do this in SSRS ,currently I am using the 2 result sets in one excel and in 1 Email
But i want send the email as Summary Dataset 2 redult in Body of the Email and result set 1 as Excel attachment ..,


Please advise ..,


thanks
John Paul-702936
John Paul-702936
SSC-Enthusiastic
SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)

Group: General Forum Members
Points: 142 Visits: 633
19 view and 0 reply ..

any one - please share ideas - if my question is not able to understand please let me know .. will post with some examples ..,
khemu8290
khemu8290
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 100
can you post a scenario
paul.ruane2
paul.ruane2
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 104
Hi
one possible solution is to use an SSIS Package
1) Add task to select dataset 1 and store in a csv flatfile (excel)
2) Depending on how many pieces of data in your summary data and how you want to display this in the email body gather the dataset2 data
a) for once piece of info use an Execute SQL and save it to a variable
B) for multiple pieces store the data in a recordset
3) use a scripttask to build the email body variable
4) use a sendmail task with an attachement

You can schedule this package to run as and when needed
Paul
eric.muller
eric.muller
Mr or Mrs. 500
Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)

Group: General Forum Members
Points: 562 Visits: 1306
A data-driven subscription sounds like it might work, also, depending on the results of your dataset and the formatting. However, without a specific scenario that's just a guess.
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14959 Visits: 38981
i know i could do it via TSQL, with two separate queries.

you could use a FOR XML to generate an html compatible BODY and a separate query for the sp_send_dbmail's @query parameter, but i'm not sure if you can do that via SSRS;

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

davoscollective
davoscollective
SSChasing Mays
SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)

Group: General Forum Members
Points: 603 Visits: 1000
One technique I've seen is to control the report depending on the render format. I *think* when you include the report in the body of an email subscription that it's actually an HTML render format, as opposed to the Excel format you are attaching.

I've seen a report that did something like this, using two different tablixes. The first only appears when you render on screen, the second only appears when you export to Excel. The point of that was to have a very simple formatting for Excel and a fancier format on screen.

I can't find the details of that report but this link below will start you off on how it's done.

If you look in the report server[ExecutionLog] table you can get some idea of the different render formats. The ones I see are
RPL
EXCEL
MHTML
PDF
CSV
HTML4.0
ATOM
IMAGE
WORD
XML

It's also possible to create your own render extensions but I think that involves loading dll's so I don't really know much about that. From what I understand, RPL is the onscreen rendering format.

http://www.mssqltips.com/sqlservertip/2106/conditional-report-rendering-based-on-render-formats-for-ssrs-reports/

EDIT:

This is a better link and confirms the various formats and how you can use them in HIDDEN expressions
http://blogs.msdn.com/b/robertbruckner/archive/2010/05/02/globals-renderformat-aka-renderer-dependent-report-layout.aspx
faiga16
faiga16
SSC Veteran
SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)

Group: General Forum Members
Points: 263 Visits: 304
John Paul-702936, did you figure out a solution to this in SSRS subscription (or data driven subscription)? I've got the same exact requirement. Two dataset, one summary, one detailed, and one email subscription. I wanted to render the summary dataset as MHTML and the detailed dataset as excel (or any other possible rendering attachment version) in one subscription.
ajey19777791
ajey19777791
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 0
Hi
Is it possible to use SSRS catch report for this purpose? and send the same as attachment and body in single email delivery.

I need to send same SSRS report as email body and attachment in single email delivery.
(This is to support group of readers who want drill down my report in attachment for their levels)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search