SSRS - Sending Excel attachment & Body in same email

Question

SSRS - Sending Excel attachment & Body in same email

John Paul-702936

SSCrazy

Points: 2182
More actions
July 28, 2013 at 2:18 pm

#275729

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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

John Paul-702936 SSCrazy Points: 2182 More actions · Answer 1

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 Old Hand Points: 325 More actions · Answer 2

khemu8290

Old Hand

Points: 325

July 29, 2013 at 4:34 am

#1636583

can you post a scenario

paul.ruane2 SSC Enthusiast Points: 187 More actions · Answer 3

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 SSCommitted Points: 1998 More actions · Answer 4

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 SSC Guru Points: 323518 More actions · Answer 5

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 SSCertifiable Points: 6405 More actions · Answer 6

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/[/url]

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 SSC Veteran Points: 289 More actions · Answer 7

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 SSC Rookie Points: 43 More actions · Answer 8

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)