Pentaho Mondrian MDX - removing parent titles

Question

I am new to Pentaho, Mondrian & MDX. I started to use Pentaho CE 5.0.1 as my OLAP tool. I am struggling with a MDX query and hoping someone can give me some pointers on my issue.

I have the following time dimension mondrian schema:

`<!-- date dimension -->
<Dimension name="Time" type="TimeDimension">
    <!-- Year, Quarter, Month, Week, Day -->        
    <Hierarchy name="YQMD" hasAll="true" allMemberName="All Dates" primaryKey="date_key" type="TimeDimension">
        <Table name="dim_date" schema="webportal"/>
        <Level name="Year" uniqueMembers="true" column="year4" levelType="TimeYears" type="Numeric"></Level>            
        <Level name="Quarter" uniqueMembers="false" column="quarter_name" levelType="TimeQuarters" type="String"></Level>
        <Level name="Month" uniqueMembers="false" column="month_number" ordinalColumn="month_number" nameColumn="month_number" levelType="TimeMonths" type="Numeric"></Level>
        <Level name="Week" column="week_in_month" uniqueMembers="false" levelType="TimeWeeks"></Level>
        <Level name="Day" column="day_in_month" uniqueMembers="false" ordinalColumn="day_in_month" nameColumn="day_in_month" levelType="TimeDays" type="Numeric"></Level>

    </Hierarchy>
</Dimension>`

I am running this MDX query:

WITH 

MEMBER [Measures].[YTD] AS 'SUM(YTD(), [Measures].[Quotation Status])'

SELECT

NON EMPTY {[Measures].[Quotation Status], [Measures].[YTD]} ON COLUMNS,

NON EMPTY {
            {[Time.YQMD].[Year].Members},
            [Time.YQMD].[Month].Members
          } ON ROWS 

FROM [Broker Portal]

The MDX query works and returns the following data:

My question(s) are this:

How do I remove the first two lines containing 2015, 2016 in MDX?

How do I remove the the lines for months 03 - 12 for the year 2016?

whytheq whytheq · Accepted Answer · 2016-02-15T12:44:30

I suspect something like the following might be heading in the right direction:

WITH 
  MEMBER [Measures].[YTD] AS 
    Sum
    (
      YTD()
     ,[Measures].[Quotation Status]
    ) 
SELECT 
  NON EMPTY 
    {
      [Measures].[Quotation Status]
     ,[Measures].[YTD]
    } ON COLUMNS
 ,NON EMPTY 
    [Time.YQMD].[Year].MEMBERS 
  * [Time.YQMD].[Month].[Month].MEMBERS HAVING 
  [Measures].[Quotation Status] > 0 ON ROWS
FROM [Broker Portal];

Pentaho Mondrian MDX - removing parent titles

1 Answers