Exclude records with value 0 for calculated KF

[Former Member]

Hi, experts,

How do I Exclude records with value 0 for calculated KF without using condition? I'm using version 7

Appreciate any help you can provide.

Thank You!

Alina

[former_member189638 · Accepted solution]

I don't think there is any other way to restrtict a Key Figure other than Conditions. May I know what is the problem that you are facing by using Condition?

[Former Member · Accepted solution]

Requirements state not to use condition

[former_member189638 · Accepted solution]

ok! Try this.

In query properties, goto Display Tab. For Suppression, select Active (All values =0). Efect on Rows and Columns.

[Former Member · Accepted solution]

Thank you, I'll try this approach. Will supressed records still be part of the totals calculation for other KF, though?

[former_member189638 · Accepted solution]

So you have other key figures as well in your query? Then I doubt if this will work. beacuse if the value CK1 is 0 and CK2 is 10. The row will not get suppressed. It will only get suppressed if both CK1 and CK2 are zero.

[Former Member · Accepted solution]

Hi,

If you want to avoid the divide by zero situtation in KCF then you can use NDIV0 function in CKF,

OR

you can filter out the Zero (0) in the tranformation by writing Routine/formula,

please let me know if you want me to write the code as well,

Thanks.

[Former Member · Accepted solution]

Thanks for the reply. Actually the requirements are to exclude records with this particular because = 0, so it's not a division by 0 issue. Also I can't exclude records using transformation routine because cube needs to have all the records and filtering should be done in the report.

Any other suggestions?

Thank You!

[Former Member · Accepted solution]

Hi,

We didn't understood ur requirement well.

1) Lets say, you have 3 KF. and you are doing CKF1 and you don't want to show the records with Zero, In that case you can go with Zero Record Supression.

2) If you want to try to delete only KF/CKF where there is zero, it won't work as others may have values in them and you may miss the whole record set in the report

3) If the zero result is part of calculation, then please go ahead as explained above.

Please explain clearly if this is what you have or is it different ?

Regards,

-S-

[Former Member · Accepted solution]

Hi, Sonti,

thank you for your questions - they help me to clarify requirements. What's neeed is to exclude records with this particular KF when it's = 0 even if other KF in the record set are not 0.

[Former Member · Accepted solution]

Hi Alina,

There is no way to achieve this without conditions. Can you tell me why don't you wanna use it ?

Best wishes,

Mayank

[Former Member]

Created an indicator on a bcak end and use filter