Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

How to suppress rows having all entries as ZEROs?

Received Response
493
Views
10
Comments
Vpandura-Oracle
Vpandura-Oracle Rank 3 - Community Apprentice
edited Aug 13, 2024 5:58PM in Oracle Analytics Cloud and Server

I have a report let us say has following output. In the below table, if I wish to suppress the rows where all columns are zero what should I do?
How to achieve this functionality? Kindly help.

pastedImage_0.png

Regards
-vinod

Answers

  • Gianni Ceresa
    Gianni Ceresa Mod

    Depends on what your columns composing the rows are.

    Are they independent measures? If yes => add a filter to not select when they are all zeros.

  • Vpandura-Oracle
    Vpandura-Oracle Rank 3 - Community Apprentice

    Can you please explain a bit more?  I would like to understand what you mean by "independent measures"?

    At the report level, I have very generic filter like

    • Product ID is equal to / is in  xxxx;


    For each column, I further have column formula defined differently. For example - I have defined for 2 columns as follows

    Column 1: All bugs

    SUM(CASE WHEN ("Bug Header"."Status" IN (11, 17, 25, 26, 30, 40, 87) then 1 else 0 END)

    Column 2 : Sev 1,2 Bugs
    SUM(CASE WHEN ("BUG HEADER"."SEVERITY" IN (1,2) AND "Bug Header"."Status" IN (10, 16, 11,17, 25, 26, 30, 40, 87)) then 1 else 0 END)

    So in such cases, how do I still suppress rows which has all ZERO return values?

    Thanks,
    -vinod

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    Edit: @Gianni Ceresa  ok smart-ass, go ahead and explain the whole, full concept to the OP

  • Gianni Ceresa
    Gianni Ceresa Mod

    Just apply De Morgan's laws : you want a NOT(A=0 AND B=0 etc), so the filter is A<>0 OR B<>0 etc.

    Build a filter with all your columns in that way (with <> 0 and OR between the conditions), if the row is only 0 it isn't returned.

  • Gianni Ceresa
    Gianni Ceresa Mod

    Did you give up on that one?

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    Looks like it

  • Vpandura-Oracle
    Vpandura-Oracle Rank 3 - Community Apprentice

    Well, each column is not simple and they themselves are of summary type. So wasn't sure how to go about it. Hence didn't pursue.

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    You could at least comment as to whether Gianni's alternative works

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    Hence the main issue with build facts logically at report run time ...

  • asim cholas
    asim cholas Rank 6 - Analytics Lead

    in filter fx add those columns (column1+column2+column3+..ColumnN) > 0  as a condition.