pentahosaikureportserver

how to use formula in Pentaho Mondrian schema XML

I'm working on Saiku reports in ReportServer. I have added a Mondrian Data Source. Now i would like to add two Rows in the report to show a Sum in the 3rd row, i did some R&D and found that we can use Cell formatter that can use Formula to use columns from the Schema XML. But i'm unable to get is exact source. Here is the source i found from Mondrian Documentation:

<CalculatedMember name="name" dimension="dimension">
<Formula>
[Measures].[Unit Sales] * 2
</Formula>
<CellFormatter>
<Script language="JavaScript">
var s = value.toString();
while (s.length() < 20) {
s = "0" + s;
}
return s;
</Script>
</CellFormatter>
</Measure>

But this seems incorrect and this the block i want as its use Formula tag and using [Measures].[Unit Sales] * 2 that is looking helpful for me.

Can any one help me to correct this tag.

Solution

  • Here is the solution I found for the problem. 

    <CalculatedMember name="Profit" dimension="Measures" formula="[Measures].[Store Sales] - [Measures].[Store Cost]">

        <CalculatedMemberProperty name="FORMAT_STRING" value="$#,##0.00"/>

</CalculatedMember>

    Hope this would be helpful for anyone else.