tag:blogger.com,1999:blog-2615703102299902978.post4444052147069112907..comments2024-02-03T00:03:10.178-08:00Comments on Glenn Schwartzberg's Essbase Blog: Time for a quick QuizGlennShttp://www.blogger.com/profile/08532634180859769798noreply@blogger.comBlogger3125tag:blogger.com,1999:blog-2615703102299902978.post-13179274712478067272010-08-19T10:02:29.009-07:002010-08-19T10:02:29.009-07:00I know that there is an option to set how the #Mis...I know that there is an option to set how the #Missing values has to be displayed. I am not able to recollect.<br /><br />If it is Classic Add-in, go to Options, Global, you can set how the #Missing has to be displayed. If you enter 0, all the #Missing will be displayed as 0 (Not numeric 0).<br /><br />I am sure that there is a way it can be done in swartview but couldn't recollect.<br /><br />Qn2<br />How he can take non-numeric data in Excel and show it in formulas as a zero without using an "IF" statement<br />NO CLUE :(..amarnathhttps://www.blogger.com/profile/12749240552285136096noreply@blogger.comtag:blogger.com,1999:blog-2615703102299902978.post-81516537702245875152010-08-12T13:16:54.676-07:002010-08-12T13:16:54.676-07:00Change the #Missing to a 0 in the Hyperion>Opti...Change the #Missing to a 0 in the Hyperion>Options dialog box, or set it to #NumericZero.<br /><br />First bonus?<br /><br />Second bonus - (0) in the classic add-in replaces #Missing with a numeric zero.<br /><br />Third bonus - when users send data with the classic add-in, the numeric zeros are stored in the cube, which takes up storage space. Smart View doesn't submit zeros.Unknownhttps://www.blogger.com/profile/11540343478780963563noreply@blogger.comtag:blogger.com,1999:blog-2615703102299902978.post-36524527551268512192010-08-12T11:45:45.034-07:002010-08-12T11:45:45.034-07:00Answers in order:
1) #NumericZero and (0), yes tha...Answers in order:<br />1) #NumericZero and (0), yes that last one works, much to my surprise.<br />2) I suspect you're being tricky, so maybe these answers aren't what you're looking for. I am guessing you mean conversion of a number stored as text. A few options:<br />1) The VALUE() function around the cell reference to the non-numeric text string will return a value whether it's the result of a SV retrieve or not. <br />2) Multiplying by 1 in another column will do it. This also works for cells with a leading single apostrohpe. <br />3) You could also copy and paste special using the multiply option. <br />4) Also you could do a text to columns conversion although that is a pain. <br /><br />I'm sure searching would reveal more approachs unless I have totally misunderstood you in which case I have no idea. :)<br />3) (0)<br />4) The answer to the second bonus question will send zeros up to db instead of #Missing, A Bad Thing.<br /><br />Regards,<br /><br />Cameron LackpourCameron Lackpourhttps://www.blogger.com/profile/07701786303677521318noreply@blogger.com