Aggregation within Dimension Attribute

I am trying to create kind of a running sum in a calculated member in a cube down a dimension attribute. In other words, if I had values 1-5, I would want to sum the corresponding financial values as I go down the list. See example below

Dimension Value Financial Value New Calc

1 100 525

2 50 425

3 75 375

4 100 300

5 200 200

Has anyone done anything like this?

Some more business details might help us understand your question. How does this strike you?

with
member [Measures].[New Calc] as Sum({null:[YourDimension].[Dimension Value].CurrentMember},[Measures].[Financial Value])
select {[Measures].[Financial Value], [Measures].[New Calc]} on columns,
[YourDimension].[Dimension Value].[Dimension Value].Members on rows
from YourCube

Aggregation of Calculated members of form: MEASURE op ATTRIBUTE VALUE

hi there. i do not understand how to use an attribute in a calculation. In the query below, I'm trying to say:

for all members of the date.calendar hierarchy, show me the sum of (sales amount fact * dealer price attribute for the product sold)

with

member [measures].[fact times attribute]

as cdbl([Measures].[Sales Amount]) * cdbl([Product].[Dealer Price].CurrentMember.memberValue)

select [measures].[fact times attribute] oncolumns,

[Date].[Calendar].allmembersonrows

from [Adventure Works]

where [Geography].[City].&[Beaverton]&[OR]

this gives a type mismatch error complaining that "All Products" cannot be cast to double. But I don't understand how to force the calculation to occur at the lowest level and for aggregation to be done subsequently.

Product is currently at the "All Product" member. What you are actually wanting to do is get the price for each product and multiply that by the sales of that product and then aggregate (i'll assume sum) those values. That query looks like the one below. (Actually, the one below is kinduva shortcut to the solution. Instead of going to the individual product, I just went to the product price. If two products have the same price, the query kinda says that we treat them the same. Nit-picky, I know.)

Good luck.

Code Snippet

withmember [measures].[fact times attribute] as

SUM([Product].[Dealer Price].[Dealer Price].Members,

[Measures].[Sales Amount])

select [measures].[fact times attribute] oncolumns,

NONEMPTY [Date].[Calendar].allmembersonrows

from [Adventure Works]

where ([Geography].[City].&[Beaverton]&[OR])

;

|||Dear Bryan,

Thanks for your pointers. I actually work in hong kong so were ~12 hours ahead of you. I'm checking this from home and so can't test it. but i'm reviewing it now, because I'd love to get this right for work tomorrow.

Looking at your solution, one thing I find very conceptually troubling:

>>I don't see a multiplication sign anywhere!!!!<<

So at the very point where I would imagine attribute and fact really connect, the code is silent! Also when you refer to a shortcut and being nitpicky, that is all just going straight over my head - my knowledge of mdx (rather like my knowledge of cantonese just isn't evolved enough to understand the subtleties here. I can just about order lunch with a lot of gesticulation but that's it.

i will of course kick the tires on this tomorrow when I get into work. But [embarrassingly] I've been trying to figure out what the missing link is here for almost 3 days without success! Would you mind just leaving a working "best practice" solution for me? I feel like I sound like a lazy person - I'm not, I just don't grock this yet and its driving me nuts.

Yours,

John G.
|||

Sorry about that. I was pushing to get the code assembled and totally dropped that one critical part of the code. Try this one. I use the CurrentMember of the Dealer Price to get to the value.

Code Snippet

withmember [measures].[fact times attribute] as

SUM(

[Product].[Dealer Price].[Dealer Price].Members,

[Measures].[Sales Amount]*[Product].[Dealer Price].CurrentMember.MemberValue

)

select [measures].[fact times attribute] on columns,

NON EMPTY [Date].[Calendar].allmembers on rows

from [Adventure Works]

where ([Geography].[City].&[Beaverton]&[OR])

;

|||

Jo,

Try to use named calculations in the datasourceview of your factTable... it would be a better performance, because you only run the formulas once when the cube is processing.

Regards!

|||

ok it works fine - THANKS!

I also tried this which returns the same results - is it logically identical?

with

member [measures].[fact times attribute]

asSUM([Product].[Product].[Product].Members,

[Measures].[Sales Amount]*[Product].[Dealer Price].CurrentMember.MemberValue

)

select {[measures].[fact times attribute]} oncolumns,

NONEMPTY [Date].[Calendar].allmembersonrows

from [Adventure Works]

where ([Geography].[City].&[Beaverton]&[OR])

i'm not sure if you'd have the patience of a saint to wade through what follows, but I'm trying to understand the execution of the query. Would you mind critiquing the below, or if it's easier for you just describe the above query in pseudo code for dummies

For my general understanding when you refer to [Product].[Dealer Price].CurrentMember.MemberValue,

what is the context of the CurrentMember? Is it:

1. The current member in the context of traversing the set of members of the product hierarchy as defined by the set argument passed to the SUM function.

or 2. the currentmember in some wider context of the query (actually I'm not sure if that makes sense, so I'll go with answer 1).

Assuming 1. above, then I would interpret the query as follows:

1. slice by beaverton

2. iterate through all members of the date hierarchy.

3. for each sales fact within the intersection of beaverton and the current date, calculate [fact times attribute] as follows:

a) for each member of the product hierarchy ...

i) ... extract the tuple set defined by the intersection of that product within the current region [for many product members this will be a null set]

