Alias or Group SSAS Dimension at Query time.

In an MDX Query i am trying to alias (or group ) the returned dimension as shown below but i am getting the wrong result.I believe the issue is in the case statement logic.

Is there a way to alias (or group dynamically) dimension without creating a named column in DSV?

Any help will be appreciated.

WITH MEMBER [Measures].[Long] AS

IIF(

[Measures].[Risk Value]<0,

[Measures].[Risk Value],

null)

SET [GroupedRatings] AS

CASE

WHEN [Curve Family].[SP Rating].&[AA-] THEN [Curve Family].[SP Rating].&[AA]

WHEN [Curve Family].[SP Rating].&[AA+] THEN [Curve Family].[SP Rating].&[AA]

WHEN [Curve Family].[SP Rating].&[AAA+] THEN [Curve Family].[SP Rating].&[AAA]

WHEN [Curve Family].[SP Rating].&[AAA+] THEN [Curve Family].[SP Rating].&[AAA]

WHEN [Curve Family].[SP Rating].&[BB-] THEN [Curve Family].[SP Rating].&[BB]

WHEN [Curve Family].[SP Rating].&[BB+] THEN [Curve Family].[SP Rating].&[BB]

WHEN [Curve Family].[SP Rating].&[BBB+] THEN [Curve Family].[SP Rating].&[BBB]

ELSE NULL

END

SELECT { [Measures].[Long]} ON COLUMNS,

{ ([GroupedRatings]*[Book].[Desk].[Desk].Members) } --cross join grouped rating and desk members

ON ROWS

FROM [DM]

This is where the similarities between MDX and SQL can be confusing. What you really want to do is to create some calculated members to do your grouping and then create a set of these members.

eg.

WITH MEMBER [Measures].[Long] AS

IIF(

[Measures].[Risk Value]<0,

[Measures].[Risk Value],

null)

MEMBER [Curve Family].[SP Rating].&[AA] AS Aggregate({[Curve Family].[SP Rating].&[AA-],[Curve Family].[SP Rating].&[AA+]})

