Ensure Non-Null Values for Calculations in IBM Cognos ...

Tip or Technique

Ensure Non-Null Values for Calculations in IBM Cognos

Analytics Reporting

Product(s): IBM Cognos Analytics Reporting

Area of Interest: Report Design

Ensure Non-Null Values for Calculations in IBM Cognos Analytics

2

Reporting

Copyright and Trademarks

Licensed Materials - Property of IBM.

? Copyright IBM Corp. 2020

IBM, the IBM logo, and Cognos are trademarks or registered trademarks of International Business Machines Corp., registered in many jurisdictions worldwide. Other product and service names might be trademarks of IBM or other companies. A current list of IBM trademarks is available on the Web at

While every attempt has been made to ensure that the information in this document is accurate and complete, some typographical errors or technical inaccuracies may exist. IBM does not accept responsibility for any kind of loss resulting from the use of information contained in this document. The information contained in this document is subject to change without notice. This document is maintained by the Best Practices, Product and Technology team. You can send comments, suggestions, and additions to cscogpp@ca..

Ensure Non-Null Values for Calculations in IBM Cognos Analytics

3

Reporting

Contents

1

INTRODUCTION ............................................................................................ 4

1.1 PURPOSE ................................................................................................................ 4 1.2 APPLICABILITY ......................................................................................................... 4 1.3 ASSUMPTIONS.......................................................................................................... 4

2

OVERVIEW .................................................................................................... 4

3

USE THE COALESCE FUNCTION TO ELIMINATE NULLS FOR RELATIONAL

DATA SOURCES ............................................................................................. 4

3.1 COALESCE EXAMPLE .................................................................................................. 5

4

USE IF THEN ELSE STATEMENTS TO ELIMINATE NULLS FOR DIMENSIONAL

DATA SOURCES ............................................................................................. 6

4.1 IF THEN ELSE EXAMPLE .............................................................................................. 6

5

FORCE NULL CHECKING AFTER A FULL OUTER JOIN ................................... 8

6

CONCLUSION .............................................................................................. 12

Ensure Non-Null Values for Calculations in IBM Cognos Analytics

4

Reporting

1 Introduction

1.1 Purpose This document will illustrate some techniques that can be used to handle null values in calculations for various situations and with various data source types.

1.2 Applicability The techniques in this document were tested using IBM Cognos 8 BI version 8.4 but is applicable to future versions including 11.x.

1.3 Assumptions This document assumes familiarity with IBM Cognos Analytics Reporting as well as some experience with Structured Query Language (SQL) and Multidimensional Expressions (MDX).

2 Overview

In certain reports, either data contains null values or the results of a complex query can return null values. When performing a calculation, null values will cause the calculation to return a null value. In some cases this may not be the desired result. The techniques in this article include the use of the coalesce function and "if then else" statements to ensure null values are eliminated when performing calculations. The first technique will show how to retrieve a value from another field when the first field is null, or a default, static value should all fields be null. A second technique will show how to substitute a null value using operators, and finally, a technique will be illustrated that overcomes null values produced by queries at run time (nulls are not in the data, but are a product of the returned record set).

3 Use the Coalesce Function to Eliminate Nulls for Relational Data Sources

In some instances, authors need to check for a value in one field and if a null is found, retrieve a value from another field. For example, if Sale Price is null, retrieve the value from Regular Price. Authors may also need to provide a default, static value such as 0 or 1 to ensure calculations do not fail. Consider a calculation that multiplies two numbers together and one of the values retrieved is a null value. The calculation will yield a null value. Different relational database vendors provide functions that deal with null values, such as NVL in Oracle or ISNULL in SQL Server. However, these functions only accept two parameters and are vendor specific. Should the application need to be portable from one vendor to another, using vendor specific functions will require some report maintenance. The Coalesce function, while supported by some vendors, is also a common function in IBM Cognos Analytics and therefore will be recognized regardless of the underlying data source. This function also allows for more than two parameters.

Ensure Non-Null Values for Calculations in IBM Cognos Analytics

5

Reporting

Since it is a relational function, the coalesce function should not be used with Dimensional data sources such as IBM Cognos PowerCubes. Some local processing on the IBM Cognos Analytics servers will be required to implement the function rather than taking full advantage of the dimensional data source's processing.

3.1 Coalesce Example In the case of Sale Price and Regular Price, consider the data shown below:

Certain items contain nulls. For example, some items were not on sale when sold and therefore Sale_Price has no value. One, item, ball, has no value for either Sale_Price or Regular_Price since it was a promotional item given away for free. If the report requires a Revenue calculation that multiplies the actual cost of the item at the point of sale with the quantity sold, the calculation might be as follows:

(if([Sale_Price] is null) then ([Regular_Price]) else ([Sale_Price])) * [Quantity]

This type of calculation would produce the following results:

Notice the Revenue calculation produced a null value for the ball item. This could be addressed by using a nested if statement to provide a value of 0 if Sale_Price and Regular_Price were null. The expression might be as follows:

(if([Sale_Price] is null and [Regular_Price] is null) then (0) else (if([Sale_Price] is null) then ([Regular_Price]) else ([Sale_Price]))) * [Quantity]

However, as a simpler syntax alternative, consider using the coalesce function as shown below:

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download