Limit text in SSRS Report Table

Question

Background:

I have a table with say 40 columns reporting Employee Details. Where 39 of 40 columns are of Datatype varchar(10) and the 40th column, being Manager's comments, is of Datatype varchar(1000).

Problem:

The report is looking bad as due to one column the height of the complete row is increasing than normal expectation.

Solutions I thought of:

1. Increase width of column > Looking bad if no comments
2. SubString the data coming > Loss of data
3. Set CanGrow to False + set the Height for Row to have 2 data lines + Show text on tool tip + Export to excel link of a different report which has CanGrow as false > not very good solution

Can someone suggest a better way to handle one column with more text than the rest of them as even my 3rd approach is looking non-ideal to me?

Thanks in advance.

Answer 1

A little unconventional, but in situations like this I've put the last column in its own row beneath the other columns, merged all the cells in that row, and then grouped by the primary key. That makes a report in which you've got a two row SSRS group for each row in your query results, the first row contains all of the short columns and the second row contains just one long column. If there is a fairly short string in that last column, or an empty value, it will just take up one row. If there is a long string, then it will have space to expand vertically (if CanGrow=True), instead of expanding horizontally and making an impractically long report.

This may not meet all reporting purposes, but if the report is intended for visual use it can work well.

Answer 2

The best solution I could find was

1. Set CanGrow = False
2. Increase Width and set a generic Height
3. Show tooltip
4. Export to excel works fine and shows data unlike I expected (as the data is not truncated but just the textbox is restricted to show data in SSRS frontend)

This works for me as there is no data loss.