Planning Analytics

 View Only
Expand all | Collapse all

TM1 null values show as (no value) in cognos, causing summarization issues

  • 1.  TM1 null values show as (no value) in cognos, causing summarization issues

    Posted Mon September 23, 2019 01:14 PM
    I created a killer dashboard using Cognos against my newly developed and super (IMO) TM1 cubes that would surely impress people.

    Unfortunately, I am running into the following issue.

    When I take two "metrics" (from a metrics dimension) and represent them on separate charts against my dimensional members, they display as numerics in the right way.

    However, when I combine two metrics in a scatter plot, one displays and the other shows as "N/A".

    After digging into the reason a little more, it appears the second metric has nulls in the cube for certain rows - this makes sense as they are not calculated for this rows.

    However, these rows do not display with numeric values FOR ANY ROW when combined with the other "metric" that has values for those rows (I am sure this sounds confusing so let me use an example).

    Dimension Member            Metric_A          Metric_B
    1                                              100                    200
    2                                               100

    On a scatter plot, Metric_A shows but not Metric_B AT ALL (i.e., not even the Row 1 is displayed).

    The hover marks every value as N/A.

    I know this is possibly a Cognos BI question, but has anyone else encountered this and what did you use as a solution?

    FYI - I tried creating a calc such as IF (Metric_B is null) THEN (0) ELSE (Metric_B) but it is raising a query service error - have not been able to dig into the details of the error in the log yet.

    ------------------------------
    Shubho Ghosal
    ------------------------------

    #PlanningAnalyticswithWatson


  • 2.  RE: TM1 null values show as (no value) in cognos, causing summarization issues

    Posted Tue September 24, 2019 03:51 AM
    Hi Shubho,
    a scatter plot requires two axes. i.e. an X value and a Y value​ so where a value is not calculated for either of the two axes it would make sense that the data point would not display.

    I suggest that you post your script for creating the calc. There is no "is null" function in TI. Try If (Metric_B @= ''); Metric_B = 0; EndIf;
    And also where in your TI you are placing this script, it is not clear (to me anyway) how you are creating the view to display the data. If this data set is purely in Cognos BI then please ignore this response :)
    ​

    ------------------------------
    Trevor Mills
    ------------------------------

    Original Message


  • 3.  RE: TM1 null values show as (no value) in cognos, causing summarization issues

    Posted Tue September 24, 2019 10:07 AM
    Hi Trevor, the is null function was applied in Cognos - it shows up as a valid function.

    I am referring to scatter points that have one of the metric values - the other one should display as zero and not N/A in that situation.

    If both metrics are zero, that should display on the scatter as well.

    The issue is on the Cognos side - I am asking if anyone has faced this issue when putting a Cognos dashboard on a TM1 cube, and what they were able to do to get around this.

    Things do display correctly in the PAW interface (nulls show up as zeroes).

    ------------------------------
    Shubho Ghosal
    ------------------------------

    Original Message