Filter without affecting Grand Total

Marco Zerbini

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?

Gianni Ceresa

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

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

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

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

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

Marco Zerbini

https://community.oracle.com/tech/apps-infra/discussion/comment/16823373#Comment_16823373

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

Christian Berg-0racle

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

https://community.oracle.com/tech/apps-infra/discussion/comment/16823453#Comment_16823453

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

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.