Oracle Transactional Business Intelligence

Home› Oracle Analytics› Oracle Analytics Forums› More Solutions› Oracle Transactional Business Intelligence

Categories

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

Exclude one account from filter

Received Response
94
Views
5
Comments
Ray Gupta
Ray Gupta Rank 1 - Community Starter
in Oracle Transactional Business Intelligence

Summary: Looking to exclude a specific account from overall filter


Content (required): Is there a way to exclude one account from a filter that suppresses accounts with zero balance? I have one account that I want to show in the report whether it has a zero balance or not. All other accounts with zero balance should still be excluded.


Version (include the version you are using, if applicable):


Code Snippet (add any code snippets that support your topic, if applicable):

Tagged:

Answers

  • Bhaskar Konar
    Bhaskar Konar Rank 8 - Analytics Strategist

    Hi @Ray Gupta,

    Can you please share some more details like which report you are trying to achieve this i.e. OTBI, BIP etc. What is the problem you are facing?

    Ideally in both OTBI or BIP you can put a filter to exclude an account.

    Cheers,

  • Ray Gupta
    Ray Gupta Rank 1 - Community Starter

    Thanks for the reply Bhaskar, this is in OTBI.

    The formula I currently have to exclude accounts with 0 balance is this:

    (IFNULL("Base"."Beginning Balance",0)+IFNULL("Base"."Period Net Activity",0) <> 0)

    I'm looking for a way to amend that formula so account "500" is excluded from the filter. In essence, I want to apply the formula to all accounts except "500". So, the report would list this account regardless of balance and suppress all others with 0 balance.

  • Nathan CCC
    Nathan CCC Rank 7 - Analytics Coach

    Use an OR. You can do this in a physical query or a logical query. 

    where ((account = 500) or (account <> 500 and balance <> 0))
  • Nathan CCC
    Nathan CCC Rank 7 - Analytics Coach

    FYI Regards "Thanks for the reply, this is in OTBI." BOTH publisher "report" "data model" (yellow) and an "analysis" (blue) are both in OTBI which is a suite of products. So you did not answer the question. The syntax of SQL is a bit different if doing a logical query in an analysis versus a publisher report data model in which you can have a dataset from an application database using traditional physical SQL or again a data set from an analysis / data source obiee using teh same logical SQL as in an analysis. But AND and OR in where clauses are the same so it makes no difference really in this case.

    Here is the reference

    logical sql (analysis) (report data model with dataset data source obiee/analysis)

    https://docs.oracle.com/middleware/12213/biee/BIESQ/toc.htm

    physical sql (report data model with dataset data source application database)

    https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/index.html

  • Ray Gupta
    Ray Gupta Rank 1 - Community Starter

    Thank you, Nathan. For the solution and the feedback on my response.