Writing Advanced Snippets When Building Templates

Both Clarity and 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.