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] >= 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] >= 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><?xml version="1.0" encoding="utf-8"?><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]" /></DataField>
</Field>
<Field Name="APPLICATION_INSTANCES_VIEW_Count">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField><?xml version="1.0" encoding="utf-8"?><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]" /></DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>4.75in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>
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
No comments:
Post a Comment