MEMBER [Curve Family].[SP Rating].&[AAA] AS Aggregate({[Curve Family].[SP Rating].&[AAA-],[Curve Family].[SP Rating].&[AAA+]}

MEMBER [Curve Family].[SP Rating].&[BB] AS Aggregate({[Curve Family].[SP Rating].&[BB-], [Curve Family].[SP Rating].&[BB+]})

MEMBER [Curve Family].[SP Rating].&[BBB] AS Aggregate({[Curve Family].[SP Rating].&[BBB+]})

SET [GroupedRatings] AS

{[Curve Family].[SP Rating].&[AA]
,[Curve Family].[SP Rating].&[AAA]
,[Curve Family].[SP Rating].&[BB]
,[Curve Family].[SP Rating].&[BBB]}

SELECT { [Measures].[Long]} ON COLUMNS,

{ ([GroupedRatings]*[Book].[Desk].[Desk].Members) } --cross join grouped rating and desk members

ON ROWS

FROM [DM]

|||

The case statement won't create new members dynamically, which it looks like you're trying to do. You could declare each member explicitly, like:

WITH MEMBER [Measures].[Long] AS

IIF(

[Measures].[Risk Value]<0,

[Measures].[Risk Value],

null)

Member [Curve Family].[SP Rating].[AA] as

Sum({[Curve Family].[SP Rating].&[AA-], [Curve Family].[SP Rating].&[AA+]}),

SOLVE_ORDER = 10

Member [Curve Family].[SP Rating].[AAA] as

Sum({[Curve Family].[SP Rating].&[AAA-], [Curve Family].[SP Rating].&[AAA+]}),

SOLVE_ORDER = 10

Member [Curve Family].[SP Rating].[BB] as

Sum({[Curve Family].[SP Rating].&[BB-], [Curve Family].[SP Rating].&[BB+]}),

SOLVE_ORDER = 10

Member [Curve Family].[SP Rating].[BBB] as

Sum({[Curve Family].[SP Rating].&[BBB-], [Curve Family].[SP Rating].&[BBB+]}),

SOLVE_ORDER = 10

SET [GroupedRatings] AS

{[Curve Family].[SP Rating].[AA], [Curve Family].[SP Rating].[AAA],

[Curve Family].[SP Rating].[BB], [Curve Family].[SP Rating].[BBB]}

SELECT { [Measures].[Long]} ON COLUMNS,

{ ([GroupedRatings]*[Book].[Desk].[Desk].Members) } --cross join grouped rating and desk members

ON ROWS

FROM [DM]

|||

Thanks Darren for pointing me in the right direction.I changed the code to the sample below to make it work properly.

WITH

MEMBER [Curve Family].[SP Rating].[AA] AS

Aggregate({FILTER([Curve Family].[SP Rating].&[AA-],([Measures].[Risk Value])<0),FILTER([Curve Family].[SP Rating].&[AA+],([Measures].[Risk Value])<0)})

MEMBER [Curve Family].[SP Rating].[AAA] AS

Aggregate({FILTER([Curve Family].[SP Rating].&[AAA-],([Measures].[Risk Value])<0),FILTER([Curve Family].[SP Rating].&[AAA+],([Measures].[Risk Value])<0)})

MEMBER [Curve Family].[SP Rating].[BB] AS

Aggregate({FILTER([Curve Family].[SP Rating].&[BB-],([Measures].[Risk Value])<0),FILTER([Curve Family].[SP Rating].&[BB+],([Measures].[Risk Value])<0)})

MEMBER [Curve Family].[SP Rating].[BBB]AS

Aggregate({FILTER([Curve Family].[SP Rating].&[BBB-],([Measures].[Risk Value])<0),FILTER([Curve Family].[SP Rating].&[BBB+],([Measures].[Risk Value])<0)})

SET [GroupedRatings] AS

{

[Curve Family].[SP Rating].[AA]

,[Curve Family].[SP Rating].[AAA]

,[Curve Family].[SP Rating].[BB]

,[Curve Family].[SP Rating].[BBB]

}

SELECT

NON EMPTY { [Measures].[Risk Value]} ON COLUMNS,

NON EMPTY {([GroupedRatings]*[Vdim Book].[Desk].[Desk].Members)} ON ROWS

FROM

[DM]

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 Problem with Hours and Minutes in Time Dimension

For the first time, I have created a time dimension that includes hours and minutes, in addition to the Year, Quarter, Month, and Date that I am familiar with.

I want to display the average of my measures at whatever level of Time the user chooses. I thus chose "AverageOfChildren" as the aggregation method for these measures.

Unfortunately, SSAS aggregates these measures by Sum over Hours and Minutes, and then by AverageOfChildren over Date, Month, Quarter, and Year.

I am confused.

How can I force it to perform an "AverageOfChildren" at all levels ?

First, have a look at this document(http://www.microsoft.com/technet/prodtechnol/sql/2005/realastd.mspx) and think about your design a second time(on page 34-35 in the word version)

Next. This is only a guess but how are your attribute relations between minutes, hours, date,quarter and year defined, if they are included in the same user hierarchy?

HTH

Thomas Ivarsson

Aggregation problem

Hi

We have a fact table contains 5 dimension keys and one measure.The measure is having value as 1for all records and it will always be 1.Total records in this fact table are 2316.

we processed cube successfully.

Problem : While viewing data in cube browser the measure count is aggregating.We want all the records for the measure is 1. But its showing aggregate values as 1, 2 6..

Thanks

karumuru

Try connecting from Excel or some other tools you have, to the cube.

Also, If you are using the default measure " Fact Count", it will aggregate, as it is a Count.

You can specify another measure and fill it with value 1 (this is just for testing, this is not a good practice), and specify the aggregateFunction property of the measure to DistinctCount.

(I hope you are aware of the Factless fact table concept)

Regards,

Jiju

|||

Jiju

yes, Our one measure is a factless fact and had value 1. This measure aggregation property we set to distinct count and count tried all. but its aggregating and we could not get 2316 rows

Thanks

Sridhar K

Aggregation issue with parent child dimension when not using primary key

When creating a parent child dimension I am not using the primary key of the underlying table. I define the key when I create the dimension. The parent/child relationship works fine but my measure aggregation does not work.

The dimension has a regular relation type to the measure and the primary key from the underlying dimension table is used in the relationship to join in the measure. Since I have not used the primary key in the dimension and have defined my own key the define relationship page warns me that I have selected a non-key granularity attribute and I must directly or indirectly relate all other attributes to it. When I attempt to relate the attributes on the dimension structure tab I receive errors that I have created an attribute loop.

Any ideas?

CLG3,

I too have been trying to solve this issue. It seems that using a non-key granularity attribute to link to the measure group was never intended to work with Parent-Child dimensions. Like you say, doing this causes conflicts with the restrictions on the dimension's attribute relationships.

What I have done solves the issue but it is a really nasty hack:

Alter your Fact table to store the dimension's Parent-Child key rather than the dimension's primary key. In the dimension usage tab, link to the measure group via the Parent-Child key rather than the dimension's key.|||Is there a reason preventing you from using the dimension primary key as the member key, even if it means having to add a new parent key onto the dimension?|||

Hi John,

I describe the reasons I need the member key to be different to the primary key in this post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1635466&SiteID=1

My latest approach is:

Use the dimension's primary key to reference the dimension from the fact. Create a view on the fact that joins to the dimension and get's the dimension's "Business Key" (which is what I use as the member key for the dimension) Build the Measure Group on the fact view rather than the actual fact.

Aggregation issue with parent child dimension when not using primary key

When creating a parent child dimension I am not using the primary key of the underlying table. I define the key when I create the dimension. The parent/child relationship works fine but my measure aggregation does not work.

The dimension has a regular relation type to the measure and the primary key from the underlying dimension table is used in the relationship to join in the measure. Since I have not used the primary key in the dimension and have defined my own key the define relationship page warns me that I have selected a non-key granularity attribute and I must directly or indirectly relate all other attributes to it. When I attempt to relate the attributes on the dimension structure tab I receive errors that I have created an attribute loop.

Any ideas?

CLG3,

I too have been trying to solve this issue. It seems that using a non-key granularity attribute to link to the measure group was never intended to work with Parent-Child dimensions. Like you say, doing this causes conflicts with the restrictions on the dimension's attribute relationships.

What I have done solves the issue but it is a really nasty hack:

Alter your Fact table to store the dimension's Parent-Child key rather than the dimension's primary key. In the dimension usage tab, link to the measure group via the Parent-Child key rather than the dimension's key.|||Is there a reason preventing you from using the dimension primary key as the member key, even if it means having to add a new parent key onto the dimension?|||

Hi John,

I describe the reasons I need the member key to be different to the primary key in this post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1635466&SiteID=1

My latest approach is:

Use the dimension's primary key to reference the dimension from the fact. Create a view on the fact that joins to the dimension and get's the dimension's "Business Key" (which is what I use as the member key for the dimension) Build the Measure Group on the fact view rather than the actual fact.

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

Aggregating two dimension members

Hi to everybody!

First of all I'd like to apologize for my english!
...Then, this is the problem of a newbie of Sql Server Analysis Services, cubes and so on...,
I've created a cube, and I can easily browse it: what I wanted to obtain is a sort of Profit and Loss statement: dimensions are Revenues, Costs and periods, while "amount" is the only measure. It works!
But my question is: how can I aggregate two member of a same dimension? I.e.: how to create a new "row" in my cube where there are cost A plus cost B?
Does it make sense?

Thanks a lot

Jane Mischis

you can create a DIMENSION calculated member in the CALCULATIONS tab and have its parent hierarchy refer to COST hierarchy in the cost dimensions

in the expressions put

(<uniquename of cost A> + <uniquename of cost B>)

the new calcultion will appear as an additional row in the cost dimension.

hope this helps

|||Thank you so much for the answer..

Actually, what you suggest me is what I had done before writing here, but it didn't work, because:
- when I browse the cube, the new calculation does appear as an additional row in my members cost dimension, but it's transparent, like if it was hidden... and when I try to drag and drop it in row fields column on the right, I can't do it. I can just drop it above, in the filter box...

What's wrong?

Thank you,

Jane Mischis
|||

did you try doing it on a different browser (example Proclarity), cos the default browser that comes with analysis services can't perform all jobs.

|||No I didn't try, but I can't try, because I don't have it and I can't think I need it to solve my problem.
But I think the problem is elsewhere, maybe defining what dimensions and mesaures are.
The most important dimension is really "costs", but just the name of them, numbers come out from measure "amount"... And if I try to add a calculated Measures member, it works (i.e. "amount/1000")... but what I'd like to reach is just aggregate two names and having related sum i amounts.

Maybe I've to study a lot more...

Thank you however, Christina

Jane

|||Oh, great, I solve it.
I had just to put "all" in father member...

Thanks again

Jane

AggregateFunction Does "None" Work?

Does the AggregateFunction of "None" actually work?

I have a fact table with a single related dimension. The dimension is for a set of products, with a four-level user hierarchy. The fact table has a measure that only makes sense at the lowest level of the product hierarchy. Thus, when designing the measure group, I created a measure from the fact table and set the AggregateFunction on the measure to "None". I expected to be able to browse down the product hierarchy and see the value of the measure at the lowest level of the hierarchy. Unfortunately, I don't see any values (everything appears to be Null).

To ensure the values were getting read, I created a second measure in the measure group using the AggregateFunction of "Sum". When I browse the product hierarchy, I indeed see the sum of the measure and I see the individual measure values if I drill down to the lowest level of the hiearchy.

Thus, it would appear to me that the AggregateFunction of "None" is not working properly. Or perhaps I am interpreting its use incorrectly (or doing something else wrong). But with a single fact table that has a single dimension, its not very complicated!

TIA for any help...

Dave Fackler

Hi Dave,

The "None" aggregations seems to return a value if there's only a single fact record at that leaf of the measure group (SP1). I seem to recall back in the days of RTM that, if there were multiple fact records at a leaf, their "Sum" was returned, rather than null. However, this no longer worked when a fact dimension was configured.

Hopefully, someone from MS can corroborate this behavior - maybe it's by design?

|||

Hi Dave/All

Did you get an answer to this issue? I have recently tried using AggregateFunction = None, and I to expected the function to behave as you described. I have a dimension which has a one-to-one mapping to the fact table, yet I am still unable to view the measure data.

Would anyone be able to advise how the Non function should be used?

Regards, Matt

|||

Hey All,

I'm having the same issue..Aggregate Function "None" doesnt work ? Any one got it to work...

I have a boolean one-one measure and want to display True/False

Thanks

AggregateFunction Does "None" Work?

Does the AggregateFunction of "None" actually work?

I have a fact table with a single related dimension. The dimension is for a set of products, with a four-level user hierarchy. The fact table has a measure that only makes sense at the lowest level of the product hierarchy. Thus, when designing the measure group, I created a measure from the fact table and set the AggregateFunction on the measure to "None". I expected to be able to browse down the product hierarchy and see the value of the measure at the lowest level of the hierarchy. Unfortunately, I don't see any values (everything appears to be Null).

To ensure the values were getting read, I created a second measure in the measure group using the AggregateFunction of "Sum". When I browse the product hierarchy, I indeed see the sum of the measure and I see the individual measure values if I drill down to the lowest level of the hiearchy.

Thus, it would appear to me that the AggregateFunction of "None" is not working properly. Or perhaps I am interpreting its use incorrectly (or doing something else wrong). But with a single fact table that has a single dimension, its not very complicated!

TIA for any help...

Dave Fackler

Hi Dave,

The "None" aggregations seems to return a value if there's only a single fact record at that leaf of the measure group (SP1). I seem to recall back in the days of RTM that, if there were multiple fact records at a leaf, their "Sum" was returned, rather than null. However, this no longer worked when a fact dimension was configured.

Hopefully, someone from MS can corroborate this behavior - maybe it's by design?

|||

Hi Dave/All

Did you get an answer to this issue? I have recently tried using AggregateFunction = None, and I to expected the function to behave as you described. I have a dimension which has a one-to-one mapping to the fact table, yet I am still unable to view the measure data.

Would anyone be able to advise how the Non function should be used?

Regards, Matt

|||

Hey All,

I'm having the same issue..Aggregate Function "None" doesnt work ? Any one got it to work...

I have a boolean one-one measure and want to display True/False

Thanks

AggregateFunction Does "None" Work?

Does the AggregateFunction of "None" actually work?

I have a fact table with a single related dimension. The dimension is for a set of products, with a four-level user hierarchy. The fact table has a measure that only makes sense at the lowest level of the product hierarchy. Thus, when designing the measure group, I created a measure from the fact table and set the AggregateFunction on the measure to "None". I expected to be able to browse down the product hierarchy and see the value of the measure at the lowest level of the hierarchy. Unfortunately, I don't see any values (everything appears to be Null).

To ensure the values were getting read, I created a second measure in the measure group using the AggregateFunction of "Sum". When I browse the product hierarchy, I indeed see the sum of the measure and I see the individual measure values if I drill down to the lowest level of the hiearchy.

Thus, it would appear to me that the AggregateFunction of "None" is not working properly. Or perhaps I am interpreting its use incorrectly (or doing something else wrong). But with a single fact table that has a single dimension, its not very complicated!

TIA for any help...

Dave Fackler

Hi Dave,

The "None" aggregations seems to return a value if there's only a single fact record at that leaf of the measure group (SP1). I seem to recall back in the days of RTM that, if there were multiple fact records at a leaf, their "Sum" was returned, rather than null. However, this no longer worked when a fact dimension was configured.

Hopefully, someone from MS can corroborate this behavior - maybe it's by design?

|||

Hi Dave/All

Did you get an answer to this issue? I have recently tried using AggregateFunction = None, and I to expected the function to behave as you described. I have a dimension which has a one-to-one mapping to the fact table, yet I am still unable to view the measure data.

Would anyone be able to advise how the Non function should be used?

Regards, Matt

|||

Hey All,

I'm having the same issue..Aggregate Function "None" doesnt work ? Any one got it to work...

I have a boolean one-one measure and want to display True/False

Thanks

Aggregate() vs. A Set in the WHERE caluse

Hi,

I recently wrote some VBA code for Excel, which allowed a user to specify a list of members from a particular dimension and then produce a report which aggregated some measures with the members of a different dimension on the rows. It did this by creating an MDX query with the listed members fed into the aggregate function and stored in a calculated member which was then added to the WHERE clause.

eg

WITH MEMBER [Dimension].[Selected_Members] As Aggregate({[Dimension].[Member1], [Dimension].[Member2],.....})
SELECT [Measures].[Measure1] ON COLUMNS,
NON EMPTY [AnotherDimension].Members ON ROWS
FROM [Cube]
WHERE ([Dimension].[Selected_Members])

We found that as the number of members specified increased, the report slowed down dramatically. So, remembering that AS2005 allows you to specify sets in the WHERE clause of your MDX we decided to change it to something like this:

SELECT [Measures].[Measure1] ON COLUMNS,
NON EMPTY [AnotherDimension].Members ON ROWS
FROM [Cube]
WHERE ({[Dimension].[Member1], [Dimension].[Member2],.....})

We were quite surprised to see a very significant improvement in speed and the same results.

So, it would appear that the end result is exactly the same, but I would like to understand what the difference is and why one is so much faster than the other.

Many thanks,

Stuart

|||

I am surprised to see performance difference between these two examples. I would think that the execution plans should be exactly the same. Perhaps there is something going on in the cube.

Please note, that Aggregate cannot always be exchanged to set in WHERE clause, the results could become different. But if the set to Aggregate over is as static as in your example, it should usually be a safe rewrite.

Aggregate with different function in different level

I have an area dimension with three levels: Area, County, and School
I want to aggregate the number of students with different function in
different level, like that in the school level, I want to aggregate student
number with max, in the County and Area level, and I want to aggregate the
student number with sum.
How can I do that?
you can create 2 measures, 1 with the max aggregation, the second with the
sum aggregation.
Create a calculated measure which use the max result if the user is at the
school level, and the sum at the other levels.
like this:
iif(Schools.Currentmember.level is Schools.School, MAXMEASURE, SUMMEASURE)
but this works if your sum is calculated from the fact table directly and
NOT the sum of the max of each school.
"ad" <ad@.wfes.tcc.edu.tw> a crit dans le message de news:
%233WnPoUrEHA.2724@.TK2MSFTNGP14.phx.gbl...
>I have an area dimension with three levels: Area, County, and School
> I want to aggregate the number of students with different function in
> different level, like that in the school level, I want to aggregate
> student
> number with max, in the County and Area level, and I want to aggregate the
> student number with sum.
> How can I do that?
>

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.