Wagemann hits us with a double-jab this month.
The following IBM Cognos 10.2 example against the Great Outdoors Sales sample cube, the ordinal function is used in combination with the CurrentMember function to provide a comparison to a conditional style which highlights all the rows within a crosstab whose members come from the first and second level.
The technique begins with a crosstab which consist of the Years level on the column edge, Revenue as the measure and the Product Hierarchy on the row edge. When dragging the Product Hierarchy into the row edge, the all members radio button was chosen. When completed the crosstab should represent the following screen capture.
In order to determine the level of the member, a query calculation of type other is placed next to the <#Products#> row data item. In order to do this, the row edge drop zone will need to be unlocked by clicking the Lock icon from the toolbar. The query calculation should have the following expression.
The expression above obtains the ordinal or level number of the level obtained from the current Member.
When completed, the crosstab should now represent the following screen capture.
If the report is executed, it will display the Years on the column edge, the Products and the level number of each product member in the row edge, along with the Revenue in the fact cells. The image below displays the output of running the report. For clarity, the ordinal value has been highlighted in red.
To apply the conditional formatting, select the fact cells of the crosstab, then right click to display the Style menu.
Within the Style menu, select Conditional Styles to display the Conditional Styles dialog box.
Within the Conditional Styles dialog box, select new Advanced Conditional Style located within the bottom left hand corner of the dialog box.
Within the Conditional Style-Advanced dialog box, select New Advanced Condition icon located at the bottom left hand corner of the dialog box.
Create a new Report Condition with the following expression:
[Query1].[ordinal] in (1,2)
Click the OK button to return to the Conditional Style-Advanced dialog box.
Click the Pencil for the previously defined condition, within the Style dialog box, change the background color to yellow. Upon completion click the OK button to return to the Conditional Style-Advanced dialog box, which should now represent the following screen capture.
Click the OK button twice to return to the IBM Cognos Report Studio canvas.
If the report is executed now, it will display the ordinal value next to the member in the row edge. The fact cell row for every member from level 1 or 2 will be highlighted in yellow. This is also illustrated by the following screen capture.
In order to remove the ordinal value from the row edge, select the row edge data item.
Within the left hand bottom properties pane, locate the Properties property.
Click the ellipsis to display the Properties property dialog box. Within this dialog box, select the Ordinal query calculation checkbox.
Click OK to return to the IBM Cognos Report Studio canvas.
Click the Ordinal query calculation and cut it from the crosstab.
Follow @basupp on Twitter for updates, news, articles and videos related to Business Analytics Support.