IBM Support

Planning Analytics 2.0.98.7 returns a null instead of non-null value when an AGGREGATE function is used

Troubleshooting


Problem

Planning Analytics (PA) 2.0.98.7 returns a null value instead of a non-null value.

Example MDX.
WITH
    MEMBER
        [plan_department].[plan_department].[XQEext_CM0] AS '
        AGGREGATE(
            {{[plan_department].[plan_department].[105],
              [plan_department].[plan_department].[110]}})',
        SOLVE_ORDER = 1
SELECT
    {[plan_department].[plan_department].[XQEext_CM0]}
    DIMENSION PROPERTIES PARENT_LEVEL,
        PARENT_UNIQUE_NAME
    ON AXIS(0),
    {{[plan_time].[plan_time].[Jan-2004],
      [plan_time].[plan_time].[Feb-2004],
      [plan_time].[plan_time].[Mar-2004]}}
    DIMENSION PROPERTIES PARENT_LEVEL,
        PARENT_UNIQUE_NAME
    ON AXIS(1)
FROM
    [plan_Report]
WHERE
    ([plan_business_unit].[plan_business_unit].[10110],
        [plan_chart_of_accounts].[plan_chart_of_accounts].[41101],
        [plan_exchange_rates].[plan_exchange_rates].[local],
        [plan_report].[plan_report].[Variance])
    CELL PROPERTIES CELL_ORDINAL,  FORMAT_STRING,   TM1RULEDERIVED,    VALUE

Symptom

PA 2.0.98.7 returns a cell set with null values instead of non-null values.
 
{
    "@odata.context": "../$metadata#Cellsets('YLBV5FkDAIBiAAAg')/Cells(Ordinal,Status,Value,FormatString,RuleDerived)",
    "@odata.count": 3,
    "value": [{
            "Ordinal": 0,
            "Status": "Null",
            "Value": null,
            "FormatString": "#,##0;(#,##0)",
            "RuleDerived": false
        }, {
            "Ordinal": 1,
            "Status": "Null",
            "Value": null,
            "FormatString": "#,##0;(#,##0)",
            "RuleDerived": false
        }, {
            "Ordinal": 2,
            "Status": "Null",
            "Value": null,
            "FormatString": "#,##0;(#,##0)",
            "RuleDerived": false
        }
    ]
}
The same MDX in PA 2.0.97.6 returns the values
{
    "@odata.context": "../$metadata#Cellsets('nd4v41cDAIBiAAAg')/Cells(Ordinal,Status,Value,FormatString,RuleDerived)",
    "@odata.count": 3,
    "value": [{
            "Ordinal": 0,
            "Status": "Data",
            "Value": -625648.780000000028,
            "FormatString": "#,##0;(#,##0)",
            "RuleDerived": false
        }, {
            "Ordinal": 1,
            "Status": "Data",
            "Value": -620238.189999999944,
            "FormatString": "#,##0;(#,##0)",
            "RuleDerived": false
        }, {
            "Ordinal": 2,
            "Status": "Data",
            "Value": -629969.630000000005,
            "FormatString": "#,##0;(#,##0)",
            "RuleDerived": false
        }
    ]
}
 

Cause

Due to a defect in PA  MDX AGGREGATE function processing

Resolving The Problem

This issue is fixed in PA 2.0.9.9 or higher.

Document Location

Worldwide

[{"Type":"MASTER","Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSTSF6","label":"IBM Cognos Analytics"},"ARM Category":[{"code":"a8m0z000000TN4sAAG","label":"Installation and Configuration->Database"}],"ARM Case Number":"","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF016","label":"Linux"},{"code":"PF033","label":"Windows"}],"Version":"All Versions"}]

Document Information

Modified date:
15 June 2021

UID

ibm16462333

Page Feedback