I'm trying to use an OLAP cube as my data source (built using SSAS 2005)
The cube has a parent-child dimension and a fact table. I would like to
display the parent-child data in a report . Does anyone know how to build an
MDX query that would give me the ID and the ParentID from the dimension to
build the parent-child grouping relationship in the report? The MDX builder
does not let me access the ID field or the ParentID field. I always get the
Name of the dimension.
Also, if there's another way to do this, please let me know.
Any help is greatly appreciated.
ThanksI attached a sample report that shows how to do this.
Notes:
* in the graphical query designer you have to select a recursive hierarchy
from the cube (such as Employee.Employees in the AdventureWorks DW cube).
Based on the metadata, RS should automatically detect that this is a
recursive parent-child hierarchy and there will be additional extended field
properties available (such as .UniqueName and .ParentUniqueName)
* add a grouping in the report. The grouping expression is based on
=Fields!Employees.UniqueName and for the parent of the group set it to
=Fields!Employees.ParentUniqueName
* right now, you will need to have table columns that also reference the
UniqueName and the ParentUniqueName in the report - but you can hide them so
they are not visible. If you don't have these columns, the recursive
hierarchy won't work correctly.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Raghu" <Raghu@.discussions.microsoft.com> wrote in message
news:A3B4E2B3-B78D-4571-9D90-69EC03FD18E4@.microsoft.com...
> I'm trying to use an OLAP cube as my data source (built using SSAS 2005)
> The cube has a parent-child dimension and a fact table. I would like to
> display the parent-child data in a report . Does anyone know how to build
> an
> MDX query that would give me the ID and the ParentID from the dimension to
> build the parent-child grouping relationship in the report? The MDX
> builder
> does not let me access the ID field or the ParentID field. I always get
> the
> Name of the dimension.
> Also, if there's another way to do this, please let me know.
> Any help is greatly appreciated.
> Thanks
==========================================================
<?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="DataSource1">
<ConnectionProperties>
<IntegratedSecurity>true</IntegratedSecurity>
<ConnectString>Data Source=(local);Initial Catalog="Adventure Works
DW"</ConnectString>
<DataProvider>OLEDB-MD</DataProvider>
</ConnectionProperties>
<rd:DataSourceID>5aaa5577-4975-4616-a136-0b713dab6948</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="table1">
<Footer>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<rd:DefaultName>textbox7</rd:DefaultName>
<ZIndex>9</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>8</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>7</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="textbox11">
<rd:DefaultName>textbox11</rd:DefaultName>
<ZIndex>6</ZIndex>
<Style>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox13">
<rd:DefaultName>textbox13</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>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Footer>
<DataSetName>DataSet1</DataSetName>
<Top>0.5in</Top>
<Details>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="Employees">
<rd:DefaultName>Employees</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style>
<PaddingLeft>=2 + Level() * 8 &
"pt"</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Employees.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox10">
<ZIndex>3</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Employees.UniqueName</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<rd:DefaultName>textbox5</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Employees.ParentUniqueName</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Reseller_Sales_Amount">
<rd:DefaultName>Reseller_Sales_Amount</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Level()</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Reseller_Sales_Amount_1">
<rd:DefaultName>Reseller_Sales_Amount_1</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!Reseller_Sales_Amount.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
<Grouping Name="table1_Details_Group">
<Parent>=Fields!Employees.ParentUniqueName</Parent>
<GroupExpressions>
<GroupExpression>=Fields!Employees.UniqueName</GroupExpression>
</GroupExpressions>
</Grouping>
</Details>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>14</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Employees</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<rd:DefaultName>textbox4</rd:DefaultName>
<ZIndex>13</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="textbox2">
<rd:DefaultName>textbox2</rd:DefaultName>
<ZIndex>12</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="textbox3">
<rd:DefaultName>textbox3</rd:DefaultName>
<ZIndex>11</ZIndex>
<Style>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Level</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<rd:DefaultName>textbox6</rd:DefaultName>
<ZIndex>10</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Reseller Sales Amount</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Header>
<TableColumns>
<TableColumn>
<Width>1.625in</Width>
</TableColumn>
<TableColumn>
<Width>0.875in</Width>
<Visibility>
<Hidden>true</Hidden>
</Visibility>
</TableColumn>
<TableColumn>
<Width>0.875in</Width>
<Visibility>
<Hidden>true</Hidden>
</Visibility>
</TableColumn>
<TableColumn>
<Width>0.5in</Width>
</TableColumn>
<TableColumn>
<Width>1.75in</Width>
</TableColumn>
</TableColumns>
<Height>0.75in</Height>
</Table>
</ReportItems>
<Height>1.375in</Height>
</Body>
<rd:ReportID>380d94a9-e8f4-4147-8f13-1f6d88259335</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate>
<CommandText> SELECT NON EMPTY { [Measures].[Reseller Sales
Amount] } ON COLUMNS, NON EMPTY {
(DESCENDANTS([Employee].[Employees].[Employee Level 02].ALLMEMBERS) ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME, PARENT_UNIQUE_NAME,
LEVEL_NUMBER ON ROWS FROM [Adventure Works] CELL PROPERTIES VALUE,
BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME,
FONT_SIZE, FONT_FLAGS</CommandText>
<DataSourceName>DataSource1</DataSourceName>
<rd:MdxQuery><QueryDefinition
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns="<CommandType>MDX</CommandType><QuerySpecification">http://schemas.microsoft.com/AnalysisServices/QueryDefinition"><CommandType>MDX</CommandType><QuerySpecification
xsi:type="MDXQuerySpecification"><Select><Items><Item><ID
xsi:type="Level"><DimensionName>Employee</DimensionName><HierarchyName>Employees</HierarchyName><HierarchyUniqueName>[Employee].[Employees]</HierarchyUniqueName><LevelName>Employee
Level 02</LevelName><UniqueName>[Employee].[Employees].[Employee Level
02]</UniqueName></ID><ItemCaption>Employees</ItemCaption><UniqueName>true</UniqueName><IsParentChild>true</IsParentChild></Item><Item><ID
xsi:type="Measure"><MeasureName>Reseller Sales
Amount</MeasureName><UniqueName>[Measures].[Reseller Sales
Amount]</UniqueName></ID><ItemCaption>Reseller Sales
Amount</ItemCaption><BackColor>true</BackColor><ForeColor>true</ForeColor><FontFamily>true</FontFamily><FontSize>true</FontSize><FontWeight>true</FontWeight><FontStyle>true</FontStyle><FontDecoration>true</FontDecoration><FormattedValue>true</FormattedValue><FormatString>true</FormatString></Item></Items></Select><From>Adventure
Works</From><Filter><FilterItems /></Filter><Calculations /><Aggregates
/><QueryProperties /></QuerySpecification><Query><Statement> SELECT NON
EMPTY { [Measures].[Reseller Sales Amount] } ON COLUMNS, NON EMPTY {
(DESCENDANTS([Employee].[Employees].[Employee Level 02].ALLMEMBERS) ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME, PARENT_UNIQUE_NAME,
LEVEL_NUMBER ON ROWS FROM [Adventure Works] CELL PROPERTIES VALUE,
BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME,
FONT_SIZE, FONT_FLAGS</Statement><ParameterDefinitions
/></Query></QueryDefinition></rd:MdxQuery>
</Query>
<Fields>
<Field Name="Employees">
<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="[Employee].[Employees]" /></DataField>
</Field>
<Field Name="Reseller_Sales_Amount">
<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].[Reseller Sales Amount]" /></DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>5.625in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>|||BTW: you don't need to have the two additional hidden table columns with
UniqueName and ParentUniqueName in the report (as shown in my sample). It is
sufficient to e.g. just change the expression in the first table column from
=Fields!Employees.Value to =Fields!Employees("Value"). This will turn off a
certain optimization related to extended field properties.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:e4fKfdq1FHA.2964@.TK2MSFTNGP09.phx.gbl...
>I attached a sample report that shows how to do this.
> Notes:
> * in the graphical query designer you have to select a recursive hierarchy
> from the cube (such as Employee.Employees in the AdventureWorks DW cube).
> Based on the metadata, RS should automatically detect that this is a
> recursive parent-child hierarchy and there will be additional extended
> field properties available (such as .UniqueName and .ParentUniqueName)
> * add a grouping in the report. The grouping expression is based on
> =Fields!Employees.UniqueName and for the parent of the group set it to
> =Fields!Employees.ParentUniqueName
> * right now, you will need to have table columns that also reference the
> UniqueName and the ParentUniqueName in the report - but you can hide them
> so they are not visible. If you don't have these columns, the recursive
> hierarchy won't work correctly.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Raghu" <Raghu@.discussions.microsoft.com> wrote in message
> news:A3B4E2B3-B78D-4571-9D90-69EC03FD18E4@.microsoft.com...
>> I'm trying to use an OLAP cube as my data source (built using SSAS 2005)
>> The cube has a parent-child dimension and a fact table. I would like to
>> display the parent-child data in a report . Does anyone know how to build
>> an
>> MDX query that would give me the ID and the ParentID from the dimension
>> to
>> build the parent-child grouping relationship in the report? The MDX
>> builder
>> does not let me access the ID field or the ParentID field. I always get
>> the
>> Name of the dimension.
>> Also, if there's another way to do this, please let me know.
>> Any help is greatly appreciated.
>> Thanks
>
> ==========================================================> <?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="DataSource1">
> <ConnectionProperties>
> <IntegratedSecurity>true</IntegratedSecurity>
> <ConnectString>Data Source=(local);Initial Catalog="Adventure Works
> DW"</ConnectString>
> <DataProvider>OLEDB-MD</DataProvider>
> </ConnectionProperties>
> <rd:DataSourceID>5aaa5577-4975-4616-a136-0b713dab6948</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="table1">
> <Footer>
> <TableRows>
> <TableRow>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox7">
> <rd:DefaultName>textbox7</rd:DefaultName>
> <ZIndex>9</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>8</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>7</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="textbox11">
> <rd:DefaultName>textbox11</rd:DefaultName>
> <ZIndex>6</ZIndex>
> <Style>
> <TextAlign>Center</TextAlign>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox13">
> <rd:DefaultName>textbox13</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>
> </TableCells>
> <Height>0.25in</Height>
> </TableRow>
> </TableRows>
> </Footer>
> <DataSetName>DataSet1</DataSetName>
> <Top>0.5in</Top>
> <Details>
> <TableRows>
> <TableRow>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="Employees">
> <rd:DefaultName>Employees</rd:DefaultName>
> <ZIndex>4</ZIndex>
> <Style>
> <PaddingLeft>=2 + Level() * 8 &
> "pt"</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>=Fields!Employees.Value</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox10">
> <ZIndex>3</ZIndex>
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>=Fields!Employees.UniqueName</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox5">
> <rd:DefaultName>textbox5</rd:DefaultName>
> <ZIndex>2</ZIndex>
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>=Fields!Employees.ParentUniqueName</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="Reseller_Sales_Amount">
> <rd:DefaultName>Reseller_Sales_Amount</rd:DefaultName>
> <ZIndex>1</ZIndex>
> <Style>
> <TextAlign>Center</TextAlign>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>=Level()</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="Reseller_Sales_Amount_1">
> <rd:DefaultName>Reseller_Sales_Amount_1</rd:DefaultName>
> <Style>
> <TextAlign>Right</TextAlign>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>=Sum(Fields!Reseller_Sales_Amount.Value)</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> <Height>0.25in</Height>
> </TableRow>
> </TableRows>
> <Grouping Name="table1_Details_Group">
> <Parent>=Fields!Employees.ParentUniqueName</Parent>
> <GroupExpressions>
> <GroupExpression>=Fields!Employees.UniqueName</GroupExpression>
> </GroupExpressions>
> </Grouping>
> </Details>
> <Header>
> <TableRows>
> <TableRow>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox1">
> <rd:DefaultName>textbox1</rd:DefaultName>
> <ZIndex>14</ZIndex>
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>Employees</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox4">
> <rd:DefaultName>textbox4</rd:DefaultName>
> <ZIndex>13</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="textbox2">
> <rd:DefaultName>textbox2</rd:DefaultName>
> <ZIndex>12</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="textbox3">
> <rd:DefaultName>textbox3</rd:DefaultName>
> <ZIndex>11</ZIndex>
> <Style>
> <TextAlign>Center</TextAlign>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>Level</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox6">
> <rd:DefaultName>textbox6</rd:DefaultName>
> <ZIndex>10</ZIndex>
> <Style>
> <TextAlign>Right</TextAlign>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>Reseller Sales Amount</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> <Height>0.25in</Height>
> </TableRow>
> </TableRows>
> </Header>
> <TableColumns>
> <TableColumn>
> <Width>1.625in</Width>
> </TableColumn>
> <TableColumn>
> <Width>0.875in</Width>
> <Visibility>
> <Hidden>true</Hidden>
> </Visibility>
> </TableColumn>
> <TableColumn>
> <Width>0.875in</Width>
> <Visibility>
> <Hidden>true</Hidden>
> </Visibility>
> </TableColumn>
> <TableColumn>
> <Width>0.5in</Width>
> </TableColumn>
> <TableColumn>
> <Width>1.75in</Width>
> </TableColumn>
> </TableColumns>
> <Height>0.75in</Height>
> </Table>
> </ReportItems>
> <Height>1.375in</Height>
> </Body>
> <rd:ReportID>380d94a9-e8f4-4147-8f13-1f6d88259335</rd:ReportID>
> <LeftMargin>1in</LeftMargin>
> <DataSets>
> <DataSet Name="DataSet1">
> <Query>
> <rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate>
> <CommandText> SELECT NON EMPTY { [Measures].[Reseller Sales
> Amount] } ON COLUMNS, NON EMPTY {
> (DESCENDANTS([Employee].[Employees].[Employee Level 02].ALLMEMBERS) ) }
> DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME,
> PARENT_UNIQUE_NAME, LEVEL_NUMBER ON ROWS FROM [Adventure Works] CELL
> PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING,
> FONT_NAME, FONT_SIZE, FONT_FLAGS</CommandText>
> <DataSourceName>DataSource1</DataSourceName>
> <rd:MdxQuery><QueryDefinition
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns="<CommandType>MDX</CommandType><QuerySpecification">http://schemas.microsoft.com/AnalysisServices/QueryDefinition"><CommandType>MDX</CommandType><QuerySpecification
> xsi:type="MDXQuerySpecification"><Select><Items><Item><ID
> xsi:type="Level"><DimensionName>Employee</DimensionName><HierarchyName>Employees</HierarchyName><HierarchyUniqueName>[Employee].[Employees]</HierarchyUniqueName><LevelName>Employee
> Level 02</LevelName><UniqueName>[Employee].[Employees].[Employee Level
> 02]</UniqueName></ID><ItemCaption>Employees</ItemCaption><UniqueName>true</UniqueName><IsParentChild>true</IsParentChild></Item><Item><ID
> xsi:type="Measure"><MeasureName>Reseller Sales
> Amount</MeasureName><UniqueName>[Measures].[Reseller Sales
> Amount]</UniqueName></ID><ItemCaption>Reseller Sales
> Amount</ItemCaption><BackColor>true</BackColor><ForeColor>true</ForeColor><FontFamily>true</FontFamily><FontSize>true</FontSize><FontWeight>true</FontWeight><FontStyle>true</FontStyle><FontDecoration>true</FontDecoration><FormattedValue>true</FormattedValue><FormatString>true</FormatString></Item></Items></Select><From>Adventure
> Works</From><Filter><FilterItems /></Filter><Calculations /><Aggregates
> /><QueryProperties /></QuerySpecification><Query><Statement> SELECT NON
> EMPTY { [Measures].[Reseller Sales Amount] } ON COLUMNS, NON EMPTY {
> (DESCENDANTS([Employee].[Employees].[Employee Level 02].ALLMEMBERS) ) }
> DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME,
> PARENT_UNIQUE_NAME, LEVEL_NUMBER ON ROWS FROM [Adventure Works] CELL
> PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING,
> FONT_NAME, FONT_SIZE, FONT_FLAGS</Statement><ParameterDefinitions
> /></Query></QueryDefinition></rd:MdxQuery>
> </Query>
> <Fields>
> <Field Name="Employees">
> <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="[Employee].[Employees]" /></DataField>
> </Field>
> <Field Name="Reseller_Sales_Amount">
> <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].[Reseller Sales Amount]" /></DataField>
> </Field>
> </Fields>
> </DataSet>
> </DataSets>
> <Width>5.625in</Width>
> <InteractiveHeight>11in</InteractiveHeight>
> <Language>en-US</Language>
> <TopMargin>1in</TopMargin>
> </Report>
>|||Thank you Robert, that was very helpful. I got past a hurdle. I do have a
couple of folllowup questions inspired by your response (and my somewhat
novice knowledge of OLAP).
Raghu
--
1. Dimension properties MEMBER_UNIQUE_NAME and PARENT_UNIQUE_NAME do get
added in the MDX as you mention. Any idea why they don't show up as fields in
the dataset? Your grouping expression used Employee.UniqueName. How are we
to know what the name of those fields are? Should we just strip of the MEMBER
keyword and any underscores and use as field names, or is there a list
somewhere? I'll try to search the docs as well. Also, when I tried to use
MEMBER_KEY property, it showed up in the result set unlike the others.
2. The grouping is based on names, which are a character based. Would this
affect performance adversely if the report data is large? One of the reasons
I wanted to use IDs was due to the names not being unique (But
MEMBER_UNIQUE_NAME seems to imply that Analysis services is generating one in
internally.)
3. I was somewhat surprised at the difference in behavior when we use
Employees("Value") and Employees.Value. Could you please shed some more light
on this as to what's happening here? I think when you use the first method,
you are basically using the indexer property in VB.NET syntax (please correct
me if I'm wrong.), but that's as far as I could get. Is this behavior
documented somewhere?
"Robert Bruckner [MSFT]" wrote:
> BTW: you don't need to have the two additional hidden table columns with
> UniqueName and ParentUniqueName in the report (as shown in my sample). It is
> sufficient to e.g. just change the expression in the first table column from
> =Fields!Employees.Value to =Fields!Employees("Value"). This will turn off a
> certain optimization related to extended field properties.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:e4fKfdq1FHA.2964@.TK2MSFTNGP09.phx.gbl...
> >I attached a sample report that shows how to do this.
> > Notes:
> > * in the graphical query designer you have to select a recursive hierarchy
> > from the cube (such as Employee.Employees in the AdventureWorks DW cube).
> > Based on the metadata, RS should automatically detect that this is a
> > recursive parent-child hierarchy and there will be additional extended
> > field properties available (such as .UniqueName and .ParentUniqueName)
> > * add a grouping in the report. The grouping expression is based on
> > =Fields!Employees.UniqueName and for the parent of the group set it to
> > =Fields!Employees.ParentUniqueName
> > * right now, you will need to have table columns that also reference the
> > UniqueName and the ParentUniqueName in the report - but you can hide them
> > so they are not visible. If you don't have these columns, the recursive
> > hierarchy won't work correctly.
> >
> > -- Robert
> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >
> >
> > "Raghu" <Raghu@.discussions.microsoft.com> wrote in message
> > news:A3B4E2B3-B78D-4571-9D90-69EC03FD18E4@.microsoft.com...
> >> I'm trying to use an OLAP cube as my data source (built using SSAS 2005)
> >> The cube has a parent-child dimension and a fact table. I would like to
> >> display the parent-child data in a report . Does anyone know how to build
> >> an
> >> MDX query that would give me the ID and the ParentID from the dimension
> >> to
> >> build the parent-child grouping relationship in the report? The MDX
> >> builder
> >> does not let me access the ID field or the ParentID field. I always get
> >> the
> >> Name of the dimension.
> >>
> >> Also, if there's another way to do this, please let me know.
> >>
> >> Any help is greatly appreciated.
> >>
> >> Thanks
> >
> >
> > ==========================================================> >
> > <?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="DataSource1">
> > <ConnectionProperties>
> > <IntegratedSecurity>true</IntegratedSecurity>
> > <ConnectString>Data Source=(local);Initial Catalog="Adventure Works
> > DW"</ConnectString>
> > <DataProvider>OLEDB-MD</DataProvider>
> > </ConnectionProperties>
> >
> > <rd:DataSourceID>5aaa5577-4975-4616-a136-0b713dab6948</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="table1">
> > <Footer>
> > <TableRows>
> > <TableRow>
> > <TableCells>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox7">
> > <rd:DefaultName>textbox7</rd:DefaultName>
> > <ZIndex>9</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>8</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>7</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="textbox11">
> > <rd:DefaultName>textbox11</rd:DefaultName>
> > <ZIndex>6</ZIndex>
> > <Style>
> > <TextAlign>Center</TextAlign>
> > <PaddingLeft>2pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingRight>2pt</PaddingRight>
> > <PaddingTop>2pt</PaddingTop>
> > </Style>
> > <CanGrow>true</CanGrow>
> > <Value />
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox13">
> > <rd:DefaultName>textbox13</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>
> > </TableCells>
> > <Height>0.25in</Height>
> > </TableRow>
> > </TableRows>
> > </Footer>
> > <DataSetName>DataSet1</DataSetName>
> > <Top>0.5in</Top>
> > <Details>
> > <TableRows>
> > <TableRow>
> > <TableCells>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="Employees">
> > <rd:DefaultName>Employees</rd:DefaultName>
> > <ZIndex>4</ZIndex>
> > <Style>
> > <PaddingLeft>=2 + Level() * 8 &
> > "pt"</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingRight>2pt</PaddingRight>
> > <PaddingTop>2pt</PaddingTop>
> > </Style>
> > <CanGrow>true</CanGrow>
> > <Value>=Fields!Employees.Value</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox10">
> > <ZIndex>3</ZIndex>
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingRight>2pt</PaddingRight>
> > <PaddingTop>2pt</PaddingTop>
> > </Style>
> > <CanGrow>true</CanGrow>
> > <Value>=Fields!Employees.UniqueName</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox5">
> > <rd:DefaultName>textbox5</rd:DefaultName>
> > <ZIndex>2</ZIndex>
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingRight>2pt</PaddingRight>
> > <PaddingTop>2pt</PaddingTop>
> > </Style>
> > <CanGrow>true</CanGrow>
> > <Value>=Fields!Employees.ParentUniqueName</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="Reseller_Sales_Amount">
> >
> > <rd:DefaultName>Reseller_Sales_Amount</rd:DefaultName>
> > <ZIndex>1</ZIndex>
> > <Style>
> > <TextAlign>Center</TextAlign>
> > <PaddingLeft>2pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingRight>2pt</PaddingRight>
> > <PaddingTop>2pt</PaddingTop>
> > </Style>
> > <CanGrow>true</CanGrow>
> > <Value>=Level()</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="Reseller_Sales_Amount_1">
> >
> > <rd:DefaultName>Reseller_Sales_Amount_1</rd:DefaultName>
> > <Style>
> > <TextAlign>Right</TextAlign>
> > <PaddingLeft>2pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingRight>2pt</PaddingRight>
> > <PaddingTop>2pt</PaddingTop>
> > </Style>
> > <CanGrow>true</CanGrow>
> >
> > <Value>=Sum(Fields!Reseller_Sales_Amount.Value)</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > </TableCells>
> > <Height>0.25in</Height>
> > </TableRow>
> > </TableRows>
> > <Grouping Name="table1_Details_Group">
> > <Parent>=Fields!Employees.ParentUniqueName</Parent>
> > <GroupExpressions>
> >
> > <GroupExpression>=Fields!Employees.UniqueName</GroupExpression>
> > </GroupExpressions>
> > </Grouping>
> > </Details>
> > <Header>
> > <TableRows>
> > <TableRow>
> > <TableCells>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox1">
> > <rd:DefaultName>textbox1</rd:DefaultName>
> > <ZIndex>14</ZIndex>
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingRight>2pt</PaddingRight>
> > <PaddingTop>2pt</PaddingTop>
> > </Style>
> > <CanGrow>true</CanGrow>
> > <Value>Employees</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox4">
> > <rd:DefaultName>textbox4</rd:DefaultName>
> > <ZIndex>13</ZIndex>
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingRight>2pt</PaddingRight>|||#1: AS dimension properties (such as Member_Unique_Name) and cell properties
(such as Formatted_Value) automatically get translated into RDL extended
properties. Please check the "Using Extended Field Properties for Analysis
Services Data" in RS 2005 Books Online (e.g. at
http://msdn2.microsoft.com/en-us/library/ms156477). Consequently, they are
just field properties instead of additional fields (as they used to be in RS
2000 through the old OleDB provider for AS 2000). Here is the
mapping/translation for the predefined RDL field properties that happens
inside our AdoMd AS data extension:
RDL field property name: Type / AS translation:
Value Object (from Member_Caption)
IsMissing Boolean (field is defined in RDL but
not returned from query)
UniqueName String (from Member_Unique_Name)
BackgroundColor String (see Style.BackgroundColor; retrieved
from Back_Color)
Color String (see Style.Color; retrieved
from Fore_Color)
FontFamily String (see Style.FontFamily; retrieved
from Font_Name)
FontSize String (see Style.FontSize; retrieved
from Font_Size)
FontWeight String (see Style.FontWeight; retrieved
from Font_Flags)
FontStyle String (see Style.FontStyle;
retrieved from Font_Flags)
TextDecoration String (see Style.TextDecoration;
retrieved from Font_Flags)
FormattedValue String (from Formatted_Value)
Key Object (this is supposed to be
retrieved from Member_Key; however right now you have to use the collection
syntax to retrieve the value as =Fields!MemberName("Member_Key") )
LevelNumber Integer (from Level_Number)
ParentUniqueName String (from Parent_Unique_Name)
Note: the Member_Key field that shows up in the designer fields list won't
be mapped correctly at report execution. Therefore the value will just be
NULL right now. You have to use the following syntax to retrieve the value:
=Fields!MemberName("Member_Key").
#2: You could define custom properties (IDs) in your cube and retrieve them
through the MDX query (not sure which steps are exactly needed to set this
up in your cube - you may want to lookup AS 2005 BOL). These custom
properties would not get mapped to predefined property names. Therefore, you
would need to use the property collection syntax to access them, e.g.
=Fields!MemberName("MyID"). Note: even if your ids are numbers, I believe
the AS data provider will return them as strings. Anyway, you could then use
e.g. =CInt(Fields!MemberName("MyID")) for the grouping.
#3: In general, there is no difference in behavior for these expressions:
=Fields!FieldName.PropertyName -- static field/property reference
=Fields("FieldName").PropertyName -- dynamic field reference
=Fields!FieldName("PropertyName") -- dynamic property reference
=Fields(Fields!SomeOtherField.Value).PropertyName -- dynamic field name is
determined by the value of another field
However, we implemented several levels of performance optimizations that
would e.g. only retrieve those property values that are really used in the
report (e.g. if you use static references only throughout the entire
report). Using a dynamic property reference will turn off the optimization
for a certain field. Due to a bug, the usage of ParentUniqueName
specifically in the parent-child recursive grouping is not detected. By
using the dynamic property reference, you turn off the optimization, we will
retrieve the ParentUniqueName property value for the field also, and
everything will work. The fix for the detection will be included in a
service pack - in the meantime, just use the dynamic property reference to
turn off the optimization.
We will look into making the extended properties more "discoverable" through
visual indications at report design time in future releases. This includes
detecting/setting up recursive hierarchies more easily from AS data.
Hope this answers your questions,
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Raghu" <Raghu@.discussions.microsoft.com> wrote in message
news:25F0BE41-DDA1-491E-9043-A7E36AA633E7@.microsoft.com...
> Thank you Robert, that was very helpful. I got past a hurdle. I do have a
> couple of folllowup questions inspired by your response (and my somewhat
> novice knowledge of OLAP).
> Raghu
> --
> 1. Dimension properties MEMBER_UNIQUE_NAME and PARENT_UNIQUE_NAME do get
> added in the MDX as you mention. Any idea why they don't show up as fields
> in
> the dataset? Your grouping expression used Employee.UniqueName. How are
> we
> to know what the name of those fields are? Should we just strip of the
> MEMBER
> keyword and any underscores and use as field names, or is there a list
> somewhere? I'll try to search the docs as well. Also, when I tried to use
> MEMBER_KEY property, it showed up in the result set unlike the others.
> 2. The grouping is based on names, which are a character based. Would this
> affect performance adversely if the report data is large? One of the
> reasons
> I wanted to use IDs was due to the names not being unique (But
> MEMBER_UNIQUE_NAME seems to imply that Analysis services is generating one
> in
> internally.)
> 3. I was somewhat surprised at the difference in behavior when we use
> Employees("Value") and Employees.Value. Could you please shed some more
> light
> on this as to what's happening here? I think when you use the first
> method,
> you are basically using the indexer property in VB.NET syntax (please
> correct
> me if I'm wrong.), but that's as far as I could get. Is this behavior
> documented somewhere?
> "Robert Bruckner [MSFT]" wrote:
>> BTW: you don't need to have the two additional hidden table columns with
>> UniqueName and ParentUniqueName in the report (as shown in my sample). It
>> is
>> sufficient to e.g. just change the expression in the first table column
>> from
>> =Fields!Employees.Value to =Fields!Employees("Value"). This will turn off
>> a
>> certain optimization related to extended field properties.
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
>> news:e4fKfdq1FHA.2964@.TK2MSFTNGP09.phx.gbl...
>> >I attached a sample report that shows how to do this.
>> > Notes:
>> > * in the graphical query designer you have to select a recursive
>> > hierarchy
>> > from the cube (such as Employee.Employees in the AdventureWorks DW
>> > cube).
>> > Based on the metadata, RS should automatically detect that this is a
>> > recursive parent-child hierarchy and there will be additional extended
>> > field properties available (such as .UniqueName and .ParentUniqueName)
>> > * add a grouping in the report. The grouping expression is based on
>> > =Fields!Employees.UniqueName and for the parent of the group set it to
>> > =Fields!Employees.ParentUniqueName
>> > * right now, you will need to have table columns that also reference
>> > the
>> > UniqueName and the ParentUniqueName in the report - but you can hide
>> > them
>> > so they are not visible. If you don't have these columns, the recursive
>> > hierarchy won't work correctly.
>> >
>> > -- Robert
>> > This posting is provided "AS IS" with no warranties, and confers no
>> > rights.
>> >
>> >
>> > "Raghu" <Raghu@.discussions.microsoft.com> wrote in message
>> > news:A3B4E2B3-B78D-4571-9D90-69EC03FD18E4@.microsoft.com...
>> >> I'm trying to use an OLAP cube as my data source (built using SSAS
>> >> 2005)
>> >> The cube has a parent-child dimension and a fact table. I would like
>> >> to
>> >> display the parent-child data in a report . Does anyone know how to
>> >> build
>> >> an
>> >> MDX query that would give me the ID and the ParentID from the
>> >> dimension
>> >> to
>> >> build the parent-child grouping relationship in the report? The MDX
>> >> builder
>> >> does not let me access the ID field or the ParentID field. I always
>> >> get
>> >> the
>> >> Name of the dimension.
>> >>
>> >> Also, if there's another way to do this, please let me know.
>> >>
>> >> Any help is greatly appreciated.
>> >>
>> >> Thanks
>> >
>> >
>> > ==========================================================>> >
>> > <?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="DataSource1">
>> > <ConnectionProperties>
>> > <IntegratedSecurity>true</IntegratedSecurity>
>> > <ConnectString>Data Source=(local);Initial Catalog="Adventure
>> > Works
>> > DW"</ConnectString>
>> > <DataProvider>OLEDB-MD</DataProvider>
>> > </ConnectionProperties>
>> >
>> > <rd:DataSourceID>5aaa5577-4975-4616-a136-0b713dab6948</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="table1">
>> > <Footer>
>> > <TableRows>
>> > <TableRow>
>> > <TableCells>
>> > <TableCell>
>> > <ReportItems>
>> > <Textbox Name="textbox7">
>> > <rd:DefaultName>textbox7</rd:DefaultName>
>> > <ZIndex>9</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>8</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>7</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="textbox11">
>> > <rd:DefaultName>textbox11</rd:DefaultName>
>> > <ZIndex>6</ZIndex>
>> > <Style>
>> > <TextAlign>Center</TextAlign>
>> > <PaddingLeft>2pt</PaddingLeft>
>> > <PaddingBottom>2pt</PaddingBottom>
>> > <PaddingRight>2pt</PaddingRight>
>> > <PaddingTop>2pt</PaddingTop>
>> > </Style>
>> > <CanGrow>true</CanGrow>
>> > <Value />
>> > </Textbox>
>> > </ReportItems>
>> > </TableCell>
>> > <TableCell>
>> > <ReportItems>
>> > <Textbox Name="textbox13">
>> > <rd:DefaultName>textbox13</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>
>> > </TableCells>
>> > <Height>0.25in</Height>
>> > </TableRow>
>> > </TableRows>
>> > </Footer>
>> > <DataSetName>DataSet1</DataSetName>
>> > <Top>0.5in</Top>
>> > <Details>
>> > <TableRows>
>> > <TableRow>
>> > <TableCells>
>> > <TableCell>
>> > <ReportItems>
>> > <Textbox Name="Employees">
>> > <rd:DefaultName>Employees</rd:DefaultName>
>> > <ZIndex>4</ZIndex>
>> > <Style>
>> > <PaddingLeft>=2 + Level() * 8 &
>> > "pt"</PaddingLeft>
>> > <PaddingBottom>2pt</PaddingBottom>
>> > <PaddingRight>2pt</PaddingRight>
>> > <PaddingTop>2pt</PaddingTop>
>> > </Style>
>> > <CanGrow>true</CanGrow>
>> > <Value>=Fields!Employees.Value</Value>
>> > </Textbox>
>> > </ReportItems>
>> > </TableCell>
>> > <TableCell>
>> > <ReportItems>
>> > <Textbox Name="textbox10">
>> > <ZIndex>3</ZIndex>
>> > <Style>
>> > <PaddingLeft>2pt</PaddingLeft>
>> > <PaddingBottom>2pt</PaddingBottom>
>> > <PaddingRight>2pt</PaddingRight>
>> > <PaddingTop>2pt</PaddingTop>
>> > </Style>
>> > <CanGrow>true</CanGrow>
>> > <Value>=Fields!Employees.UniqueName</Value>
>> > </Textbox>
>> > </ReportItems>
>> > </TableCell>
>> > <TableCell>
>> > <ReportItems>
>> > <Textbox Name="textbox5">
>> > <rd:DefaultName>textbox5</rd:DefaultName>
>> > <ZIndex>2</ZIndex>
>> > <Style>
>> > <PaddingLeft>2pt</PaddingLeft>
>> > <PaddingBottom>2pt</PaddingBottom>
>> > <PaddingRight>2pt</PaddingRight>
>> > <PaddingTop>2pt</PaddingTop>
>> > </Style>
>> > <CanGrow>true</CanGrow>
>> > <Value>=Fields!Employees.ParentUniqueName</Value>
>> > </Textbox>
>> > </ReportItems>
>> > </TableCell>
>> > <TableCell>
>> > <ReportItems>
>> > <Textbox Name="Reseller_Sales_Amount">
>> >
>> > <rd:DefaultName>Reseller_Sales_Amount</rd:DefaultName>
>> > <ZIndex>1</ZIndex>
>> > <Style>
>> > <TextAlign>Center</TextAlign>
>> > <PaddingLeft>2pt</PaddingLeft>
>> > <PaddingBottom>2pt</PaddingBottom>
>> > <PaddingRight>2pt</PaddingRight>
>> > <PaddingTop>2pt</PaddingTop>
>> > </Style>
>> > <CanGrow>true</CanGrow>
>> > <Value>=Level()</Value>
>> > </Textbox>
>> > </ReportItems>
>> > </TableCell>
>> > <TableCell>
>> > <ReportItems>
>> > <Textbox Name="Reseller_Sales_Amount_1">
>> >
>> > <rd:DefaultName>Reseller_Sales_Amount_1</rd:DefaultName>
>> > <Style>
>> > <TextAlign>Right</TextAlign>
>> > <PaddingLeft>2pt</PaddingLeft>
>> > <PaddingBottom>2pt</PaddingBottom>
>> > <PaddingRight>2pt</PaddingRight>
>> > <PaddingTop>2pt</PaddingTop>
>> > </Style>
>> > <CanGrow>true</CanGrow>
>> >
>> > <Value>=Sum(Fields!Reseller_Sales_Amount.Value)</Value>
>> > </Textbox>
>> > </ReportItems>
>> > </TableCell>
>> > </TableCells>
>> > <Height>0.25in</Height>
>> > </TableRow>
>> > </TableRows>
>> > <Grouping Name="table1_Details_Group">
>> > <Parent>=Fields!Employees.ParentUniqueName</Parent>
>> > <GroupExpressions>
>> >
>> > <GroupExpression>=Fields!Employees.UniqueName</GroupExpression>
>> > </GroupExpressions>
>> > </Grouping>
>> > </Details>
>> > <Header>
>> > <TableRows>
>> > <TableRow>
>> > <TableCells>
>> > <TableCell>
>> > <ReportItems>
>> > <Textbox Name="textbox1">
>> > <rd:DefaultName>textbox1</rd:DefaultName>
>> > <ZIndex>14</ZIndex>
>> > <Style>
>> > <PaddingLeft>2pt</PaddingLeft>
>> > <PaddingBottom>2pt</PaddingBottom>
>> > <PaddingRight>2pt</PaddingRight>
>> > <PaddingTop>2pt</PaddingTop>
>> > </Style>
>> > <CanGrow>true</CanGrow>
>> > <Value>Employees</Value>
>> > </Textbox>
>> > </ReportItems>
>> > </TableCell>
>> > <TableCell>
>> > <ReportItems>
>> > <Textbox Name="textbox4">
>> > <rd:DefaultName>textbox4</rd:DefaultName>
>> > <ZIndex>13</ZIndex>
>> > <Style>
>> > <PaddingLeft>2pt</PaddingLeft>
>> > <PaddingBottom>2pt</PaddingBottom>
>> > <PaddingRight>2pt</PaddingRight>|||Robert-
Fabulous post. Was wondering if you had any suggestions for using
parent-child dimensions in Report Builder...
(Of course, I'm talking about using a model which is generated on top of an
SSAS cube.) If I'm looking at a standard dimension, each level in the
hierarchy will be a separate attribute (such as Region and City), so it's
easy to just drop Region and City onto a Report Builder report and get the
nice nesting I want. However, with parent-child dimensions, each level isn't
a separate attribute... and since Report Builder doesn't render any
hierarchies in a dimension (whether it's a user-defined hierarchy or the
hierarchy built by the marking an attribute as the Parent), I can't figure
out a way to build a Report Builder report which would have a Region column
and a City column if both Region and City are part of the parent child
dimension. Any suggestions would be appreciated.
The reason we had to make it a parent-child dimension is because one measure
group can have facts at several levels... so the only way we could do this is
with a parent-child hierarchy as standard dimensions can only have data at
one grain per measure group. (Correct me if I'm wrong.)
Thanks for any suggestions.
"Robert Bruckner [MSFT]" wrote:
> I attached a sample report that shows how to do this.
> Notes:
> * in the graphical query designer you have to select a recursive hierarchy
> from the cube (such as Employee.Employees in the AdventureWorks DW cube).
> Based on the metadata, RS should automatically detect that this is a
> recursive parent-child hierarchy and there will be additional extended field
> properties available (such as .UniqueName and .ParentUniqueName)
> * add a grouping in the report. The grouping expression is based on
> =Fields!Employees.UniqueName and for the parent of the group set it to
> =Fields!Employees.ParentUniqueName
> * right now, you will need to have table columns that also reference the
> UniqueName and the ParentUniqueName in the report - but you can hide them so
> they are not visible. If you don't have these columns, the recursive
> hierarchy won't work correctly.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Raghu" <Raghu@.discussions.microsoft.com> wrote in message
> news:A3B4E2B3-B78D-4571-9D90-69EC03FD18E4@.microsoft.com...
> > I'm trying to use an OLAP cube as my data source (built using SSAS 2005)
> > The cube has a parent-child dimension and a fact table. I would like to
> > display the parent-child data in a report . Does anyone know how to build
> > an
> > MDX query that would give me the ID and the ParentID from the dimension to
> > build the parent-child grouping relationship in the report? The MDX
> > builder
> > does not let me access the ID field or the ParentID field. I always get
> > the
> > Name of the dimension.
> >
> > Also, if there's another way to do this, please let me know.
> >
> > Any help is greatly appreciated.
> >
> > Thanks
>
> ==========================================================> <?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="DataSource1">
> <ConnectionProperties>
> <IntegratedSecurity>true</IntegratedSecurity>
> <ConnectString>Data Source=(local);Initial Catalog="Adventure Works
> DW"</ConnectString>
> <DataProvider>OLEDB-MD</DataProvider>
> </ConnectionProperties>
> <rd:DataSourceID>5aaa5577-4975-4616-a136-0b713dab6948</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="table1">
> <Footer>
> <TableRows>
> <TableRow>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox7">
> <rd:DefaultName>textbox7</rd:DefaultName>
> <ZIndex>9</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>8</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>7</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="textbox11">
> <rd:DefaultName>textbox11</rd:DefaultName>
> <ZIndex>6</ZIndex>
> <Style>
> <TextAlign>Center</TextAlign>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox13">
> <rd:DefaultName>textbox13</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>
> </TableCells>
> <Height>0.25in</Height>
> </TableRow>
> </TableRows>
> </Footer>
> <DataSetName>DataSet1</DataSetName>
> <Top>0.5in</Top>
> <Details>
> <TableRows>
> <TableRow>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="Employees">
> <rd:DefaultName>Employees</rd:DefaultName>
> <ZIndex>4</ZIndex>
> <Style>
> <PaddingLeft>=2 + Level() * 8 &
> "pt"</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>=Fields!Employees.Value</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox10">
> <ZIndex>3</ZIndex>
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>=Fields!Employees.UniqueName</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox5">
> <rd:DefaultName>textbox5</rd:DefaultName>
> <ZIndex>2</ZIndex>
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>=Fields!Employees.ParentUniqueName</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="Reseller_Sales_Amount">
> <rd:DefaultName>Reseller_Sales_Amount</rd:DefaultName>
> <ZIndex>1</ZIndex>
> <Style>
> <TextAlign>Center</TextAlign>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>=Level()</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="Reseller_Sales_Amount_1">
> <rd:DefaultName>Reseller_Sales_Amount_1</rd:DefaultName>
> <Style>
> <TextAlign>Right</TextAlign>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>=Sum(Fields!Reseller_Sales_Amount.Value)</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> <Height>0.25in</Height>
> </TableRow>
> </TableRows>
> <Grouping Name="table1_Details_Group">
> <Parent>=Fields!Employees.ParentUniqueName</Parent>
> <GroupExpressions>
> <GroupExpression>=Fields!Employees.UniqueName</GroupExpression>
> </GroupExpressions>
> </Grouping>
> </Details>
> <Header>
> <TableRows>
> <TableRow>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox1">
> <rd:DefaultName>textbox1</rd:DefaultName>
> <ZIndex>14</ZIndex>
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>Employees</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox4">
> <rd:DefaultName>textbox4</rd:DefaultName>
> <ZIndex>13</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="textbox2">
> <rd:DefaultName>textbox2</rd:DefaultName>
> <ZIndex>12</ZIndex>
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
