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

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

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

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

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

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

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

Excel does not honor page settings

Rendering in Excel does not honor my page layouts (landscape, 8.5x14, etc.) -
every report comes out 8.5x11 portrait. PDF works fine, as does printing
from the HTML default. Any input is appreciated.
ScottI have got a similar problem.. report is set for portrait format but it comes
out Landscape in Excel. If you have found any solution, please do let me know.
Thanks,
Milin
"ScottB" wrote:
> Rendering in Excel does not honor my page layouts (landscape, 8.5x14, etc.) -
> every report comes out 8.5x11 portrait. PDF works fine, as does printing
> from the HTML default. Any input is appreciated.
> Scott|||I've had similar issues with Excel exports - it appears to use the
ratio of body height to body width to decide on orientation, not the
report page sizes. Only workaround I've used to force a portrait
orientation is to make the body height slightly larger than the body
width which appears to have the desired effect.
I've now got the opposite problem, I want the report to be landscape
and have the width set to 29.7cm, but because of the number of tables I
have in the report, the body height is greater than the width and it is
portrait when exported to Excel. Only way I can think of getting round
this is to put the tables into separate sub-reports, but I haven't had
time to try it out yet....
Milu wrote:
> I have got a similar problem.. report is set for portrait format but it comes
> out Landscape in Excel. If you have found any solution, please do let me know.
> Thanks,
> Milin
> "ScottB" wrote:
> > Rendering in Excel does not honor my page layouts (landscape, 8.5x14, etc.) -
> > every report comes out 8.5x11 portrait. PDF works fine, as does printing
> > from the HTML default. Any input is appreciated.
> >
> > Scott|||I have the same issue. Any word on whether this is a setting issue or a bug?
"Milu" wrote:
> I have got a similar problem.. report is set for portrait format but it comes
> out Landscape in Excel. If you have found any solution, please do let me know.
> Thanks,
> Milin
> "ScottB" wrote:
> > Rendering in Excel does not honor my page layouts (landscape, 8.5x14, etc.) -
> > every report comes out 8.5x11 portrait. PDF works fine, as does printing
> > from the HTML default. Any input is appreciated.
> >
> > Scott