Measure should only be calculated for certain Dimension Attributes

Hi,

I've created a Dimension - Organisation (company_name,
department_name,room_name). The primary key is Organisation_ID and I
have a Measure Room Utilization. I'd like to show this Measure only
when Room_Name is selected and with every other member of this
Dimension there should occur a 0 for Room Utilization.

It works when I create a hierachy inside the Dimension with the
following MDX statement:

CREATE MEMBER CURRENTCUBE.[MEASURES].Room_Utilization
AS
case when [Organisation].CurrentMember.Level.Ordinal = 4
then [Measures].Room_Utilization
else 0
end
VISIBLE = 1;

how can I get it going when my Organisation Dimension has the following
attributes:
company_name,
department_name,
room_name

without hierachy and I only want to calculate the Measure Room
Utilization for the room_name attribute?

Thanks!

Here is how you can do it with Analysis Services 2005

Create RoomUtilization;

(Organization.[Room Name].[Room Name].MEMBERS, Measures.RoomUtilization) = Measures.PhysicalMeasureRoomUtilization;

And, of course, you would make PhysicalMeasureRoomUtilization a hidden one.

HTH,

Mosha (http://www.mosha.com/msolap)

sql

MDX Statement

Hi
I've four Dimensions
1. WAGroup (it's a Value type tree)
2. HDS (it's an organisation tree)
3. Dates
4. ValueLayer (a scenario selector)
I’ve to calculated Members
1. WAGroupVector based on the Dimension WAGroup-- Memberproperty
“WAGroupVector”
2. Vector based on the Dimension HDS-Memberproperty “Vector”
Now I’ve the follwing MDX-Query:
WITH MEMBER Measures.WAGroupVector AS
'[WAGroup].CURRENTMEMBER.PROPERTIES("WAGroupVector")'
MEMBER Measures.Vector AS '[HDS].CURRENTMEMBER.PROPERTIES("Vector")'
SELECT{Measures.WAGroupVector,Measures.Vector,Measures.[Value]} ON
COLUMNS ,
NonEmptyCrossJoin(
NonEmptyCrossJoin(
DESCENDANTS(HDS.[85000 (20050228)]),
Dates.Members
),
WAGroup.Members
) ON ROWS
FROM [CostOnMonthlyBasis]
WHERE (ValueLayer.IST)
Now to my question:
Is there a way to show within the result ONLY the Calculated Members and the
Measures –Value without all the other dynamic fields? I’m getting back s
o
many fields. But I’m only interested for the mentioned ones. And if I remo
ve
the Joins and use only the Dates Dimension (ON ROWS) - I’m only getting th
e
top level values…
Thanks a lot for any suggestions….
Regards,
DominicIf you're using Reporting Services (i.e. flattened rowset), then this
version may eliminate unwanted columns:
[vbcol=seagreen]
SELECT{Measures.[Value]} ON COLUMNS ,
NonEmptyCrossJoin(
NonEmptyCrossJoin(
DESCENDANTS(HDS.[85000
(20050228)]),
Dates.Members
),
WAGroup.Members
)
DIMENSION PROPERTIES
[WAGroup].[WAGroupVector],
[HDS].[Vector]
ON ROWS
FROM [CostOnMonthlyBasis]
WHERE (ValueLayer.IST)[vbcol=seagreen]
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!