Exportin to Excel Format Issue

I have a report that contains a matrix control on it. The problem I'm having
is when I export the report to Excel some of the data is not recognized a
numeric and I get the green triangle in the corner of the cell. I believe
this is due to the matrix having some blank columns along with numeric data.
If I fill the report with a 0â's instead of "", the report becomes very hard
to read and is not acceptable by the client.
I have tried setting the textbox visibility to false, but then that also
removes the grid lines around the cell.
I have tried to create a custom format based on the data type and that did
not work.
Does anyone know how I can format just the numeric fields to a number and
the still leave the blank cells as blank when I export to Excel? Thanks...
I have provided a basic example below.
Row1 - 45 - 55 - - 60
Row2 - 50 - - 45 - 45We are having a similiar problem. The only way we have found is the copy,
paste special, values in a new worksheet. You loose all formating in that
case.
"cws" wrote:
> I have a report that contains a matrix control on it. The problem I'm having
> is when I export the report to Excel some of the data is not recognized a
> numeric and I get the green triangle in the corner of the cell. I believe
> this is due to the matrix having some blank columns along with numeric data.
> If I fill the report with a 0â's instead of "", the report becomes very hard
> to read and is not acceptable by the client.
> I have tried setting the textbox visibility to false, but then that also
> removes the grid lines around the cell.
> I have tried to create a custom format based on the data type and that did
> not work.
> Does anyone know how I can format just the numeric fields to a number and
> the still leave the blank cells as blank when I export to Excel? Thanks...
> I have provided a basic example below.
> Row1 - 45 - 55 - - 60
> Row2 - 50 - - 45 - 45
>sql

Export to PDF Different!?

I am having problems with my matrix that is embedded in a list. I used a
workaround to create a horizontal table (matrix + list). Now, when i run my
report, the row sizes of the matrix always shrink, even if i put CanGrow and
CanShrink to false.
So now, i haev set CanGrow to true, and have aligned other fields
(textboxes) to line up with their corresponding rows. It looks "decent" when
i run the report on the report server, but when i click export to pdf, all
the textboxes are shifted up and are no longer lined up. Does this happen to
anyone else, and is this a known issue (differences in rendering between the
report server and exporting) ?
Any help is GREATLY appreciated. Thanks!
--whitOn Apr 25, 3:14 pm, Whitney Yiu <Whitney...@.discussions.microsoft.com>
wrote:
> I am having problems with my matrix that is embedded in a list. I used a
> workaround to create a horizontal table (matrix + list). Now, when i run my
> report, the row sizes of the matrix always shrink, even if i put CanGrow and
> CanShrink to false.
> So now, i haev set CanGrow to true, and have aligned other fields
> (textboxes) to line up with their corresponding rows. It looks "decent" when
> i run the report on the report server, but when i click export to pdf, all
> the textboxes are shifted up and are no longer lined up. Does this happen to
> anyone else, and is this a known issue (differences in rendering between the
> report server and exporting) ?
> Any help is GREATLY appreciated. Thanks!
> --whit
You might want to check the properties for each cell in the matrix
control. Check the Format tab and make sure that: 'Can decrease to
accommodate contents' is not selected. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Export to PDF

I have created report using the Matrix control. It looks fine in the
HTML format. The problem arises when I export the report to PDF. The
starting pages of the report look very good, but latter on some pages
are missing header and lines are out of order. This behavior continues
for few pages and after that everything is back to normal. However
after few pages everything starts all over again.
Does anybody have encountered similar problem and found a solution for
it?
Any help would be appreciated.
Thanks,
AlexWhat version of Reporting Services are you using?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
<avaranauskas@.starit.com> wrote in message
news:1131332394.237051.128260@.g43g2000cwa.googlegroups.com...
>I have created report using the Matrix control. It looks fine in the
> HTML format. The problem arises when I export the report to PDF. The
> starting pages of the report look very good, but latter on some pages
> are missing header and lines are out of order. This behavior continues
> for few pages and after that everything is back to normal. However
> after few pages everything starts all over again.
> Does anybody have encountered similar problem and found a solution for
> it?
> Any help would be appreciated.
> Thanks,
> Alex
>

