Descendants () Function in Cognos Dimensional Reporting

This function is used to extract set of descendants at a specified level/member (or) distance from the specified set of level/member.

Lab 1: extract descendants of a Level at specified distance

Syntax: descendants (level, number)

Requirement is to show all the Product lines in a Product dimension using descendants () function.

1.       Take a crosstab.

2.       Insert Year to the Columns.

3.       Insert Revenue to the Measures.

4.       Insert a Query Calculation with a name Product Line to the Rows and add below expression.

descendants ([Sales (analysis)].[Products].[Products].[Products], 1)

In the above expression, we are trying to extract the descendants of root level [Products] by specifying the distance ‘1’. If you see below image, the immediate descendant of [Products] root level is [Product line] and is at a distance ‘1’ so, the expression displays all the Product Lines in the report output.

5.       Run the report.

6.       Now, change the expression as follows:

descendants ([Sales (analysis)].[Products].[Products].[Products], 2)

Since, we specified distance as ‘2’, the second descendant of [Products] root level is [Product type] so, the expression displays all the Product types in the report output. Note: I am just showing few Product types in the output because of space constraints.

7.       Run the report. 

Lab 2: extract descendants of a Level at specified level

Syntax: descendants (level, level)

1.       Take a crosstab.

2.       Insert Year to the Columns.

3.       Insert Revenue to the Measures.

4.       Insert a Query Calculation with a name Product Line to the Rows and add below expression.

descendants ([Sales (analysis)].[Products].[Products].[Products], [Sales (analysis)].[Products].[Products].[Product line])

Using above expression, we are trying to do the same thing as we did in the Lab 1. The only difference here is, instead of specifying the distance, we are directly specifying level at which members to be displayed.

5.       Run the report.

Lab 3: extract all the descendants of a Level before a specified level

Syntax: descendants (level, level/number, before)

1.       Take a crosstab.

2.       Insert Year to the Columns.

3.       Insert Revenue to the Measures.

4.       Insert a Query Calculation with a name Product Line to the Rows and add below expression.

descendants ([Sales (analysis)].[Products].[Products].[Products], [Sales (analysis)].[Products].[Products].[Product], before)

In the above expression, [Product] is the 4^th level in the Products dimension. We are trying to display all the members before the [Product] level. It means, display members of the [Product line] and [Product type] levels. Note: I am not showing all the rows in the output due to space constraints.

5.       Run the report.

6.       Change the expression.

descendants ([Sales (analysis)].[Products].[Products].[Products], [Sales (analysis)].[Products].[Products].[Product type], before)

Now, the output only shows members of [Product line] level.

7.       Run the report.

Lab 4: extract all the descendants of a Level after a specified level

Syntax: descendants (level, level/number, after)

1.       Take a crosstab.

2.       Insert Year to the Columns.

3.       Insert Revenue to the Measures.

4.       Insert a Query Calculation with a name Product Line to the Rows and add below expression.

descendants ([Sales (analysis)].[Products].[Products].[Products], [Sales (analysis)].[Products].[Products].[Product type], after)

In the above expression, [Product type] is the 3^rd level in the Products dimension. We are trying to display all the members after the [Product type] level. It means, display members of the [Product] and [Product details] levels. Note: I am not showing all the rows in the output due to space constraints.

5.       Run the report.

Lab 5: extract all the descendants of a Level beforewithmember of a specified level

Syntax: descendants (level, level/number, beforewithmember)

1.       Take a crosstab.

2.       Insert Year to the Columns.

3.       Insert Revenue to the Measures.

4.       Insert a Query Calculation with a name Product Line to the Rows and add below expression.

descendants ([Sales (analysis)].[Products].[Products].[Products], [Sales (analysis)].[Products].[Products].[Product type], beforewithmember)

5.       Run the report.