EPM Formula to Remove Empty and Zero Values

[Former Member]

Hello,

Looking for a quick formula (will be driven by user dropdown selection) that will basically do what the sheet options does to remove empty and zero. EPMSUPPRESS did not work well because it removed empty rows too instead of those rows with zero dollar amounts. Thanks

[former_member186338 · Accepted solution]

Please read help: 33.8.6 EPMReportOptions

...

KeepEmptyRows

...

[Former Member · Accepted solution]

I entered it into my worksheet but the report does not collapse the zero or empty.

[former_member186338 · Accepted solution]

Sorry, but what do you have in C6??

It has to be report ID. Like "000" for default report....

[former_member186338 · Accepted solution]

P.S. And for this report you have to uncheck Inherit Sheet Options!

[Former Member · Accepted solution]

Hello,

This was the trick - removing inherit sheet options. My C6 is my ReportID. Thanks Vadim

[Former Member · Accepted solution]

So another question regarding this same formula.

I wanted to use or activate this formulas based on a user saying Yes or No to hide zero rows. Through my testing I've noticed that this formula will flip the Zero Rows to hide and if the user selects no to deactivate this formula the report still maintains the hide Zero Rows.

[Former Member · Accepted solution]

Actually, I came up with another IF statement and it works now. Hopefully this will be useful to someone in the BPC world.

[former_member186338 · Accepted solution]

=EPMReportOptions("000",IF(A13="YES","KeepEmptyRows =RemoveEmptyandZero","KeepEmptyRows =true"))

[former_member186338 · Accepted solution]

Just posted the same Everything in a single cell

[Former Member · Accepted solution]

Ha, that is a better formula. Thanks Vadim