Strip html tags when exporting to Excel

[huhumix]

HI,

Does anyone know how i can strip a text of HTML tags ? The text is saved in the database with the html tags... if i put them in a html item in a report , of course they look ok. But when i try to export , nothing shows up ( of course, because the html item is not rendered in excel..)

I have tried using "replace" function, but the tags are different so no sense in using that...

Also, i have read that a scalar function executed on the database directly could help.. but in this case i would need to change also the FM model.

So looking for a solution / functions to use directly in Report Studio..

Many thanks for the help!

[MFGF]

HI,

Does anyone know how i can strip a text of HTML tags ? The text is saved in the database with the html tags... if i put them in a html item in a report , of course they look ok. But when i try to export , nothing shows up ( of course, because the html item is not rendered in excel..)

I have tried using "replace" function, but the tags are different so no sense in using that...

Also, i have read that a scalar function executed on the database directly could help.. but in this case i would need to change also the FM model.

So looking for a solution / functions to use directly in Report Studio..

Many thanks for the help!

You could perhaps use a conditional block with one set of contents (including the HTML items) that displays when rendering as HTML and a different set of contents (without the HTML tags) when rendering as any other type. The expression for the block variable would simply need to be a boolean to check for rendering type HTML.

Cheers!

MF.

[huhumix]

thanks, but the thing is that i don't have anywhere the text items without the html tags :) in the database they appear with them
how do i get strip them ?

[MFGF]

thanks, but the thing is that i don't have anywhere the text items without the html tags :) in the database they appear with them
how do i get strip them ?

Oh - ok, so your tags are actually stored in the database data? In that case you would need query calculations to strip them off - using substring() functions. Messy!

MF.

[huhumix]

yep. they are stored in the database..  tried with substr  :) only that the tags are not only on left and right of the text , but also within the text :) ... multiple paragraphs of text with tags . so no way really even if i use substr  .

[cognos810]

Hello huhumix,
Do you have the flexibility on the backend to get a function created? If yes, something like this would work:
Function name in this example udf_StripHTML

CREATE FUNCTION [dbo].[udf_StripHTML] (@HTMLText VARCHAR(MAX))
RETURNS VARCHAR(MAX) AS
BEGIN
    DECLARE @Start INT
    DECLARE @End INT
    DECLARE @Length INT
    SET @Start = CHARINDEX('<',@HTMLText)
    SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText))
    SET @Length = (@End - @Start) + 1
    WHILE @Start > 0 AND @End > 0 AND @Length > 0
    BEGIN
        SET @HTMLText = STUFF(@HTMLText,@Start,@Length,'')
        SET @Start = CHARINDEX('<',@HTMLText)
        SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText))
        SET @Length = (@End - @Start) + 1
    END
    RETURN LTRIM(RTRIM(@HTMLText))
END
GO