I have a dimension in my OLAP cube that has 4 levels. At the lowest
level I have a member property created. I want to be able to create a
calculated measure that will count the number of rows in the dimension
with this property at the lowest level sent to 'Y', for each level in
the dimension.



Example:
Site Dimension

Country
State
City
Zipcode(with member property valid)

If the city of Phoenix has 3 zipcodes with member propery valid set to
Y, then I want to see a measure showing 3 at the City level for
Phoenix.

If the city of Tucson has 2 zipcodes with member property set to Y,
then I want to see the measue showing 2 at the city level of Tucson,
and 5 at the state level of Arizona, etc.

This is needed for another calcuation that I am working on, so it is
important that I know the number of rows at each level in the
dimension that has the member properties set to 'Y'.

Any help would be greatly appreciated.


Thanks!

Hi,

If this is a star schema dimension that means your values are on the level
of ZipCode,
why don't you just create another column in fact table where for each
ZipCode with 'Y' you put in value 1
and then you create a measure with sum function and that is it!


HTH,
Andrej




"Mitch" <mitchchristensen (AT) yahoo (DOT) com> wrote

Thanks for the suggestion. Unfortunately, that will not work for me. I
don't need to count the number of facts with that property set to 'Y', I
only need to count the number of rows in the dimension that has the
property set to 'Y' irregardless of the time, etc.

So if the dimensions has three records with a property set to 'Y', then
at the highest level in the dimension, my measure would show 3.


Thanks!


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

How about defining [Measures].[ValidZipCount] as follows:

[Site].CurrentMember.Properties("Valid") = "Y").Count
- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Thanks! That works great. I appreciate the help.





*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!