Russell Christopher's Semi-Useful BI Musings

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.

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

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?

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?

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.

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.

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

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

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>

Is there by now a way to do this?  

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.

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.

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, "OfferingDetails")),

Nothing,

Globals!ReportServerUrl + "?" +

Globals!ReportFolder +

"/" +

Globals!ReportName +

"&offeringId=" + Parameters!offeringId.Value.ToString() +

"&rs:Command=Render" +

"&rs:format=XML" +

"&rc:XSLT=Offering Outline Transformer.xsl" +

"&rc:MIMEType=text/html" +

"&rc:Toolbar=false"

)

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)

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.

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 ?

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!

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

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, "<br />", "")

, "<p>", "")

, "</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.

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

What gives?

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

thanks

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

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

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,"[\n\r\f]", ""), "({\\)(.+?)(})|(\\)(.+?)(\b)", ""), "{", ""), "}", "").Trim()

       End If

       Return value

   End Function

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

=Code.RtfToText(Fields!PrintName.Value)

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

PingBack from http://www.keyongtech.com/389710-formatting-crm-descript-fields-to

PingBack from http://paidsurveyshub.info/story.php?title=russell-christopher-s-semi-useful-bi-musings-i-want-to-render-html

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.