I want to render HTML / RTF *as* HTML / RTF inside a SQL Reporting Services Report



Too bad.


Many people have HTML and/or RTF stored in a database, a dataset, or wherever. If you display this text in a Reporting Services textbox, it gets rendered as markup versus properly formatted HTML or RTF.


 


Currently, Reporting Services gives you no way to render “HTML as HTML” — basically because doing so would open the door to HTML injection attacks.


 


Two possible workarounds have been discussed, but I’ve never seen them implemented:


 


Post-processing: After a report has been fully rendered, intercept the document and re-process it, turning the HTML (displayed as a string) into HTML which is “really” displayed.


 


 Custom Control (2005 only): One could theoretically build a custom control in 2005 which takes the HTML/RTF, saves the rendered output as an image, and then displays the image inside the custom report item. This looks really hard as the managed GDI namespace doesn’t give us anything to easily approach this sort of scenario.

Comments (40)

  1. James says:

    In SQL 2005, the approach I’m experimenting with is a SQLCLR function to read the HTML, and render it as a PNG for Reporting Services to display at run-time.

  2. MarkR says:

    Not an efficient way of doing this by any means, but for small datasets, I was able to loop through the column I have containing HTML after first putting the data I am selecting into a TMP table. Here is an example: (ActivityDetails is the column containing the HTML)

    WHILE (Select count(*) FROM #tmp

    WHERE ActivityDetails like ‘%<%’ and ActivityDetails like ‘%>%’

    and patindex(‘%>%’,ActivityDetails)-patindex(‘%<%’,ActivityDetails)+1 > 0

    ) > 0

    BEGIN

    UPDATE #tmp

    SET ActivityDetails =

    REPLACE(

    ActivityDetails,

    substring(ActivityDetails,patindex(‘%<%’,ActivityDetails), patindex(‘%>%’,ActivityDetails)-patindex(‘%<%’,ActivityDetails)+1),

    ”)

    WHERE ActivityDetails like ‘%<%’ and ActivityDetails like ‘%>%’

    and patindex(‘%>%’,ActivityDetails)-patindex(‘%<%’,ActivityDetails)+1 > 0

    END

  3. Doug says:

    I too am interested in rendering the HTMLFragment that exists in a column in a CRM report inside a Table control.  Understand there may be some global setting that may be set or passed iin Device Information Setting?  

    Does anyone know if this is true and/or how to configure those settings?

  4. russch says:

    Doug, there is no way to do this save the methods I mentioned above as far as I know. You could do something like use a drill-through and let the user click a URL in the table to launch the HTML in another window…

  5. Doug says:

    Russ, Appreciate the tip – too bad.  Wondering how to use the the small dataset solution MarkR presented.  How from the CRM data source would I reference the temporary and then how would I use the output in my report data.

    Has anyone used this in as a solution in CRM and would you be able to pass along an example?

  6. russch says:

    It looks like Marc is post-processing the HTML to pull all the data out and stick it into SQL — How he’s doing so is anyone’s guess.

  7. Doug Mannon says:

    Wondering if the rendering issue is reslved if using .NET 2.0 ? I have the same situation in that I am unable to render HTML in report from a description field in CRM 3.0 running on SQL 2005 SP1.

  8. Thomas says:

    Preventing HTML injection is well and good, but there should at least be an option to allow it through from the server-side.

    There is an ASP.NET server side html unescape function I think it’s called Server.HtmlDecode(htmlEncodedString) that seems tailor made for this, but I couldn’t figure out how to call it from report server.  

    So, I’m also using the Post-Processing trick.  Microsoft has a pretty decent client-side, javascript implementation (about 600 lines long) of this function at:

    http://lab.msdn.microsoft.com/annotations/htmldecode.js

    I used my own (fairly sloppy) little function to call microsoft’s HtmlDecode on the client:

    function dec(){

    var allEls = document.all;

    for (var i = 0; i < allEls.length; i++){

    if (allEls[i].innerHTML.substring(0,4) == "&lt;"){

    allEls[i].innerHTML = HtmlDecode(allEls[i].innerHTML);

    }

    }

    }

    This function is weak for a number of reasons — it’s client side, it uses document.all (pretty much limited IE only), and, most importantly, it "detects" the escaped html by searching only for the escaped "less-than" character &lt; in the first postion of the content (for us, this is always a less-than, but obviously it could be whitespace or another html constant like &nbsp;).

    Still, it seems to be adequate for our reports.  I think the detector could be made much better with a RegExp, and I think the source HTML could be treated (e.g. remove the "script" tags, and maybe the "img" and "a" tags before rendering).  Also, I haven’t figured out an easy way to serve this funciton within the report — instead I’m having to use an external frame to call it.

  9. Does anyone have a complete and working solution to this issue? This seems like a major deficiency in RS as this is a very common situation.

    As for rendering the HTML as an image the Syncfusion suite has a control to do this, but the quality seems very poor with the samples I’ve tried.

    How would this effect exporting and printing of the reports?

    Thanks

    CT

  10. Tony says:

    HI all,

    well, I´ve the same problem: a lot of HTML data in my databases and no way to use the "formatted" data in a report.

    The only solution I found is to replace alle HTML-Tags with a RegEx before – so I get only the text – which is mostly useless (e.g. a lot of table content is useless).

    I would appreciate some solution for this issue (maybe a html content control in rs or similiar), but till now I didn´t find a workaorund…

    CU, Tony

  11. Peter says:

    I dont’t understand why Microsoft doesn’t offer an issue to decode html in reporting services.

    Crystal report can do that in a property

    "Html/text" but still it does not handle all html format. ie bold format supported is <B> and not <STRONG>

  12. Don Stickle says:

    Is there by now a way to do this?  

  13. Ujjwal says:

    I am also looking a way to do this. I know Crystal Reports when we use http://<yourserver>/MSCRMServices as data source, can render formatted HTML.

    Need to know how to do with SQL reporting services. Need to create reports for Activlity- Description.

  14. russch says:

    Here’s another solution someone came up with…it’s a code block that transforms RTF to a bitmap you then can display:

    http://blogs.digineer.com/blogs/jasons/archive/2006/10/03/520.aspx

  15. MP says:

    You think MS would come up with a solution for this.  Their next generation of sharepoint and project server is filled with controls that generate and store HTML text.

  16. Martin D says:

    I have a workaround that can satisfy some scenarios. It assumes:

    1) You’re happy to replace the HTML content with a hyperlink that, when clicked, will take you to a rendition of the rich content

    2) But you want to stay within Reporting Services (not link to some external file like an ASPX)

    Overview:

    1) Set some properties on the report to ensure that the rich content is outputted when rendering the report as XML

    2) Set up a hyperlink to navigate back to the report but in an XML mode, where we have more control over the display

    3) Construct an XSL stylesheet that will transform the XML into a suitable HTML display

    The piece in the XSL that does the real trick is this: <xsl:value-of select="yourHtmlField" disable-output-escaping="yes" /> I have to admit I don’t know whether I’m using it for its intended purpose, but hey, it did what I needed.

    Another important bit is recognising that you can render the report into XML, transformed into HTML with XSL, using a couple of URL parameters. My navigation expression looked like this (My report involves "training offerings" and the course "outline" is an HTML field):

    =

    IIf

    (

    IsNothing(First(Fields!Outline.Value, &quot;OfferingDetails&quot;)),
    
    Nothing,
    
    Globals!ReportServerUrl + &quot;?&quot; + 
    
    Globals!ReportFolder + 
    
    &quot;/&quot; +
    
    Globals!ReportName +
    
    &quot;&amp;offeringId=&quot; + Parameters!offeringId.Value.ToString() +
    
    &quot;&amp;rs:Command=Render&quot; +
    
    &quot;&amp;rs:format=XML&quot; +
    
    &quot;&amp;rc:XSLT=Offering Outline Transformer.xsl&quot; +
    
    &quot;&amp;rc:MIMEType=text/html&quot; +
    
    &quot;&amp;rc:Toolbar=false&quot;
    

    )

    Clicking the hyperlinked field takes the viewer to a re-rendered version of the rich field (although you could include other report fields too).

    If this approach is of interest, email me at martindr_do_not_spam_@avision.co.za (remove the superfluous part of the address, of course) and I’ll send you a copy of a more comprehensive discussion)

  17. Mikel Stott says:

    Hello folks.

    I have struggled with this since RS 2000 and here is the solution I present… Be warned, it is a pain in the arse because we have to use GDI…

    First, I call rtf data (stored as a VARCHAR) from the SQL db.  I simply bind the RTF to a form designed to represent the visual version of the report.  This window is set with the border off with a white background to make it not look like a window.

    Then, I call a "bitblaster" method from a custom class I wrote that renders a bitmap of the form to a temp directory.  These images are saved into an IIS site (with anon access on).

    Now the fun part.  I use the RS Web Services to call my SQL report passing in paramters and programatically rendering its output to PDF. The report has image anchors that call the images saved in the previous step dynamic based on the primary key of the data row we are working on. (Actually, it is a cover key- so, i just concatenate them together).  Then use the expression editor to dynamic change the image control’s URL.

    Finally, I call another abstract class that merges multiple reports to "batch" the reports into a single PDF with mutiple reports (as pages) within it.

    This is the best thing I can come up with.  I would love to hear other solutions or ideas.

    Good luck and happy coding.

  18. Jasper says:

    Mikel,

    You said "use the expression editor to dynamic change the image control’s URL."  I dont see the URL property or the value property to plug in the expression.  How do you set the image control’s URL ?

  19. Justin Saraceno says:

    Hello Russ,

    Have you come across any new solutions for displaying stored HTML markup in a SSRS Report textbox?  Ideally I’d like to actually use that HTML markup for formatting as opposed to a solution that strips all HTML markup out.  Thanks!

  20. russch says:

    Nope. This functionality will be included in 2008 if it’s any consolation, however.

  21. Lizet says:

    Have anyone tried to make a custom renderer for rendering XHTML from the database?

  22. David Martin says:

    This fix just just removes tags you specify so that at least it looks like plain text to the users.  

    I have a database field that is filled from a simple web html editor control.  Of course this doesn’t work if you don’t have control over the html source.  Specify the value of the textBox as:

    =

    Replace(

    Replace(
    
        Fields!Comment.Value, &quot;&lt;br /&gt;&quot;, &quot;&quot;)
    
    , &quot;&lt;p&gt;&quot;, &quot;&quot;)
    

    , "</p>", "")

    As you can see you could add as many tags as you want.

    As a side note, I’m shocked and find it completely unacceptable the MS has not provided an "html" format tag.  If they are concerned about security fine, at very least provide a "safehtml" format tag that will remove all non safe tags and scripts.  This is a no brainer, get with it MS.

  23. zacuke says:

    The November CTP of SQL Server 2008 still does not support this as far as I can tell…

    What gives?

  24. Dawn says:

    Still looking for a solution to this problem.  I need to render rich text field to SQL Report.  Has anyone had any luck?

    thanks

  25. Amanda says:

    Look at this tip.  It’s an easy way to do this.

    http://dotnettips.com/2007/09/20/ConvertRTFToText.aspx

  26. Rangnath says:

    I created this function and added it to the Report Properties –> Code

    Function RtfToText(ByVal value As String) As String

           If value.Contains("rtf1") Then

               Return System.Text.RegularExpressions.Regex.Replace(System.Text.RegularExpressions.Regex.Replace(System.Text.RegularExpressions.Regex.Replace(System.Text.RegularExpressions.Regex.Replace(value,"[nrf]", ""), "({)(.+?)(})|()(.+?)(b)", ""), "{", ""), "}", "").Trim()

           End If

           Return value

       End Function

    This method is then called by the text box as below:

    =Code.RtfToText(Fields!PrintName.Value)

  27. sfblackl says:

    Russ – You mentioned that RS 2008 would have a solution to this.  I’m using RS 2008 and it isn’t becoming obvious.

  28. Rob Cline says:

    FYI since this thread seems to still be active, this can easily be done in 2008 now, I found it in the Help index under "HTML – rich text"

    In your report, select the data placeholder *inside* your textbox (not the textbox itself, the placeholder has the field name in brackets like "[ows…]") I find you may have to double-click to select it.

    Right-click the placeholder and then click Placeholder Properties (if double-clicking didn’t open the dialog for you.)

    In the General tab at the bottom is the "HTML – Interpret HTML tags as styles" option that you want.

  29. Linda P says:

    Rangnath’s solution worked perfectly for me.  I don’t have the luxury of upgrading to SQL 2008

  30. Camilo Serna says:

    I need to justify the text on my report. I’m using reporting services 2005.  any ideas how?

    Thanks in advance for your help.

  31. Chad says:

    Ahhhh… Thanks Rob, works great in 2008.

  32. Rahul says:

    placeholder works fine but if you have any images in the RTF then those images are not displayed. I am still not able to find any simple workaround to display the RTF text with images in SSRS. Can someone help me??

  33. Jamie Wang says:

    I like the RtfToText conversion using regex above, but if you want to preserve formatting you can convert the RTF to HTML using a utility such as this one: http://pebblereports.com/reportingservicesutilities/

  34. Oliver says:

    The placeholder properties appear when using the BI template. But I am using a web application template with a report viewer control. When I add a new report to my project and a field in this report, I can’t see anywere the properties for a placeholder.

  35. Geeta Gupta says:

    I need to justify the text on my report. I’m using reporting services 2005.  any ideas how? any custom control available…its urgent

    Thanks in advance for your help.

  36. Shane says:

    I found a really simple way to display HTML as HTML using a placeholder and a textarea tag. Basically, just right click in the desired area, select Create Placeholder, select the desired field for the "Value", then select the "HTML – Interpret HTML tags as styles" and click OK. It takes the HTML markup and renders it as HTML. This was based off SQL Server 2008 Reporting Services. Not sure if something similar exists in earlier versions.

  37. HERE'S THE REAL SOLUTION FOR RS 2005!!! says:

    Go to this link and copy the code:

    lazycoders.blogspot.com/…/stripping-html-from-text-in-sql-server.html

    This creates the function in SQL. Then go to SSRS and reference that same function for just the field in your dataset that you're returning such like this:

    SELECT

          [DepartmentCode]

         ,[Year]

         ,[Month]

         ,dbo.udf_StripHTML(Comments) as Comments

    FROM [yourtable]

    I sure hope this helps someone; cause it helped me bounce back from  a painful morning!

  38. Shell D says:

    Further to Rahul's post on 7Oct2009…

    I too am yet to find a solution that works to display RTF that contains images in SSRS, just like it would display in word (formatted text & images).

    The PebbleReportsSsrsUtils.dll from pebblereports.com/reportingservicesutilities works to display works a treat but it doesn't display the images, apparently because SSRS reports don't handle images in HTML.

    Anyone have a solution yet please?