Interview Questions   Tutorials   Discussions   Programs

# Cognos - How many levels can be used in drill through reports?

1049
views
asked mar October 4, 2014 09:43 AM

How many levels can be used in drill through reports?

Dimensional expressions can offer a way to navigate your data in a powerful manner, and extract information about your organization. For example, do you know how many levels exist in your staff organization? Could this information be useful when discussing a re-org? You can use your Staff hierarchy in a dimensional expression to determine this. How many levels are in your Products hierarchy? The value will determine how many levels of detail users will have to drill down to get to the lowest level of detail in reports. In this article, you will explore how to determine the number of levels in a hierarchy using two different techniques, one by using the lowest level in a hierarchy and the other by using the hierarchy in an expression.

For these examples, I used IBM Cognos Report Studio 10.1, with a dimensional package (Sales and Marketing (cube)) from the IBM Cognos Samples, and created a query calculation for the development of the expressions. This technique also works with Cognos Report Studio 8.4. The step-by-step instructions are provided later in this article, but first, let's take a look at the expressions you will explore.

Method 1: If you know the lowest level of a hierarchy, you can use the following expression, (where [salesandmarketing].[Retailers].[Retailers].[Retailer site] is the lowest level in the Retailers hierarchy):

ordinal([salesandmarketing].[Retailers].[Retailers].[Retailer site])+1

Syntax: ordinal ( level ) Description: Returns the zero-based ordinal value (distance from the root level) of the specified level. If you take the last level listed in a hierarchy, using the ordinal function, you can determine what distance it is from the root level. If you add 1 (because it is zero-based), then the number of levels in the hierarchy is returned.

Method 2: If you don't know the lowest level, using the hierarchy, you can determine the number of levels in a hierarchy with the following expression, (where [salesandmarketing].[Products].[Products] is the hierarchy):

ordinal(level(item(tail(hierarchize(members([salesandmarketing].[Products].[Products]))),0)))+1

Descr iption: Members in the hierarchy are listed in order, then the last item (which should be a member from the lowest level) is returned. Once you know the lowest level, as with the first expression, you can determine the number of levels in that hierarchy.

Method 1: Step-by-step instructions to create the number of levels expression where you know the lowest level of the hierarchy: 1. Launch Report Studio, with the Sales and Marketing (cube) package. 2. On the Insertable Objects pane, click the Toolbox tab, drag a List to the report layout, and then drag a Query Calculation to the list. Note that you could use container objects other than a list object, to hold the query calculation. 3. In the Create Calculation dialog box, in the Name box type Number of Levels - Lowest Level Method, click Other expression, and then click OK. 4. In the Expression Definition pane, type ordinal(. 5. In the Available Components pane, expand the Retailers dimension, and the Retailers hierarchy. Notice the levels in this hierarchy, and that the lowest level is Retailer site. image 6. Drag the lowest level (Retailer site) to the Expression Definition pane, to the end of the current expression. 7. To complete the expression, type )+1. The expression appears as follows: ordinal([salesandmarketing].[Retailers].[Retailers].[Retailer site])+1 8. Click Validate to ensure there are no errors, and then click OK. 9. On the toolbar, click Run Report, and you will see the number of levels in the hierarchy is 5.

flag

### Join with account you already have

Preview

Online-Classroom Classes
www.writeabc.com

1 person following this question

1 person following this tag

Question tags

cognos × 1

Asked 1 year and 1 month ago ago
Number of Views -1049