Export To Excel, CSV, XML with expression in Hidden property omits data

I have a matrix table with a rectangle in the data cell. The rectangle has an image and textbox. The textbox has an expression in it's Hidden property based on the column name. The report renders fine on screen. When the report is exported to Excel, CSV, XML the textbox contents are not output (the images display as expected). I've tried setting the DataElementOutput to Output/Yes with no success. Exporting to TIFF, PDF, Web Archive/MTHML is fine.

Here is a sample RDL which exhibits the issue:

<?xml version="1.0" encoding="utf-8"?>

<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">

<DataSources>

<DataSource Name="Gemini50DataSource">

<DataSourceReference>Gemini50DataSource</DataSourceReference>

<rdataSourceID>bb03313c-48a4-4e40-af99-ed584847ca20</rdataSourceID>

</DataSource>

</DataSources>

<BottomMargin>1in</BottomMargin>

<RightMargin>1in</RightMargin>

<ReportParameters>

<ReportParameter Name="ImagePath">

<DataType>String</DataType>

<DefaultValue>

<Values>

<Value>c:\</Value>

</Values>

</DefaultValue>

<Prompt>Image Path</Prompt>

</ReportParameter>

</ReportParameters>

<rdrawGrid>true</rdrawGrid>

<InteractiveWidth>8.5in</InteractiveWidth>

<rdnapToGrid>true</rdnapToGrid>

<Body>

<ReportItems>

<Matrix Name="matrix1">

<MatrixColumns>

<MatrixColumn>

<Width>3.75in</Width>

</MatrixColumn>

</MatrixColumns>

<RowGroupings>

<RowGrouping>

<Width>1.75in</Width>

<DynamicRows>

<ReportItems>

<Textbox Name="PicIndex">

<rdefaultName>PicIndex</rdefaultName>

<ZIndex>1</ZIndex>

<Style>

<TextAlign>Right</TextAlign>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>=Fields!PicIndex.Value</Value>

</Textbox>

</ReportItems>

<Grouping Name="matrix1_PicIndex">

<GroupExpressions>

<GroupExpression>=Fields!PicIndex.Value</GroupExpression>

</GroupExpressions>

</Grouping>

</DynamicRows>

</RowGrouping>

</RowGroupings>

<ColumnGroupings>

<ColumnGrouping>

<DynamicColumns>

<ReportItems>

<Textbox Name="ColumnName">

<rdefaultName>ColumnName</rdefaultName>

<ZIndex>2</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<BackgroundColor>LightBlue</BackgroundColor>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>=Fields!ColumnName.Value</Value>

</Textbox>

</ReportItems>

<Grouping Name="matrix1_ColumnName">

<GroupExpressions>

<GroupExpression>=Fields!ColumnName.Value</GroupExpression>

</GroupExpressions>

</Grouping>

</DynamicColumns>

<Height>0.25in</Height>

</ColumnGrouping>

</ColumnGroupings>

<DataSetName>DataSet2</DataSetName>

<Width>5.5in</Width>

<Corner>

<ReportItems>

<Textbox Name="textbox1">

<rdefaultName>textbox1</rdefaultName>

<ZIndex>3</ZIndex>

<Style>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value />

</Textbox>

</ReportItems>

</Corner>

<Height>0.55208in</Height>

<MatrixRows>

<MatrixRow>

<Height>0.30208in</Height>

<MatrixCells>

<MatrixCell>

<ReportItems>

<Rectangle Name="rectangle1">

<ReportItems>

<Image Name="image1">

<Sizing>AutoSize</Sizing>

<Left>0.25in</Left>

<MIMEType />

<ZIndex>1</ZIndex>

<Visibility>

<Hidden>=IIF(First(Fields!ColumnName.Value = "image"), Len(Fields!CellValue.Value)=0, true)</Hidden>

</Visibility>

<Width>0.3in</Width>

<Source>External</Source>

<Style />

<Value>="file:" + Parameters!ImagePath.Value + Fields!CellValue.Value</Value>

</Image>

<Textbox Name="textbox2">

