I'm working with Pentaho Mondrian. I have a Fiscal Period dimension that looks like this:


<Dimension name="FiscalPeriodDim">
<Hierarchy name="Fiscal Date" hasAll="true" primaryKey="fiscalPeriodID">
<Table name="FiscalPeriodDim" schema="dw"/>
<Level name="Company" column="company" type="String" visible="false"/>
<Level name="Year" column="fiscalYear" captionColumn="fiscalYearString" type="Integer" uniqueMembers="false" levelType="Regular" ordinalColumn="fiscalYear"/>
<Level name="Quarter" column="fiscalQuarter" captionColumn="fiscalQuarterString" type="Integer" uniqueMembers="false" levelType="Regular" ordinalColumn="fiscalQuarter"/>
<Level name="Period" column="fiscalPeriod" captionColumn="fiscalPeriodString" type="Integer" uniqueMembers="false" levelType="Regular" ordinalColumn="fiscalPeriod">
<Property name="Month" column="Description"/>
</Level>
</Hierarchy>


<Hierarchy name="Fiscal Period" hasAll="true" primaryKey="fiscalPeriodID">
<Table name="FiscalPeriodDim" schema="dw"/>
<Level name="Company" column="company" type="String" visible="false"/>
<Level name="Period" column="fiscalPeriod" captionColumn="fiscalPeriodString" type="Integer" uniqueMembers="false" levelType="Regular" ordinalColumn="fiscalPeriod">
<Property name="Month" column="Description"/>
</Level>
</Hierarchy>
</Dimension>


This dimension can't be set up as a 'real' date dimension because our customers can have different companies and different calendar setups for each one of them.


In my cube, I have a calculated measure that looks like this:


<CalculatedMember name="Cost To Date" dimension="Measures" formatString="$ #,###.00;($ #,###.00)" aggregator="sum">
<Formula>Aggregate(generate(ascendants([FiscalPeriodDim.Fiscal Period].currentmember),
iif([FiscalPeriodDim.Fiscal Period].currentmember IS [FiscalPeriodDim.Fiscal Period].firstsibling, {},
[FiscalPeriodDim.Fiscal Period].firstsibling:[FiscalPeriodDim.Fiscal Period].prevmember)),
[Measures].[Cost]) + [Measures].[Cost]
</Formula>
</CalculatedMember>


When I filter my report for a specific company and period, and drag the period to the report, I see the correct results, but as soon as I add the Cost to Date measure, the report shows the measure for all companies, not only the company I filtered for.


I was checking the Last Analyzer Query results and found this message as a warning:


Unable to use native SQL evaluation for 'NonEmptyCrossJoin'; reason: One or more calculated measures conflict with crossjoin args


I checked the SQL code used and there are different companies used for both the fiscal period and job tables. Not sure if this is because my formula is incorrect.

Could anyone help, please?


Thanks.