locked
Parsing RTF Strings into a Report RRS feed

  • Question

  • Hello all,
    I'm a DBA creating a functional report for our team, just so you know that I'm not a true report dev.

    I have a RTF string that I need to display in a report:

    From this:
    {\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 Microsoft Sans Serif;}{\f1\fnil Microsoft Sans Serif;}}  \viewkind4\uc1\pard\f0\fs17 http://sharepoint.what.net/sites/it/Web/WebDev/Change%20Notices1/June%202009%20month%20releases/yes.xml\f1\par  \par  \f0 Pleash push the files located at this location to production.\par  } 

    To this:
    http://sharepoint.what.net/sites/it/Web/WebDev/Change%20Notices1/June%202009%20month%20releases/yes.xml

    Pleash push the files located at this location to production.

    How can I accomplish this? Is this doable at the SSRS level?

    Thanks in advance for the help!
    Monday, June 8, 2009 8:39 PM

Answers

  • Hi Igor,

     

    From your description, you want to convert RTF to TXT in SQL Server Reporting Services.

     

    In SQL Server Reporting Services, we can use the custom code function to convert the RTF to TXT. In the custom code function, use the window RichTextBox control to read the RTF, and then return the TXT.

     

    Here are the detailed steps to convert RTF to TXT in SQL Server Reporting Services:

    1.       Use the following custom code in the Report. The custom code includes a function named ConvertRtfToText. This function require a parameter that is the RTF.

     

    Public Shared Function ConvertRtfToText(ByVal input As String) As String
     
    Dim returnValue As String = String.Empty
     
    Using converter As New System.Windows.Forms.RichTextBox()
     
    converter.Rtf = input.Trim
     
    returnValue = converter.Text
     
    End Using
     
    Return returnValue
     
    End Function

     

    2.       To use the above custom code function, we need to add a reference that refers to the instance “System.Windows.Forms”.

    Please add the reference “System.Windows.Forms” in the “Reference” tab of Report properties dialog.

     

    3.       Setup a domain account for “Execution Account” using the Reporting Services Configuration Manager.

    Executing the above custom code is an unattended report processing.

     

    Unattended report processing refers to any report execution process that is triggered by an event (either a schedule-driven event or data refresh event) rather than a user request. The report server uses the unattended report processing account to log on to the computer that hosts the external data source. This account is necessary because the credentials of the Report Server service account are never used to connect to other computers.

     

    Reporting Services provides a special account named “Execution Account” that is used for unattended report processing and for sending connection requests across the network. To solve the issue, you could setup a domain account for “Execution Account”.

     

    4.       For security reasons, the default permission of report expressions is “Execution” which is defined in the SQL Server Reporting Service CONFIG file “rssrvpolicy.config”.  If we use a custom function in the expression, and the custom function uses code like this “Using converter As New System.Windows.Forms.RichTextBox()” , the expression will require more permissions in addition to “Execution”.

     

    To provide proper execution permission, we can change the permission set of the following code groups to be “FullTrust”:

     

    <CodeGroup class="FirstMatchCodeGroup" version="1" PermissionSetName="Nothing">
                <IMembershipCondition class="AllMembershipCondition" version="1" />
                <CodeGroup class="UnionCodeGroup" version="1" PermissionSetName="FullTrust" Name="Report_Expressions_Default_Permissions" Description="This code group grants default permissions for code in report expressions and Code element. ">
     
      <CodeGroup class="FirstMatchCodeGroup" version="1" PermissionSetName="FullTrust" Description="This code group grants MyComputer code Execution permission. ">
                  <IMembershipCondition class="ZoneMembershipCondition" version="1" Zone="MyComputer" />
     

     

     

     

    After finishing the following steps, we can convert the RTF to TXT in the SQL Server Reporting Services.

     

    For more information about code access security, please see:

    Understanding Code Access Security in Reporting Services: http://msdn.microsoft.com/en-us/library/ms155108.aspx

    Execution Account (Reporting Services Configuration): http://msdn.microsoft.com/en-us/library/ms181156(SQL.90).aspx

     

    If there is anything unclear, please feel free to ask.

     

    Thanks,

    Jin Chen

     
    Jin Chen - MSFT
    Wednesday, June 10, 2009 3:48 AM
  • Hi IgorDBA,

    It seems to be security issue.

    Could you please change the following 2 groups in rsreportserver.config to be fulltrust:
                <CodeGroup class="UnionCodeGroup" version="1" PermissionSetName="FullTrust" Name="Report_Expressions_Default_Permissions" Description="This code group grants default permissions for code in report expressions and Code element. ">

      <CodeGroup class="FirstMatchCodeGroup" version="1" PermissionSetName="FullTrust" Description="This code group grants MyComputer code Execution permission. ">

    And then specify a Execution account that has mentioned in previous post.

    We can get the rsreportserver.config file from:
    <Install Driver>:\Program Files\Microsoft SQL Server\MSSQL.<X>\Reporting Services\ReportServer

    Please feel free to ask, if you have any more questions.

    Thanks,
    Jin Chen


    Jin Chen - MSFT
    • Marked as answer by sqlsantos Tuesday, June 30, 2009 7:52 PM
    Tuesday, June 30, 2009 1:52 AM

