Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Calculated Members issue

  1. #1

    Default 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?
    Thanks in advance
    Last edited by HimBromBeere; 09-25-2012 at 09:11 AM. Reason: orthography

  2. #2

    Default

    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. #3

    Default

    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
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.