Search code examples
mdxpentaho

MDX: How to select all Hierarchy levels

I have the following simple Cube:

enter image description here

where i have 3 axes , and i wanna display it all within JasperSoft OLAP viewer. Si i need towrite a MDX command to specify in the ROWS : the Product properties and in the COLUMNS the Time Properties.

i'm trying this : 

SELECT {([Product].[HierarchyProduct].[Name] , [Product].[HierarchyProduct].[Line])}  ON ROWS,
{([Client].[HierarchyClient].[Ville] , [Time].[HierarchyClient].[Pays])} ON COLUMNS
FROM Cube

but i have had an error of not knowing "[Product].[HierarchyProduct].[Name]"

So how may i access to it ??

Solution

  • In Mondrian hierarchy name has to be specified in the same square brackets as dimension name: [Dimension.Hierarchy].[Level] However, hierarchy name can be omitted (at least in case you have only one hierarchy in the dimension): just use [Dimension].[Level]

    And AFAIK, you can't mix members of same dimension in tuple definition, like you've tried with Line and Name. And you, actually, don't have to: names of members on lower levels always include name of theirs parents. However, your visualization tool may hide upper level names (sorry, I don't know whether Jasper does). In this case you'll probably need to add upper level names as a calculated member.

    I would suggest to try something like following (I couldn't really understand your requirements for COLUMNS, so I've added [Time].[Month] to the Client info):

    SELECT 
    [Product.HierarchyProduct].[Line].AllMembers ON ROWS
    , NonEmptyCrossJoin(
        [Client.HierarchyClient].[Pays].AllMembers
        , [Time.HierarchyTime].[Month].AllMembers
    ) ON COLUMNS
FROM [Cube]

    By the way, your levels seem to me like they are reversed: the most detailed levels are on the top of hierarchy. Is it really intended?