PM91981: Cognos 10.2.1 BI report aggregates values inconsistently IF-THEN condition combines two vendor specific functions.

APAR status

• Closed as unreproducible.

Error description

• Cognos 10.2.1 BI report has YTD/MTD calculation which uses
  IF-THEN construction where conditional clause contains SQL
  Server vendor specific functions: getdate() year() month()   to
  determine years/months that will get aggregated into total. When
  running under CQM, IF-THEN construct gives inconsistent results.
  Also when order of statements in IF-THEN   conditional clause is
  changed, results change. When using same logic in case-when
  construct, aggregated results are different (correct).   IF-THEN
  calculation Expression:    IF ( [Year] = year(getdate()) AND
  [Month] <= month(getdate()) )   THEN ( [Revenue] )   ELSE ( 0 )
   CASE WHEN calculation Expression:   CASE   WHEN(      [Year] =
  year(getdate())      AND      [Month (numeric)] =
  month(getdate())   )   THEN([Revenue])   ELSE(0)   END  Using
  only one statement, there is no difference in total values
  between IF-THEN and CASE-WHEN calculations and statements itself
  and logic generally seems to / should work work   ok in this
  situation. Running report under DQM gives result as in CASE-WHEN
  under CQM.
  

Local fix

• Use CASE-WHEN construct or Dynamic Query Mode instead (DQM)
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All Users                                                    *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See error description.                                       *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to IBM Cognos Business Intelligence 10.2.1 Fix Pack  *
  * 1                                                            *
  ****************************************************************
  

Problem conclusion

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

Applicable component levels

• RA21 PSN

     UP