1. Member
Join Date
Aug 2012
Posts
35

## Calculated Members issue

Hi Community,

I have a question concerning the possibilities of calculating members while executing a mdx-query. In my data-cube there is a dimension called persons with a hieracrhie called title. Title is sth. like Mr, Mrs, but also Mr. Dc or Mrs. Prof and also Comp (company). Not I want to aggregate all my entries to the three group Man/Women and Company and calculate how many persons of every group exist inside my data-table.

I already tried to achieve this wit a calculated member as follows:
Code:
```WITH
MEMBER [Measures].[Man] AS
sum(filter([Persons.Title].Children, left([Persons.Title].CurrentMember.Name, 2) = "Mr"))
MEMBER [Measures].[Women] AS
sum(filter([Persons.Title].Children, left([Persons.Title].CurrentMember.Name, 3) = "Mrs"))
MEMBER [Measures].[Company] AS
sum(filter([Persons.Title].Children, left([Persons.Title].CurrentMember.Name, 4) = "Comp"))

select {[Measures].[Man], [Measures].[Women], [Measures].[Company]} on columns
from [Persons]```
To determine if a given entry belongs to group, I try extract the substring "Mr" from the title, if this fails I try extracting "Mrs" and if this fails too it might be a company.
The following is the output I recieve:
Code:
```Axis #0:
{}
Axis #1:
{[Measures].[Man]}
{[Measures].[Women]}
{[Measures].[Company]}
Row #0: 17,155
Row #0: 4,163
Row #0: 897```
That works so far as it gives me the aggregated number of all the groups but since there is no row-data I am not able to print these data inside a pieChart or any other.

Do you have any idea how I can achieve this aim?
Last edited by HimBromBeere; 09-25-2012 at 09:11 AM. Reason: orthography

2. Member
Join Date
Aug 2012
Posts
35
I got it. I defined the member as hierarchy of the dimension persons to add on rows, the number of persons as measure on columns.

3. Member
Join Date
Aug 2012
Posts
35
OK... it works - more or less reliable. But I wonder if it is possible to add a calculated member to a hierarchy. This would be nicer since man or women has nothing to do with the dimension person but with its hierarchy gender.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•