Yes, You Can Start Writing Expressions in Cognos!

Introduction

Analytics development in Cognos Reporting can take place on a variety of levels. It’s possible for someone with very little expertise to jump right in and start making content by using only the drag-and-drop elements of the tool. Reporting has evolved over the years so that more and more can be done without getting into writing code.

All that being said, in order to progress from an “Analytics Newbie” to “Power User”, it is critical to be comfortable writing expressions. In this blog post we will walk through a few use case scenarios for simple ways to utilize the Expression Definition box within Cognos for those who are just getting started.

The Expression Definition box can be seen as the utility tool of Cognos. Think of it as the Swiss army pocketknife of Cognos tools. With uses ranging from filtering to complex custom logic there is always a function that can get the job done--you are only limited to how deep you delve into exploring these features. In this post I’ll illustrate a few ways to use Expression Definitions to enhance your reporting, and you’ll see there’s no need to be intimidated by expressions.

Filters

Using the Expression Definition to Hard Code Filters to a Report

Let’s say we only want to Filter for Product line ‘Camping Equipment’ and not show all the other Product Lines. Here is how we can do it using Expression Definition.

Image 1. Filtering Product Line

Steps:

• Pen the query associate with the data container.

• In the Detail Filters box click the Add icon +

Image 2. Add Detail Filters

• In the Expression Definition box, select Data items, this will make the Available Components of your query available.

• Drag or double tap the Product line Data Item to the Expression Definition box.

Image 3. Add Product Line as a Detail Filter

• Select the functions from your bottom row of options – open the operator’s folder and scroll down to the ‘in’ function and double click, it will then appear in the Expression Definition box.

• Next type in the following string (‘Camping Equipment’) then verify and click OK

Image 4. Creating the Expression Definition string

• Return to the Page in Page view and check the result.

• The report now only shows results for Camping Equipment.

Image 5. Report After Filtering for Camping Equipment

This is a simple example—though we created an expression, we didn’t even need to write any code!

Using the Expression Definition to Build Useful Prompts

Building on the above example, we can create a more flexible report. Instead of filtering out all other product lines, we can instead use the Expression definition to build a prompt that allows us to navigate between which product lines we want to see on the report.

Steps:

• Bring Product Line into the Expression Definition box.

• Use the ‘in’ function or simply type ‘IN’ followed by ?p_ProductLine?

Image 6. Expression Definition

• Validate and OK

• Then Run the report in HTML to test the results of the prompt.

• Click Camping Equipment then OK

Image 7. Product Line as Expression Definition

The results will appear as follows:

Image 8. Prompt Built using Expression Definition

Using Case Statements in Report Expression Definitions

One common use case I’ve encountered: having to come up with custom logic in order to render what I want displayed in a report. A CASE statement is great for this. CASE allows the user to apply multiple conditions to perform different sets of actions. It returns a corresponding value associated with the condition defined by the user.

In this example we have a Ranking for Customer Experience ranging from 1 to 5 (bad to exceptional experience), and for the sake of this example we going to apply it per Branch Region.

We will use the Case statement in the report expression definition as follows:

Case

When [Ranking code]= '1'

Then 'Poor Experience'

When [Ranking code]= '2'

Then 'Satisfactory Experience'

When [Ranking code]= '3'

Then 'Good Experience'

When [Ranking code]= '4'

Then 'Very Good Experience'

When [Ranking code]= '5'

Then 'Exceptional Experience' 

Else Null 

End

Steps:

• With Ranking Code and Branch Region already in your query add a Data Item and name it ‘Customer Experience’.

• In the Expression Definition, type the following syntax also dragging in Ranking Code from the Available Components and verify once complete.

Image 9. Creation of the Ranking Code

• Then build a list using this new piece of data. In the Query related to you Data Container i.e. for this example it will be query 2.

• While in page preview mode drag in Customer Experience into the list container.

Image 10. Add Customer Experience for additional ranking details

Now there is more detail associated with Rank code and gives more detail on what the numbers mean.

Calculations in Expression Definition

For the example, we will show how to calculate Gross Profit using Expression Definition. Gross Profit, for this example, will be defined as (Revenue – Product cost).

Steps:

• In the query add new Data Item and rename it to Gross Profit.

• Drag Revenue into the Expression Definition box.

Image 11. Add Revenue to calculation in Expression Definition

• Next, in the functions panel under Operators folder drag the (-) sign next to Revenue and then drag Product Cost from the Available Components next to the (-) sign.

Image 12. Add (-) to calculation in Expression Definition

Image 13. Add Product Cost to calculation in Expression Definition

• Click Validate and OK.

In page preview mode drag the new Data Item ‘Gross Profit’ into your list. The result is seen below.

Image 14. Add ‘Gross Profit’ as calculated to list preview

Conclusion

These are simple examples intended to get you comfortable with writing custom expressions in Cognos. As you have seen here, custom expressions don’t have to be complicated in order to be powerful. Once you start creating simple expressions, you’ll start to figure out ways to combine them together to address more complex scenarios. You’re on your way to being able to create powerful reports and analytics for yourself and your team!

We hope you found this article to be engaging and informative. If you have any questions, contact us today and be sure to subscribe to our newsletter for data and analytics news, updates, and insights delivered directly to your inbox.