SSRS - Sending Excel attachment & Body in same email

  • 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

  • 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 ..,

  • can you post a scenario

  • 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

  • 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.

  • 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!

  • 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

  • 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.

  • 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)

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

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