EVEXP Supress 0

[Former Member]

is there a way to suprress a 0 row when using EVEXP? does this have to be done via a macro?

[Former Member]

Hi,

You can run suppression based on your requirements.

If you open a dynamic template created using EVEXP, then open the control panel and go to the suppress zero column. You can enable YES for row or column, depending on whether you want the suppression to take place or not. If you click on the cell where you enable YES, you will be able to see a formula. See the last parameter, which is pointing towards another cell. In most of the cases it will be E2. You can see how the conditions have been written. You can change that condition based on your requirement.

Hope this helps.

[Former Member]

Brent - To follow up what Nilinjan said, If you look at the syntax in the Any by Any EVEXP dynamic template (eAnalyze...Open Dynamic Report Template....Any by Any....Look at the 'suppress' cell F25. If you trace the precedents of the EVEXP function in cell F28, you'll see that the "Filter" parameter I mentioned in fact can also be used to suppress zeros. The syntax to use for this is the following MDX... In this case for the Any By Any EVEXP, I have Account in Rows and Category = Actual in Columns. Enter this as the "Filter" parameter for suppression. The MDX is (Note the less than sign and greater than sign will not post to this blog): ([ACCOUNT].Currentmember,[ACTUAL])LessThanSignGreaterThanSign<>0

You can see this formula built automatically in cell E3 or B8 of the Any by Any report. (depending on whether you're suppressing on rows or columns)...

Greg

Edited by: Greg Anderson on Sep 24, 2010 4:26 PM

Edited by: Greg Anderson on Sep 24, 2010 4:26 PM

[Former Member]

Brent - You can suppress an EVEXP based on a filter or a property, but i am unaware of any way to suppress an EVEXP based on the value of the result, like you can suppress 0 in an EVDRE. However I'd be interested in hearing what other have to say.

Greg

[Former Member]

That's what I think because it is just expanding the list, and not really doing anything with the data, I think that I need to do this in a macro, but I want to see if others have a way to do this, Excel 2003