Excel doesn''t honor CanGrow setting.

Hi,

I have a matrix table in a report. The cells CanGrow field is set to true. When the report is generated in the browser, the row height grows according to the content in the cells. However, when the same report is exported to an Excel document, the row height is fixed and the content in the cells are partially hidden.

Is there anyway to get Excel report generator to recognize the CanGrow setting?

Thanks.

-SouBee

This is happening bacause of merged cells in excel. TO check export the report in excel and right click the cell which is not growing, you can see merged cell and can wrap both properties are checked.

In order to remmove this you need to avoid merging by using full length textboxes and by avoiding any component which can cause a merged cell for that particular column.

Please note that SSRS creates a new column i excel for starting and ending of each component (textbox,table column,rectangle,line) and so avoid short length components for excel support.

Priyank

|||

Hi Priyank,

The cell that needs to grow dynamically is the Total/Subtotal cell. I've implementated custom data to be displayed in this cell and the data has variable length which will require CanGrow to work in order to grow its size accordingly. I don't think I can avoid using merged cell because Total/Subtotal cells are generated in Excel as merged cells. Is there any other workaround?

Thanks for the help!

-SouBee

|||

i think i didnt make it clear enough

"Please note that SSRS creates a new column in excel for starting and ending of each component (textbox,table column,rectangle,line) and so avoid short length components for excel support."

So what you need to check is that your total/subtotal cell in report layout should not be starting or end point of any other component vertically.

You have to avoid merged cell in excel to get this done.

|||

Hi Priyank,

I hope I understand you better this time. I don't have any components (textbox, rectangle, etc.) in my total/subtotal cell. This total/subtotal cell contains a very long string that span across multiple lines. The expression for this cell looks like this...

StrDup(70, Chr(160)) &

"000 " & First(Fields!CurrencyName.Value, "Currency") & StrDup(17, Chr(160))

Format(Sum(CInt(Fields!AmountQUEST_YAgo.Value))/13, "N0") & vbCRLF &

StrDup(70, Chr(160)) &

First(Fields!CurrencyName.Value, "Currency") & "/" & First(Fields!UOMName.Value, "ProductionUnitsUOM") & StrDup(17, Chr(160)) &

Format(Sum(CDbl(Fields!Amount_QUEST_per_Finished_Unit_YAgo.Value))/13, "N4") & vbCRLF &

StrDup(70, Chr(160)) & "Index"

Thanks.

-SouBee

|||

Neither length of expression nor the presence of textboxes or rectangle inside the cell can be a problem.

Just export your report to excel, you have your subtotal/total column which is not growing, right click that cell in excel goto format cells ->alignment you can see wrap text and merged cells both are checked, since merged cells is checked this cell cannot grow. You can verify this even in any xls, just merge two cells and check both the properties ,enter a long text doesnt fit in that cell and you will see its getting chopped but if you do samr in non merged cell the text wont be chopped.

So what i am suggesting is to create your RDL in a way that not many meged columns should be resulted in the excel. Atleast not those which you are expecting to grow, the reason of the merged cells is having one or more components starting or ending location above or below the component(which has can grow property). So suppose you are having a column from 1 in (starting location) to 2 in (ending location) and you want to grow this column cells in excel, for doing so you need to make sure that in your report no textbox,rectangle,line anything has starting of ending location between 1in and 2 in ( so your textbox,rectangle,line anything can start from (0-1 in) but not at (1 to 2) and same rule for ending location nothing can have ending location between 1 and 2in)

HTH.

|||

Hi Priyank,

I understand merge and wrap properties in Excel. However, as indicated in my previous email RS generated the subtotal cell as merged cell because of multi row grouping in matrix table.

Here is the layout of my matrix table

Row Group 1: Category Type - column A in Excel (Subtotal is added for this group)

Row Group 2: Category - column B in Excel

Row Group 3: Description - column C in Excel

Row Group 4: Baseline - column D in Excel

Because the Subtotal cell is added for the top most row group (group 1), when the report is generated in Excel this Subtotal cell spans across column B, C and D in a single merged cell. If you know of a way to generate this subtotal cell without using merged cell I would love to hear about it.

Thanks.

-SouBee

sql