Once in a while you need to create a stacked bar graph grouped by some values. Unfortunately, OBIEE doesn't quite support this, because the stacked bars are distributed evenly among the X axis. However, its possible to get it done with some workarounds.
Imagine you would have a fact table structure where in one row you would have two current year measures and two last year measures and you would like to show them by months as in the following picture.
 |
| Desirable outcome |
OBIEE doesn't support this straight forward, but you can manage to pull it off with some tricks. I used a "UNION ALL" report in OBIEE answers to get it done and added three separate parts each part having the month column, the four measure columns and an additional dummy column to distinguish the three datasets.
The first part includes measures of the current year and SUM(0) in the column formula for the both LY columns.
 |
| First part of UNION ALL |
The second part includes SUM(0) for the current year measures and the actual LY measures.
 |
| Second part of UNION ALL |
The third part includes SUM(0) for all four measures. This set will be the set that will serve as the divisor in the graph.
 |
| Third part of UNION ALL |
Now hide the dummy column.
Next, arrange all the columns in the graph build dialogue in the following way.
 |
| Arranging columns in the graph |
And there you have it!
 |
| OBIEE grouped stacked bars |
This comment has been removed by the author.
ReplyDeleteHi, I followed the same steps mentioned above but my final graph is not getting displayed as shown above. Please help
ReplyDeleteAnd how do you imagine I could help you?
DeletePerfect i was breaking my head from past two weeks to create a multiple stack bar charts, thanks much for the post. Only issue i have here is month name is getting repeated in horizontal axis.
ReplyDeleteLike Jan-19 Jan-19, Feb-19 Feb-19. Is there a way to avoid ?
Sorry, nothing comes to my mind and I don't have OBIEE installation in my hands at the moment. When I used this solution, I actually just hid the titles on the X axis. You can still see everything on the mouseovers...
DeleteThanks much for replying..
DeleteYeah Hover works but my scenario is different, unfortunately I'm not able to paste the screenshot here :(
My requirement is for 4 stacked bars, each bar pulls one month value under x-axis as it is expected behavior but trying to club them to one instead. That gives different look.