Aggregation Problem - Analysis Services

Learn Microsoft BI Forum Core Servers Analysis Servic... Aggregation Problem

7/6/2009 12:16 AM

	Qazafi
51 posts

Aggregation Problem

Hi All

I have a hierarchy with three level say

Plan

Scenario

Program

What I would like to know is there any possibility so that I can stop aggregation on Plan level, Mean I do not want to show the aggregation on Plan level. For Example

Plan Plan-1 160 ----------à I do not want to show this value

Scenario-1 Test 90

Program 101 50

102 40

Scenario-2 Trial 70

Program 203 30

204 40

If any one has any idea how I can stop aggregation on this level, I still need to show that level because that make my Key unique. I just wanted to show a blank value instead of 160.

Many thanks

7/8/2009 4:48 PM

	cutley
363 posts

Re: Aggregation Problem

One way to address this is to create a calculation that uses a SCOPE statement. Your scope would look something like this:

Scope
(
[DimName].[HierarchyName].[Plan].Members, [Measures].[MeasureName]
);
This = NULL;
End Scope;

7/8/2009 8:39 PM

	Qazafi
51 posts

Re: Aggregation Problem

This is What I have done

CREATE MEMBER CURRENTCUBE.[MEASURES].[Input Value]

AS [Measures].[Input],

VISIBLE = 0;

Scope

([Dimension Scenario-Program].[Scenario-Program Structure].[Plan]

.Members,[Measures].[Input]

);

This = NULL;

End Scope;

It work perfectly alright with this statement but it does not show my hierarchy inside my Excel 2007 until I set the property of Pivot table show item with no data in rows and column . By saying this I mean it does not even show the other two levels

Scenario-1 Test 90

Program 101 50

102 40

How can I show data on my other two level

Many thanks

Syed

7/9/2009 2:20 PM

	cutley
363 posts

Re: Aggregation Problem

Syed, it's strange how Excel works, since the original works fine in the BIDS browser. Anyway, I changed

This = Null;

This = "";

and now it works fine in the BIDS browser and Excel 2007. Let me know if that works for you.

7/9/2009 3:29 PM

	Qazafi
51 posts

Re: Aggregation Problem

Modified By Qazafi on 7/9/2009 2:09:42 PM)

Hi Craig

Now I am facing some other problem with this now. If I use the (Plan, Scenario, Program) hierarchy it is fine but I do have some other dimension and hierarchies. It is not working with any other hierarchies and attributes. It is displaying nothing. But if I drag those attribute under my (Plan, Scenario, Program) hierarchy then the data is displaying. I do not know what I am doing wrong

Please give me some suggestion

Many thanks

Qazafi

Page 1 of 3

Learn Microsoft BI Forum

Core Servers

Analysis Servic...

Aggregation Problem