Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSRS - Sending Excel attachment & Body in same email Expand / Collapse
Author
Message
Posted Sunday, July 28, 2013 2:18 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 12:30 PM
Points: 141, Visits: 545
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




Post #1478405
Posted Sunday, July 28, 2013 8:21 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 12:30 PM
Points: 141, Visits: 545
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 ..,
Post #1478430
Posted Monday, July 29, 2013 4:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:00 AM
Points: 19, Visits: 94
can you post a scenario
Post #1478522
Posted Monday, July 29, 2013 9:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 3:43 AM
Points: 32, Visits: 62
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
Post #1478644
Posted Monday, July 29, 2013 10:55 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 12:05 PM
Points: 339, Visits: 879
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.
Post #1478673
Posted Monday, July 29, 2013 12:19 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:23 PM
Points: 12,927, Visits: 32,330
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1478698
Posted Monday, July 29, 2013 6:28 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 6:55 AM
Points: 484, Visits: 890
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
Post #1478786
Posted Tuesday, November 12, 2013 5:44 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, November 13, 2014 11:59 AM
Points: 245, Visits: 283
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.
Post #1513670
Posted Sunday, March 23, 2014 1:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, March 23, 2014 1:39 AM
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)
Post #1553799
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse