Showing posts with label exported. Show all posts
Showing posts with label exported. Show all posts

Monday, March 12, 2012

Reports exported as Excel cannot be read by Mac version of Excel

I searched the list but did not see anything related.

I cannot open a file directly exported from RS in the Excel format. If I open the file in the Windows version and then close it, no need to even save it, I can then open it with the Mac version of Excel. The file size after closing is about 2K less so the RS program is adding something onto the file that the Mac version does not like. It will crash Excel when trying to open.

I cannot export the report as CSV as it will not correctly import into Excel on the Mac.

Is there a work around for this?

Thanks.

--seani have the exact same problem. the excel files open on windows fine, but on the mac they crash excel. if you start excel and then use file-> open, it just hangs.|||

I am also having this problem with users trying to open the xlm file created from RS and using a MAC. Please let us know what is causing this problem and how to correct it.

I will note the spreadsheet did open for the user when they used EXCEL 98, but doesn't work with EXCEL 2001 or EXCEL 2004.

I found this http://support.microsoft.com/kb/918529 . It fixes 2005 SQL server but I see nothing for SQL server 2000.

Thanks.

|||

Hey, I'm also have this problem, I read that the SP2 of SQL Server 2005 fix this problem, but we are having some problems with SSAS and the SP2, so we can't install it.

Doe's any one installed the fix mentioned in the post above? ... Any troubles? ... Thanks!

|||We installed SP2 and low and behold the problem is gone. Our MAC users are very happy.

Thanks.

--sean

Reports exported as Excel cannot be read by Mac version of Excel

I searched the list but did not see anything related.

I cannot open a file directly exported from RS in the Excel format. If I open the file in the Windows version and then close it, no need to even save it, I can then open it with the Mac version of Excel. The file size after closing is about 2K less so the RS program is adding something onto the file that the Mac version does not like. It will crash Excel when trying to open.

I cannot export the report as CSV as it will not correctly import into Excel on the Mac.

Is there a work around for this?

Thanks.

--sean
i have the exact same problem. the excel files open on windows fine, but on the mac they crash excel. if you start excel and then use file-> open, it just hangs.
|||

I am also having this problem with users trying to open the xlm file created from RS and using a MAC. Please let us know what is causing this problem and how to correct it.

I will note the spreadsheet did open for the user when they used EXCEL 98, but doesn't work with EXCEL 2001 or EXCEL 2004.

I found this http://support.microsoft.com/kb/918529 . It fixes 2005 SQL server but I see nothing for SQL server 2000.

Thanks.

|||

Hey, I'm also have this problem, I read that the SP2 of SQL Server 2005 fix this problem, but we are having some problems with SSAS and the SP2, so we can't install it.

Doe's any one installed the fix mentioned in the post above? ... Any troubles? ... Thanks!

|||We installed SP2 and low and behold the problem is gone. Our MAC users are very happy.

Thanks.

--sean

Reports exported as Excel cannot be read by Mac version of Excel

I searched the list but did not see anything related.

I cannot open a file directly exported from RS in the Excel format. If I open the file in the Windows version and then close it, no need to even save it, I can then open it with the Mac version of Excel. The file size after closing is about 2K less so the RS program is adding something onto the file that the Mac version does not like. It will crash Excel when trying to open.

I cannot export the report as CSV as it will not correctly import into Excel on the Mac.

Is there a work around for this?

Thanks.

--seani have the exact same problem. the excel files open on windows fine, but on the mac they crash excel. if you start excel and then use file-> open, it just hangs.|||

I am also having this problem with users trying to open the xlm file created from RS and using a MAC. Please let us know what is causing this problem and how to correct it.

I will note the spreadsheet did open for the user when they used EXCEL 98, but doesn't work with EXCEL 2001 or EXCEL 2004.

I found this http://support.microsoft.com/kb/918529 . It fixes 2005 SQL server but I see nothing for SQL server 2000.

Thanks.

|||

Hey, I'm also have this problem, I read that the SP2 of SQL Server 2005 fix this problem, but we are having some problems with SSAS and the SP2, so we can't install it.

Doe's any one installed the fix mentioned in the post above? ... Any troubles? ... Thanks!

|||We installed SP2 and low and behold the problem is gone. Our MAC users are very happy.

Thanks.

--sean

Reports cannot be exported to csv format

Hi,

We have developed few reports through SQL Server Reporting Service 2005.
All those reports are columnar graphs (BAR charts).
One of the requirement of the project is to have the ability to export the report in different formats.
Since our reports are columnar graphs we are unable to export it to CSV or MHTML format.
Is there are setting in the report, which allows me to display the values, when exported to csv format?
also is there any setting for MHTML format. Some machine open the report in MHTML format and some machine does not.

Any help would be appreciated.

Thanks in advance!

No replies yet:(

The only solution we can think about is to develop those many reports in tabular format. (only for CSV format)

Any other solution available?

Thanks!

|||

CSV output should include Chart Data. http://msdn2.microsoft.com/en-us/library/aa178937(SQL.80).aspx

If you are not getting data, make sure to set DataElementOutput to true. Here is a link to how it works for XML.

http://msdn2.microsoft.com/en-us/library/aa179497(SQL.80).aspx

|||

Even I read the first MSDN link where it mentions charts can be rendered to CSV. Thats why I am a bit sure that there may be some setting in the properties, which will show the data values in CSV.

I also tried the setting mentioned in the CSV Device Information Settings and mentioned Encoding value for CSV as ASCII. Restarted SQL Server.

I also set the DataElementOutput property to "Output" but still I am not able to see the expected result.

Please do let me know if there is any solution available.

Thanks!

|||

Chart Data is supposed to be exported to CSV.

Can you attach the report definition so I can take a look and diagnose the problem?

Thanks!

|||

Hi Denis,

Following is the report definition. Hope to find some solution to my problem.

Thanks in advance!

<?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="ML_OLAP_DS">
<DataSourceReference>ML_OLAP_DS</DataSourceReference>
<rd:DataSourceID>2e071b5e-2d8d-4aab-94ff-edf8900886a2</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<ReportParameters>
<ReportParameter Name="startDate">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>[DATE].[Startdate].[All]</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>Start Date</Prompt>
</ReportParameter>
<ReportParameter Name="endDate">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>[DATE].[Startdate].[All]</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>End Date</Prompt>
</ReportParameter>
<ReportParameter Name="users">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>[USERS].[WINDOWS LOGON NAME].[All]</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>Users</Prompt>
</ReportParameter>
<ReportParameter Name="appName">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>[APPLICATIONS].[APPLICATION NAME].[All]</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>Applications</Prompt>
</ReportParameter>
<ReportParameter Name="chartTitle">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>Ten Most Used Applications</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>Chart Title</Prompt>
</ReportParameter>
</ReportParameters>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Chart Name="MostUsedAppChart">
<Legend>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Position>RightCenter</Position>
</Legend>
<Subtype>Plain</Subtype>
<Title>
<Caption>=RTRIM( Parameters!chartTitle.Value)</Caption>
<Style>
<FontFamily>Arial Black</FontFamily>
</Style>
</Title>
<NoRows>The current report criteria does not satisfy any records.</NoRows>
<Height>6.125in</Height>
<CategoryAxis>
<Axis>
<Title>
<Caption>Applications</Caption>
<Style>
<FontFamily>Arial Black</FontFamily>
</Style>
</Title>
<MajorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Min>0</Min>
<Visible>true</Visible>
</Axis>
</CategoryAxis>
<PointWidth>0</PointWidth>
<Left>0.125in</Left>
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>30</Inclination>
<Shading>Simple</Shading>
<WallThickness>50</WallThickness>
</ThreeDProperties>
<DataSetName>MostUsedAppDS</DataSetName>
<SeriesGroupings>
<SeriesGrouping>
<StaticSeries>
<StaticMember>
<Label>Value1</Label>
</StaticMember>
</StaticSeries>
</SeriesGrouping>
</SeriesGroupings>
<PlotArea>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<BackgroundColor>LightGrey</BackgroundColor>
</Style>
</PlotArea>
<ValueAxis>
<Axis>
<Title>
<Caption>Occurences</Caption>
<Style>
<FontFamily>Arial Black</FontFamily>
</Style>
</Title>
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Min>0</Min>
<Margin>true</Margin>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
<Type>Column</Type>
<Width>4.5375in</Width>
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="chart1_CategoryGroup1">
<GroupExpressions>
<GroupExpression>=Fields!APPLICATION_NAME.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Label />
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<Palette>Default</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Sum(Fields!APPLICATION_INSTANCES_VIEW_Count.Value)</Value>
</DataValue>
</DataValues>
<DataLabel>
<Style>
<FontSize>8pt</FontSize>
</Style>
<Value>=Fields!APPLICATION_INSTANCES_VIEW_Count.Value</Value>
<Visible>true</Visible>
</DataLabel>
<Marker>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
</ChartSeries>
</ChartData>
<Style>
<BackgroundColor>White</BackgroundColor>
</Style>
</Chart>
</ReportItems>
<Height>7.875in</Height>
</Body>
<rd:ReportID>a7bd15d4-cace-4fe8-b2d3-62bc6fc786a8</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="MostUsedAppDS">
<Query>
<rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate>
<CommandText>WITH
MEMBER [DATE].[StartDate].[DateRange] AS
AGGREGATE
( STRTOMEMBER(@.startDate) : STRTOMEMBER(@.endDate), [Measures].CURRENTMEMBER
)
MEMBER [USERS].[WINDOWS LOGON NAME].[UserName] AS
AGGREGATE
( {STRTOSET(@.users)} )
SELECT
NON EMPTY { [Measures].[APPLICATION INSTANCES VIEW Count] } ON COLUMNS,
NON EMPTY TopCount ( Filter ( {[APPLICATIONS].[APPLICATION NAME].[APPLICATION NAME]}, [Measures].[APPLICATION INSTANCES VIEW Count] &gt;= 0 ) , 10, [Measures].[APPLICATION INSTANCES VIEW Count] ) ON ROWS
FROM
(select STRTOSET(@.appName) on 0 from [ApplicationUsage] )
WHERE
(([USERS].[WINDOWS LOGON NAME].[UserName]),[DATE].[StartDate].[DateRange])</CommandText>
<QueryParameters>
<QueryParameter Name="startDate">
<Value>=Parameters!startDate.Value</Value>
</QueryParameter>
<QueryParameter Name="endDate">
<Value>=Parameters!endDate.Value</Value>
</QueryParameter>
<QueryParameter Name="users">
<Value>=Parameters!users.Value</Value>
</QueryParameter>
<QueryParameter Name="appName">
<Value>=Parameters!appName.Value</Value>
</QueryParameter>
<QueryParameter Name="chartTitle">
<Value>=Parameters!chartTitle.Value</Value>
</QueryParameter>
</QueryParameters>
<DataSourceName>ML_OLAP_DS</DataSourceName>
<rd:MdxQuery><QueryDefinition xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/AnalysisServices/QueryDefinition"><CommandType>MDX</CommandType><Type>Query</Type><QuerySpecification xsi:type="MDXQuerySpecification"><Select><Items><Item><ID xsi:type="Level"><DimensionName>APPLICATIONS</DimensionName><HierarchyName>APPLICATION NAME</HierarchyName><HierarchyUniqueName>[APPLICATIONS].[APPLICATION NAME]</HierarchyUniqueName><LevelName>APPLICATION NAME</LevelName><UniqueName>[APPLICATIONS].[APPLICATION NAME].[APPLICATION NAME]</UniqueName></ID><ItemCaption>APPLICATION NAME</ItemCaption></Item><Item><ID xsi:type="Measure"><MeasureName>APPLICATION INSTANCES VIEW Count</MeasureName><UniqueName>[Measures].[APPLICATION INSTANCES VIEW Count]</UniqueName></ID><ItemCaption>APPLICATION INSTANCES VIEW Count</ItemCaption><FormattedValue>true</FormattedValue></Item></Items></Select><From>ApplicationUsage</From><Filter><FilterItems /></Filter><Calculations /><Aggregates /><QueryProperties /></QuerySpecification><Query><Statement>WITH
MEMBER [DATE].[StartDate].[DateRange] AS
AGGREGATE
( STRTOMEMBER(@.startDate) : STRTOMEMBER(@.endDate), [Measures].CURRENTMEMBER
)
MEMBER [USERS].[WINDOWS LOGON NAME].[UserName] AS
AGGREGATE
( {STRTOSET(@.users)} )
SELECT
NON EMPTY { [Measures].[APPLICATION INSTANCES VIEW Count] } ON COLUMNS,
NON EMPTY TopCount ( Filter ( {[APPLICATIONS].[APPLICATION NAME].[APPLICATION NAME]}, [Measures].[APPLICATION INSTANCES VIEW Count] &gt;= 0 ) , 10, [Measures].[APPLICATION INSTANCES VIEW Count] ) ON ROWS
FROM
(select STRTOSET(@.appName) on 0 from [ApplicationUsage] )
WHERE
(([USERS].[WINDOWS LOGON NAME].[UserName]),[DATE].[StartDate].[DateRange])</Statement><ParameterDefinitions><ParameterDefinition><Name>startDate</Name><DefaultValues><DefaultValue>[DATE].[Startdate].[All]</DefaultValue></DefaultValues><Caption>startDate</Caption><HierarchyUniqueName>[DATE].[Startdate]</HierarchyUniqueName><ParameterValuesQuery><Statement>WITH MEMBER [Measures].[ParameterCaption] AS '[DATE].[Startdate].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[DATE].[Startdate].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[DATE].[Startdate].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [DATE].[Startdate].ALLMEMBERS ON ROWS FROM [ApplicationUsage]</Statement><ParameterizedStatement><ReferencedParameters /></ParameterizedStatement></ParameterValuesQuery></ParameterDefinition><ParameterDefinition><Name>endDate</Name><DefaultValues><DefaultValue>[DATE].[Startdate].[All]</DefaultValue></DefaultValues><Caption>endDate</Caption><HierarchyUniqueName>[DATE].[Startdate]</HierarchyUniqueName><ParameterValuesQuery><Statement>WITH MEMBER [Measures].[ParameterCaption] AS '[DATE].[Startdate].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[DATE].[Startdate].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[DATE].[Startdate].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [DATE].[Startdate].ALLMEMBERS ON ROWS FROM [ApplicationUsage]</Statement><ParameterizedStatement><ReferencedParameters /></ParameterizedStatement></ParameterValuesQuery></ParameterDefinition><ParameterDefinition><Name>users</Name><DefaultValues><DefaultValue>[USERS].[WINDOWS LOGON NAME].[All]</DefaultValue></DefaultValues><Caption>users</Caption><HierarchyUniqueName>[USERS].[WINDOWS LOGON NAME]</HierarchyUniqueName><ParameterValuesQuery><Statement>WITH MEMBER [Measures].[ParameterCaption] AS '[USERS].[WINDOWS LOGON NAME].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[USERS].[WINDOWS LOGON NAME].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[USERS].[WINDOWS LOGON NAME].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [USERS].[WINDOWS LOGON NAME].ALLMEMBERS ON ROWS FROM [ApplicationUsage]</Statement><ParameterizedStatement><ReferencedParameters /></ParameterizedStatement></ParameterValuesQuery></ParameterDefinition><ParameterDefinition><Name>appName</Name><DefaultValues><DefaultValue>[APPLICATIONS].[APPLICATION NAME].[All]</DefaultValue></DefaultValues><Caption>appName</Caption><HierarchyUniqueName>[APPLICATIONS].[APPLICATION NAME]</HierarchyUniqueName><ParameterValuesQuery><Statement>WITH MEMBER [Measures].[ParameterCaption] AS '[APPLICATIONS].[APPLICATION NAME].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[APPLICATIONS].[APPLICATION NAME].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[APPLICATIONS].[APPLICATION NAME].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [APPLICATIONS].[APPLICATION NAME].ALLMEMBERS ON ROWS FROM [ApplicationUsage]</Statement><ParameterizedStatement><ReferencedParameters /></ParameterizedStatement></ParameterValuesQuery></ParameterDefinition><ParameterDefinition><Name>chartTitle</Name><DefaultValues><DefaultValue>Ten Most Used Applications</DefaultValue></DefaultValues><Caption>chartTitle</Caption><ParameterValuesQuery><ParameterizedStatement><ReferencedParameters /></ParameterizedStatement></ParameterValuesQuery></ParameterDefinition></ParameterDefinitions></Query></QueryDefinition></rd:MdxQuery>
</Query>
<Fields>
<Field Name="APPLICATION_NAME">
<rd:TypeName>System.String</rd:TypeName>
<DataField>&lt;?xml version="1.0" encoding="utf-8"?&gt;&lt;Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Level" UniqueName="[APPLICATIONS].[APPLICATION NAME].[APPLICATION NAME]" /&gt;</DataField>
</Field>
<Field Name="APPLICATION_INSTANCES_VIEW_Count">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>&lt;?xml version="1.0" encoding="utf-8"?&gt;&lt;Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Measure" UniqueName="[Measures].[APPLICATION INSTANCES VIEW Count]" /&gt;</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>4.75in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>

|||No luck yet?|||

It appears to be an error in Books Online: CSV renderer does not render chart data, while XML renderer does. Sorry about confusion.

One way you can export chart data is by applying XSL transformation to the XML data output of the report:

http://msdn2.microsoft.com/en-us/library/aa178953(SQL.80).aspx

Reports cannot be exported to csv format

Hi,

We have developed few reports through SQL Server Reporting Service 2005.
All those reports are columnar graphs (BAR charts).
One of the requirement of the project is to have the ability to export the report in different formats.
Since our reports are columnar graphs we are unable to export it to CSV or MHTML format.
Is there are setting in the report, which allows me to display the values, when exported to csv format?
also is there any setting for MHTML format. Some machine open the report in MHTML format and some machine does not.

Any help would be appreciated.

Thanks in advance!

No replies yet:(

The only solution we can think about is to develop those many reports in tabular format. (only for CSV format)

Any other solution available?

Thanks!

|||

CSV output should include Chart Data. http://msdn2.microsoft.com/en-us/library/aa178937(SQL.80).aspx

If you are not getting data, make sure to set DataElementOutput to true. Here is a link to how it works for XML.

http://msdn2.microsoft.com/en-us/library/aa179497(SQL.80).aspx

|||

Even I read the first MSDN link where it mentions charts can be rendered to CSV. Thats why I am a bit sure that there may be some setting in the properties, which will show the data values in CSV.

I also tried the setting mentioned in the CSV Device Information Settings and mentioned Encoding value for CSV as ASCII. Restarted SQL Server.

I also set the DataElementOutput property to "Output" but still I am not able to see the expected result.

Please do let me know if there is any solution available.

Thanks!

|||

Chart Data is supposed to be exported to CSV.

Can you attach the report definition so I can take a look and diagnose the problem?

Thanks!

|||

Hi Denis,

Following is the report definition. Hope to find some solution to my problem.

Thanks in advance!

<?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="ML_OLAP_DS">
<DataSourceReference>ML_OLAP_DS</DataSourceReference>
<rd:DataSourceID>2e071b5e-2d8d-4aab-94ff-edf8900886a2</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<ReportParameters>
<ReportParameter Name="startDate">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>[DATE].[Startdate].[All]</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>Start Date</Prompt>
</ReportParameter>
<ReportParameter Name="endDate">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>[DATE].[Startdate].[All]</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>End Date</Prompt>
</ReportParameter>
<ReportParameter Name="users">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>[USERS].[WINDOWS LOGON NAME].[All]</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>Users</Prompt>
</ReportParameter>
<ReportParameter Name="appName">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>[APPLICATIONS].[APPLICATION NAME].[All]</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>Applications</Prompt>
</ReportParameter>
<ReportParameter Name="chartTitle">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>Ten Most Used Applications</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>Chart Title</Prompt>
</ReportParameter>
</ReportParameters>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Chart Name="MostUsedAppChart">
<Legend>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Position>RightCenter</Position>
</Legend>
<Subtype>Plain</Subtype>
<Title>
<Caption>=RTRIM( Parameters!chartTitle.Value)</Caption>
<Style>
<FontFamily>Arial Black</FontFamily>
</Style>
</Title>
<NoRows>The current report criteria does not satisfy any records.</NoRows>
<Height>6.125in</Height>
<CategoryAxis>
<Axis>
<Title>
<Caption>Applications</Caption>
<Style>
<FontFamily>Arial Black</FontFamily>
</Style>
</Title>
<MajorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Min>0</Min>
<Visible>true</Visible>
</Axis>
</CategoryAxis>
<PointWidth>0</PointWidth>
<Left>0.125in</Left>
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>30</Inclination>
<Shading>Simple</Shading>
<WallThickness>50</WallThickness>
</ThreeDProperties>
<DataSetName>MostUsedAppDS</DataSetName>
<SeriesGroupings>
<SeriesGrouping>
<StaticSeries>
<StaticMember>
<Label>Value1</Label>
</StaticMember>
</StaticSeries>
</SeriesGrouping>
</SeriesGroupings>
<PlotArea>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<BackgroundColor>LightGrey</BackgroundColor>
</Style>
</PlotArea>
<ValueAxis>
<Axis>
<Title>
<Caption>Occurences</Caption>
<Style>
<FontFamily>Arial Black</FontFamily>
</Style>
</Title>
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Min>0</Min>
<Margin>true</Margin>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
<Type>Column</Type>
<Width>4.5375in</Width>
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="chart1_CategoryGroup1">
<GroupExpressions>
<GroupExpression>=Fields!APPLICATION_NAME.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Label />
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<Palette>Default</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Sum(Fields!APPLICATION_INSTANCES_VIEW_Count.Value)</Value>
</DataValue>
</DataValues>
<DataLabel>
<Style>
<FontSize>8pt</FontSize>
</Style>
<Value>=Fields!APPLICATION_INSTANCES_VIEW_Count.Value</Value>
<Visible>true</Visible>
</DataLabel>
<Marker>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
</ChartSeries>
</ChartData>
<Style>
<BackgroundColor>White</BackgroundColor>
</Style>
</Chart>
</ReportItems>
<Height>7.875in</Height>
</Body>
<rd:ReportID>a7bd15d4-cace-4fe8-b2d3-62bc6fc786a8</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="MostUsedAppDS">
<Query>
<rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate>
<CommandText>WITH
MEMBER [DATE].[StartDate].[DateRange] AS
AGGREGATE
( STRTOMEMBER(@.startDate) : STRTOMEMBER(@.endDate), [Measures].CURRENTMEMBER
)
MEMBER [USERS].[WINDOWS LOGON NAME].[UserName] AS
AGGREGATE
( {STRTOSET(@.users)} )
SELECT
NON EMPTY { [Measures].[APPLICATION INSTANCES VIEW Count] } ON COLUMNS,
NON EMPTY TopCount ( Filter ( {[APPLICATIONS].[APPLICATION NAME].[APPLICATION NAME]}, [Measures].[APPLICATION INSTANCES VIEW Count] &gt;= 0 ) , 10, [Measures].[APPLICATION INSTANCES VIEW Count] ) ON ROWS
FROM
(select STRTOSET(@.appName) on 0 from [ApplicationUsage] )
WHERE
(([USERS].[WINDOWS LOGON NAME].[UserName]),[DATE].[StartDate].[DateRange])</CommandText>
<QueryParameters>
<QueryParameter Name="startDate">
<Value>=Parameters!startDate.Value</Value>
</QueryParameter>
<QueryParameter Name="endDate">
<Value>=Parameters!endDate.Value</Value>
</QueryParameter>
<QueryParameter Name="users">
<Value>=Parameters!users.Value</Value>
</QueryParameter>
<QueryParameter Name="appName">
<Value>=Parameters!appName.Value</Value>
</QueryParameter>
<QueryParameter Name="chartTitle">
<Value>=Parameters!chartTitle.Value</Value>
</QueryParameter>
</QueryParameters>
<DataSourceName>ML_OLAP_DS</DataSourceName>
<rd:MdxQuery><QueryDefinition xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/AnalysisServices/QueryDefinition"><CommandType>MDX</CommandType><Type>Query</Type><QuerySpecification xsi:type="MDXQuerySpecification"><Select><Items><Item><ID xsi:type="Level"><DimensionName>APPLICATIONS</DimensionName><HierarchyName>APPLICATION NAME</HierarchyName><HierarchyUniqueName>[APPLICATIONS].[APPLICATION NAME]</HierarchyUniqueName><LevelName>APPLICATION NAME</LevelName><UniqueName>[APPLICATIONS].[APPLICATION NAME].[APPLICATION NAME]</UniqueName></ID><ItemCaption>APPLICATION NAME</ItemCaption></Item><Item><ID xsi:type="Measure"><MeasureName>APPLICATION INSTANCES VIEW Count</MeasureName><UniqueName>[Measures].[APPLICATION INSTANCES VIEW Count]</UniqueName></ID><ItemCaption>APPLICATION INSTANCES VIEW Count</ItemCaption><FormattedValue>true</FormattedValue></Item></Items></Select><From>ApplicationUsage</From><Filter><FilterItems /></Filter><Calculations /><Aggregates /><QueryProperties /></QuerySpecification><Query><Statement>WITH
MEMBER [DATE].[StartDate].[DateRange] AS
AGGREGATE
( STRTOMEMBER(@.startDate) : STRTOMEMBER(@.endDate), [Measures].CURRENTMEMBER
)
MEMBER [USERS].[WINDOWS LOGON NAME].[UserName] AS
AGGREGATE
( {STRTOSET(@.users)} )
SELECT
NON EMPTY { [Measures].[APPLICATION INSTANCES VIEW Count] } ON COLUMNS,
NON EMPTY TopCount ( Filter ( {[APPLICATIONS].[APPLICATION NAME].[APPLICATION NAME]}, [Measures].[APPLICATION INSTANCES VIEW Count] &gt;= 0 ) , 10, [Measures].[APPLICATION INSTANCES VIEW Count] ) ON ROWS
FROM
(select STRTOSET(@.appName) on 0 from [ApplicationUsage] )
WHERE
(([USERS].[WINDOWS LOGON NAME].[UserName]),[DATE].[StartDate].[DateRange])</Statement><ParameterDefinitions><ParameterDefinition><Name>startDate</Name><DefaultValues><DefaultValue>[DATE].[Startdate].[All]</DefaultValue></DefaultValues><Caption>startDate</Caption><HierarchyUniqueName>[DATE].[Startdate]</HierarchyUniqueName><ParameterValuesQuery><Statement>WITH MEMBER [Measures].[ParameterCaption] AS '[DATE].[Startdate].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[DATE].[Startdate].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[DATE].[Startdate].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [DATE].[Startdate].ALLMEMBERS ON ROWS FROM [ApplicationUsage]</Statement><ParameterizedStatement><ReferencedParameters /></ParameterizedStatement></ParameterValuesQuery></ParameterDefinition><ParameterDefinition><Name>endDate</Name><DefaultValues><DefaultValue>[DATE].[Startdate].[All]</DefaultValue></DefaultValues><Caption>endDate</Caption><HierarchyUniqueName>[DATE].[Startdate]</HierarchyUniqueName><ParameterValuesQuery><Statement>WITH MEMBER [Measures].[ParameterCaption] AS '[DATE].[Startdate].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[DATE].[Startdate].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[DATE].[Startdate].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [DATE].[Startdate].ALLMEMBERS ON ROWS FROM [ApplicationUsage]</Statement><ParameterizedStatement><ReferencedParameters /></ParameterizedStatement></ParameterValuesQuery></ParameterDefinition><ParameterDefinition><Name>users</Name><DefaultValues><DefaultValue>[USERS].[WINDOWS LOGON NAME].[All]</DefaultValue></DefaultValues><Caption>users</Caption><HierarchyUniqueName>[USERS].[WINDOWS LOGON NAME]</HierarchyUniqueName><ParameterValuesQuery><Statement>WITH MEMBER [Measures].[ParameterCaption] AS '[USERS].[WINDOWS LOGON NAME].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[USERS].[WINDOWS LOGON NAME].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[USERS].[WINDOWS LOGON NAME].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [USERS].[WINDOWS LOGON NAME].ALLMEMBERS ON ROWS FROM [ApplicationUsage]</Statement><ParameterizedStatement><ReferencedParameters /></ParameterizedStatement></ParameterValuesQuery></ParameterDefinition><ParameterDefinition><Name>appName</Name><DefaultValues><DefaultValue>[APPLICATIONS].[APPLICATION NAME].[All]</DefaultValue></DefaultValues><Caption>appName</Caption><HierarchyUniqueName>[APPLICATIONS].[APPLICATION NAME]</HierarchyUniqueName><ParameterValuesQuery><Statement>WITH MEMBER [Measures].[ParameterCaption] AS '[APPLICATIONS].[APPLICATION NAME].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[APPLICATIONS].[APPLICATION NAME].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[APPLICATIONS].[APPLICATION NAME].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [APPLICATIONS].[APPLICATION NAME].ALLMEMBERS ON ROWS FROM [ApplicationUsage]</Statement><ParameterizedStatement><ReferencedParameters /></ParameterizedStatement></ParameterValuesQuery></ParameterDefinition><ParameterDefinition><Name>chartTitle</Name><DefaultValues><DefaultValue>Ten Most Used Applications</DefaultValue></DefaultValues><Caption>chartTitle</Caption><ParameterValuesQuery><ParameterizedStatement><ReferencedParameters /></ParameterizedStatement></ParameterValuesQuery></ParameterDefinition></ParameterDefinitions></Query></QueryDefinition></rd:MdxQuery>
</Query>
<Fields>
<Field Name="APPLICATION_NAME">
<rd:TypeName>System.String</rd:TypeName>
<DataField>&lt;?xml version="1.0" encoding="utf-8"?&gt;&lt;Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Level" UniqueName="[APPLICATIONS].[APPLICATION NAME].[APPLICATION NAME]" /&gt;</DataField>
</Field>
<Field Name="APPLICATION_INSTANCES_VIEW_Count">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>&lt;?xml version="1.0" encoding="utf-8"?&gt;&lt;Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Measure" UniqueName="[Measures].[APPLICATION INSTANCES VIEW Count]" /&gt;</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>4.75in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>

|||No luck yet?|||

It appears to be an error in Books Online: CSV renderer does not render chart data, while XML renderer does. Sorry about confusion.

One way you can export chart data is by applying XSL transformation to the XML data output of the report:

http://msdn2.microsoft.com/en-us/library/aa178953(SQL.80).aspx

Monday, February 20, 2012

Reporting Services/DTS issues

Hello,
I have a Reporting Services report that gets exported to Excel. A DTS
package (running on SQL-Server 2000) then attemps to import that Excel
file into an existing table. Occasionally, I receive the following
error:
Error Source: Microsoft JET Database Engine
Error Description: External table is not in the expected format.
There doesn't appear to be anything unusual about the spreadsheet when
this error is thrown (it contains good data, and is able to be opened
in Excel with no errors). The strange thing is that if I open the
spreadsheet and re-save it, the DTS package runs fine.
This error occurs completely randomly (at least it seems like it does).
Has anyone encountered anything similar?
Thanks!This seems like a roundabout way of doing things. Why don't you code an SP
that loads the data. then run the report off the loaded data. It should be
significantly more reliable and put a significantly smaller load on the
server if it is a large amount of data.
"bmcelhany@.gmail.com" wrote:
> Hello,
> I have a Reporting Services report that gets exported to Excel. A DTS
> package (running on SQL-Server 2000) then attemps to import that Excel
> file into an existing table. Occasionally, I receive the following
> error:
> Error Source: Microsoft JET Database Engine
> Error Description: External table is not in the expected format.
> There doesn't appear to be anything unusual about the spreadsheet when
> this error is thrown (it contains good data, and is able to be opened
> in Excel with no errors). The strange thing is that if I open the
> spreadsheet and re-save it, the DTS package runs fine.
> This error occurs completely randomly (at least it seems like it does).
> Has anyone encountered anything similar?
> Thanks!
>|||Actually, the report isn't run FROM the spreadsheet. The report pulls data
from a SQL-Server table (via a stored procedure) and then EXPORTS that report
to Excel. That spreadsheet is then e-mailed to one of our clients who uses a
DTS package to import the data into their local database...that's the point
in the process that's throwing the error. Sorry...should have been a little
more clear.
"johnE" wrote:
> This seems like a roundabout way of doing things. Why don't you code an SP
> that loads the data. then run the report off the loaded data. It should be
> significantly more reliable and put a significantly smaller load on the
> server if it is a large amount of data.
> "bmcelhany@.gmail.com" wrote:
> > Hello,
> >
> > I have a Reporting Services report that gets exported to Excel. A DTS
> > package (running on SQL-Server 2000) then attemps to import that Excel
> > file into an existing table. Occasionally, I receive the following
> > error:
> >
> > Error Source: Microsoft JET Database Engine
> > Error Description: External table is not in the expected format.
> >
> > There doesn't appear to be anything unusual about the spreadsheet when
> > this error is thrown (it contains good data, and is able to be opened
> > in Excel with no errors). The strange thing is that if I open the
> > spreadsheet and re-save it, the DTS package runs fine.
> >
> > This error occurs completely randomly (at least it seems like it does).
> > Has anyone encountered anything similar?
> >
> > Thanks!
> >
> >|||Why not use the DTS package directly to output the resultset to Excel
rather than running it through Reporting Services? I know, because
the output from RS is more aesthetically pleasing. So, the solution is
to do it both ways, the RS Excel file for humans to look at and the
DTS Excel file for the client's SQL Server database to import.
"bmcelhany" <bmcelhany@.discussions.microsoft.com> wrote in message
news:6402C5D3-0038-4172-9E28-C99943850EC8@.microsoft.com...
> Actually, the report isn't run FROM the spreadsheet. The report pulls data
> from a SQL-Server table (via a stored procedure) and then EXPORTS that report
> to Excel. That spreadsheet is then e-mailed to one of our clients who uses a
> DTS package to import the data into their local database...that's the point
> in the process that's throwing the error. Sorry...should have been a little
> more clear.
> "johnE" wrote:
>> This seems like a roundabout way of doing things. Why don't you code an SP
>> that loads the data. then run the report off the loaded data. It should be
>> significantly more reliable and put a significantly smaller load on the
>> server if it is a large amount of data.
>> "bmcelhany@.gmail.com" wrote:
>> > Hello,
>> >
>> > I have a Reporting Services report that gets exported to Excel. A DTS
>> > package (running on SQL-Server 2000) then attemps to import that Excel
>> > file into an existing table. Occasionally, I receive the following
>> > error:
>> >
>> > Error Source: Microsoft JET Database Engine
>> > Error Description: External table is not in the expected format.
>> >
>> > There doesn't appear to be anything unusual about the spreadsheet when
>> > this error is thrown (it contains good data, and is able to be opened
>> > in Excel with no errors). The strange thing is that if I open the
>> > spreadsheet and re-save it, the DTS package runs fine.
>> >
>> > This error occurs completely randomly (at least it seems like it does).
>> > Has anyone encountered anything similar?
>> >
>> > Thanks!
>> >
>> >|||As a last resort, that would work, however there should be no reason we can't
do it all from RS. In fact, the client's DTS import works fine about 75% of
the time...it's that last 25% that's got me. I'd rather not have to maintain
two versions (one from RS and one from SQL) of the same report.
"GeoSynch" wrote:
> Why not use the DTS package directly to output the resultset to Excel
> rather than running it through Reporting Services? I know, because
> the output from RS is more aesthetically pleasing. So, the solution is
> to do it both ways, the RS Excel file for humans to look at and the
> DTS Excel file for the client's SQL Server database to import.
> "bmcelhany" <bmcelhany@.discussions.microsoft.com> wrote in message
> news:6402C5D3-0038-4172-9E28-C99943850EC8@.microsoft.com...
> > Actually, the report isn't run FROM the spreadsheet. The report pulls data
> > from a SQL-Server table (via a stored procedure) and then EXPORTS that report
> > to Excel. That spreadsheet is then e-mailed to one of our clients who uses a
> > DTS package to import the data into their local database...that's the point
> > in the process that's throwing the error. Sorry...should have been a little
> > more clear.
> >
> > "johnE" wrote:
> >
> >> This seems like a roundabout way of doing things. Why don't you code an SP
> >> that loads the data. then run the report off the loaded data. It should be
> >> significantly more reliable and put a significantly smaller load on the
> >> server if it is a large amount of data.
> >>
> >> "bmcelhany@.gmail.com" wrote:
> >>
> >> > Hello,
> >> >
> >> > I have a Reporting Services report that gets exported to Excel. A DTS
> >> > package (running on SQL-Server 2000) then attemps to import that Excel
> >> > file into an existing table. Occasionally, I receive the following
> >> > error:
> >> >
> >> > Error Source: Microsoft JET Database Engine
> >> > Error Description: External table is not in the expected format.
> >> >
> >> > There doesn't appear to be anything unusual about the spreadsheet when
> >> > this error is thrown (it contains good data, and is able to be opened
> >> > in Excel with no errors). The strange thing is that if I open the
> >> > spreadsheet and re-save it, the DTS package runs fine.
> >> >
> >> > This error occurs completely randomly (at least it seems like it does).
> >> > Has anyone encountered anything similar?
> >> >
> >> > Thanks!
> >> >
> >> >
>
>|||I think I know what the problem is. Excel is more forgiving of what's in a field
than either Access or SQL Server are. Null values may be showing up in
Excel as blank strings, that is the only thing in a blank cell in Excel will be
an apostrophe, which a database import will not accept into a date or value
field.
So, here's what to do. The client's DTS package should be writing out a log
file - if it isn't, talk them through the process of setting it up. When the DTS
job
fails, have them send you the log file and it should show at which record the
job
failed. Compare that to the row number in the RS-generated Excel file to confirm
what went wrong. The solution will be for the client's DTS package to be
modified
to do transforms on these suspect fields, which will probably fall on your
shoulders.
GeoSynch
"bmcelhany" <bmcelhany@.discussions.microsoft.com> wrote in message
news:D507373F-7B2E-4D37-B6AE-52FEF3F1FB58@.microsoft.com...
> As a last resort, that would work, however there should be no reason we can't
> do it all from RS. In fact, the client's DTS import works fine about 75% of
> the time...it's that last 25% that's got me. I'd rather not have to maintain
> two versions (one from RS and one from SQL) of the same report.
> "GeoSynch" wrote:
>> Why not use the DTS package directly to output the resultset to Excel
>> rather than running it through Reporting Services? I know, because
>> the output from RS is more aesthetically pleasing. So, the solution is
>> to do it both ways, the RS Excel file for humans to look at and the
>> DTS Excel file for the client's SQL Server database to import.
>> "bmcelhany" <bmcelhany@.discussions.microsoft.com> wrote in message
>> news:6402C5D3-0038-4172-9E28-C99943850EC8@.microsoft.com...
>> > Actually, the report isn't run FROM the spreadsheet. The report pulls data
>> > from a SQL-Server table (via a stored procedure) and then EXPORTS that
>> > report
>> > to Excel. That spreadsheet is then e-mailed to one of our clients who uses
>> > a
>> > DTS package to import the data into their local database...that's the point
>> > in the process that's throwing the error. Sorry...should have been a little
>> > more clear.
>> >
>> > "johnE" wrote:
>> >
>> >> This seems like a roundabout way of doing things. Why don't you code an
>> >> SP
>> >> that loads the data. then run the report off the loaded data. It should
>> >> be
>> >> significantly more reliable and put a significantly smaller load on the
>> >> server if it is a large amount of data.
>> >>
>> >> "bmcelhany@.gmail.com" wrote:
>> >>
>> >> > Hello,
>> >> >
>> >> > I have a Reporting Services report that gets exported to Excel. A DTS
>> >> > package (running on SQL-Server 2000) then attemps to import that Excel
>> >> > file into an existing table. Occasionally, I receive the following
>> >> > error:
>> >> >
>> >> > Error Source: Microsoft JET Database Engine
>> >> > Error Description: External table is not in the expected format.
>> >> >
>> >> > There doesn't appear to be anything unusual about the spreadsheet when
>> >> > this error is thrown (it contains good data, and is able to be opened
>> >> > in Excel with no errors). The strange thing is that if I open the
>> >> > spreadsheet and re-save it, the DTS package runs fine.
>> >> >
>> >> > This error occurs completely randomly (at least it seems like it does).
>> >> > Has anyone encountered anything similar?
>> >> >
>> >> > Thanks!
>> >> >
>> >> >
>>|||OK, we're getting closer. I believe I've identified the column that's causing
the problem. In the database, the field is set up as a VARCHAR(4000). When
the value in that field is <= 255 characters, the DTS package runs fine. As
soon as the value goes beyond the 255 character mark the package fails.
So, I guess at this point I've got two questions: 1) What is it that opening
and re-saving the spreadsheet does that "fixes" an import that would
otherwise fail and 2) Short of truncating the data for that field to 255
characters, what other options might I have?
"GeoSynch" wrote:
> I think I know what the problem is. Excel is more forgiving of what's in a field
> than either Access or SQL Server are. Null values may be showing up in
> Excel as blank strings, that is the only thing in a blank cell in Excel will be
> an apostrophe, which a database import will not accept into a date or value
> field.
> So, here's what to do. The client's DTS package should be writing out a log
> file - if it isn't, talk them through the process of setting it up. When the DTS
> job
> fails, have them send you the log file and it should show at which record the
> job
> failed. Compare that to the row number in the RS-generated Excel file to confirm
> what went wrong. The solution will be for the client's DTS package to be
> modified
> to do transforms on these suspect fields, which will probably fall on your
> shoulders.
>
> GeoSynch
>
> "bmcelhany" <bmcelhany@.discussions.microsoft.com> wrote in message
> news:D507373F-7B2E-4D37-B6AE-52FEF3F1FB58@.microsoft.com...
> > As a last resort, that would work, however there should be no reason we can't
> > do it all from RS. In fact, the client's DTS import works fine about 75% of
> > the time...it's that last 25% that's got me. I'd rather not have to maintain
> > two versions (one from RS and one from SQL) of the same report.
> >
> > "GeoSynch" wrote:
> >
> >> Why not use the DTS package directly to output the resultset to Excel
> >> rather than running it through Reporting Services? I know, because
> >> the output from RS is more aesthetically pleasing. So, the solution is
> >> to do it both ways, the RS Excel file for humans to look at and the
> >> DTS Excel file for the client's SQL Server database to import.
> >>
> >> "bmcelhany" <bmcelhany@.discussions.microsoft.com> wrote in message
> >> news:6402C5D3-0038-4172-9E28-C99943850EC8@.microsoft.com...
> >> > Actually, the report isn't run FROM the spreadsheet. The report pulls data
> >> > from a SQL-Server table (via a stored procedure) and then EXPORTS that
> >> > report
> >> > to Excel. That spreadsheet is then e-mailed to one of our clients who uses
> >> > a
> >> > DTS package to import the data into their local database...that's the point
> >> > in the process that's throwing the error. Sorry...should have been a little
> >> > more clear.
> >> >
> >> > "johnE" wrote:
> >> >
> >> >> This seems like a roundabout way of doing things. Why don't you code an
> >> >> SP
> >> >> that loads the data. then run the report off the loaded data. It should
> >> >> be
> >> >> significantly more reliable and put a significantly smaller load on the
> >> >> server if it is a large amount of data.
> >> >>
> >> >> "bmcelhany@.gmail.com" wrote:
> >> >>
> >> >> > Hello,
> >> >> >
> >> >> > I have a Reporting Services report that gets exported to Excel. A DTS
> >> >> > package (running on SQL-Server 2000) then attemps to import that Excel
> >> >> > file into an existing table. Occasionally, I receive the following
> >> >> > error:
> >> >> >
> >> >> > Error Source: Microsoft JET Database Engine
> >> >> > Error Description: External table is not in the expected format.
> >> >> >
> >> >> > There doesn't appear to be anything unusual about the spreadsheet when
> >> >> > this error is thrown (it contains good data, and is able to be opened
> >> >> > in Excel with no errors). The strange thing is that if I open the
> >> >> > spreadsheet and re-save it, the DTS package runs fine.
> >> >> >
> >> >> > This error occurs completely randomly (at least it seems like it does).
> >> >> > Has anyone encountered anything similar?
> >> >> >
> >> >> > Thanks!
> >> >> >
> >> >> >
> >>
> >>
> >>
>
>|||Excel limits the number of characters in a cell to 256, including the
leftmost alignment character of ' or " or ^. Nonetheless, a foreign
app can create an Excel file with the number of character exceeding
the 256 threshold. If you open the file and edited the offensive cell,
Excel will truncate it to the leftmost 256 characters. I suppose
explicitly saving the file from within Excel truncates all offensive cells.
You'll can modify your SRS field to Left(DataSet!FieldName.Value, 255)
to make sure all goes smoothly, or add more fields to the SRS
report for each incremental chunk of 255 characters. The client's
import process could then concatenate these fields into one.
GeoSynch
"bmcelhany" <bmcelhany@.discussions.microsoft.com> wrote in message
news:D19E073D-989B-4517-8695-68EC82C73053@.microsoft.com...
> OK, we're getting closer. I believe I've identified the column that's causing
> the problem. In the database, the field is set up as a VARCHAR(4000). When
> the value in that field is <= 255 characters, the DTS package runs fine. As
> soon as the value goes beyond the 255 character mark the package fails.
> So, I guess at this point I've got two questions: 1) What is it that opening
> and re-saving the spreadsheet does that "fixes" an import that would
> otherwise fail and 2) Short of truncating the data for that field to 255
> characters, what other options might I have?
> "GeoSynch" wrote:
>> I think I know what the problem is. Excel is more forgiving of what's in a
>> field
>> than either Access or SQL Server are. Null values may be showing up in
>> Excel as blank strings, that is the only thing in a blank cell in Excel will
>> be
>> an apostrophe, which a database import will not accept into a date or value
>> field.
>> So, here's what to do. The client's DTS package should be writing out a log
>> file - if it isn't, talk them through the process of setting it up. When the
>> DTS
>> job
>> fails, have them send you the log file and it should show at which record the
>> job
>> failed. Compare that to the row number in the RS-generated Excel file to
>> confirm
>> what went wrong. The solution will be for the client's DTS package to be
>> modified
>> to do transforms on these suspect fields, which will probably fall on your
>> shoulders.
>>
>> GeoSynch
>>
>> "bmcelhany" <bmcelhany@.discussions.microsoft.com> wrote in message
>> news:D507373F-7B2E-4D37-B6AE-52FEF3F1FB58@.microsoft.com...
>> > As a last resort, that would work, however there should be no reason we
>> > can't
>> > do it all from RS. In fact, the client's DTS import works fine about 75% of
>> > the time...it's that last 25% that's got me. I'd rather not have to
>> > maintain
>> > two versions (one from RS and one from SQL) of the same report.
>> >
>> > "GeoSynch" wrote:
>> >
>> >> Why not use the DTS package directly to output the resultset to Excel
>> >> rather than running it through Reporting Services? I know, because
>> >> the output from RS is more aesthetically pleasing. So, the solution is
>> >> to do it both ways, the RS Excel file for humans to look at and the
>> >> DTS Excel file for the client's SQL Server database to import.
>> >>
>> >> "bmcelhany" <bmcelhany@.discussions.microsoft.com> wrote in message
>> >> news:6402C5D3-0038-4172-9E28-C99943850EC8@.microsoft.com...
>> >> > Actually, the report isn't run FROM the spreadsheet. The report pulls
>> >> > data
>> >> > from a SQL-Server table (via a stored procedure) and then EXPORTS that
>> >> > report
>> >> > to Excel. That spreadsheet is then e-mailed to one of our clients who
>> >> > uses
>> >> > a
>> >> > DTS package to import the data into their local database...that's the
>> >> > point
>> >> > in the process that's throwing the error. Sorry...should have been a
>> >> > little
>> >> > more clear.
>> >> >
>> >> > "johnE" wrote:
>> >> >
>> >> >> This seems like a roundabout way of doing things. Why don't you code
>> >> >> an
>> >> >> SP
>> >> >> that loads the data. then run the report off the loaded data. It
>> >> >> should
>> >> >> be
>> >> >> significantly more reliable and put a significantly smaller load on the
>> >> >> server if it is a large amount of data.
>> >> >>
>> >> >> "bmcelhany@.gmail.com" wrote:
>> >> >>
>> >> >> > Hello,
>> >> >> >
>> >> >> > I have a Reporting Services report that gets exported to Excel. A DTS
>> >> >> > package (running on SQL-Server 2000) then attemps to import that
>> >> >> > Excel
>> >> >> > file into an existing table. Occasionally, I receive the following
>> >> >> > error:
>> >> >> >
>> >> >> > Error Source: Microsoft JET Database Engine
>> >> >> > Error Description: External table is not in the expected format.
>> >> >> >
>> >> >> > There doesn't appear to be anything unusual about the spreadsheet
>> >> >> > when
>> >> >> > this error is thrown (it contains good data, and is able to be opened
>> >> >> > in Excel with no errors). The strange thing is that if I open the
>> >> >> > spreadsheet and re-save it, the DTS package runs fine.
>> >> >> >
>> >> >> > This error occurs completely randomly (at least it seems like it
>> >> >> > does).
>> >> >> > Has anyone encountered anything similar?
>> >> >> >
>> >> >> > Thanks!
>> >> >> >
>> >> >> >
>> >>
>> >>
>> >>
>>|||OK, I'll buy that. Thanks a lot for all of your help with this!
"GeoSynch" wrote:
> Excel limits the number of characters in a cell to 256, including the
> leftmost alignment character of ' or " or ^. Nonetheless, a foreign
> app can create an Excel file with the number of character exceeding
> the 256 threshold. If you open the file and edited the offensive cell,
> Excel will truncate it to the leftmost 256 characters. I suppose
> explicitly saving the file from within Excel truncates all offensive cells.
> You'll can modify your SRS field to Left(DataSet!FieldName.Value, 255)
> to make sure all goes smoothly, or add more fields to the SRS
> report for each incremental chunk of 255 characters. The client's
> import process could then concatenate these fields into one.
>
> GeoSynch
>
> "bmcelhany" <bmcelhany@.discussions.microsoft.com> wrote in message
> news:D19E073D-989B-4517-8695-68EC82C73053@.microsoft.com...
> > OK, we're getting closer. I believe I've identified the column that's causing
> > the problem. In the database, the field is set up as a VARCHAR(4000). When
> > the value in that field is <= 255 characters, the DTS package runs fine. As
> > soon as the value goes beyond the 255 character mark the package fails.
> >
> > So, I guess at this point I've got two questions: 1) What is it that opening
> > and re-saving the spreadsheet does that "fixes" an import that would
> > otherwise fail and 2) Short of truncating the data for that field to 255
> > characters, what other options might I have?
> >
> > "GeoSynch" wrote:
> >
> >> I think I know what the problem is. Excel is more forgiving of what's in a
> >> field
> >> than either Access or SQL Server are. Null values may be showing up in
> >> Excel as blank strings, that is the only thing in a blank cell in Excel will
> >> be
> >> an apostrophe, which a database import will not accept into a date or value
> >> field.
> >> So, here's what to do. The client's DTS package should be writing out a log
> >> file - if it isn't, talk them through the process of setting it up. When the
> >> DTS
> >> job
> >> fails, have them send you the log file and it should show at which record the
> >> job
> >> failed. Compare that to the row number in the RS-generated Excel file to
> >> confirm
> >> what went wrong. The solution will be for the client's DTS package to be
> >> modified
> >> to do transforms on these suspect fields, which will probably fall on your
> >> shoulders.
> >>
> >>
> >> GeoSynch
> >>
> >>
> >> "bmcelhany" <bmcelhany@.discussions.microsoft.com> wrote in message
> >> news:D507373F-7B2E-4D37-B6AE-52FEF3F1FB58@.microsoft.com...
> >> > As a last resort, that would work, however there should be no reason we
> >> > can't
> >> > do it all from RS. In fact, the client's DTS import works fine about 75% of
> >> > the time...it's that last 25% that's got me. I'd rather not have to
> >> > maintain
> >> > two versions (one from RS and one from SQL) of the same report.
> >> >
> >> > "GeoSynch" wrote:
> >> >
> >> >> Why not use the DTS package directly to output the resultset to Excel
> >> >> rather than running it through Reporting Services? I know, because
> >> >> the output from RS is more aesthetically pleasing. So, the solution is
> >> >> to do it both ways, the RS Excel file for humans to look at and the
> >> >> DTS Excel file for the client's SQL Server database to import.
> >> >>
> >> >> "bmcelhany" <bmcelhany@.discussions.microsoft.com> wrote in message
> >> >> news:6402C5D3-0038-4172-9E28-C99943850EC8@.microsoft.com...
> >> >> > Actually, the report isn't run FROM the spreadsheet. The report pulls
> >> >> > data
> >> >> > from a SQL-Server table (via a stored procedure) and then EXPORTS that
> >> >> > report
> >> >> > to Excel. That spreadsheet is then e-mailed to one of our clients who
> >> >> > uses
> >> >> > a
> >> >> > DTS package to import the data into their local database...that's the
> >> >> > point
> >> >> > in the process that's throwing the error. Sorry...should have been a
> >> >> > little
> >> >> > more clear.
> >> >> >
> >> >> > "johnE" wrote:
> >> >> >
> >> >> >> This seems like a roundabout way of doing things. Why don't you code
> >> >> >> an
> >> >> >> SP
> >> >> >> that loads the data. then run the report off the loaded data. It
> >> >> >> should
> >> >> >> be
> >> >> >> significantly more reliable and put a significantly smaller load on the
> >> >> >> server if it is a large amount of data.
> >> >> >>
> >> >> >> "bmcelhany@.gmail.com" wrote:
> >> >> >>
> >> >> >> > Hello,
> >> >> >> >
> >> >> >> > I have a Reporting Services report that gets exported to Excel. A DTS
> >> >> >> > package (running on SQL-Server 2000) then attemps to import that
> >> >> >> > Excel
> >> >> >> > file into an existing table. Occasionally, I receive the following
> >> >> >> > error:
> >> >> >> >
> >> >> >> > Error Source: Microsoft JET Database Engine
> >> >> >> > Error Description: External table is not in the expected format.
> >> >> >> >
> >> >> >> > There doesn't appear to be anything unusual about the spreadsheet
> >> >> >> > when
> >> >> >> > this error is thrown (it contains good data, and is able to be opened
> >> >> >> > in Excel with no errors). The strange thing is that if I open the
> >> >> >> > spreadsheet and re-save it, the DTS package runs fine.
> >> >> >> >
> >> >> >> > This error occurs completely randomly (at least it seems like it
> >> >> >> > does).
> >> >> >> > Has anyone encountered anything similar?
> >> >> >> >
> >> >> >> > Thanks!
> >> >> >> >
> >> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>