I'm using the reporting services web service to export reports to csv files.
When I export to a csv, a quote mark is appended at the beginning and end of
each line. I'm using the default device info settings. This is not the
Qualifier, because I tried changing that from ["] to other characters in the
devinfo settings, and the ["] is still inserted.
How can I prevent the ["] from being inserted? It causes problems when I
open the file in Excel, because Excel thinks it means the whole row is one
field, and it gets truncated after 256 characters.
Thanks!
BillTwo different solutions depending on RS 2000 or RS 2005. Note, either you
need to be concerned with a merged cells problem when exporting CSV.
Solution 1:
Depending on how you design your reports you can do the following to export
to Excel. Or, what I do sometimes is make a copy of the report and clean it
up for data export and then hide it in list view. If you export from Report
Manager it puts CSV data in unicode which Excel puts all in one column. If
you export in ASCII then Excel does just as you want. To prevent a problem
with cells (Excel will object to sorting the data) you need to remove any
textboxes you have (for instance with a title, showing the parameters run
etc) and instead add additional header rows, merge the cells and put your
text in there instead. I add a link at the top of the report that says
Export Data. With RS 2005 you will be able to configure it to use ASCII
instead of Unicode.
Here is an example of a Jump to URL link I use. This causes Excel to come up
with the data in a separate window:
="javascript:void(window.open('" & Globals!ReportServerUrl &
"?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
"&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
If you don't want to have it appear in a new window then do this in jump to
URL:
=Globals!ReportServerUrl & "?/SomeFolder/SomeReport&ParamName=" &
Parameters!ParamName.Value & "&rs:Format=CSV&rc:Encoding=ASCII"
Solution 2:
Do the above design change to avoid the merged cell problem with sorting in
Excel. Then modify rsreportserver.config. Reboot after the change. The below
shows commenting out the existing entry and putting in the needed change to
have CSV export as ASCII
. <!--
<Extension Name="CSV"
Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering"/>
-->
<Extension Name="CSV"
Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
<Configuration>
<DeviceInfo>
<Encoding>ASCII</Encoding>
</DeviceInfo>
</Configuration>
</Extension>
Very nice and very fast.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"bill" <belgie@.datamti.com> wrote in message
news:%235tVqK7ZGHA.3684@.TK2MSFTNGP05.phx.gbl...
> I'm using the reporting services web service to export reports to csv
> files.
> When I export to a csv, a quote mark is appended at the beginning and end
> of each line. I'm using the default device info settings. This is not
> the Qualifier, because I tried changing that from ["] to other characters
> in the devinfo settings, and the ["] is still inserted.
> How can I prevent the ["] from being inserted? It causes problems when I
> open the file in Excel, because Excel thinks it means the whole row is one
> field, and it gets truncated after 256 characters.
> Thanks!
> Bill
>
>
>|||I just realized that the quotes are being added when I save the csv, and
aren't there when it is generated by the reporting service.
"bill" <belgie@.datamti.com> wrote in message
news:%235tVqK7ZGHA.3684@.TK2MSFTNGP05.phx.gbl...
> I'm using the reporting services web service to export reports to csv
> files.
> When I export to a csv, a quote mark is appended at the beginning and end
> of each line. I'm using the default device info settings. This is not
> the Qualifier, because I tried changing that from ["] to other characters
> in the devinfo settings, and the ["] is still inserted.
> How can I prevent the ["] from being inserted? It causes problems when I
> open the file in Excel, because Excel thinks it means the whole row is one
> field, and it gets truncated after 256 characters.
> Thanks!
> Bill
>
>
>
Showing posts with label mark. Show all posts
Showing posts with label mark. Show all posts
Tuesday, March 20, 2012
Monday, February 20, 2012
Questions~~.. how to show the percentage in Pie chart. please help
i try to show the percentage tage in pie chart. can anyone teach how to show them.? please
for example
mark have 3
james have 5
john have 7
so total is 15.
i can show the value ok. but not the percentage %.
how can i show each person's percentage inside the pie chart.?You can calculate the percentage in the data point label expression: e.g.
=Sum(Fields!Sales.Value)/Sum(Fields!Sales.Value,"SalesChart")
For the format code of the label you would use e.g. P1 to get percentage
formatting with 1 decimal.
The example at the bottom requires SP1 installed and also shows a few other
things - like hiding labels for very small pie slices (e.g. smaller than
3%).
--
This posting is provided "AS IS" with no warranties, and confers no rights.
<help@.help.com> wrote in message news:ck51id$ijs@.library1.airnews.net...
> i try to show the percentage tage in pie chart. can anyone teach how to
show them.? please
> for example
> mark have 3
> james have 5
> john have 7
> so total is 15.
> i can show the value ok. but not the percentage %.
> how can i show each person's percentage inside the pie chart.?
======================================================
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Matrix Name="matrix1">
<Corner>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</Corner>
<Height>0.5in</Height>
<ZIndex>1</ZIndex>
<Style />
<MatrixRows>
<MatrixRow>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="Sales">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>C</Format>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>Sales</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!Sales.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
<Height>0.25in</Height>
</MatrixRow>
</MatrixRows>
<MatrixColumns>
<MatrixColumn>
<Width>1.125in</Width>
</MatrixColumn>
</MatrixColumns>
<DataSetName>DataSet1</DataSetName>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<Grouping Name="matrix1_ProdCat">
<GroupExpressions>
<GroupExpression>=Fields!ProdCat.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="ProdCat">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>ProdCat</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!ProdCat.Value</Value>
</Textbox>
</ReportItems>
</DynamicColumns>
<Height>0.25in</Height>
</ColumnGrouping>
</ColumnGroupings>
<Width>3.125in</Width>
<Top>4.5in</Top>
<RowGroupings>
<RowGrouping>
<DynamicRows>
<Grouping Name="matrix1_OrderYear">
<GroupExpressions>
<GroupExpression>=Fields!OrderYear.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="OrderYear">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>OrderYear</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!OrderYear.Value</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>1in</Width>
</RowGrouping>
<RowGrouping>
<DynamicRows>
<Grouping Name="matrix1_OrderQtr">
<GroupExpressions>
<GroupExpression>=Fields!OrderQtr.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="OrderQtr">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>OrderQtr</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!OrderQtr.Value</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>1in</Width>
</RowGrouping>
</RowGroupings>
</Matrix>
<Chart Name="SalesChart">
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>30</Inclination>
<Shading>Simple</Shading>
<WallThickness>50</WallThickness>
</ThreeDProperties>
<Style />
<Legend>
<Visible>true</Visible>
<Style />
<Position>BottomCenter</Position>
<Layout>Table</Layout>
</Legend>
<Palette>Default</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Sum(Fields!Sales.Value)</Value>
</DataValue>
</DataValues>
<DataLabel>
<Style>
<Format>P2</Format>
</Style>
<Value>=iif(
Sum(Fields!Sales.Value)/Sum(Fields!Sales.Value,"SalesChart") < 0.03, " ",
Sum(Fields!Sales.Value)/Sum(Fields!Sales.Value,"SalesChart"))</Value>
<Position>Top</Position>
<Visible>true</Visible>
</DataLabel>
<Style>
<BorderWidth>
<Default>=iif(
Sum(Fields!Sales.Value)/Sum(Fields!Sales.Value,"SalesChart") < 0.03, "0.5
pt", "1 pt")</Default>
</BorderWidth>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Marker>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
</ChartSeries>
</ChartData>
<CategoryAxis>
<Axis>
<Title>
<Style />
</Title>
<Style />
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style />
</MajorGridLines>
<MinorGridLines>
<Style />
</MinorGridLines>
<Margin>true</Margin>
<Visible>true</Visible>
</Axis>
</CategoryAxis>
<DataSetName>DataSet1</DataSetName>
<PointWidth>0</PointWidth>
<Type>Pie</Type>
<Title>
<Style />
</Title>
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="newChart1_CategoryGroup1">
<GroupExpressions>
<GroupExpression>=Fields!ProdCat.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Label>=Fields!ProdCat.Value</Label>
</DynamicCategories>
</CategoryGrouping>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="newChart1_CategoryGroup2">
<GroupExpressions>
<GroupExpression>=Fields!OrderYear.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Label />
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<Height>4.375in</Height>
<Subtype>Plain</Subtype>
<PlotArea>
<Style />
</PlotArea>
<ValueAxis>
<Axis>
<Title>
<Style />
</Title>
<Style />
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style />
</MajorGridLines>
<MinorGridLines>
<Style />
</MinorGridLines>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
</Chart>
</ReportItems>
<Style />
<Height>5.5in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="AdventureWorks2000">
<rd:DataSourceID>78faf15d-b746-4b6a-8f10-86ed6ddf1787</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=(local);initial
catalog=AdventureWorks2000</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>7in</Width>
<DataSets>
<DataSet Name="DataSet1">
<Fields>
<Field Name="ProdCat">
<DataField>ProdCat</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="SubCat">
<DataField>SubCat</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="OrderYear">
<DataField>OrderYear</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="OrderQtr">
<DataField>OrderQtr</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Sales">
<DataField>Sales</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>AdventureWorks2000</DataSourceName>
<CommandText>SELECT ProductCategory.Name AS ProdCat,
ProductSubCategory.Name AS SubCat, DATEPART(yy, SalesOrderHeader.OrderDate)
AS OrderYear,
'Q' + DATENAME(qq, SalesOrderHeader.OrderDate) AS
OrderQtr, SUM(SalesOrderDetail.UnitPrice * SalesOrderDetail.OrderQty) AS
Sales
FROM ProductSubCategory INNER JOIN
SalesOrderHeader INNER JOIN
SalesOrderDetail ON SalesOrderHeader.SalesOrderID =SalesOrderDetail.SalesOrderID INNER JOIN
Product ON SalesOrderDetail.ProductID =Product.ProductID ON
ProductSubCategory.ProductSubCategoryID =Product.ProductSubCategoryID INNER JOIN
ProductCategory ON
ProductSubCategory.ProductCategoryID = ProductCategory.ProductCategoryID
WHERE (SalesOrderHeader.OrderDate BETWEEN '1/1/2002' AND '12/31/2003')
GROUP BY DATEPART(yy, SalesOrderHeader.OrderDate), ProductCategory.Name,
ProductSubCategory.Name, 'Q' + DATENAME(qq,
SalesOrderHeader.OrderDate),
ProductSubCategory.ProductSubCategoryID</CommandText>
<Timeout>30</Timeout>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>9059fe01-2a5d-4d68-bfd9-2a49ccc184e6</rd:ReportID>
<BottomMargin>1in</BottomMargin>
</Report>
for example
mark have 3
james have 5
john have 7
so total is 15.
i can show the value ok. but not the percentage %.
how can i show each person's percentage inside the pie chart.?You can calculate the percentage in the data point label expression: e.g.
=Sum(Fields!Sales.Value)/Sum(Fields!Sales.Value,"SalesChart")
For the format code of the label you would use e.g. P1 to get percentage
formatting with 1 decimal.
The example at the bottom requires SP1 installed and also shows a few other
things - like hiding labels for very small pie slices (e.g. smaller than
3%).
--
This posting is provided "AS IS" with no warranties, and confers no rights.
<help@.help.com> wrote in message news:ck51id$ijs@.library1.airnews.net...
> i try to show the percentage tage in pie chart. can anyone teach how to
show them.? please
> for example
> mark have 3
> james have 5
> john have 7
> so total is 15.
> i can show the value ok. but not the percentage %.
> how can i show each person's percentage inside the pie chart.?
======================================================
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Matrix Name="matrix1">
<Corner>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</Corner>
<Height>0.5in</Height>
<ZIndex>1</ZIndex>
<Style />
<MatrixRows>
<MatrixRow>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="Sales">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>C</Format>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>Sales</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!Sales.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
<Height>0.25in</Height>
</MatrixRow>
</MatrixRows>
<MatrixColumns>
<MatrixColumn>
<Width>1.125in</Width>
</MatrixColumn>
</MatrixColumns>
<DataSetName>DataSet1</DataSetName>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<Grouping Name="matrix1_ProdCat">
<GroupExpressions>
<GroupExpression>=Fields!ProdCat.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="ProdCat">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>ProdCat</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!ProdCat.Value</Value>
</Textbox>
</ReportItems>
</DynamicColumns>
<Height>0.25in</Height>
</ColumnGrouping>
</ColumnGroupings>
<Width>3.125in</Width>
<Top>4.5in</Top>
<RowGroupings>
<RowGrouping>
<DynamicRows>
<Grouping Name="matrix1_OrderYear">
<GroupExpressions>
<GroupExpression>=Fields!OrderYear.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="OrderYear">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>OrderYear</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!OrderYear.Value</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>1in</Width>
</RowGrouping>
<RowGrouping>
<DynamicRows>
<Grouping Name="matrix1_OrderQtr">
<GroupExpressions>
<GroupExpression>=Fields!OrderQtr.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="OrderQtr">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>OrderQtr</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!OrderQtr.Value</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>1in</Width>
</RowGrouping>
</RowGroupings>
</Matrix>
<Chart Name="SalesChart">
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>30</Inclination>
<Shading>Simple</Shading>
<WallThickness>50</WallThickness>
</ThreeDProperties>
<Style />
<Legend>
<Visible>true</Visible>
<Style />
<Position>BottomCenter</Position>
<Layout>Table</Layout>
</Legend>
<Palette>Default</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Sum(Fields!Sales.Value)</Value>
</DataValue>
</DataValues>
<DataLabel>
<Style>
<Format>P2</Format>
</Style>
<Value>=iif(
Sum(Fields!Sales.Value)/Sum(Fields!Sales.Value,"SalesChart") < 0.03, " ",
Sum(Fields!Sales.Value)/Sum(Fields!Sales.Value,"SalesChart"))</Value>
<Position>Top</Position>
<Visible>true</Visible>
</DataLabel>
<Style>
<BorderWidth>
<Default>=iif(
Sum(Fields!Sales.Value)/Sum(Fields!Sales.Value,"SalesChart") < 0.03, "0.5
pt", "1 pt")</Default>
</BorderWidth>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Marker>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
</ChartSeries>
</ChartData>
<CategoryAxis>
<Axis>
<Title>
<Style />
</Title>
<Style />
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style />
</MajorGridLines>
<MinorGridLines>
<Style />
</MinorGridLines>
<Margin>true</Margin>
<Visible>true</Visible>
</Axis>
</CategoryAxis>
<DataSetName>DataSet1</DataSetName>
<PointWidth>0</PointWidth>
<Type>Pie</Type>
<Title>
<Style />
</Title>
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="newChart1_CategoryGroup1">
<GroupExpressions>
<GroupExpression>=Fields!ProdCat.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Label>=Fields!ProdCat.Value</Label>
</DynamicCategories>
</CategoryGrouping>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="newChart1_CategoryGroup2">
<GroupExpressions>
<GroupExpression>=Fields!OrderYear.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Label />
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<Height>4.375in</Height>
<Subtype>Plain</Subtype>
<PlotArea>
<Style />
</PlotArea>
<ValueAxis>
<Axis>
<Title>
<Style />
</Title>
<Style />
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style />
</MajorGridLines>
<MinorGridLines>
<Style />
</MinorGridLines>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
</Chart>
</ReportItems>
<Style />
<Height>5.5in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="AdventureWorks2000">
<rd:DataSourceID>78faf15d-b746-4b6a-8f10-86ed6ddf1787</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=(local);initial
catalog=AdventureWorks2000</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>7in</Width>
<DataSets>
<DataSet Name="DataSet1">
<Fields>
<Field Name="ProdCat">
<DataField>ProdCat</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="SubCat">
<DataField>SubCat</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="OrderYear">
<DataField>OrderYear</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="OrderQtr">
<DataField>OrderQtr</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Sales">
<DataField>Sales</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>AdventureWorks2000</DataSourceName>
<CommandText>SELECT ProductCategory.Name AS ProdCat,
ProductSubCategory.Name AS SubCat, DATEPART(yy, SalesOrderHeader.OrderDate)
AS OrderYear,
'Q' + DATENAME(qq, SalesOrderHeader.OrderDate) AS
OrderQtr, SUM(SalesOrderDetail.UnitPrice * SalesOrderDetail.OrderQty) AS
Sales
FROM ProductSubCategory INNER JOIN
SalesOrderHeader INNER JOIN
SalesOrderDetail ON SalesOrderHeader.SalesOrderID =SalesOrderDetail.SalesOrderID INNER JOIN
Product ON SalesOrderDetail.ProductID =Product.ProductID ON
ProductSubCategory.ProductSubCategoryID =Product.ProductSubCategoryID INNER JOIN
ProductCategory ON
ProductSubCategory.ProductCategoryID = ProductCategory.ProductCategoryID
WHERE (SalesOrderHeader.OrderDate BETWEEN '1/1/2002' AND '12/31/2003')
GROUP BY DATEPART(yy, SalesOrderHeader.OrderDate), ProductCategory.Name,
ProductSubCategory.Name, 'Q' + DATENAME(qq,
SalesOrderHeader.OrderDate),
ProductSubCategory.ProductSubCategoryID</CommandText>
<Timeout>30</Timeout>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>9059fe01-2a5d-4d68-bfd9-2a49ccc184e6</rd:ReportID>
<BottomMargin>1in</BottomMargin>
</Report>
Subscribe to:
Posts (Atom)