SouBee


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




Re: Excel doesn't honor CanGrow setting.

Priyank Pandey


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






Re: Excel doesn't honor CanGrow setting.

SouBee

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






Re: Excel doesn't honor CanGrow setting.

Priyank Pandey

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.





Re: Excel doesn't honor CanGrow setting.

SouBee

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!Amount___QUEST_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





Re: Excel doesn't honor CanGrow setting.

Priyank Pandey

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.





Re: Excel doesn't honor CanGrow setting.

SouBee

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





Re: Excel doesn't honor CanGrow setting.

Rickwhipp

Hey SouBee,

I'm having the same issue and I was wondering if you ever got a resolution to this problem.

Thanks,
Rick




Re: Excel doesn't honor CanGrow setting.

Lisa Nicholls

>> if you know of a way...

I do, but you may not like it... lots of people don't.

Excel as provided by RS is not the only Excel RS can provide. Request the data output as XML and use XSLT to get it the way you want.

If you *mostly* like what RS gives you in the way of Excel output, then use that as a model from which you derive your XSLT. Save it as SSML If you *don't* like it or just happen to want completely different output from the same data, then design your report in Excel instead, Save it AS SSML in Excel, and use *that* as your model.

I have provided a walkthrough of this process, here: http://spacefold.com/lisa/post/Changing-the-Sheet-names-in-SQL-Server-RS-Excel-QnD-XSLT.aspx This was in answer to a question about how to change the RS-provided Excel worksheet names.

In the walkthrough, you attach the XSLT directly to the report on the Data output tab, which makes the whole thing "automagic". However, that's obviously not the only way to do it. You can just take the XML output and do the transform yourself. This gives you a whole lot more scope; you can parameterize the XSLT or use different ones depending on the situation.

You can really do anything you want.

>L<