x

Multi Line email body in Data Driven Subscription in SSRS 2008

Any body knows how to create a multi-line email body in Data Driven Subscription in SSRS 2008?

The body of email created form the Comment parameter of the Data Driven Subscription, unfortunately even the parameter is filled from a db field which contains multi-line data, all the CR-LF are removed from the text and text in the email arrives as one line(paragraph).
more ▼

asked Nov 25, 2010 at 02:55 AM in Default

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

As I wrote in my question. When you have a Data Driven Subscription in SSRS and when you choose email delivery, there is a field named Comment, which represents the body of the email. It is the message in the email and the report will be attached to this email.

You can write a simple text into this field or choose a db field from the query used as source of the subscription. Problem is when you need a multi-line email with several articles in it (Need to explain what the report is and how to use it). Unfortunately SSRS always removed all the new-lines and put all the articles together as a single line.

After several hours of playing with this I found, that you can put a HTML Source for this field and it will be put as the email body and then correctly showed by the email client :-)

So you can use HTML code:

<html>
  <head>
    <meta content="text/html; charset=ISO-8859-1" http-equiv="content-type">
  </head>
  <body>
    <p>First article</p>
    <p>Second atricle<p>
  </body>
</html>
You can use all the HTML features and the text is then correctly rendered in the client. At least in Outlook. You have to put the HTML source into this field. :-)
more ▼

answered Nov 25, 2010 at 04:54 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

You just have to make sure that the clients accept HTML emails (some people don't like this) it shouldn't be a problem for internal reporting though.
Nov 25, 2010 at 04:58 AM WilliamD

Yeah, right. I wrote, that in Outlook it works correctly.. And I have to send an email with several articles, describing the report and how to use it.

Better HTML than a mess of all the articles combined together into single line without any format. :-)
Nov 25, 2010 at 05:08 AM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left
What CR-LF characters are they? I would try out different options - vbcrlf, Char(10), Char(13), etc etc.
more ▼

answered Nov 25, 2010 at 03:43 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.6k 75 79 108

@Fatherjack, I wrote CR-LF only to mention, that in the the string stored in db field there are new lines Char(10), Char(13). The new line are present, but when the field is passed in the Data Driven Subscription as Comment (email body), all the new lines in the text are removed and rendered as a single line.
Nov 25, 2010 at 03:53 AM Pavel Pawlowski
@Pavel - yes, I appreciate your problem. You could try substituting the Char(10) Char(13) with the other linefeed characters to see if they pass thru the SSRS processing without 'damage'.
Nov 25, 2010 at 03:59 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

Pavel - found this hiding in some awful multi-level forum/newsgroup type place - I am not sure if it works, but could be worth a try:

=REPLACE(Fields!YourFieldHere.Value,CHR(10), vbcrlf)
more ▼

answered Nov 25, 2010 at 04:03 AM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

@WilliamD, It's not a problem of displaying multi-line field. The problem is in the Subscription.

When you define Data Driven Subscription, select email delivery and report is rendered as Excel and there is also possible Specify Comment (it is email body - message which will be written in email). You can specify a static value, nothing or select a field from combo. You cannot specify such expression for this.

Playing with for several hours and googled for this but didn't find anything. Found similar problems using Google but not answers, so tried to post it here.

But Just about 15 minutes ago I found solution :-). Going to post it here. :-)
Nov 25, 2010 at 04:37 AM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1839
x553
x22

asked: Nov 25, 2010 at 02:55 AM

Seen: 4498 times

Last Updated: Nov 25, 2010 at 02:55 AM