All replies

  • Hi Igor,

     

    From your description, you want to convert RTF to TXT in SQL Server Reporting Services.

     

    In SQL Server Reporting Services, we can use the custom code function to convert the RTF to TXT. In the custom code function, use the window RichTextBox control to read the RTF, and then return the TXT.

     

    Here are the detailed steps to convert RTF to TXT in SQL Server Reporting Services:

    1.       Use the following custom code in the Report. The custom code includes a function named ConvertRtfToText. This function require a parameter that is the RTF.

     

    Public Shared Function ConvertRtfToText(ByVal input As String) As String
     
    Dim returnValue As String = String.Empty
     
    Using converter As New System.Windows.Forms.RichTextBox()
     
    converter.Rtf = input.Trim
     
    returnValue = converter.Text
     
    End Using
     
    Return returnValue
     
    End Function

     

    2.       To use the above custom code function, we need to add a reference that refers to the instance “System.Windows.Forms”.

    Please add the reference “System.Windows.Forms” in the “Reference” tab of Report properties dialog.

     

    3.       Setup a domain account for “Execution Account” using the Reporting Services Configuration Manager.

    Executing the above custom code is an unattended report processing.

     

    Unattended report processing refers to any report execution process that is triggered by an event (either a schedule-driven event or data refresh event) rather than a user request. The report server uses the unattended report processing account to log on to the computer that hosts the external data source. This account is necessary because the credentials of the Report Server service account are never used to connect to other computers.

     

    Reporting Services provides a special account named “Execution Account” that is used for unattended report processing and for sending connection requests across the network. To solve the issue, you could setup a domain account for “Execution Account”.

     

    4.       For security reasons, the default permission of report expressions is “Execution” which is defined in the SQL Server Reporting Service CONFIG file “rssrvpolicy.config”.  If we use a custom function in the expression, and the custom function uses code like this “Using converter As New System.Windows.Forms.RichTextBox()” , the expression will require more permissions in addition to “Execution”.

     

    To provide proper execution permission, we can change the permission set of the following code groups to be “FullTrust”:

     

    <CodeGroup class="FirstMatchCodeGroup" version="1" PermissionSetName="Nothing">
                <IMembershipCondition class="AllMembershipCondition" version="1" />
                <CodeGroup class="UnionCodeGroup" version="1" PermissionSetName="FullTrust" Name="Report_Expressions_Default_Permissions" Description="This code group grants default permissions for code in report expressions and Code element. ">
     
      <CodeGroup class="FirstMatchCodeGroup" version="1" PermissionSetName="FullTrust" Description="This code group grants MyComputer code Execution permission. ">
                  <IMembershipCondition class="ZoneMembershipCondition" version="1" Zone="MyComputer" />
     

     

     

     

    After finishing the following steps, we can convert the RTF to TXT in the SQL Server Reporting Services.

     

    For more information about code access security, please see:

    Understanding Code Access Security in Reporting Services: http://msdn.microsoft.com/en-us/library/ms155108.aspx

    Execution Account (Reporting Services Configuration): http://msdn.microsoft.com/en-us/library/ms181156(SQL.90).aspx

     

    If there is anything unclear, please feel free to ask.

     

    Thanks,

    Jin Chen

     
    Jin Chen - MSFT
    Wednesday, June 10, 2009 3:48 AM
  • I'll be trying this on the report tomorrow as I just saw this reply. Thank you very much for your response.
    Monday, June 15, 2009 9:15 PM
  • I got this to work on VS but it doesn't work when I deploy to the SSRS server, I only see "#Error" where I should be seeing the txt string.
    I does work completely while in debug mode in VS, just when I deploy to the server that I get this error.

    Any suggestions?
    Monday, June 29, 2009 5:28 PM
  • Hi IgorDBA,

    It seems to be security issue.

    Could you please change the following 2 groups in rsreportserver.config to be fulltrust:
                <CodeGroup class="UnionCodeGroup" version="1" PermissionSetName="FullTrust" Name="Report_Expressions_Default_Permissions" Description="This code group grants default permissions for code in report expressions and Code element. ">

      <CodeGroup class="FirstMatchCodeGroup" version="1" PermissionSetName="FullTrust" Description="This code group grants MyComputer code Execution permission. ">

    And then specify a Execution account that has mentioned in previous post.

    We can get the rsreportserver.config file from:
    <Install Driver>:\Program Files\Microsoft SQL Server\MSSQL.<X>\Reporting Services\ReportServer

    Please feel free to ask, if you have any more questions.

    Thanks,
    Jin Chen


    Jin Chen - MSFT
    • Marked as answer by sqlsantos Tuesday, June 30, 2009 7:52 PM
    Tuesday, June 30, 2009 1:52 AM
  • If those classes are not available on my rsreportserver.config file, can I just add them?
    I did a search for <Code, code, CodeGroup and nothing can be found. On a side note, I'm opening the file using notepad.

    Thanks!
    Tuesday, June 30, 2009 7:42 PM
  • I guess I was looking at the wrong file. I went back to the very first post from Jin and noticed he mentioned the rssrvpolicy.config file, that's where the changes needed to occur and that fixed my issue, the subreport on my report now displays TXT strings instead of RTF.

    Thanks for the help Jin.
    Tuesday, June 30, 2009 7:52 PM
  • Hi Guys,

    If you create report in VS2008 and don't deploy it yet, do you have to modify settings?  I am kind of confused, I created report, followed all steps for adding function (in code window) and references for rtf control, but still getting error.  Am I missing something?  If I do development in VS - no reporting services configs are involved, correct?
    Here is warning I got:
    Warning 1 [rsRuntimeErrorInExpression] The Value expression for the textrun ‘Notes.Paragraphs[0].TextRuns[0]’ contains an error: Request for the permission of type 'System.Security.Permissions.UIPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed. C:\Documents and Settings\oleg\My Documents\Visual Studio 2008\Projects\DW_Reports\DW_Reports\Test.rdl 0 0 

    Any help would be greatly appreciated.

    Thanks in advance,

    Oleg
    Thursday, October 8, 2009 9:38 PM
  • Hi Guys,

    I even wonder how to get rid of this exception. I use some code as property of a report. The code looks like:

    Public Shared Function ConvertRtfToText(ByVal input As String) As String
        Dim perm As New System.Security.Permissions.UIPermission(Security.Permissions.PermissionState.Unrestricted)
        perm.Assert()
       
        Dim returnValue As String = String.Empty

         Using converter As New System.Windows.Forms.RichTextBox()
            converter.Rtf = input.Trim
            returnValue = converter.Text
         End Using
     
        Return returnValue
    End Function


    This is just some user-defined code, no external assembly. I also added System.Windows.Forms to the list of assemblies in the report properties.

    Any help would be great.

    Thank you in advance,
    Bets Regards,
    Nico

    Friday, November 20, 2009 4:08 PM
  • Reporting Services 2008 doesn't understand RTF format, but it does understand HTML. So you can use a custom assembly to convert RTF to HTML.  Here's the code and instructions. If you are using Reporting Services 2005 then convert the RTF to bitmap (or to plain text) as others have suggested.
    Thursday, January 28, 2010 5:05 PM
  • Jin,

    Now this doesn't work on my desktop either.

    I get #Error when I try to display this same report from VS.

    What local settings could be causing this issue?

    Wednesday, August 25, 2010 6:22 PM
  • Wednesday, August 25, 2010 7:17 PM
  • Hello,

    I did all of the above coding and have this working with SSRS 2008, Win 2003 Server, and IIS 6.0. 

    We've created a new server running SSRS 2008R2, Win 2008, and IIS 7.  It does not work on this new environment and I do not get any error, but instead the SSRS Service reboots itself.

    Can you give me an idea of what I may need to do or test to get this working?  I've checked the log files and the Event Viewer, but not seeing any errors.  It does call my assembly and works if I just output some text to the report.  It fails when I try to reference System.Windows.Forms.RichTextBox.  I also installed to the GAC with no luck.

    My code:

    using System;
    using System.Windows.Forms;
    using System.Diagnostics;

    namespace ConversionUtility
    {
        public class ConversionUtility
        {
            [System.Security.Permissions.PermissionSet(System.Security.Permissions.SecurityAction.Assert, Unrestricted = true)]
            public static string ConvertRTFToText(string richText)
            {
                string sText = "";

                try
                {

                    'if I comment out these lines, this will work
                    RichTextBox richTextBox = new System.Windows.Forms.RichTextBox();
                    richTextBox.Rtf = richText;
                    sText = richTextBox.Text;
                    //throw new Exception("Log this error");
                }
                catch (Exception e)
                {
                      sText = "Error Caught: " + e.Message;
                }
              
                return sText;
               
            }
        }
    }

    If I don't have these permissions, it will error.  I add the group and it works if I don't reference the RichTextBox.

    <CodeGroup
    	class="UnionCodeGroup"
    	version="1"
    	Name="SecurityExtensionCodeGroup"
    	Description="Code group Conversion Utility"
    	PermissionSetName="FullTrust">
    	<IMembershipCondition
    		class="UrlMembershipCondition"
    		version="1"
    		Url="C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\bin		\ConversionUtility.dll"
    	/>
    </CodeGroup>

    I'm thinking it's having an issue with permissions to System.Windows.Forms, but haven't been able to figure out what I need to do.

    Also, I do have the Execution Account Setup. 

    Thanks,

    Randy

    Wednesday, December 1, 2010 9:43 PM
  • I ended up contacting Microsoft because this should have worked. When I gave them a sample application in worked in their environment. It turned out that they had a hotfix installed for Reporting Services that I did not. After I installed the hotfix, everything worked.

    build 10.50.1720 - applied CU2 for SSRS 2008 R2 RTM (http://support.microsoft.com/kb/2072493).

    • Proposed as answer by Iceman_Bauer Tuesday, December 14, 2010 3:09 PM
    Tuesday, December 14, 2010 3:08 PM
  • Deleted
    Tuesday, November 29, 2011 9:20 PM
  • Did you complete this step at the properties of your report?

     

    2.       To use the above custom code function, we need to add a reference that refers to the instance “System.Windows.Forms”. 

    Please add the reference “System.Windows.Forms” in the “Reference” tab of Report properties dialog.


    --- Best Regards, Igor Santos Twitter: @sqlsantos Blog: sqlsantos.wordpress.com
    Thursday, January 5, 2012 5:18 PM
  • Deleted
    Friday, January 6, 2012 4:44 PM
  • Can I get more detailed info for step 2 please?

    Using Report Builder 3.0, these options exist in the 'Referenes' tab:

    * Add or remove assemblies:   dll name

    * Add or remove classes:  Class Name    &    Instance Name

    what exactly do I fill in for these 3 options please?


    Regards

    Shell

    SQL Server 2012

    MS SQL Server Report Builder 3.0


    • Edited by shell_l_d Wednesday, June 24, 2015 2:41 AM
    Wednesday, June 24, 2015 2:40 AM