CODEWITHSUNDEEP
mdxmondrian
GriffiN
GriffiN

Reputation: 84

How to add level to column to result MDX in mondrian

I have the following table created by the following MDX

SELECT
{
    [Measures].numTickets 
}ON COLUMNS,
{
Descendants(DateCreacion.Children, DateCreacion.Month) 
}ON ROWS
FROM tickets

enter image description here

The thing is that i want to add another column to the numTickets but every time i add a dimension to the column, i get an empty column.

select {[Clinica].Children} ON COLUMNS,
  {Descendants([DateCreacion].Children, [DateCreacion.YQMD].[Month])} ON ROWS
from [tickets]

enter image description here

How would i show the same data as the first picture but in the second format?

<Schema name="New Schema1">
  <Cube name="tickets" visible="true" cache="true" enabled="true">
    <Table name="fact">
    </Table>
    <Dimension type="TimeDimension" visible="true" foreignKey="fecha_tickets_id" name="DateCreacion">
      <Hierarchy name="YQMD" visible="true" hasAll="true">
        <Table name="dim_fecha_creacion_tickets" alias="">
        </Table>
        <Level name="Year" visible="true" column="a&#241;o" type="Numeric" uniqueMembers="false" levelType="TimeYears">
        </Level>
        <Level name="Quarter" visible="true" column="cuarto" type="Numeric" uniqueMembers="false" levelType="TimeQuarters">
        </Level>
        <Level name="Month" visible="true" column="mes" type="Numeric" uniqueMembers="false" levelType="TimeMonths">
        </Level>
        <Level name="Day" visible="true" column="dia" type="Numeric" uniqueMembers="false" levelType="TimeDays">
          <Property name="date_iso" column="date_iso" type="Numeric">
          </Property>
        </Level>
      </Hierarchy>
    </Dimension>
    <Dimension type="StandardDimension" visible="true" foreignKey="clinica_id" name="Clinica">
      <Hierarchy name="New Hierarchy 0" visible="true" hasAll="true">
        <Table name="dim_posicion" alias="">
        </Table>
        <Level name="Posicion" visible="true" column="sigla" type="String" uniqueMembers="false">
        </Level>
      </Hierarchy>
    </Dimension>
    <Measure name="numTickets" column="idTicket" datatype="Numeric" aggregator="count" visible="true">
    </Measure>
  </Cube>
</Schema>

Upvotes: 0

Views: 613

Answers (1)

FrankPl
FrankPl

Reputation: 13315

When adding the [Clinica].Children to the columns, you removed the measures. You probably want to keep them, using a cross join, which can be stated using the * operator in MDX: Either

select {[Clinica].Children} 
       *
       { [Measures].numTickets }
       ON COLUMNS,
...

or

select { [Measures].numTickets } 
       *
       {[Clinica].Children}
       ON COLUMNS,
...

depending on the order of columns you want to see.

Upvotes: 1

Related Questions