Both IBM Clarity and IBM Cognos TM1 support the MultiDimension eXpression (MDX) query language through snippets. Snippets are single MDX phrases that can be evaluated as part of an MDX query. At first glance, it can seem intimidating, but starting with a few key functions, it’s easier to start using it to put together advanced snippets.
Let’s go through an example. Let’s say we have the following cube.
Our dimensions are:
- Year
- Time
- Entity
- Account
- Measures
When building a template in ClarityStudio, I can select Member, Children, IChildren, Descendants, IDescendants and BottomLevelDescendants by right-clicking on an element in a dimension hierarchy. Let’s start by explaining how these ones map to MDX expressions, using the Full Year member from the Time dimension.
| Clarity Function | Description | MDX Function | MDX Snippet | Members Included |
| Member | Show just the one element I picked from the list | None | [Time].[Full Year] | Full Year |
| Children | Show just the elements directly below the one I selected | .Children | [Time].[Full Year].Children | Q1, Q2, Q3, Q4 |
| IChildren | Show the elements directly below the one I selected, plus the one I selected | .Children | {[Time].[Full Year].Children, [Time].[Full Year]} | Q1, Q2, Q3, Q4, Full Year |
| Descendants | Show all elements anywhere below the one I selected, regardless of distance | Descendants | Descendants([Time].[Full Year]) | Q1, Q2, Q3, Q4, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec |
| IDescendants | Show all elements anywhere below the one I selected, regardless of distance, including the one I selected | Descendants | {Descendants([Time].[Full Year]) , [Time].[Full Year]} | Q1, Q2, Q3, Q4, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec, Full Year |
| Bottom Level Descendants | Show only the elements that have no children below the one I selected. These are also known as leaf elements. | Descendants | Descendants([Time].[Full Year] ,,LEAVES) | Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec |
| Now that you’ve seen how the ones you already know map to MDX expressions, you can play with other MDX expressions to help get exactly the sets you want for your template. | ||||