none
SSRS Report rendering different in VS2005 and in Report Viewer via URL Access

    Question

  • Hello,

    I'm using Reporting Services to render a text (stored in sql as varchar(max)). The text is all plain text, with some lines having trailing spaces.

     

    Source text file i've imported to SQL via SSIS:

    CLIENT: 10055
                                   STATEMENT 2007
    DATE:1002993

    THIS IS THE OTHER STATEMENT

    COLUMN1       COLUMN2          COLUMN 3
    TRY THIS


    *Note the trailing spaces on the line before 'STATEMENT 2007'.

     

    I've designed a report using the Report Project in VS2005 to retrieve this text via a stored procedure. When I test the report using the 'Preview' tab in the IDE, it looks good

    CLIENT: 10055
                                   STATEMENT 2007
    DATE:1002993

    THIS IS THE OTHER STATEMENT

    COLUMN1       COLUMN2          COLUMN 3
    TRY THIS

     

    But when I deploy the report and run it using URL Access:

    CLIENT: 10055
    STATEMENT 2007
    DATE:1002993

    THIS IS THE OTHER STATEMENT

    COLUMN1 COLUMN2 COLUMN 3
    TRY THIS

     

    On all lines with trailing spaces, they (the trailing spaces) have been removed. This is affecting my formatting of some reports and statements. We really want to use the report viewer as it has built in paging, print and export capabilities.

     

    Why does it look okay in VS2005 but different in Report Viewer via URL Access and Report Manager?

     

    Note: When I export as PDF, it looks okay.

     

    The stored procedure I use to return the data is a CLR Hosted assembly as below:

    Code Snippet

    Partial Public Class StoredProcedures

    <Microsoft.SqlServer.Server.SqlProcedure()> _

    Public Shared Sub GetPagedDocument(ByVal inText As SqlString)

    Dim dr As SqlDataReader

    Dim row As New SqlDataRecord(New SqlMetaData("RowText", SqlDbType.Text))

    Dim cmd As New SqlCommand("select cast(doc as varchar(max)) as 'DOCTEXT' from testdoc WHERE id='" + inText + "'")

    Dim cn As New SqlConnection("context connection=true")

    cn.Open()

    cmd.Connection = cn

    SqlContext.Pipe.SendResultsStart(row) 'initialise the resultset to be returned

    dr = cmd.ExecuteReader

    'If no records in result set, return.

    If Not dr.HasRows Then

    row.SetString(0, "There is no document to display or you do not have permission to view the document.")

    SqlContext.Pipe.SendResultsRow(row)

    SqlContext.Pipe.SendResultsEnd()

    ' SqlContext.Pipe.Send("There is no document to display.")

    Return

    End If

    'Read rows in the result set

    dr.Read()

     

    'Get the entire text

    Dim docText As String = dr.Item("DOCTEXT")

    'debug

    row.SetString(0, docText)

    SqlContext.Pipe.SendResultsRow(row)

    SqlContext.Pipe.SendResultsEnd()

    Return

    'end debug

     

    End Sub

    End Class

     

     

    Any help will be appreciated.
    Thursday, April 17, 2008 8:48 AM

Answers

  • I think i got a workaround. The problem was that SSRS was removing whitespaces from the text, which was messing up

    my fixed with text.

     

    So in my report designer, in the texbox where i display the text, i replace all spaces with Chr(0160), a non breaking space. This gets rendered correctly in HTML.

    Friday, April 18, 2008 8:17 AM

All replies

  • I forgot to note. Not just trailing spaces, but where mutliple spaces are between texts, they are trimmed to single space too.

     

    I've tried rendering differently via URL access with the query string rs:Format=HTML4.0 / HTML3.2, all to no avail.

     

    Please help.

    Thursday, April 17, 2008 8:58 AM
  • I think i got a workaround. The problem was that SSRS was removing whitespaces from the text, which was messing up

    my fixed with text.

     

    So in my report designer, in the texbox where i display the text, i replace all spaces with Chr(0160), a non breaking space. This gets rendered correctly in HTML.

    Friday, April 18, 2008 8:17 AM