Submit a request

Use member functions

Avatar
Kepion Support
  • Updated

Introduction

Kepion has member functions, which are SQL functions that return a set of members. These functions can be used in a SQL database or any SQL-related rules in Kepion.

 

Note: Member Ffunctions before Kepion version 6.1.22209 were called Hierarchy API and needed to be manually enabled. Refer here for more information. 

 

The functions currently are:

 

Function Purpose

fnANCESTORS

 Given a member, it will return all the ancestor members. A member's ancestry is its direct path of members to the All member. 

fnDESCENDANTS

 Given a member, it will return all descendant members.

fnCHILDREN

 Given a member, it will return all child members.

fnLEAVES

 Given a member, it will return all leaf Members. 

 

Tip: Member Functions are based on nested sets. Refer to the Background section for more information. 

 

 

Parameters

You will need to use these parameters when using a Member Function:

 

Parameter Description
@Dimension A string field to enter the dimension name.
@Hierarchy A string field to enter the hierarchy or member list name.
@Label A string field to enter the given member's name.
@IncludeSelf A byte field to decide whether you want the given member to be returned in the result (1 for yes, 0 for no).

 

 

Functions

fnANCESTORS

For an illustration of how fnANCESTORS works, refer to the following image:

 

Ancestors.png

 

To use fnAncestors: 

 

1. Create a new query and use a SELECT statement with [dbo].[fnANCESTORS]:

 

SELECT * FROM [dbo].[fnANCESTORS] (N'Dimension', N'Hierarchy', N'GivenMember', 0);

 

2. Select Execute. 

 

Ancestorsresult.png

 

 

fnDESCENDANTS

For an illustration of how fnDESCENDANTS works, refer to the following image:

 

Descendants.png

 

To use fnDescendants: 

 

1. Create a new query and use a SELECT statement with [dbo].[fnDESCENDANTS]:

 

SELECT * FROM [dbo].[fnDESCENDANTS] (N'Dimension', N'Hierarchy', N'GivenMember', 0);

 

2. Select Execute. 

 

descendantsresult.png

 

 

fnCHILDREN

For an illustration of how fnCHILDREN works, refer to the following image:

 

Children.png

 

To use fnChildren: 

 

1. Create a new query and use a SELECT statement with [dbo].[fnCHILDREN]:

 

SELECT * FROM [dbo].[fnCHILDREN] (N'Dimension', N'Hierarchy', N'GivenMember', 0);

 

2. Select Execute. 

 

childrenresult.png

 

fnLEAVES

For an illustration of how fnLEAVES works, refer to the following image:

 

Leaves.png

 

To use fnLeaves: 

 

1. Create a new query and use a SELECT statement with [dbo].[fnLEAVES]:

 

SELECT * FROM [dbo].[fnLEAVES] (N'Dimension', N'Hierarchy', N'GivenMember', 0);

 

2. Select Execute. 

 

leavesresult.png

 

 

Background

We'll use the following parent-child hierarchy to illustrate how nested sets work: 

 

examplehierarchy.png

 

Nested sets assign two numbers to each node (i.e., hierarchy member) according to a 'depth-first' traversal. Depth-first traversals work from left to right, assigning a number in order upon entering and exiting each node. Thus, each node will have two numbers, stored as two attributes.

 

Here's our example hierarchy represented as a nested set: 

 

NestedSet.drawio__2_.png

 

Node Left Right
A 1 12
A1 2 5
A1.1 3 5
A2 6 11
A2.1 7 8
A2.2 9 10

 

Nested sets help make querying efficient. You can, for example, test hierarchy memberships by comparing members' assigned numbers.

Related articles