Hitachi Vantara Pentaho Community Forums
Results 1 to 9 of 9

Thread: MDX empty values

  1. #1
    Join Date
    Sep 2010
    Posts
    5

    Default MDX empty values

    Hi everybody,
    i have the following MDX:

    with member [Measures].[% of Total] as '(([Measures].[DayMovement]) /
    ([Measures].[DayMovement], [PeriodTyp.PeriodCode].Parent))', format_string = "|#0.00%|"

    select NON EMPTY Crossjoin(Hierarchize({[PeriodTyp.PeriodCode].[* PeriodCodes].Children}),
    {[Measures].[% of Total]}) ON COLUMNS,
    NON EMPTY {[Station.OTyp_Station].[own].Children} ON ROWS
    from [CostObjectTime]
    where ([ReportYear].[2005], [ReportMonth.RMonth].[6], [RevenueTyp].[MA])

    my problem is that i have a null value in the result (see result.png). If I use that query in an action sequence for a dashboard, it crashes because of the empty cell.

    So I'am looking for a way to replace that null value. I tryed coalesceempty but that does not work because then rows which have only null values are displayed.

    Has anyone a suggestion for me.

    Thank you in advance.


    Name:  result.png
Views: 121
Size:  5.9 KB

  2. #2
    Join Date
    Nov 2008
    Posts
    777

    Default

    There is some good information on the format string parameter but several of the links along the chain are broken.

    Start here and look for the section called "Format Strings": http://mondrian.pentaho.com/documentation/mdx.php

    But the link to the mondrial.util.Format class is broken so look here: http://mondrian.pentaho.com/api/mond...il/Format.html

    And there you will find that the link to "more detail" is also broken so look here: http://apostate.com/vb-format-syntax

    Toward the bottom of that page look for a section called "Multiple Formats". It shows how you can use a different format string for positive, negative, zero, and null values. I've never had the issue you've had so please post back and share your results.
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  3. #3
    Join Date
    Sep 2010
    Posts
    5

    Default

    Hi, first thanks for the reply.

    But it seems that the format_string isnt evaluated if the measure itself is null.
    I tryed:

    with member [Measures].[% of Total] as '([Measures].[DayMovement] / ([Measures].[DayMovement], [PeriodTyp.PeriodCode].Parent))'
    ,FORMAT_STRING = Iif(isempty([Measures].[% of Total]), "|0.00|","|#0.00%|" )

    but the result didn't changed.

    any other suggestions ??



  4. #4
    Join Date
    Nov 2008
    Posts
    777

    Default

    After re-reading the "Multiple Formats" section and the examples in the vb-format-syntax article, I would try this:

    FORMAT_STRING = "#0.00%;;;Nil"
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  5. #5
    Join Date
    Sep 2010
    Posts
    5

    Default

    I also tryed that, FORMAT_STRING = "#0.00%;;;Nil" has also no effect.

  6. #6
    Join Date
    Apr 2008
    Posts
    4,684

    Default

    Might it have to do with the NON EMPTY clause in your MDX?

  7. #7
    Join Date
    Sep 2010
    Posts
    5

    Default

    Quote Originally Posted by gutlez View Post
    Might it have to do with the NON EMPTY clause in your MDX?
    no, if i try the mdx witheout the NON EMPTY there are just a few more rows where all values are null.

  8. #8
    Join Date
    Apr 2008
    Posts
    4,684

    Default

    Quote Originally Posted by bigdaddy View Post
    no, if i try the mdx witheout the NON EMPTY there are just a few more rows where all values are null.
    That's what I expected would happen, but wanted to ensure that everything possible was ruled out.

  9. #9

    Default

    Hi:

    Does anyone solve this issue?
    I'm having troubles building a CCC stacked bar chart. It seems like the Protovis library doesn't like the empty cells in a resulset (I'm using a MDX query to populate the chart). Is something related to the way the library manage the layers and areas (I'm talking too much about CCC )

    I'll appreciate a lot any comment. Thanks in advance:

    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.