[Mondrian] Help - Calculated Member

[Subramani(Adyar)]

                [X]
        Internal        Page 1 of 1
Hi,

Issue Description:

When we use Calculated Member in the MDX Query, Measure data is not displaying.

Dimension Name: Period

Dimension Level details:

Year
        Quarter

                Month

Measure Name : Count of Accounts

Following are the XMLA Output

Jan    10
Nov    30
Dec    40

Here Jan belongs to the Year 2008 and Nov,Dec belongs to year 2009.
The issue is Year and quarter is not coming in XMLA Ouput


Schema XML:

 <Dimension name="Period" type="TimeDimension" foreignKey="PERIOD_WID">

                                                <Hierarchy name="Period" hasAll="true" primaryKey="PERIOD_WID" >

                                                                <Table name="PERIOD_DIM_TB"/>

                                                                <Level name="Year" column="current_yr" type="Numeric" uniqueMembers="false" levelType="TimeYears"/>

                                                                <Level name="Quarter" column="current_yr_qtr" type="String" uniqueMembers="false" levelType="TimeQuarters"/>

                                                                <Level name="Month" column="monthname" type="String" uniqueMembers="false" levelType="TimeMonths"/>

                                                </Hierarchy>

                                </Dimension>

<Dimension name="Branch Hierarchy" foreignKey="REPORTING_SOL_WID">

                                                <Hierarchy name="Branch Hierarchy" hasAll="true" primaryKey="REPORTING_WID">

                                                                <Table name="REPORTING_DIM_VW"/>

                                                                <Level name="Head Office" column="HO_sol_name" type="String" uniqueMembers="false"/>

                                                                <Level name="Zonal Office" column="ZO_sol_name" type="String" uniqueMembers="false"/>

                                                                <Level name="Regional Office" column="RO_sol_name" type="String" uniqueMembers="false"/>

                                                                <Level name="Branch" column="BO_sol_name" type="String" uniqueMembers="false"/>

                                                </Hierarchy>

                                </Dimension>

                                <Measure name="No Of Accounts" column="no_of_accounts" aggregator="sum" formatString="##,##,###.00"/>

                                <CalculatedMember name="Count of Accounts" dimension="Measures" visible="true">

                                                <Formula>iif([Period.Period].currentMember.level.name = "Year",([Period.Period].currentMember.LastChild.lastchild, [Measures].[No Of Accounts]),iif([Period.Period].currentMember.level.name = "Quarter",([Period.Period].currentMember.LastChild, [Measures].[No Of Accounts]),iif([Period.Period].currentMember.level.name = "Month" ,([Period.Period].currentMember, [Measures].[No Of Accounts] ),([Period.Period].currentMember.LastChild.lastchild.lastchild,  [Measures].[No Of Accounts]))))</Formula>

                                </CalculatedMember>

MDX Query:

WITH

SET  [Period_Period_Year_1] AS  '
{  { [Period.Period] }  ,  HIERARCHIZE  (  AddCalculatedMembers  ( [Period.Period].[Year].Members )  )  }  '
SET  [Period_Period_Quarter_2] AS  '  { [Period_Period_Year_1] ,  HIERARCHIZE  (  AddCalculatedMembers  ( [Period.Period].[Quarter].Members )  )  }  '
SET  [Period_Period_Month_3] AS  '  { [Period_Period_Quarter_2] ,  HIERARCHIZE  (  AddCalculatedMembers  ( [Period.Period].[Month].Members )  )  }  '
SET  [Branch Hierarchy_Branch Hierarchy_Head Office_1] AS  '  {  { [Branch Hierarchy.Branch Hierarchy] }  ,  HIERARCHIZE  (  AddCalculatedMembers  ( [Branch Hierarchy.Branch Hierarchy].[Head Office].Members )  )  }  '
SET  [Branch Hierarchy_Branch Hierarchy_Zonal Office_2] AS  '  { [Branch Hierarchy_Branch Hierarchy_Head Office_1] ,  HIERARCHIZE  (  AddCalculatedMembers  ( [Branch Hierarchy.Branch Hierarchy].[Zonal Office].Members )  )  }  '
SET  [Branch Hierarchy_Branch Hierarchy_Regional Office_3] AS  '  { [Branch Hierarchy_Branch Hierarchy_Zonal Office_2] ,  HIERARCHIZE  (  AddCalculatedMembers  ( [Branch Hierarchy.Branch Hierarchy].[Regional Office].Members )  )  }  '
SET  [Branch Hierarchy_Branch Hierarchy_Branch_4] AS  '  { [Branch Hierarchy_Branch Hierarchy_Regional Office_3] ,  HIERARCHIZE  (  AddCalculatedMembers  ( [Branch Hierarchy.Branch Hierarchy].[Branch].Members )  )  }  '      SELECT   NON EMPTY [Period_Period_Month_3] DIMENSION  PROPERTIES  PARENT_UNIQUE_NAME  ON 0 ,  NON EMPTY [Branch Hierarchy_Branch Hierarchy_Branch_4] DIMENSION  PROPERTIES  PARENT_UNIQUE_NAME  ON 1 ,  { [MEASURES].[Count of Accounts] }  ON 2 FROM  [Loan Account Analysis]

Can any one help us to resolve the issue?

Regards
A.Subramanian


[X]



DISCLAIMER: 

Information transmitted by this e-mail may be proprietary to Ramco Systems Ltd., and / or the
authors of the information and is intended for use only by the individual or entity to which it is
addressed, and may contain confidential or legally privileged information. If you are not the 
intended recipient or it appears that this mail has been forwarded to you without proper authority, 
you are not authorised to access, read, disclose, copy, use or otherwise deal with it and any such 
actions are prohibited and may be unlawful. 

Internet communications cannot be guaranteed to be secure or error-free as information could be 
intercepted, corrupted, lost, arrive late or contain viruses. Ramco Systems Limited therefore does 
not accept liability for any errors, omissions, viruses or computer problems experienced as a 
result of this transmission.

If you have received this e-mail in error, please notify us immediately at 
mail to: mailadmin at rsi.ramco.com and delete this mail from your records. Notice is hereby 
given that no representation, contract or other binding obligation shall be created by this e-mail.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20100109/2a55d188/attachment.html