Creating an HR Analysis using column filters, toggling to convert "OR" to "AND" doesn't work — Oracle Analytics

Oracle Analytics Cloud and Server

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

Creating an HR Analysis using column filters, toggling to convert "OR" to "AND" doesn't work

Accepted answer
111
Views
9
Comments

Using the column filter interface, I need to set an analysis that may be as simple as...

Meets condition A

AND meets condition B

AND meets condition C

OR meets condition D

AND meets condition E

When I first emplace the filter for condition D, it starts as an "AND" statement. When I click on the "AND" to convert it to "OR", the line for condition D gets demoted and grouped with condition C. As such, it seems the interface will not permit creation of multiple independent grouped conditions like the literature indicates can be done.

We've just migrated our LMS over to Oracle Learn, so maybe I'm missing something? Any help/guidance is most appreciated because we have many complex training assignment audiences that will be unnecessarily "heavy" to create and maintain in SQL. Thanks in advance.

Tagged:

Best Answer

  • (a piece disappeared from my post, and the forum is weirdly configured to only allow editing for 1h, because obviously one spot something needed an edit in the next 60 minutes only...)

    When I say: Duplicate this block, remove the conditions D and E from the first, and the conditions A, B and C from the second.

    You then must place these 2 sawx:expr with the AND operator inside a OR operator, like the last piece of XML posted shows.

Answers

  • Ambient
    Ambient Rank 5 - Community Champion

    Hi Scott,

    So you want to the logic to be:

    (Meets condition A

    AND meets condition B

    AND meets condition C)

    OR

    (meets condition D

    AND meets condition E)

  • Scott Manning
    Scott Manning Rank 2 - Community Beginner

    Yes, that's exactly the premise. The challenge is that while the premise stands, I may have 20 or 25 of those grouped sets of conditions, all with as many as 20-40 individual parameters defined within them.

    Thanks for your thoughts!

  • Scott Manning
    Scott Manning Rank 2 - Community Beginner

    Here's a screen shot of how this plays out...

    image.png

    When I click on the "AND" to create the new "OR" condition and establish a new grouped set of conditions, it ends up like this...

    image.png


  • Scott,

    The UI is a bit "strange", because it guess what you are trying to do and 99% of the time it just does something else.

    In your case, because the filter you look for is quite simple, (A and B and C) or (D and E) , Just do it in the XML of your analysis.

    image.png

    Add your 5 conditions all at once to your analysis, and then go in the "advanced" tab and edit the XML to make your condition.

    The 5 filters will be contained inside a sawx:expr block with the operator "and":

    <sawx:expr xsi:type="sawx:logical" op="and">
    ...
    </sawx:expr>
    

    Duplicate this block, remove the conditions D and E from the first, and the conditions A, B and C from the second.

    <sawx:expr xsi:type="sawx:logical" op="or">
       <sawx:expr xsi:type="sawx:logical" op="and">
          <!-- A -->
          <!-- D -->
          <!-- C -->
       </sawx:expr>
       <sawx:expr xsi:type="sawx:logical" op="and">
          <!-- D -->
          <!-- E-->
       </sawx:expr>
    </sawx:expr>
    

    For this kind of work, 2 minutes editing the XML is a lot easier than trying to add the filters in the right order after your spent 30 minutes studying how the tool reacts at every single click...

  • Scott Manning
    Scott Manning Rank 2 - Community Beginner

    Gianni...

    Thank you SO much for the concise guidance. It's most definitely not the answer I was hoping for but I've gotten it to work in my simple example so I'm sure it can be taken to scale.

    My team is having to move from SumTotal to Oracle. In SumTotal, we'd become very accustomed to "lay person friendly" filters and having the ability to readily put complex layered logic into place through a "click based" UI. Getting put into an environment where everything requires at least a degree of "semi coding" is going to be a considerable climb.

    I'm grateful for this community and very much appreciate the kind and quick responses I received.

  • Gianni Ceresa
    edited Sep 20, 2023 5:09PM

    It's doable via the GUI, but it does look "less clean" and it's a bit less intuitive to achieve:

    image.png image.png

    These are just all equivalent from a functional point of view.

    Click on the first 2 (on top) "and" to make them "or", the same on the last (bottom) "and". Then click on the last "and". Finally click on the 3 "or" that should become a "and" (the first 2 and the last one) to change them.

    And you get it, but it just make you think twice when you are doing the clicking and it also look less intuitive: A and B and C == (A and B) and C == A and (B and C), but seeing A and B and C looks more natural than the other 2.

  • Scott Manning
    Scott Manning Rank 2 - Community Beginner

    Gianni...

    Thank you for the additional effort of illustrating how Oracle's brain works when handling filters via the GUI. You're so right in that it's not at all intuitive. It IS however, something my team can create work instructions around, set up some mock-ups for illustrative purposes and manage a work task against.

    I've been able to get the GUI filters to work in a manner that is both predictable and replicable. Thanks again!!

  • Ambient
    Ambient Rank 5 - Community Champion

    @Scott Manning

    Maybe create an IdeaLab/Enhancement request for the addition of brackets as a operators in the filter tool.

    This would make defining complex, nested logic simpler, the resulting filter easier to understand by others and probably make the indentation logic simpler to boot.