Topic
  • 16 replies
  • Latest Post - ‏2015-03-23T04:05:37Z by vipirailo
vipirailo
vipirailo
63 Posts

Pinned topic Hidding zero values in websheet

‏2012-09-07T05:03:01Z |
Hi, all
How can i hide zeroes in TM1 websheet
pls, help
Updated on 2012-09-14T10:06:11Z at 2012-09-14T10:06:11Z by vipirailo
  • Rizwan.Kaif
    Rizwan.Kaif
    197 Posts

    Re: Hidding zero values in websheet

    ‏2012-09-07T14:03:02Z  
    If you are using Active Forms in the websheet then you can Suppress Zeros in the sheet.

    Cheers!
    Rizwan Kaif
  • vipirailo
    vipirailo
    63 Posts

    Re: Hidding zero values in websheet

    ‏2012-09-07T15:57:21Z  
    If you are using Active Forms in the websheet then you can Suppress Zeros in the sheet.

    Cheers!
    Rizwan Kaif
    Hi, Rizwan
    Now i understand that the question was not clear. I don't need the suppresion because i need all the rows and columns with zeroes were visible but cells looks like empty
    It's something about formatting options
  • Rizwan.Kaif
    Rizwan.Kaif
    197 Posts

    Re: Hidding zero values in websheet

    ‏2012-09-07T16:03:26Z  
    • vipirailo
    • ‏2012-09-07T15:57:21Z
    Hi, Rizwan
    Now i understand that the question was not clear. I don't need the suppresion because i need all the rows and columns with zeroes were visible but cells looks like empty
    It's something about formatting options
    You are still not clear to me. Please post a screen shot of the sample sheet what you want to see.
  • vipirailo
    vipirailo
    63 Posts

    Re: Hidding zero values in websheet

    ‏2012-09-07T16:28:27Z  
    You are still not clear to me. Please post a screen shot of the sample sheet what you want to see.
    hi, done
  • dusherwo
    dusherwo
    73 Posts

    Re: Hidding zero values in websheet

    ‏2012-09-09T22:28:24Z  
    • vipirailo
    • ‏2012-09-07T16:28:27Z
    hi, done
    Nothing to do with TM1...
    Use an Excel format like #,##0.00;(#,##0.00);
    The format after the second semicolon applies to zero values - so they will be blank.
    You can use formats like these in Perspectives too and the can help to (as it were) 'narrow your eyes' to see where you have data.
  • vipirailo
    vipirailo
    63 Posts

    Re: Hidding zero values in websheet

    ‏2012-09-10T08:17:08Z  
    • dusherwo
    • ‏2012-09-09T22:28:24Z
    Nothing to do with TM1...
    Use an Excel format like #,##0.00;(#,##0.00);
    The format after the second semicolon applies to zero values - so they will be blank.
    You can use formats like these in Perspectives too and the can help to (as it were) 'narrow your eyes' to see where you have data.
    So as you've said there is no other way except changing format. But maybe you know some tricks to set format translating zero to empty. Now i can set point or dash sign. It's much better than zeroes but not a blank yet
  • some_tm1_guy
    some_tm1_guy
    177 Posts

    Re: Hidding zero values in websheet

    ‏2012-09-12T20:48:34Z  
    • vipirailo
    • ‏2012-09-10T08:17:08Z
    So as you've said there is no other way except changing format. But maybe you know some tricks to set format translating zero to empty. Now i can set point or dash sign. It's much better than zeroes but not a blank yet
    You were already given the answer by dusherwo!
    Excel number formatting is of the form:
    positive;negative;zero;text

    If the second semi-colon after the format for negative values is included but no format is given for zero values then the format for null values is ... you guessed it, blank! Which is what you want.
  • vipirailo
    vipirailo
    63 Posts

    Re: Hidding zero values in websheet

    ‏2012-09-13T09:08:57Z  
    You were already given the answer by dusherwo!
    Excel number formatting is of the form:
    positive;negative;zero;text

    If the second semi-colon after the format for negative values is included but no format is given for zero values then the format for null values is ... you guessed it, blank! Which is what you want.
    Yes, it works in Perspectives but does not in TM1 Contributor websheet
  • some_tm1_guy
    some_tm1_guy
    177 Posts

    Re: Hidding zero values in websheet

    ‏2012-09-13T14:36:27Z  
    • vipirailo
    • ‏2012-09-13T09:08:57Z
    Yes, it works in Perspectives but does not in TM1 Contributor websheet
    It works in websheet for me.
  • vipirailo
    vipirailo
    63 Posts

    Re: Hidding zero values in websheet

    ‏2012-09-13T17:41:34Z  
    It works in websheet for me.
    Sounds great.
    Can you, pls, copy-paste what exactly are in your cells format. What is your version of TM1 (i'm on 10.1)
    Also maybe it depends on config settins. As i'm new to TM1 i don't understand the relationship between what i do and what i get
  • some_tm1_guy
    some_tm1_guy
    177 Posts

    Re: Hidding zero values in websheet

    ‏2012-09-14T08:21:16Z  
    • vipirailo
    • ‏2012-09-13T17:41:34Z
    Sounds great.
    Can you, pls, copy-paste what exactly are in your cells format. What is your version of TM1 (i'm on 10.1)
    Also maybe it depends on config settins. As i'm new to TM1 i don't understand the relationship between what i do and what i get
    I think your problem is 1/ that there is a bug in 10.1 however your main issue is 2/ that you know nothing about Excel and Excel is an essential development tool when workgin with TM1.

    In 9.5.2 this Excel number format produces the desired result of blank for zeros in TM1 Web
    #,##0;-#,##0;

    However in 10.1 this number format is incorrectly displayed in websheets and actually is translated as the format
    #,##0;#,##0;0

    To work around this however is easy just use
    #,##0;-#,##0;""

    Then you will get the same result in Excel and web and achieve what you want of the web UI matching the Excel UI. IMO this is a regression bug and should be reported as a bug.
  • vipirailo
    vipirailo
    63 Posts

    Re: Hidding zero values in websheet

    ‏2012-09-14T10:06:11Z  
    I think your problem is 1/ that there is a bug in 10.1 however your main issue is 2/ that you know nothing about Excel and Excel is an essential development tool when workgin with TM1.

    In 9.5.2 this Excel number format produces the desired result of blank for zeros in TM1 Web
    #,##0;-#,##0;

    However in 10.1 this number format is incorrectly displayed in websheets and actually is translated as the format
    #,##0;#,##0;0

    To work around this however is easy just use
    #,##0;-#,##0;""

    Then you will get the same result in Excel and web and achieve what you want of the web UI matching the Excel UI. IMO this is a regression bug and should be reported as a bug.
    > some_tm1_guy wrote:
    > I think your problem is ...2/ that you know nothing about Excel and Excel is an essential development tool when workgin with TM1.

    It's amazing how you were able to know one of my top secret, Sherlock :)
    It just because of my oracle experience i was trully think about excel, MS sql etc as it were like a child toy. Maybe now is the time to break my old mistakes down

    PS: i tried you suggested and it works fine. Thanks a lot for your help and now i know who are familar with Excel. Let me get your help when i'll met some issues if any
  • effigy
    effigy
    2 Posts

    Re: Hidding zero values in websheet

    ‏2015-03-20T03:44:26Z  
    • vipirailo
    • ‏2012-09-14T10:06:11Z
    > some_tm1_guy wrote:
    > I think your problem is ...2/ that you know nothing about Excel and Excel is an essential development tool when workgin with TM1.

    It's amazing how you were able to know one of my top secret, Sherlock :)
    It just because of my oracle experience i was trully think about excel, MS sql etc as it were like a child toy. Maybe now is the time to break my old mistakes down

    PS: i tried you suggested and it works fine. Thanks a lot for your help and now i know who are familar with Excel. Let me get your help when i'll met some issues if any

    pls let me know, how you can fix this problem. I can not to hidden the 0 or 0.00 value to empty cell. Thanks

  • vipirailo
    vipirailo
    63 Posts

    Re: Hidding zero values in websheet

    ‏2015-03-20T12:18:44Z  
    • effigy
    • ‏2015-03-20T03:44:26Z

    pls let me know, how you can fix this problem. I can not to hidden the 0 or 0.00 value to empty cell. Thanks

    Hi,

    I've used the format mask see below

    # ##0,0;-# ##0,0;"" 

  • effigy
    effigy
    2 Posts

    Re: Hidding zero values in websheet

    ‏2015-03-23T03:03:41Z  
    • vipirailo
    • ‏2015-03-20T12:18:44Z

    Hi,

    I've used the format mask see below

    # ##0,0;-# ##0,0;"" 

    Hi, Vipirailo

    It's doesn't work for me.

    I've used Perspective MS Excel 2007 and set the cell format follow your suggestion(# ##0,0;-# ##0,0;"" ), but when deploy to web application display 0.0

     

    please suggest me,

    Thanks

    Attachments

  • vipirailo
    vipirailo
    63 Posts

    Re: Hidding zero values in websheet

    ‏2015-03-23T04:05:37Z  
    • effigy
    • ‏2015-03-23T03:03:41Z

    Hi, Vipirailo

    It's doesn't work for me.

    I've used Perspective MS Excel 2007 and set the cell format follow your suggestion(# ##0,0;-# ##0,0;"" ), but when deploy to web application display 0.0

     

    please suggest me,

    Thanks

    Hi, so you get different result in Perspectives vs Web?

    If it's true i think there is nothing to do except changing your TM1 version

    I'm on 10.2.2 and it works for me

    PS. In addition i suggest you to recheck your cell formating. What exactly applied to the cell. Cause i can't understand how in could be 250 next to 0.0. I guess it should be 250.0