<Left>1.625in</Left>

<DataElementOutput>Output</DataElementOutput>

<rdefaultName>textbox2</rdefaultName>

<Visibility>

<Hidden>=IIF(Fields!ColumnName.Value &lt;&gt; "image", False, True)</Hidden>

</Visibility>

<Width>1.875in</Width>

<Style>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<BackgroundColor>PeachPuff</BackgroundColor>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Height>0.25in</Height>

<Value>=Fields!CellValue.Value</Value>

</Textbox>

</ReportItems>

<Visibility>

<Hidden>=IIF(True, False, True)</Hidden>

</Visibility>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<BackgroundColor>LightGrey</BackgroundColor>

</Style>

</Rectangle>

</ReportItems>

</MatrixCell>

</MatrixCells>

</MatrixRow>

</MatrixRows>

</Matrix>

</ReportItems>

<Height>0.625in</Height>

</Body>

<rd:ReportID>a944d20c-558a-4805-9d4c-aecc9757f678</rd:ReportID>

<LeftMargin>1in</LeftMargin>

<DataSets>

<DataSet Name="DataSet2">

<Query>

<rd:UseGenericDesigner>true</rd:UseGenericDesigner>

<CommandText>SELECT 1 as PicIndex, 'image' as ColumnName, 'image1.jpg' as CellValue

union

SELECT 2,'image','image2.jpg'

union

SELECT 3,'image','image3.jpg'

union

SELECT 4,'image',null

union

SELECT 5,'something else',null

union

SELECT 6,'another column', 'display my text!'</CommandText>

<DataSourceName>Gemini50DataSource</DataSourceName>

</Query>

<Fields>

<Field Name="PicIndex">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>PicIndex</DataField>

</Field>

<Field Name="ColumnName">

<rd:TypeName>System.String</rd:TypeName>

<DataField>ColumnName</DataField>

</Field>

<Field Name="CellValue">

<rd:TypeName>System.String</rd:TypeName>

<DataField>CellValue</DataField>

</Field>

</Fields>

</DataSet>

</DataSets>

<Width>5.625in</Width>

<InteractiveHeight>11in</InteractiveHeight>

<Language>en-US</Language>

<TopMargin>1in</TopMargin>

</Report>

Here is a posting related to this that may help.

http://blogs.msdn.com/bimusings/archive/2007/02/07/reporting-services-why-aren-t-all-my-report-columns-exporting-to-csv-and-or-xml.aspx

cheers,

Andrew

|||

Andrew,

Thanks for the posting. I've seen that blog. Unfortunately, the "good news" wasn't so good for this case. The setting of DataElementOutput(yes, no, auto) has no effect on the output for the situation described . If you find any other possibilities I would very much appreciate it.

Thanks,

Brian

export to excel question

i used matrix control.

Grouping and Sorting Property

I checked the property

visibility can be to toggled by another report item

report show is right. the item is toggled by another report item

but export the excel file

the report item is not togled by another report item

the excel show like a table.

how can i set the excel show.

set the item is toggled by another report item

this is i want .

JAPAN TOTAL
	TKY MET-1 SD
	TKY MET-2 SD

this is not i want

JAPAN TOTAL
	TKY MET-1 SD
	TOKYO-1
	TOKYO-2
	TOKYO-3
	TOKYO-4
	TOKYO-5
	TKY MET-2 SD

please explain your question.|||

sorry, I am not explain my question.

Export to Excel problem in RS (updated)

Hello there,

Does anyone have a solution to the following export to excel problem i am experiencing in RS 2005 Standard Ed.

When exporting a matrix report in VS2005 (or in Report Manager) I get the following export error displayed in VS :

An error occurred during local report processing.

An error occurred during rendering of the report

Operation is not valid due to the current state of the object.

This is only a problem where exporting data into multiple worksheets - such as dept by dept on different wsheets.

Export to excel with 1 dept works fine.

I have managed to pinpoint the situation where this happens :

Matrix report with page break for the grouping.

I think there might be a fault with the export function.

Anyone like to confirm on this ?

|||

Did you ever find out if this was a bug?

