How to call measures on Mondrian?

Question

I'm try to create simple Mondrian schema with OLAP connection, but I have a problem.
I have two Measures. But when I called it, it just call one measures. I'm new on Mondrian and OLAP.
My schema look like this.

<Schema name="Test">
  <Cube name="Report1" visible="true" cache="true" enabled="true">
    <Table name="mstjobs" schema="public">
    </Table>
    <Dimension type="StandardDimension" visible="true" foreignKey="regionid" highCardinality="false" name="Reg">
      <Hierarchy name="hiReg" visible="true" hasAll="true" allMemberName="All Region" primaryKey="code1">
        <Table name="mstlov" schema="public" alias="">
        </Table>
        <Level name="Category" visible="true" table="mstlov" column="cat" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
        </Level>
        <Level name="ListRegion" visible="true" table="mstlov" column="desc1" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
        </Level>
      </Hierarchy>
    </Dimension>
    <Dimension type="StandardDimension" visible="true" foreignKey="titleid" highCardinality="false" name="Title">
      <Hierarchy visible="true" hasAll="true" allMemberName="All Title" primaryKey="code1">
        <Table name="mstlov" schema="public">
        </Table>
        <Level name="Category" visible="true" column="cat" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
        </Level>
        <Level name="Title" visible="true" column="desc1" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
        </Level>
      </Hierarchy>
    </Dimension>
    <Measure name="mtot" column="mantotal" datatype="Integer" aggregator="sum" visible="true">
    </Measure>
    <Measure name="wtot" column="womantotal" aggregator="sum" visible="true">
    </Measure>
  </Cube>
</Schema>`

I'm just called it with simple MDX query

    Select 
       Reg.[All Region].region on COLUMNS,
       Title.[ALl Title].titlekhas on ROWS
    from Report1

And the output look like this, with mtot measures.

|           |City1   |City2   |City3   |
|           |20      |30      |30      |
|Job1       |10      |20      |10      |
|Job2       |10      |10      |20      |

But I want to call all measures together. so, the output should look like.

|           |City1   |City2  |City3   |
|           |20  |10 |30 |20 |30 |25  |
|Job1       |10  |10 |20 |10 |10 |10  |
|Job2       |10  |0  |10 |10 |20 |15  |

What should I do? Should I change the schema?

score 0 · Accepted Answer · answered May 21 '14 at 13:05

0

If you don't specify your measure, it'll use the first one as the default.

Use

  Select 
   [Reg].[region] on COLUMNS,
   [Title].[titlekhas] on ROWS
from Report1
where [Measures].[wtot]

instead.

Alternatively, you can also use

  Select 
   {[Reg].[region]} * {[Measures].[wtot]} on COLUMNS,
   [Title].[titlekhas] on ROWS
from Report1

or

  Select 
   [Reg].[region] * {[Measures].[wtot], [Measures].[mtot] } on COLUMNS,
   [Title].[titlekhas] on ROWS
from Report1

to display both measures.

Remarks: - Even when not required, you should always use brackets when refering to dimensions, levels or members; - You don't need to add the All member name after the dimension, you can jump straight to the first level member; - I don't recommend using such "coded" names for measures, levels or dimensions. [Measures].[wtot] is much harder to read than [Measures].[Women Total].

answered May 21 '14 at 13:05

nsousa

4,448
1
10
15

Another remark: your split between Men and Women doesn't seem to belong in measures, but instead in a dimension. – nsousa May 21 '14 at 13:05
Thanks for your answer and advice. But what you mean with your "another remark"? Sorry I don't get it. My English is poor. – newbie May 22 '14 at 02:10
can you help me on another problem at [this page](http://stackoverflow.com/questions/23776132/no-such-tuple-on-axis-0-error-on-ireport)? If you wish I'll be thankful. – newbie May 22 '14 at 02:18
Men total and Women total should not be two separate measures. There should be one measure only, Total, and a dimension with two members, Men and Women. You filter by that dimension to get the totals for each gender. – nsousa May 22 '14 at 16:37
Sip thanks. But I don't know what should I do? This is my first time. Can you give me a simple code to do that? – newbie May 23 '14 at 04:10
1. You shouldn't have two columns for the wtot and mtot, but just one for total; 2. You should have a gender_id foreign key, pointing to a dim_gender table; 3. You should add a Gender tag; 4. You should replace your two measures by just one. Conclusion: It's not a simple change of code, your star schema needs changing too. – nsousa May 23 '14 at 10:42

How to call measures on Mondrian?

1 Answers1