...

Ensure Non-Null Values for Calculations in IBM Cognos 8 Report Studio

by user

on
Category: Documents
10

views

Report

Comments

Transcript

Ensure Non-Null Values for Calculations in IBM Cognos 8 Report Studio
Tip or Technique
Ensure Non-Null Values for
Calculations in IBM Cognos 8
Report Studio
Product(s): IBM Cognos 8 Report Studio
Area of Interest: Report Design
Ensure Non-Null Values for Calculations in IBM Cognos 8 Report Studio
2
Copyright and Trademarks
Licensed Materials - Property of IBM.
© Copyright IBM Corp. 2009
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
http://www.ibm.com/legal/copytrade.shtml
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 [email protected]
Ensure Non-Null Values for Calculations in IBM Cognos 8 Report Studio
3
Contents
1
INTRODUCTION ............................................................................................ 4
1.1
1.2
1.3
PURPOSE ................................................................................................................ 4
APPLICABILITY ......................................................................................................... 4
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 8 Report Studio
4
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.
1.3 Assumptions
This document assumes familiarity with Report Studio 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 8 and therefore will be recognized regardless of the underlying data
source. This function also allows for more than two parameters.
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 8 servers will be required to implement the function rather than
taking full advantage of the dimensional data source's processing.
Ensure Non-Null Values for Calculations in IBM Cognos 8 Report Studio
5
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:
coalesce([Sale_Price], [Regular_Price], 0) * [Quantity]
Ensure Non-Null Values for Calculations in IBM Cognos 8 Report Studio
6
The coalesce function returns the first non null value. In the expression above, if
Sale_Price is not null, it will be returned. If Sale_Price is null, it will check
Regular_Price. If Regular_Price is not null, it will be returned. If Regular_Price is null,
it will return the static value of 0. The report output using this calculation is shown
below:
The Revenue calculation no longer returns null values.
The coalesce function provides flexibility since it can accept two or more values for
null checking as well as static values, which ensures a value is always returned.
4 Use If then Else Statements to Eliminate Nulls for
Dimensional Data Sources
While the coalesce function works well with relational sources and provides a simple
syntax to deal with null values, it is not recommended for use with dimensional data
sources where Multidimensional Expressions (MDX) statements are used to query the
source rather than SQL.
Using If Then Else statements in Report Studio for dimensional sources will be
passed down in the MDX thereby ensuring processing is done by the data source
rather than by IBM Cognos 8 servers.
4.1 If Then Else Example
Consider the following list report based on an IBM Cognos 8 PowerCube:
Quantity after Returns is a calculation with the following expression:
[Quantity]-[Returns]
Ensure Non-Null Values for Calculations in IBM Cognos 8 Report Studio
7
Since there were no returns for the item in the report for some of the years, the null
value in the cube causes the simple subtraction calculation to return a null value. For
this report however, if there were no returns, the quantity should still be displayed in
the column.
Replace the Returns measure item on the report with a Query Calculation (in this
case called Return Quantity) with the following expression:
if([sales_and_marketing].[Measures].[Returns] is null) then (0) else
([sales_and_marketing].[Measures].[Returns])
Alter the Quantity after Returns calculation to use the new Query Calculation as
shown below:
[Quantity]-[Return Quantity]
Now when the report is run, the results are as follows:
Notice that zeros are returned instead of nulls which allows the Quantity after
Returns calculation to return a value in all cases.
If the generated SQL/MDX from the Tools menu is examined, one can see that the If
Then Else statement is converted into the MDX equivalent as highlighted below,
which indicates the processing will be done by the data source.
WITH MEMBER [MEASURES]..[@MEMBER].[COG_OQP_USR_Return Quantity] AS
'IIF(ISEMPTY([MEASURES]..[Returns]), 0, [MEASURES]..[Returns])',
SOLVE_ORDER = 2, MEMBER_CAPTION = 'Return Quantity' MEMBER
[MEASURES]..[@MEMBER].[COG_OQP_USR_Quantity after Returns] AS
'([MEASURES]..[Quantity] - [MEASURES]..[@MEMBER].[COG_OQP_USR_Return
Quantity])', SOLVE_ORDER = 2, MEMBER_CAPTION = 'Quantity after Returns'
SET [COG_OQP_INT_s1] AS 'CROSSJOIN([Time]..[Year 2].MEMBERS,
{[Products]..[@MEMBER].[77]})' SELECT CROSSJOIN([COG_OQP_INT_s1],
{[MEASURES]..[Quantity], [MEASURES]..[@MEMBER].[COG_OQP_USR_Return
Quantity], [MEASURES]..[@MEMBER].[COG_OQP_USR_Quantity after Returns]})
DIMENSION PROPERTIES PARENT_LEVEL, CHILDREN_CARDINALITY ON AXIS(0) FROM
[Sales and Marketing]
Ensure Non-Null Values for Calculations in IBM Cognos 8 Report Studio
8
5 Force Null Checking After a Full Outer Join
When queries are performed in IBM Cognos 8 that request facts from multiple
underlying database tables, the query engine performs something IBM Cognos calls a
"stitch query". Stitch queries consist of sub queries, one for each disparate fact,
which are then merged together on their common attributes.
Examine the report below:
This report is the same as in the previous example except that it is written using a
relational data source. Notice the Quantity after Returns calculation returned a null
value where Return quantity is null. Quantity minus a null value returns a null value
in the calculation. There are no nulls in the Returned items table in the database. So
why is there a null in the report?
Ensure Non-Null Values for Calculations in IBM Cognos 8 Report Studio
9
Quantity and Return quantity are each facts taken from a separate query subject and
have Year from the Time dimension and Product name from Products in common.
IBM Cognos 8 generates two subqueries which are then merged together to fulfil the
parent query which populates the report.
Ensure Non-Null Values for Calculations in IBM Cognos 8 Report Studio
1
Pseudo SQL:
Select
coalesce(D2.Year1,D3.Year1) as Year1,
coalesce(D2.Product_Name,D3.Product_name) as Product_name,
D2.Quantity as Quantity,
D3.Return_quantity as Return_quantity,
(D2.rc - D3.rc) as Quantity_after_Returns
from
(Sub query 1) D2
full outer join
(Sub query 2) D3
on
((D2.Year = D3.Year) and (D2.Product_name = D3.Product_name))
In the pseudo SQL example above, the coalesce function is used to return the first
non-null record set from the sub queries. If both are null, then no record is returned.
If one is null and the other is not, then a record will be returned, but the sub query
that had no match will display null values. This is how null values can be generated
during a query against tables that have no null values in them.
Examine the results of each sub query. First look at the sub query results that
retrieve data for the Quantity fact.
There are four records. Now examine the sub query results for the Return quantity
fact.
Notice that there are only 3 records. There were no returns for 2007.
Ensure Non-Null Values for Calculations in IBM Cognos 8 Report Studio
1
In a stitch query however, when there are more records in one sub query than the
other, nulls will be returned for the rows where there is no match as seen below:
So how can a calculation be created for Quantity after Returns that will ensure there
will not be null values? The following expression for Quantity after Returns ensures
the null checking is done after the full outer join of the stitch query.
if ([Return quantity] is Null) then ([Quantity]) else ([Quantity] [Return quantity])
The report output appears as shown below:
Since Return quantity and Quantity are both included in the calculation, it can only
be evaluated once all values from each sub query have been returned and merged
together. The evaluation is therefore done on the parent query rather than on the
individual Return quantity sub query as shown in the pseudo SQL code below:
Select
coalesce(D2.Year1,D3.Year1)
as
Year1,
coalesce(D2.Product_name,D3.Product_name)
D2.Quantity
as
as
Product_name,
Quantity,
D3.Return_quantity
as
Return_quantity,
case when (D3.rc is NULL) then D2.rc else (D2.rc - D3.rc) end
Quantity_after_Returns
from
(Sub query 1) D2
full outer join
(Sub query 2) D3
on
((D2.Year = D3.Year) and (D2.Product_name = D3.Product_name))
as
Ensure Non-Null Values for Calculations in IBM Cognos 8 Report Studio
1
For more complex evaluations that include more scenarios for null checking, a Case
statement can be used instead of an If statement in the calculation as shown below.
CASE
WHEN [Quantity] is Null and [Return quantity] is Null then (0)
WHEN [Return quantity] is Null then ([Quantity])
ELSE ([Quantity] - [Return quantity])
END
6 Conclusion
Using the various techniques found in this document can help prevent undesired
behaviour for calculations that reference values or results that may return nulls.
Fly UP