PI96395: INCORRECT VALUE SHOWN FOR DIVIDE BY ZERO CHARACTERS IN CROSSTAB REPORT OUTPUT AFTER UPGRADE FROM COGNOS BI TO COGNOS ANALYTICS.

APAR status

• Closed as suggestion for future release.

Error description

• Whenever a divide by zero character appears in a data item in a
  crosstab report then the output should show the value as /0 if
  nothing is specified in the data properties but it shows
  incorrect value.
  

Local fix

Problem summary

Problem conclusion

Temporary fix

Comments

• This is not a defect.
  
  With both CQM and DQM, the Cognos software will always using
  NULLIF when there is a dividing function. I create a dataItem
  [GOSALES_SS_10_0].[PRODUCT].[MARGIN]/[GOSALES_SS_10_0].[PRODUCT]
  .[PRODUCTIONCOST], the Native SQL generated is
  select sum("PRODUCT"."MARGIN") AS "MARGIN",
  sum("PRODUCT"."MARGIN" / nullif("PRODUCT"."PRODUCTIONCOST", 0))
  AS "Data_Item1"
  from "GOSALES"."dbo"."PRODUCT" "PRODUCT"
  having count(*) > 0.
  
  If the expression is changed to
  [GOSALES_SS_10_0].[PRODUCT].[MARGIN]/0, the native SQL is
  
  select sum("PRODUCT"."MARGIN") AS "MARGIN",
  sum("PRODUCT"."MARGIN" / nullif(0, 0)) AS "Data_Item1"
  from "GOSALES"."dbo"."PRODUCT" "PRODUCT"
  having count(*) > 0.
  
  So what gets pushed down to the database is xxxx/nullif(xxx, 0)
  since we know if we push xxx/0 or there is 0 values, DB may
  crash. So when there is 0 value, instead of having divided by
  zero, we use divided by null and the result is null.
  
  To avoid this casting, in reporting studio, open the Queries
  page and in the QUERY HINTS section(the governor), override
  Avoid division by zero with value no then the DIV node will not
  be cast to NULLIF. However by doing this, you may see error when
  running report.
  

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