Aggregate function vs parent-child dimension (SSAS)

Hi!
I have to create a report using a cross-join of an attribute hierarchy and a
parent-child hierarchy. For the parent-child hierarchy, I use a nested detail
group in my report. (group by uniquename and parent group by parents
uniquename) The attribute hierarchy is shown as a group around the detail
level.
For regular crossjoins of non-jagged hierarchies, I can use Aggregate() to
get the value of the dataset line with (null) shown as the all level. In this
case though, the parent-child hierarchy's all member is shown as "All". If I
use aggregate in the attribute hierarchy group now, the value returned is
blank.
Is there a way to make the parent-child all memeber show as null, or any
other way to eliminate it so Aggregate() will work? Eventually, any other way
to implement this report?
--
Lars-ErikHello Lars,
I would like to get more detailed information to assist this issue.
What's the MDX query you use to get the data?
And how you join the parent-child hierarchy to others?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi!
Not sure what you mean by how I join. There's an intermediate dimension
between the measures and the parent-child dimension.
The query looks something like this:
select {} on 0, crossjoin([AttributeDim].[AttributeHierarchy].Members,
[ParentChildDim].[Hierarchy].Members) on 1 from [MyCube]
Result in SSRS will be
[Attribute], [ParentChildAtt]
(null), All
Att1, All
Att2, All
Att1, Level 2
Att1, Level 2.2
If the parent-child hierarchy was a regular hierarchy, the All member would
be (null) too.. I guess it's because the members are flattened to the same
column instead of separate ones as for a regular hierarchy.
As far as I remember, RS 2000 created a column for each level that existed
at design time. 2005 puts all in one column.
--
Lars-Erik
"Wei Lu [MSFT]" wrote:
> Hello Lars,
> I would like to get more detailed information to assist this issue.
> What's the MDX query you use to get the data?
> And how you join the parent-child hierarchy to others?
>
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Lars,
This issue may related to the MDX Query you use. I am consulting some
internal person.
I appreciate your patience.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Lars,
Here is an example of query created using Adventure Works by means of
dragging "Internet Order Count" measure, "Employees" parent-child hierarchy
and "Employee Title" attribute hierarchy.
SELECT NON EMPTY { [Measures].[Internet Order Count] } ON COLUMNS, NON
EMPTY { (DESCENDANTS([Employee].[Employees].[Employee Level 02].ALLMEMBERS)
* [Employee].[Title].[Title].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
Hope this helps.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello,
I would like to know whether you have resolved this issue or not. If you
have any question, please feel free to let me know.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi again!
Thanks for your effort. Sorry for being late. I've been on vacation for
three weeks.
In my query, the attribute hierarchy is the leftmost rowheader and the
parent-child hierarchy should be second. Not sure if that is relevant.
Anyway, we're not at a solution yet.
Have a look at this modified query:
SELECT NON EMPTY { [Measures].[Internet Order Count] } ON COLUMNS, NON EMPTY
{ ([Employee].[Title].ALLMEMBERS * {[Employee].[Employees].[All Employees],
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
I need the all level from the title attribute, so I used the attribute
hierarchy instead of the attribute alone, yealding a (null) item with an
aggregate of all titles. I also added the All Employees member to get
aggregates for all employees per title, and all titles per employee (ie. boss
with all subordinates titles).
The problem is that the All Employees member is returned as "All Employees",
while the All Titles member is returned as (null). SSRS will use the
Aggregate function fine whith (null) representations, but the result of
Aggregate(field) is blank for the top level of the parent-child hierarchy.
(Due to the all emps. member not being null)
--
Lars-Erik
"Wei Lu [MSFT]" wrote:
> Hello,
> I would like to know whether you have resolved this issue or not. If you
> have any question, please feel free to let me know.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Lars,
With the MDX query on my side, I also see "All Employees" as "all" level of
Title attribute in the result if I run the MDX query in management studio.
However, in VS Dataset view, I did see the behavior you described.
It seems the SSRS dataset query has different process method for
parent-child dimension such as Employees.
I'm not quite sure about what you get to by using aggregation function. If
you don't want the All level to be included in the aggregation. You may
want to set IsAggregatable to false of the IsAggregatable property of the
attribute at the top-most level
In Microsoft SQL Server 2005 Analysis Services (SSAS), the (All) level is
an optional, system-generated level. It contains only one member whose
value is the aggregation of the values of all members in the immediately
subordinate level. This member is called the All member. It is a
system-generated member that is not contained in the dimension table.
Because the member in the (All) level is at the top of the hierarchy, the
member's value is the consolidated aggregation of the values of all members
in the hierarchy. The All member often serves as the default member of a
hierarchy.
The presence of an (All) level in an attribute hierarchy depends on the
IsAggregatable property setting for the attribute and the presence of an
(All) level in a multilevel hierarchy depends on the IsAggregatable
property of the attribute at the top-most level of multilevel hierarchy.
If the IsAggregatable property is set to True, an (All) level will exist. A
hierarchy has no (All) level if the sAggregatable property is set to False.
If this does not meet your requirement, please let's know more details
about the report/aggregation you'd like to get so that we may be able to
find other workarounds. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi again!
The Aggregate function is SSRS seems to look for the record with (null) in
all grouped fields and return the aggregate value from the dataset instead of
doing like Sum and the others. (Summing the values of the grouped records
itself instead of taking the SSAS one)
I might have been too eager on using it though. I've been fiddling a bit
with the last AW query we used, and the value Aggregate would've returned if
it worked with parent-child hierarchies is actually the same as First
returns. As long as the hierarchy isn't broken by ie. Order at least.
I think I can solve my problem that way for now.
To clear things up, I am after the All members value, but if you want the
value for (All titles, All employees) or (Sales Representative, All
employees) you won't get it in a group row with Aggregate, and the value will
be wrong if you use Sum, but it seems to be correct with First. :)
Here's an RDL showing exactly what I wanted. To see the erroneous behavior
of Aggregate, remove the Group1 filter and swap First for Aggregate. I tried
to hack the behavior with Iifs to make All null, but wouldn't do. ;)
<?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="AdventureWorks">
<DataSourceReference>AdventureWorks</DataSourceReference>
<rd:DataSourceID>176784d1-3d01-42a1-aab4-eb2e55edaa2f</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>2.5cm</BottomMargin>
<RightMargin>2.5cm</RightMargin>
<PageWidth>21cm</PageWidth>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>21cm</InteractiveWidth>
<rd:GridSpacing>0.25cm</rd:GridSpacing>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ColumnSpacing>1cm</ColumnSpacing>
<ReportItems>
<Table Name="table1">
<Footer>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<rd:DefaultName>textbox5</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="textbox6">
<rd:DefaultName>textbox6</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">
<ZIndex>3</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=First(Fields!Reseller_Sales_Amount.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.63492cm</Height>
</TableRow>
</TableRows>
</Footer>
<DataSetName>EmployeeTitleSales</DataSetName>
<Top>2cm</Top>
<TableGroups>
<TableGroup>
<Grouping Name="table1_Group1">
<Filters>
<Filter>
<Operator>NotEqual</Operator>
<FilterValues>
<FilterValue>=Nothing</FilterValue>
</FilterValues>
<FilterExpression>=Fields!Title.Value</FilterExpression>
</Filter>
</Filters>
<GroupExpressions>
<GroupExpression>=Fields!Title.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
</TableGroups>
<Details>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="Title">
<rd:DefaultName>Title</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Iif(Fields!Employees.LevelNumber > 0, Nothing,
Fields!Title.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="TempEmployee">
<rd:DefaultName>TempEmployee</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<TextAlign>Left</TextAlign>
<PaddingLeft>=CStr(Fields!Employees.LevelNumber*5) +
"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="Reseller_Sales_Amount">
<rd:DefaultName>Reseller_Sales_Amount</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Reseller_Sales_Amount.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.63492cm</Height>
</TableRow>
</TableRows>
<Grouping Name="table1_Details_Group">
<Parent>=Fields!TempEmployeeParent.Value</Parent>
<GroupExpressions>
<GroupExpression>=Fields!TempEmployee.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Visibility>
<ToggleItem>TempEmployee</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
</Details>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<rd:DefaultName>textbox2</rd:DefaultName>
<ZIndex>8</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Title</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>7</ZIndex>
<Style>
<TextAlign>Left</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Employee</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<rd:DefaultName>textbox3</rd:DefaultName>
<ZIndex>6</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.63492cm</Height>
</TableRow>
</TableRows>
</Header>
<TableColumns>
<TableColumn>
<Width>5.25cm</Width>
</TableColumn>
<TableColumn>
<Width>5.25cm</Width>
</TableColumn>
<TableColumn>
<Width>5.25cm</Width>
</TableColumn>
</TableColumns>
<Height>1.90476cm</Height>
</Table>
</ReportItems>
<Height>5.1746cm</Height>
</Body>
<rd:ReportID>ac5ed0ff-058a-4574-b854-d56d3898731d</rd:ReportID>
<LeftMargin>2.5cm</LeftMargin>
<DataSets>
<DataSet Name="EmployeeTitleSales">
<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="<CommandType>MDX</CommandType><Type>Query</Type><QuerySpecification">http://schemas.microsoft.com/AnalysisServices/QueryDefinition"><CommandType>MDX</CommandType><Type>Query</Type><QuerySpecification
xsi:type="MDXQuerySpecification"><Select><Items><Item><ID
xsi:type="Level"><DimensionName>Employee</DimensionName><HierarchyName>Title</HierarchyName><HierarchyUniqueName>[Employee].[Title]</HierarchyUniqueName><LevelName>Title</LevelName><UniqueName>[Employee].[Title].[Title]</UniqueName></ID><ItemCaption>Title</ItemCaption><UniqueName>true</UniqueName></Item><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>TempEmployee</MeasureName><UniqueName>[Measures].[TempEmployee]</UniqueName></ID><ItemCaption>TempEmployee</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><Item><ID
xsi:type="Measure"><MeasureName>TempEmployeeParent</MeasureName><UniqueName>[Measures].[TempEmployeeParent]</UniqueName></ID><ItemCaption>TempEmployeeParent</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><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>WITH MEMBER
[Measures].[TempEmployee] AS
Iif([Employee].[Employees].CurrentMember.Level.Ordinal = 0 OR
[Measures].[Reseller Sales Amount] = 0, null,
[Employee].[Employees].CurrentMember.UniqueName)
MEMBER [Measures].[TempEmployeeParent] AS
Iif([Employee].[Employees].CurrentMember.Level.Ordinal = 1 OR
[Measures].[Reseller Sales Amount] = 0, null,
[Employee].[Employees].CurrentMember.Parent.UniqueName)
SELECT NON EMPTY { [Measures].[TempEmployee],
[Measures].[TempEmployeeParent], [Measures].[Reseller Sales Amount] } ON
COLUMNS, NON EMPTY
{ ([Employee].[Title].ALLMEMBERS * {[Employee].[Employees].[All Employees],
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:DesignerState>
<CommandText>WITH MEMBER [Measures].[TempEmployee] AS
Iif([Employee].[Employees].CurrentMember.Level.Ordinal = 0 OR
[Measures].[Reseller Sales Amount] = 0, null,
[Employee].[Employees].CurrentMember.UniqueName)
MEMBER [Measures].[TempEmployeeParent] AS
Iif([Employee].[Employees].CurrentMember.Level.Ordinal = 1 OR
[Measures].[Reseller Sales Amount] = 0, null,
[Employee].[Employees].CurrentMember.Parent.UniqueName)
SELECT NON EMPTY { [Measures].[TempEmployee],
[Measures].[TempEmployeeParent], [Measures].[Reseller Sales Amount] } ON
COLUMNS, NON EMPTY
{ ([Employee].[Title].ALLMEMBERS * {[Employee].[Employees].[All Employees],
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>AdventureWorks</DataSourceName>
</Query>
<Fields>
<Field Name="Title">
<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].[Title].[Title]" /></DataField>
</Field>
<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="TempEmployee">
<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].[TempEmployee]" /></DataField>
</Field>
<Field Name="TempEmployeeParent">
<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].[TempEmployeeParent]" /></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>15.75cm</Width>
<InteractiveHeight>29.7cm</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>2.5cm</TopMargin>
<PageHeight>29.7cm</PageHeight>
</Report>
--
Lars-Erik
""Peter YangMSFT]"" wrote:
> Hello Lars,
> With the MDX query on my side, I also see "All Employees" as "all" level of
> Title attribute in the result if I run the MDX query in management studio.
> However, in VS Dataset view, I did see the behavior you described.
> It seems the SSRS dataset query has different process method for
> parent-child dimension such as Employees.
> I'm not quite sure about what you get to by using aggregation function. If
> you don't want the All level to be included in the aggregation. You may
> want to set IsAggregatable to false of the IsAggregatable property of the
> attribute at the top-most level
> In Microsoft SQL Server 2005 Analysis Services (SSAS), the (All) level is
> an optional, system-generated level. It contains only one member whose
> value is the aggregation of the values of all members in the immediately
> subordinate level. This member is called the All member. It is a
> system-generated member that is not contained in the dimension table.
> Because the member in the (All) level is at the top of the hierarchy, the
> member's value is the consolidated aggregation of the values of all members
> in the hierarchy. The All member often serves as the default member of a
> hierarchy.
> The presence of an (All) level in an attribute hierarchy depends on the
> IsAggregatable property setting for the attribute and the presence of an
> (All) level in a multilevel hierarchy depends on the IsAggregatable
> property of the attribute at the top-most level of multilevel hierarchy.
> If the IsAggregatable property is set to True, an (All) level will exist. A
> hierarchy has no (All) level if the sAggregatable property is set to False.
> If this does not meet your requirement, please let's know more details
> about the report/aggregation you'd like to get so that we may be able to
> find other workarounds. Thank you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
>|||Hello Lars,
Great to see you have found a workaround on this issue. I managed to get
the report work on my side and I was able to reproduce the issue you
encountered. Currently I was not able to find ohter workaround for this
issue.
I tried to filter the [all] member in the Title and Employee attribute in
MDX query and Aggregate function still returns blank result.
WITH MEMBER
[Measures].[TempEmployee] AS
Iif([Employee].[Employees].CurrentMember.Level.Ordinal = 0 OR
[Measures].[Reseller Sales Amount] = 0, null,
[Employee].[Employees].CurrentMember.UniqueName)
MEMBER [Measures].[TempEmployeeParent] AS
Iif([Employee].[Employees].CurrentMember.Level.Ordinal = 1 OR
[Measures].[Reseller Sales Amount] = 0, null,
[Employee].[Employees].CurrentMember.Parent.UniqueName)
SELECT NON EMPTY { [Measures].[TempEmployee],
[Measures].[TempEmployeeParent], [Measures].[Reseller Sales Amount] } ON
COLUMNS, NON EMPTY
{ ( Except({[Employee].[Title].ALLMEMBERS}, {Employee.[Title].[All]}) * {
Except({[Employee].[Employees].[All Employees]},
{[Employee].[Employees].[All]} ),
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
It seems the issue is caused by parent-child attribute itself other than
the All member. I have reported this issue to the product channel. If there
is any update, we will let you know.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.