MDX query in i-reports(Report Query)

[shivareddyk]

HI

1. I wrote query like this [this query displaying data of one dimension with two measures)

"select NON EMPTY {[Measures].[balance],[Measures].[Peso Equiv]} ON COLUMNS,
              NON EMPTY {[depositesize].members} ON ROWS
 from [demo_p]"

2. I wrote query like this [this query displaying data of one dimension with two measures)

"select NON EMPTY {[Measures].[balance],[Measures].[Peso Equiv]} ON COLUMNS,
              NON EMPTY {[PRODUCT].members} ON ROWS
 from [demo_p]"

Note: The 2nd query also same as 1st one(but i use different dimension), if i executed separatly both mdx are dispalying corresponding data in pivote view.

 

Q:-When i am trying to mix both queries i got a problem(tree structure not displaying in right side) ?

i mixed like this (1 way)

select NON EMPTY {[Measures].[balance],[Measures].[Peso Equiv]} ON COLUMNS,
 NON EMPTY Hierarchize(Union({[depositesize].members,[depositesize].members})) ON ROWS
from [demo_p]

2nd way(with out Union)

 

Pls...Could u let me know what are the causes.

 

Regards

 

Code:

CUBE XML CODE:<Schema name="Deposit_pra">        <Cube name="demo_p" cache="true" enabled="true">                <Table name="f_deposit">                </Table>                <Dimension type="TimeDimension" foreignKey="RUNNING_DATE" name="REPORT DATE">                        <Hierarchy name="REPORT DATE" hasAll="true" allMemberName="All REPORT DATE" primaryKey="EXTRACTION_DATE" primaryKeyTable="extraction_date">                                <Join leftKey="extraction_date" rightKey="date">                                        <Table name="extraction_date">                                        </Table>                                        <Table name="d_time">                                        </Table>                                </Join>                                <Level name="year" table="d_time" column="YEAR" type="Integer" uniqueMembers="false" levelType="TimeYears" hideMemberIf="Never">                                </Level>                                <Level name="quarter" table="d_time" column="QUARTER" type="String" uniqueMembers="false" levelType="TimeQuarters" hideMemberIf="Never">                                </Level>                        </Hierarchy>                </Dimension>                <Dimension type="StandardDimension" foreignKey="" name="depositesize" caption="">                        <Hierarchy name="" hasAll="true" allMemberName="All depositesize" caption="">                                <Table name="f_deposit">                                </Table>                                <Level approxRowCount="" name="dep_size" table="f_deposit" column="AMOUNT_EXRATE_DESCR" nameColumn="" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="IfBlankName" formatter="">                                </Level>                        </Hierarchy>                </Dimension>                <Dimension type="StandardDimension" foreignKey="PRODUCT_ID" name="PRODUCT" caption="">                        <Hierarchy name="New Hierarchy 0" hasAll="true" primaryKey="PRODUCT_ID" primaryKeyTable="d_product" caption="">                                <Table name="d_product" schema="" alias="">                                </Table>                                <Level name="New Level 0" table="d_product" column="CRLINE_DESCR" nameColumn="" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="IfBlankName" formatter="" caption="">                                </Level>                        </Hierarchy>                </Dimension>                <Measure name="Balance" column="AMOUNT" datatype="Numeric" formatString="#,##0.00" aggregator="sum" visible="true">                </Measure>                <Measure name="pesoequiv" column="ACTUAL_AMOUNT" datatype="Numeric" formatString="#,##0.00" aggregator="sum" formatter="" caption="" visible="true">                </Measure>        </Cube></Schema>