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):
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):
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.
Ready to start your tutorial with us? That's great! Send us an email and we will get back to you as soon as possible!