Expression Field Does Not Word Wrap in Excel

JP Swamy 0 Reputation points
2023-02-07T14:54:44.7533333+00:00

I have an SSRS report created in Report Builder. When I use a report field as part of a formula expression, the column does NOT word wrap when exported to Excel. However, if I remove the expression and use just the report field, then the column does word wrap when exported to Excel. The word wrapping is not working on formula expression fields in SSRS, so this issues is not related to merged cells. How do I get SSRS formula expressions to word wrap when exported to Excel?

Example: The below expression does NOT word wrap in Excel.

=IIF(IsNothing(Fields!TaskDateCreated.Value), "", System.TimeZone.CurrentTimeZone.ToLocalTime(Fields!TaskDateCreated.Value))

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,772 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Michael Taylor 45,996 Reputation points
    2023-02-07T15:44:35.39+00:00

    Expression fields can word wrap in SSRS. If you think about it for a second then it wouldn't make sense otherwise. Almost everything shown on a report comes from one or more datasets. To get the dataset fields to show up you have to use an expression. Thus if word wrapping didn't work then no data from the queries would ever wrap.

    Exporting a report that has the field expression you specified does show wrapped in the report itself and when exported in Excel. If you click the column (or any cell in that column) then you can see the wrapping is turned on in Excel. But you are potentially still seeing the column not being wide enough and therefore the ### stuff show up. Whether you use a complex formula or the raw dataset field doesn't change anything about this behavior. I suspect that it just happens to be the data you're showing that causes you to see differences. But I'm just guessing based upon what could go wrong.

    The issue is actually on the Excel side and you can replicate it without SSRS. In a spreadsheet type in a full date value. Then resize the column so it is smaller than what the text value is. Notice that Excel converts it to ### instead of wrapping. Now turn on wrapping for the column and notice nothing changes. This is how Excel works. Excel will not word wrap numbers and dates are numbers to Excel. You can confirm this by changing the formatting to a number. There are ways to force Excel to wrap date/time values but it requires changing the Excel spreadsheet and that isn't something you can do from SSRS. Here's a link to some approaches to getting Excel to wrap date/time values.

    One possible way to handle this in SSRS is to not show the date time using the default formatting. Use ToString to format the data and put a newline between the date and time. Unfortunately that would require you to break things up a little more than you might like.

    0 comments No comments

  2. JP Swamy 0 Reputation points
    2023-02-07T20:17:41.3266667+00:00

    I resolved the issue by just adding & vbCrlf to the end of SSRS expression field. Now, the fields word-wrap in Excel without any extra formatting by end users. Thanks!

    =IIF(IsNothing(Fields!TaskDateCreated.Value), "", System.TimeZone.CurrentTimeZone.ToLocalTime(Fields!TaskDateCreated.Value)) & vbCrlf