Forum Stats

  • 3,851,471 Users
  • 2,263,984 Discussions
  • 7,904,720 Comments

Discussions

Filter without affecting Grand Total

Marco Zerbini
Marco Zerbini Member Posts: 7 Green Ribbon

Hi All,


Is there way to exclude rows in OBIEE Answers table report but without affecting grand total?

Example:

Following the start table:

Category Value1 Value2

Food 10 5

Technology 20 5

Automotive 30 5

Total 60 15


I set the filter Category not in "Automotive"

and I want look following table:

Category Value1 Value2

Food 10 5

Technology 20 5

Total 60 15


is it possible?

Tagged:

Best Answer

  • Gianni Ceresa
    Gianni Ceresa Managing Director | Oracle ACE Director Member Posts: 6,675 Blue Diamond
    Answer ✓

    You could try adding a calculated item or a new group entry.

    That's the only possible thing crossing my mind that could have a chance to get you closer to what you are after. But it will require to not have a filter in your criteria because that is applied before everything else and therefore will not even return the category you want to exclude.

    Try to play with that + selection steps to hide what you don't want to see, but really not sure it will work because that's going against the implicit logic of the tool.

    And also no idea how it will deal with your hierarchy, it could look a bit "dirty", but maybe still doing the job.

Answers

  • Gianni Ceresa
    Gianni Ceresa Managing Director | Oracle ACE Director Member Posts: 6,675 Blue Diamond

    The grand total is an automated aggregation of all your row, if you remove a row (by filtering) it will be impacted.

    Because what you try to do isn't natural (seeing some rows and one last called "grand total" but mismatching numbers is something that luckily the tool doesn't do automatically), you have to add a grand total row yourself with the correct formula to bypass the filter you applied.

    Some do it with a separate analysis without the filter, some other have a model letting them cheat and getting a total bypassing a filter.

  • Marco Zerbini
    Marco Zerbini Member Posts: 7 Green Ribbon

    Thank you Gianni,

    but I can't create separate analysis because I have to use the hierarchy objects.


    The solution, I can use combine criteria, so in the first criteria I use the filter, in the second criteria I dont' use the filter (so I have the total).... But in this way, Hierarchy object and multi criteria is not compatible... :(


    Some solution?

  • Gianni Ceresa
    Gianni Ceresa Managing Director | Oracle ACE Director Member Posts: 6,675 Blue Diamond
    Answer ✓

    You could try adding a calculated item or a new group entry.

    That's the only possible thing crossing my mind that could have a chance to get you closer to what you are after. But it will require to not have a filter in your criteria because that is applied before everything else and therefore will not even return the category you want to exclude.

    Try to play with that + selection steps to hide what you don't want to see, but really not sure it will work because that's going against the implicit logic of the tool.

    And also no idea how it will deal with your hierarchy, it could look a bit "dirty", but maybe still doing the job.

  • Marco Zerbini
    Marco Zerbini Member Posts: 7 Green Ribbon
    edited Jan 26, 2022 2:19PM

    Great!

    I resolved using the selection steps and Calculated items or group.

    But I don't remember functionality the "Override Prompt" option, so:

    1- Create the Group Item contained all values (Total field)

    2- Keep only values that I select on Prompt, using the "Override Prompt" option

    Now, I have only a issue, the values selected in point 1 is static, if I will add a new value, in the Total field is not consider.... So, or I add manually this value (is better create a agent that send a mail when intercept new value, or???

    Any idea?

  • Christian Berg-0racle
    Christian Berg-0racle Everything Analytics And Data Member Posts: 9,671 Gold Crown

    Have you already looked at selection steps? Selection steps happen post-aggregation.

  • Marco Zerbini
    Marco Zerbini Member Posts: 7 Green Ribbon

    Yes, but in the calculated items or group, is not exist the Override Prompt option...

  • Christian Berg-0racle
    Christian Berg-0racle Everything Analytics And Data Member Posts: 9,671 Gold Crown

    Yes, those would need to exist in the data stream as in terms of execution precedence they sit next to selection steps rather than before them.

  • Marco Zerbini
    Marco Zerbini Member Posts: 7 Green Ribbon

    I found this in Oracle Doc:

    "Instead of specifying a named item for columns, you can specify $n or $-n. Here, n is an integer that indicates the item's row position. If you specify $n, then the measure is taken from the nth row. If you specify $-n, then the measure is taken from the nth to the last row."

    So, I create calculated items instead of group with this formula:

    $1+$2+..... $100


    So, If I have 2 values, it is work, after if I will add values, and I will have 99 values it will work....maybe I resolved this issue. I testing all...


    I will let you know

  • Christian Berg-0racle
    Christian Berg-0racle Everything Analytics And Data Member Posts: 9,671 Gold Crown

    Yes the dollar notation exists, but beware: it's positional and can't be tied to any other logic. So if you move around columns because you're working on the analysis your results will change.