Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.3K Intelligent Advisor
- 65 Insurance
- 536.7K On-Premises Infrastructure
- 138.4K Analytics Software
- 38.6K Application Development Software
- 5.8K Cloud Platform
- 109.5K Database Software
- 17.6K Enterprise Manager
- 8.8K Hardware
- 71.2K Infrastructure Software
- 105.3K Integration
- 41.6K Security Software
Filter without affecting Grand Total
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?
Best 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
-
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.
-
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?
-
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.
-
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?
-
Have you already looked at selection steps? Selection steps happen post-aggregation.
-
Yes, but in the calculated items or group, is not exist the Override Prompt option...
-
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.
-
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
-
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.