ii) for that set of tuples, sum up (sales amount * dealer price).

iii) keep running total of that sum, and the final total will be your result of the current date member.

|||

thanks - this did occur to me, since as i understand it calculated members (even cube scoped ones) are calculated at runtime not at processing time, correct?

however, in my real calculation I'm using another calculated member which doesn't exist in the underlying table so this makes it tricky.

Would you say that in general, one should strive to do as much data cleaning / preparation / precalculation as possible outside the cube, and then just leave the cube to prepare aggregations and pure analysis calculations that are hard to do outside of mdx?

|||Yeah joGo, I'm with you! If you have other calculated members that you cannot replace for named calculation, so you are right...|||

Your code is logically similar and should produce the same result. You may want to test for performance differences, but the only way I can imagine a performance difference would exist would be under a very particular situation I suspect does not exist in the cube. (In other words, they probably have the same performance so don't sweat it.)

Regarding the CURRENTMEMBER question, you have to keep in mind context at all times. In the SUM function, you generate a set. That set definition is in the context of the cube as a whole. If I want to limit that context based on my slicer (WHERE clause), I can use the EXISTING keyword in the set definition.

So, now I have a set. Then, for each member in that set, I will return and/or calculate a value. That value is determined in the context of the member from the set I am currently working with. At this point, how that set came to be is unknown to me. The set has been determined and I'm just working through it blindly. I think this is what you're saying in the section at the bottom of your email.

Sorry I am being slow to respond today. The MSDN forum email seems to be jammed up a bit and I'm not getting alerts like I should.

Thanks,
Bryan

Aggregation dependent on dimension attribute

Hi,

I have little tricky situation here and I'll try to describe it as accurately as possible...

Using Analysis Services 2005, I need to provide a measure in which the aggregation is basically a sum, but sometimes based on a maximum within a dimension member. Here's the situation:

Table: Event
Available fields: Event Group, Date, Attendance

Attendance is the measure and Event Group and Date (Time) are dimensions. Time has a Year - Month - Day hierarchy.

Event Groups have an attribute "Same Attendance" that signifies that the same people attended all events in that Event Group.

Example:
Event Group: "VB.Net Course" - Same Attendance = true
Related Events:
Nov 10, 2006 - Attendance = 12
Nov 20, 2006 - Attendance = 11
Dec 10, 2006 - Attendance = 10

Event Group: "SSAS Road Show" - Same Attendance = false
Related Events:
Nov 15, 2006 - Attendance = 40
Nov 25, 2006 - Attendance = 50
Dec 15, 2006 - Attendance = 60

What I need is:

Total attendance (Event Group - AllMember; Time - AllMember): 162 ( = max(Attendance) from VB.Net Course + sum(Attendance) from SSAS Road Show)
Attendance for Nov (Event Group - AllMember; Time - Nov): 102
Attendance for Dec (Event Group - AllMember; Time - Nov): 72
Attendance for Nov 20, 2006 (Event Group - AllMember; Time - Nov 10, 2006): 12

...so regardless what I select in the Time dimension, I always want it to count only Attendance = 12 for the Event Groups that have Same Attendance = true.

Please be as detailed as possible with your reply.

Thanks,
Sven

"...so regardless what I select in the Time dimension, I always want it to count only Attendance = 12 for the Event Groups that have Same Attendance = true" - but what happens when a time member outside the range of dates for that Event Group is selected? So, for Oct. 2006, would you count 12 or 0?|||

0

Good point. I should have said "to count a maximum Attendance of 12"...and yes, at least one of the Events of that Event Group must be in the selected period.

Sven

|||

I would probably use the attendance field from the database to create two physical measures. The [Sum Attendance] measure would use the Sum aggregate function and the [Max Attendance] function would use the Max aggregate function. Make those measures Visible=false. Then add a calc measure which would look something like this:

create member CurrentCube.[Measures].[Attendance]
as
([Event Group].[Same Attendance].[True],[Measures].[Max Attendance])
+ ([Event Group].[Same Attendance].[False],[Measures].[Sum Attendance]);

Will that work for you?

|||

Yes, it works....thanks.

I just have to add a little bit if [Same Attendance] is selected as a Dimension itself and drilled down into the individual members. That won't be a problem. Right now it still shows the Max + Sum when it should show only one or the other dependent on the CurrentMember.

|||

Sorry, I have to correct myself (again).

It is correct as long as you only look at the data by Event Group, which was all I needed so far, but as soon as you have multiple Event Groups with the [Same Attendance] = true, then it only takes the max of all of these. It would be nice to get the correct number across Event Groups.

I saw an approach with a recursive calculation that I may try to tweak to work here: Calculate for the Event Group first and then add the numbers up using the same function.

Sven

|||

I added the recursive calculation some time ago, but finally found a minute to post it here:

This is the new calculation: [Max Participants Per Event]:

IIF([Event].[Event Code].CurrentMember.Level IS [Event].[Event Code].[Event Code],

[Measures].[Max Attendance], *the one from furmangg's post

SUM(Descendants([Event].[Event Code].CurrentMember,[Event].[Event Code].[Event Code]), Measures.[Max Participants Per Event])

)

Then I take the max + sum as suggested.

Sven

Aggregation dependent on dimension attribute

Hi,

I have little tricky situation here and I'll try to describe it as accurately as possible...

Using Analysis Services 2005, I need to provide a measure in which the aggregation is basically a sum, but sometimes based on a maximum within a dimension member. Here's the situation:

Table: Event
Available fields: Event Group, Date, Attendance

Attendance is the measure and Event Group and Date (Time) are dimensions. Time has a Year - Month - Day hierarchy.

Event Groups have an attribute "Same Attendance" that signifies that the same people attended all events in that Event Group.

Example:
Event Group: "VB.Net Course" - Same Attendance = true
Related Events:
Nov 10, 2006 - Attendance = 12
Nov 20, 2006 - Attendance = 11
Dec 10, 2006 - Attendance = 10

Event Group: "SSAS Road Show" - Same Attendance = false
Related Events:
Nov 15, 2006 - Attendance = 40
Nov 25, 2006 - Attendance = 50
Dec 15, 2006 - Attendance = 60

What I need is:

Total attendance (Event Group - AllMember; Time - AllMember): 162 ( = max(Attendance) from VB.Net Course + sum(Attendance) from SSAS Road Show)
Attendance for Nov (Event Group - AllMember; Time - Nov): 102
Attendance for Dec (Event Group - AllMember; Time - Nov): 72
Attendance for Nov 20, 2006 (Event Group - AllMember; Time - Nov 10, 2006): 12

...so regardless what I select in the Time dimension, I always want it to count only Attendance = 12 for the Event Groups that have Same Attendance = true.

Please be as detailed as possible with your reply.

Thanks,
Sven

"...so regardless what I select in the Time dimension, I always want it to count only Attendance = 12 for the Event Groups that have Same Attendance = true" - but what happens when a time member outside the range of dates for that Event Group is selected? So, for Oct. 2006, would you count 12 or 0?|||

0

Good point. I should have said "to count a maximum Attendance of 12"...and yes, at least one of the Events of that Event Group must be in the selected period.

Sven

|||

I would probably use the attendance field from the database to create two physical measures. The [Sum Attendance] measure would use the Sum aggregate function and the [Max Attendance] function would use the Max aggregate function. Make those measures Visible=false. Then add a calc measure which would look something like this:

create member CurrentCube.[Measures].[Attendance]
as
([Event Group].[Same Attendance].[True],[Measures].[Max Attendance])
+ ([Event Group].[Same Attendance].[False],[Measures].[Sum Attendance]);

Will that work for you?

|||

Yes, it works....thanks.

I just have to add a little bit if [Same Attendance] is selected as a Dimension itself and drilled down into the individual members. That won't be a problem. Right now it still shows the Max + Sum when it should show only one or the other dependent on the CurrentMember.

|||

Sorry, I have to correct myself (again).

It is correct as long as you only look at the data by Event Group, which was all I needed so far, but as soon as you have multiple Event Groups with the [Same Attendance] = true, then it only takes the max of all of these. It would be nice to get the correct number across Event Groups.

I saw an approach with a recursive calculation that I may try to tweak to work here: Calculate for the Event Group first and then add the numbers up using the same function.

Sven

|||

I added the recursive calculation some time ago, but finally found a minute to post it here:

This is the new calculation: [Max Participants Per Event]:

IIF([Event].[Event Code].CurrentMember.Level IS [Event].[Event Code].[Event Code],

[Measures].[Max Attendance], *the one from furmangg's post

SUM(Descendants([Event].[Event Code].CurrentMember,[Event].[Event Code].[Event Code]), Measures.[Max Participants Per Event])

)

Then I take the max + sum as suggested.

Sven

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.