Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 14 Oracle Analytics Lounge
- 211 Oracle Analytics News
- 42 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 78 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Conditionally Hide Pivot Table Columns by Section
I have data that looks like:
| Category | Item | Defect |
| AAA | 1 | Burnt |
| AAA | 2 | Burnt |
| BBB | 4 | Bad Weld |
| BBB | 2 | Broken Bolt |
| CCC | 1 | Burr |
| CCC | 3 | Broken Bolt |
| CCC | 5 | Broken Bolt |
I've created a sectioned Pivot Table:
Category AAA
| Defect | Item 1 | Item 2 | Item 3 | Item 4 | Item 5 | Total |
|---|---|---|---|---|---|---|
| Burnt | 1 | 1 | 2 |
Category BBB
| Defect | Item 1 | Item 2 | Item 3 | Item 4 | Item 5 | Total |
|---|---|---|---|---|---|---|
| Bad Weld | 1 | 1 | ||||
| Broken Bolt | 1 | 1 |
...
What I'm looking to do is suppress the columns that don't have data by section. So section AAA should only show columns for Item 1 and Item 2. Section BBB should only show columns for Item 2 and Item 4. And then section CCC should only show columns for item 3 and 5.
Is there any way to do that? Items and sections vary by day, so we can't use any solution that would make the columns static. The rows are dynamic by the section, so why can't I make the columns do the same?
Thanks,
Jessica
Answers
-
The answer for a PIVOT is a bit below, posted by @Andrew Fomin.
Hi Jessica,
Not really possible ...
Every table/pivot for each section value will have exactly the same number of columns as the others, so all your table/pivot will always have all the 5 items columns.
You can of course get into some JS / CSS if you really want but honestly it will cost you quite a lot of time and with a high risk of hiding the wrong column or not hiding it and something that will stop working with the next patch.
0 -
Thanks Gianni - Are there any workarounds that you can think of? Like a cursor to loop through the data and building a separate pivot table report for each Category AAA, BBB, etc? Or I guess going back to my Crystal Report days, a pivot table subreport?
Sorry if these are stupid questions. I'm a BI newbie.
Jessica
0 -
Honestly other than the JS/CSS unmaintainable and expensive hack I already wrote about there aren't simple workarounds.
If you use sections everything will have the same structure. To have different number of columns you would have to build individual analysis, so nothing dynamic at all. You can also of course just build HTML by hand completely but doing that what is the point of paying an OBIEE license? SQL could do the same ....
0 -
Thanks again Gianni!
It seems the Published Reporting - Report does what I expected with the columns, but not nearly as friendly to work with. I'm going to give this a shot.
Jessica
0 -
If understand what you want, here is the answer (screens from 12c, but works for 11g as well).
0 -
My bad, I missed the "pivot" point and started thinking at table
(so columns being real columns and not values of a single column)@Andrew Fomin. has it!
Switch the right answer to his own.
0 -
Thank you both a ton!
0