I also have a problem with page breaks in matrix groupings where by I get blank pages when Print Previewing in Excel. However I am using RS 2000 with SP2.

Thanks

Scott

Export to Excel problem in RS (updated - some progress !!)

Hello there,

Does anyone have a solution to the following export to excel problem i am experiencing in RS 2005 Standard Ed.

When exporting a matrix report in VS2005 (or in Report Manager) I get the following export error displayed in VS :

An error occurred during local report processing.

An error occurred during rendering of the report

Operation is not valid due to the current state of the object.

This is only a problem where exporting data into multiple worksheets - such as dept by dept on different wsheets.

Export to excel with 1 dept works fine.

I have managed to pinpoint the situation where this happens :

Matrix report with page break for the grouping.

I think there might be a fault with the export function.

Anyone like to confirm on this ?

|||

Did you ever find out if this was a bug?

I also have a problem with page breaks in matrix groupings where by I get blank pages when Print Previewing in Excel. However I am using RS 2000 with SP2.

Thanks

Scott

Export to Excel problem in RS (updated - some progress !!)

Hello there,

Does anyone have a solution to the following export to excel problem i am experiencing in RS 2005 Standard Ed.

When exporting a matrix report in VS2005 (or in Report Manager) I get the following export error displayed in VS :

An error occurred during local report processing.

An error occurred during rendering of the report

Operation is not valid due to the current state of the object.

This is only a problem where exporting data into multiple worksheets - such as dept by dept on different wsheets.

Export to excel with 1 dept works fine.

I have managed to pinpoint the situation where this happens :

Matrix report with page break for the grouping.

I think there might be a fault with the export function.

Anyone like to confirm on this ?

|||

Did you ever find out if this was a bug?

I also have a problem with page breaks in matrix groupings where by I get blank pages when Print Previewing in Excel. However I am using RS 2000 with SP2.

Thanks

Scott

Export To Excel problem

Hi,
When I select the export to excel file format, I get the following in the
excel file:
"Data Regions within table/matrix cells are ignored".
Is this a known bug in RS..
Any info will be appreciated.
Thanks
SureshHi,
The message tells you exactly how the Excel export does with data regions
encapsulated in tables/matrixes. The same is for subreports.
This behavior is by design.
--
| Jan Pieter Posthuma
--
"Suresh" wrote:
> Hi,
> When I select the export to excel file format, I get the following in the
> excel file:
> "Data Regions within table/matrix cells are ignored".
> Is this a known bug in RS..
> Any info will be appreciated.
> Thanks
> Suresh
>

Export to Excel and PDF

About Excel:
I found that the content of the subreports can't export to the Excel. It
said "Subreports within table/matrix cells are ignored." Is this mean that
it is not support or there are some way to do that. the subreports is very
important to me.
About PDF:
There are Chinese content in my report that I can see it in IE but when I
export to PDF, it is all become "' ' '". I check that it is not related
to the version of Acrobat reader. I guess it is something about the
reporting server. Any idea?
Thanks,
CharlesRS is known to be very buggy with exports to both Excel and PDF. Subreports
cause lots of problems as well. Maybe this will be fixed in future versions?
"Charles Lee" <CharlesLee@.discussions.microsoft.com> wrote in message
news:8134A9FE-BEEC-418F-AB29-6BD493617B95@.microsoft.com...
> About Excel:
> I found that the content of the subreports can't export to the Excel. It
> said "Subreports within table/matrix cells are ignored." Is this mean
that
> it is not support or there are some way to do that. the subreports is
very
> important to me.
> About PDF:
> There are Chinese content in my report that I can see it in IE but when I
> export to PDF, it is all become "' ' '". I check that it is not
related
> to the version of Acrobat reader. I guess it is something about the
> reporting server. Any idea?
> Thanks,
> Charles

Export to Excel - Number formatted cells export to Excel as 'General' ?

Anyone know why cells within a matrix that are formatted as numeric export to Excel with a cell format proprty of "General"? Cells within a table however export with an appropriate format.

Thanks

Try converting the data within the cell - something like cdbl() or cint() may work.