OLAP/MDX - define calculated member, sum all time to date data

Question

I would like to define "all time to date" calculated member in OLAP cube. I'm able to calculate YTD by using the following:

SUM(YTD([Time].[Month].CurrentMember), [Measures].[Suits])

How can I include all dates since the beginning of my data? My time dimension looks like:

<Dimension type="TimeDimension" visible="true" foreignKey="granularity" highCardinality="false" name="Time">
  <Hierarchy name="Time" visible="true" hasAll="true" primaryKey="eom_date">
    <Table name="v_months" schema="bizdata">
    </Table>
    <Level name="Year" visible="true" column="year_number" type="String" uniqueMembers="false" levelType="TimeYears" hideMemberIf="Never">
    </Level>
    <Level name="Quarter" visible="true" column="quarter_number" type="String" uniqueMembers="false" levelType="TimeQuarters" hideMemberIf="Never">
    </Level>
    <Level name="Month" visible="true" column="month_number" type="String" uniqueMembers="false" levelType="TimeMonths" hideMemberIf="Never">
    </Level>
  </Hierarchy>
</Dimension>

Not sure if relevant: I'm using mondrian olap server (running on tomcat), Saiku as frontend, postgres as database

I've tried a lot of combinations, but I can't figure it out.

Update: I've tried to use syntax suggested by Gonsalu:

<CalculatedMember name="YTD Suits" formatString="" formula="SUM(YTD([Time].[Month].CurrentMember), [Measures].[Suits])" dimension="Measures" visible="true">
</CalculatedMember>
<CalculatedMember name="PTD Suits" formatString="" formula="Sum({NULL:[Time].[Month].CurrentMember },[Measures].[Suits])" dimension="Measures" visible="true">
</CalculatedMember>

Using this I get the following error message when starting mondrian (note that YTD function works well without the second calculated member):

Caused by: mondrian.olap.MondrianException: Mondrian Error:Failed to parse query
 'WITH
MEMBER [Measures].[Measures].[YTD Suits]
  AS 'SUM(YTD([Time].[Month].CurrentMember), [Measures].[Suits])',
    [$member_scope] = 'CUBE',
MEMBER_ORDINAL = 6
MEMBER [Measures].[Measures].[PTD Suits]
  AS 'Sum({NULL:[Time].[Month].CurrentMember },[Measures].[Suits])',
[$member_scope] = 'CUBE',
MEMBER_ORDINAL = 7
SELECT FROM [Project Performance]'

Thank you for any ideas.

gonsalu gonsalu · Accepted Answer · 2012-06-07T19:45:29

I haven't used Mondrian, but in SQL Server Analysis Services (SSAS), using the NULL member causes the range to go from one end of the level to the specified member.

In your case, the calculated member you're looking for might be something like this:

Sum( { NULL : [Time].[Month].CurrentMember }
   , [Measures].[Suits]
   )

You could also do a to the end of times calculated member using the NULL member on the other end, like so:

{ [Time].[Month].CurrentMember : NULL }

OLAP/MDX - define calculated member, sum all time to date data

2 Answers