We help IT Professionals succeed at work.

Oracle CLOB datatype in SSRS report

sfuk
sfuk asked
on
1,018 Views
Last Modified: 2014-11-16
Dear Experts,

I need to display CLOB column in SSRS report, the length of which is more than 32K. The column contains HTML tags as well as formatted text. When the HTML tags are striped out, the output loses its formatting, including defined paragraphs and font differences (except capitals and lowercases).
Could you please help me to resolve this issue?  

Thank you in advance,
sfslku
Comment
Watch Question

CERTIFIED EXPERT

Commented:
there are few new data types which can handle long objects.. did you explore those options? check the below site for details..

http://sqlmag.com/t-sql/dont-be-afraid-blobs-and-clobs

Responding to a neglected question alert.  Not an expert from SQL Server background. Till some one arrives see if this helps
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
>> I need to display CLOB column in SSRS report, the length of which is more than 32K.

Just curious on what kind of data greater than 32K you are trying to show in SSRS report. If you are going to show an 32K data in your report for assuming 10 records, it would be difficult to view it in the Report.
So, my first suggestion would be to limit and show only useful data in the Report and not just the entire data present in the html tags.

>> The column contains HTML tags as well as formatted text. When the HTML tags are striped out, the output loses its formatting, including defined paragraphs and font differences (except capitals and lowercases).

Yes, by default SSRS will not render HTML values unless you define or instruct it to show HTML tags in the report.
And please note that SSRS has a limitation on rendering HTML tags and hence it will render only limited HTML tags as mentioned in the below MSDN link..
http://msdn.microsoft.com/en-us/library/ff519562%28v=sql.105%29.aspx

And you need to define the Placeholder properties to ensure the HTML tags are rendered properly.
For the CLOB column displaying in SSRS, Right click the Field and choose PlaceHolder properties -> General -> For Markup type choose "HTML-Interpret HTML tags as styles".
Also note that SSRS cannot render or interpret all HTML tags and only the tags listed in the above MSDN link only..

Author

Commented:
Thank you for the replies.
Unfortunately,  change of the datatype is not an option until CLOB may be converted into it while a report is generating.
The comments that bigger than 32K are some reviews. They look fine in SSRS if entered as copy-past from Notepad.
But when MS Word is used or the data is entered straight from a web app then it is a problem.

Author

Commented:
For more details:
Below is example of the output that I need to format.  Is there anyway to remove these XML tags and keep the rest of the text as it is? Thanks again.

1. The offseason is all but over – not in terms of duration, unfortunately, but based on the moves teams are going to make in order to improve their roster.<?xml:namespace prefix = "o" ns = "urn:schemas-microsoft-com:office:office" />
2. To see today’s results based solely on the draft, click here.<?xml:namespace prefix = "o" ns = "urn:schemas-microsoft-com:office:office" />
3.  Movies and More:<?xml:namespace prefix = "o" ns = "urn:schemas-microsoft-com:office:office" />
     In the 2008 movie "Iron Man," the main character becomes a superhero after building a suit of armor with an      
     exoskeleton that gives him incredible strength.<?xml:namespace prefix = "o" ns = "urn:schemas-microsoft-
     com:office:office" />
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
Hi sfuk,

As mentioned earlier, xml:namespace is not supported by SSRS and hence those HTML data will not get displayed properly.
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
Thanks for the update, sfuk..
Steve WalesSenior Database Administrator
CERTIFIED EXPERT

Commented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.