Hi guys,
I posted this question a couple of weeks ago, however, I got no response at all,
. It seems that no body tried this before. Anyhow if anybody did the same thing, here is my issue;
Assume I have a pie chart whose Value: is Internet Sales Amount and Category group: is Product Subcategroy. Suppose I have 5 segments(slices) with the value 200,150,80,50,and 40. Now, here is the question; I want to aggregate those values that are less than 100 (in this case the last three values: 80+50+40 = 170) and put the sum(170) in one segment, so that in the new pie chart we will have only 3 segments with the value 200,250, and 170.
Any idea is appreciated.
There are several other ways of doing this:
- perform the pre-aggregation in the dataset query / stored procedure. If the data source is Analysis Services, you can use e.g. the MDX iif functions to "re-group" certain members within a hierarchy.
- look into RS 2005 CustomReportItem-based third party charting solutions which may offer this functionality.
- wait for a future release of RS. Adding this particular re-group feature as a native concept within RS is under consideration for a future release.
- there is a solution based on using the OLEDB provider to connect to AS. See my posting of 08/23 with a sample report in this thread.
-- Robert
|||Robert,
The data source is Analysis Services Cube; that makes it a litle bit difficult. Anyhow, I tried all my best to aggregate the values (that are less than 100, for instance) and put them in one segment. What I did is in the value tab I wrote the following expression:
=Iif(Fields!Internet_Sales_Amount.Value < 100,Fields!sumofothers.value,fields!Internet_Sales_Amount.value),
where Fields!sumeofothers.value is sum of those values that are less than 100.
And in the Data Leble I wrote the following expression
=IIF(fields!Internet_Sales_Amount.Value <100,"Others",Fields!Subcategory.Value).
So, here what I want is to group those values that are less than 100 and aggregate them in one segment called "Others", however, I am getting multiple "others" segments.
Can you please give me some comment on the above expressions or an example of expression which perform this functionality?
The other thing is that where shall we expect the next release of reporting service?
--Amde
|||Let's take a look at the following MDX query where you perform essentially "re-grouping" of data, such as the following query based on the Adventure Works sample cube:
WITH
SET [ProductLineTop5ModelName] AS 'Generate([Product].[Product Model Lines].Children, {{TOPCOUNT([Product].[Product Model Lines].Children, 10, [Measures].[Order Count]) as TC}, VisualTotals({[Product].[Product Model Lines].CurrentMember, [Product].[Product Model Lines].Children - TC},
"* - Other") - [Product].[Product Model Lines].Children, [Product].[Product Model Lines].CurrentMember})'
SELECT {[Measures].[Order Count]} ON COLUMNS,
[ProductLineTop5ModelName] ON ROWS FROM [Adventure Works]
The "Other" group is represented as a subtotal aggregate row in the flattened rowset from the AS server. The only way to retrieve this aggregate row when using the AS data extension in RS 2005 RTM/SP1 is to use the RS server aggregate function =Aggregate(...) in the correct scope in the report - otherwise the row is ignored since it is marked as an AS server aggregate row. However, in that particular case the "re-grouped" data actually is not the subtotal of the other groups. It is rather "detail data".
I attached a report at the bottom of this posting which implements two options side by side for the MDX query above:
1. the first dataset / first table in the report is based on using the data source type "Analysis Services" and it will require a new feature under consideration to be added in the next service pack of RS 2005. Without that new feature, the "VisualTotal" rows will not show up in the first table because the server aggregate function is not used. With the new feature however they will show up in the first table because the report does not explicitly use the RS server aggregate row to actually retrieve the aggregate rows as a subtotals - and they will therefore be automatically recategorized as "detail data".
2. the second dataset / second table in the report uses the data source type "OLEDB" and connects through the OleDB for AS9.0 provider - this is available in RS 2005 today. The fact that the OLEDB provider does not understand anything about hierarchies, measures, extended properties, server aggregate rows, etc. is actually a benefit in this case: the "VisualTotal" shows up as detail row in the table - as desired.
You should be able to apply the same concept to your particular situation with the chart. The important part is that for now you will need to use the OLEDB provider to connect to AS and execute the queries.
-- Robert
==================================================================================
<?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="RoseAS">
<ConnectionProperties>
<IntegratedSecurity>true</IntegratedSecurity>
<ConnectString>Data Source=roseas;Initial Catalog="Adventure Works DW"</ConnectString>
<DataProvider>OLEDB-MD</DataProvider>
</ConnectionProperties>
<rd:DataSourceID>2853688f-8806-4d0a-ae38-2a865ce38226</rd:DataSourceID>
</DataSource>
<DataSource Name="DataSource1">
<ConnectionProperties>
<IntegratedSecurity>true</IntegratedSecurity>
<ConnectString>Provider=MSOLAP.3;Data Source=roseas;Initial Catalog="Adventure Works DW"</ConnectString>
<DataProvider>OLEDB</DataProvider>
</ConnectionProperties>
<rd:DataSourceID>90e8d194-dfcc-42a3-826e-042e456854e3</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Table Name="table2">
<Footer>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox15">
<rd:DefaultName>textbox15</rd:DefaultName>
<ZIndex>5</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox16">
<rd:DefaultName>textbox16</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox17">
<rd:DefaultName>textbox17</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Footer>
<Left>0.25in</Left>
<DataSetName>DataSet_OleDB</DataSetName>
<Top>1.875in</Top>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="Product_Product_Model_Lines_Product_Line">
<rd:DefaultName>Product_Product_Model_Lines_Product_Line</rd:DefaultName>
<ZIndex>8</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<BackgroundColor>LightSkyBlue</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Product_Product_Model_Lines_Product_Line.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox19">
<rd:DefaultName>textbox19</rd:DefaultName>
<ZIndex>7</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<BackgroundColor>LightSkyBlue</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox20">
<rd:DefaultName>textbox20</rd:DefaultName>
<ZIndex>6</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<BackgroundColor>LightSkyBlue</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table2_Group1">
<GroupExpressions>
<GroupExpression>=Fields!Product_Product_Model_Lines_Product_Line.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
</TableGroups>
<ZIndex>1</ZIndex>
<Width>4.625in</Width>
<Details>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<rd:DefaultName>textbox12</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Product_Product_Model_Lines_Model">
<rd:DefaultName>Product_Product_Model_Lines_Model</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=iif(Fields!Product_Product_Model_Lines_Model.Value is Nothing, Fields!Product_Product_Model_Lines_Product_Line.Value & " - Other", Fields!Product_Product_Model_Lines_Model.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Measures_Order_Count">
<rd:DefaultName>Measures_Order_Count</rd:DefaultName>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Measures_Order_Count.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Details>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<rd:DefaultName>textbox5</rd:DefaultName>
<ZIndex>11</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Product Line</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<rd:DefaultName>textbox6</rd:DefaultName>
<ZIndex>10</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Model</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox10">
<rd:DefaultName>textbox10</rd:DefaultName>
<ZIndex>9</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Order Count</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Header>
<TableColumns>
<TableColumn>
<Width>1.5in</Width>
</TableColumn>
<TableColumn>
<Width>2.25in</Width>
</TableColumn>
<TableColumn>
<Width>0.875in</Width>
</TableColumn>
</TableColumns>
<Height>1in</Height>
</Table>
<Table Name="table1">
<Footer>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<rd:DefaultName>textbox7</rd:DefaultName>
<ZIndex>5</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<rd:DefaultName>textbox8</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<rd:DefaultName>textbox9</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Footer>
<Left>0.25in</Left>
<DataSetName>DataSet_ADOMD</DataSetName>
<Top>0.25in</Top>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="Product_Line">
<rd:DefaultName>Product_Line</rd:DefaultName>
<ZIndex>8</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<BackgroundColor>LightGrey</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Product_Line.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<rd:DefaultName>textbox11</rd:DefaultName>
<ZIndex>7</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<BackgroundColor>LightGrey</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Order_Count_1">
<rd:DefaultName>Order_Count_1</rd:DefaultName>
<ZIndex>6</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<BackgroundColor>LightGrey</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!Order_Count.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group1">
<GroupExpressions>
<GroupExpression>=Fields!Product_Line.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
</TableGroups>
<Width>4.625in</Width>
<Details>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<rd:DefaultName>textbox4</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Model">
<rd:DefaultName>Model</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=iif(Fields!Model.Value is Nothing, Fields!Product_Line.Value & " - Other", Fields!Model.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Order_Count">
<rd:DefaultName>Order_Count</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Order_Count.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Details>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>11</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Product Line</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<rd:DefaultName>textbox2</rd:DefaultName>
<ZIndex>10</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Model</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<rd:DefaultName>textbox3</rd:DefaultName>
<ZIndex>9</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Order Count</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Header>
<TableColumns>
<TableColumn>
<Width>1.5in</Width>
</TableColumn>
<TableColumn>
<Width>2.25in</Width>
</TableColumn>
<TableColumn>
<Width>0.875in</Width>
</TableColumn>
</TableColumns>
<Height>1in</Height>
</Table>
</ReportItems>
<Height>3in</Height>
</Body>
<rd:ReportID>2158ae1a-bacd-437c-ace7-4413c1c708e8</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet_ADOMD">
<Query>
<rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate>
<rd:DesignerState><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>Product</DimensionName><HierarchyName>Product Model Lines</HierarchyName><HierarchyUniqueName>[Product].[Product Model Lines]</HierarchyUniqueName><LevelName>Product Line</LevelName><UniqueName>[Product].[Product Model Lines].[Product Line]</UniqueName></ID><ItemCaption>Product Line</ItemCaption></Item><Item><ID xsi:type="Level"><DimensionName>Product</DimensionName><HierarchyName>Product Model Lines</HierarchyName><HierarchyUniqueName>[Product].[Product Model Lines]</HierarchyUniqueName><LevelName>Model</LevelName><UniqueName>[Product].[Product Model Lines].[Model]</UniqueName></ID><ItemCaption>Model</ItemCaption></Item><Item><ID xsi:type="Measure"><MeasureName>Order Count</MeasureName><UniqueName>[Measures].[Order Count]</UniqueName></ID><ItemCaption>Order Count</ItemCaption><FormattedValue>true</FormattedValue></Item></Items></Select><From>Adventure Works</From><Filter><FilterItems /></Filter><Calculations /><Aggregates /><QueryProperties /></QuerySpecification><Query><Statement>WITH
SET [ProductLineTop5ModelName] AS
'Generate([Product].[Product Model Lines].Children, {{TOPCOUNT([Product].[Product Model Lines].Children, 10, [Measures].[Order Count]) as TC}, VisualTotals({[Product].[Product Model Lines].CurrentMember, [Product].[Product Model Lines].Children - TC},
"* - Other") - [Product].[Product Model Lines].Children, [Product].[Product Model Lines].CurrentMember})'
SELECT {[Measures].[Order Count]} ON COLUMNS,
[ProductLineTop5ModelName] ON ROWS
FROM [Adventure Works]
</Statement><ParameterDefinitions /></Query></QueryDefinition></rd:DesignerState>
<CommandText>WITH
SET [ProductLineTop5ModelName] AS
'Generate([Product].[Product Model Lines].Children, {{TOPCOUNT([Product].[Product Model Lines].Children, 10, [Measures].[Order Count]) as TC}, VisualTotals({[Product].[Product Model Lines].CurrentMember, [Product].[Product Model Lines].Children - TC},
"* - Other") - [Product].[Product Model Lines].Children, [Product].[Product Model Lines].CurrentMember})'
SELECT {[Measures].[Order Count]} ON COLUMNS,
[ProductLineTop5ModelName] ON ROWS
FROM [Adventure Works]
</CommandText>
<DataSourceName>RoseAS</DataSourceName>
</Query>
<Fields>
<Field Name="Product_Line">
<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="[Product].[Product Model Lines].[Product Line]" /></DataField>
</Field>
<Field Name="Model">
<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="[Product].[Product Model Lines].[Model]" /></DataField>
</Field>
<Field Name="Order_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].[Order Count]" /></DataField>
</Field>
</Fields>
</DataSet>
<DataSet Name="DataSet_OleDB">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>WITH
SET [ProductLineTop5ModelName] AS
'Generate([Product].[Product Model Lines].Children, {{TOPCOUNT([Product].[Product Model Lines].Children, 10, [Measures].[Order Count]) as TC}, VisualTotals({[Product].[Product Model Lines].CurrentMember, [Product].[Product Model Lines].Children - TC},
"* - Other") - [Product].[Product Model Lines].Children, [Product].[Product Model Lines].CurrentMember})'
SELECT {[Measures].[Order Count]} ON COLUMNS,
[ProductLineTop5ModelName] ON ROWS
FROM [Adventure Works]</CommandText>
<DataSourceName>DataSource1</DataSourceName>
</Query>
<Fields>
<Field Name="Product_Product_Model_Lines_Product_Line">
<rd:TypeName>System.String</rd:TypeName>
<DataField>[Product].[Product Model Lines].[Product Line].[MEMBER_CAPTION]</DataField>
</Field>
<Field Name="Product_Product_Model_Lines_Model">
<rd:TypeName>System.String</rd:TypeName>
<DataField>[Product].[Product Model Lines].[Model].[MEMBER_CAPTION]</DataField>
</Field>
<Field Name="Measures_Order_Count">
<rd:TypeName>System.Object</rd:TypeName>
<DataField>[Measures].[Order Count]</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Author>Robert M. Bruckner, Microsoft</Author>
<Width>5.54167in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>
No comments:
Post a Comment