,

Highlighting Every Other Row in a Report Using IBM Cognos Workspace Advanced with a Twist

/by

For an updated version of this blog post using IBM Cognos Analytics 11.1.7, click here.

For users of IBM Cognos Workspace Advanced (formerly Business Insight Advanced), a popular request is to have the ability to highlight every other row in a list report. This technique has been demonstrated with IBM Cognos Report Studio using the running-count() and mod() functions. The issue is that IBM Cognos Workspace Advanced does not expose all functions that can be used in query calculations, so users think they are not available.

This article will demonstrate how to highlight every other row using IBM Cognos Workspace Advanced by entering a ‘non-exposed’ function in the expression editor (the twist!).

In order to create the report, the following objects will be used:

– List container

– Running-count() function (not-exposed)

– Mod() function (exposed)

– List Columns Body Style ancestry

– Conditional Styles

– Query calculations

Create the query calculation

For this exercise, a query calculation will be created to display the numbers 0 or 1 on every other row. If the row has the number 1 on it, it will be filled with color. To accomplish this, the running-count() function will be used to get a sequential list of numbers. The mod() function will be used to divide the running count results by 2, which will result in calculations with a remainder of 0 or 1.

 

1. Create a simple list report.

2. From the Toolbox, drag a Query Calculation to the list report and name it Count and click OK.
3. Select the Functions folders. The first function we want to use is running-count.

Note: It is not available for selection, however, the ‘twist’ is that it can be manually entered (this requires some knowledge of common and database related specific functions). Out of sight does not mean out of mind!

 

4. In the Expression Definition, enter running-count(). Since Revenue appears on each one, it is a good candidate to count.

You should drag the revenue column to the expression from the Data Items tab. Click OK.

 

5. The results of the running-count() function show sequential numbers.

Now the mod() function should be added around the expression to generate the remainder numbers 0 or 1.

6. Highlight the Count column title and right-click to expose the sub menu. Select Edit Query Expression.

7. Modify the expression as follows:

mod(running-count([Revenue),2).

Note: the mod() is an exposed function, however, the expression is typed to ensure the correct syntax is applied. Click OK.

8. The results of the nested query calculation show 0 and 1 on every other row.

 

Apply conditional formatting

Since the conditional formatting should apply to the entire report, the List Columns Body Style ancestry of the List must be highlighted.

1. Click any column in the List container to put focus on it. On the Properties bar, click the Select Ancestor icon and choose List Columns Body Style. This will highlight the body and not the column titles.

2. From the Menu select Style > Conditional Styles > New Conditional styles > Count. Click OK.

For this example, the rows with the number 1 will be colored.

3. Enter 0 in the lower field (note the arrow next to the field is pointing upward indicating greater than 0, and select a color (pencil) . Click OK.

4. The results show every other row highlighted.

5. Since the Count column is no longer needed, remove it from the report using the Cut button (scissors). Do not delete (X) it as the underlying query is needed to render the conditional formatting.

As you can see, alternating row colors in a list report is pretty simple in IBM Cognos Workspace Advanced. More importantly, if a common or database specific function is not exposed and you know how to use it, don’t be shy about entering it in the expression editor (the twist!). You will be pleased with the result!